[Feature] Google Drive Integration

GreenFlux commented 3 years ago

Summary

Requesting an additional method for Google Sheets API to fetch an array of all files (and associated meta-data) for a given folder Id, instead of rows for a given sheet Id. The method should return an array of JSON objects representing all files in the given folder (matching optional filter conditions).

Motivation

Provide developers the ability to monitor a folder as a data-source, with each row of data being driven by the folder's current list of files and meta-data, including EXIF/Geo-location, dimensions, file size, file type, etc.

Additional Context

Use Cases:

users bulk-upload photos to Google Folder => table populates with multiple rows representing "inspection_photo", "survey_location", "timestamp", "filename", etc
User captures photo with auto-upload to Google folder already configured. Taking a photo will create a row that captures the user's location, along with the timestamp and photo as a row of data.
Other script/integration saves email attachments to a folder: if (Label="X") {saveAttachments ("folderId")} => view as table in Appsmith
Google form with file upload => Save files to folder and monitor as data-source

Similar feature on AppSheet:

https://help.appsheet.com/en/articles/4865398-folder-data-source-the-essentials

Nikhil-Nandagopal commented 3 years ago

@GreenFlux thank you for the feature request! This seems unrelated to Google Sheets however and I think a separate Google Drive Integration would be a better fit here?

GreenFlux commented 3 years ago

@GreenFlux thank you for the feature request! This seems unrelated to Google Sheets however and I think a separate Google Drive Integration would be a better fit here?

Agreed. It is a separate Google API endpoint so it's not really a new method for the Sheets API.

As a separate API, it could have a whole list of other methods like fetch files, create file, update file, delete file, etc.

mattbOSA commented 2 years ago

Hello, just sharing our use case for this feature.

We have all of our non-sensitive documents hosted on google drive, and we are using appsmith to build an HR system to track our applicants path from being an applicant to being an employee and through termination (in the event that happens); we also are trying to use it to track other employees properties/stats (such as expiration of certifications, date of birth, progress reports, emergency contacts, etc).

In order to more accurately track employee properties and statuses, I want to link every status/property of our employees to the supporting documentation that is hosted on google drive. For example, if a certification is expiring on a certain date, I want to be able to click on that record and verify the expiration date by viewing the actual document (which is saved on google drive). And this brings me back full circle to the details of our similar feature request.

We need a method of doing the following:

Searching for, browsing for, and finally selecting a file on google drive and having that file's document's ID or document url returned to appsmith
Uploading a file to google drive, optionally over-writing the file's original name, choosing where the file will be stored, and finally having that uploaded file's document ID or document URL returned to appsmith

Here is a short gif explaining our needs and use case. https://drive.google.com/file/d/1u8VBBaXIbcP9Tup4qRZVscMrIN3m5-_R/view?usp=sharing

Here is google's API which should make this easily doable: https://developers.google.com/drive/picker/guides/overview

On a side note, I tried starting to build my own interface to link to google drive by following googles own API code example (see here https://developers.google.com/drive/picker/guides/sample) but, the interface relies on the IFRAME widget, which is not properly rendering the html code below:

<!DOCTYPE html>
<html>
<head>
  <title>Picker API Quickstart</title>
  <meta charset="utf-8" />
</head>
<body>
<p>Picker API API Quickstart</p>

<!--Add buttons to initiate auth sequence and sign out-->
<button id="authorize_button" onclick="handleAuthClick()">Authorize</button>
<button id="signout_button" onclick="handleSignoutClick()">Sign Out</button>

<pre id="content" style="white-space: pre-wrap;"></pre>

<script type="text/javascript">
  /* exported gapiLoaded */
  /* exported gisLoaded */
  /* exported handleAuthClick */
  /* exported handleSignoutClick */

  // Authorization scopes required by the API; multiple scopes can be
  // included, separated by spaces.
  const SCOPES = 'https://www.googleapis.com/auth/drive.metadata.readonly';

  // TODO(developer): Set to client ID and API key from the Developer Console
  const CLIENT_ID = '<YOUR_CLIENT_ID>';
  const API_KEY = '<YOUR_API_KEY>';

  // TODO(developer): Replace with your own project number from console.developers.google.com.
  const APP_ID = '<YOUR_APP_ID>';

  let tokenClient;
  let accessToken = null;
  let pickerInited = false;
  let gisInited = false;

  document.getElementById('authorize_button').style.visibility = 'hidden';
  document.getElementById('signout_button').style.visibility = 'hidden';

  /**
   * Callback after api.js is loaded.
   */
  function gapiLoaded() {
    gapi.load('picker', intializePicker);
  }

  /**
   * Callback after the API client is loaded. Loads the
   * discovery doc to initialize the API.
   */
  function intializePicker() {
    pickerInited = true;
    maybeEnableButtons();
  }

  /**
   * Callback after Google Identity Services are loaded.
   */
  function gisLoaded() {
    tokenClient = google.accounts.oauth2.initTokenClient({
      client_id: CLIENT_ID,
      scope: SCOPES,
      callback: '', // defined later
    });
    gisInited = true;
    maybeEnableButtons();
  }

  /**
   * Enables user interaction after all libraries are loaded.
   */
  function maybeEnableButtons() {
    if (pickerInited && gisInited) {
      document.getElementById('authorize_button').style.visibility = 'visible';
    }
  }

  /**
   *  Sign in the user upon button click.
   */
  function handleAuthClick() {
    tokenClient.callback = async (response) => {
      if (response.error !== undefined) {
        throw (response);
      }
      accessToken = response.access_token;
      document.getElementById('signout_button').style.visibility = 'visible';
      document.getElementById('authorize_button').innerText = 'Refresh';
      await createPicker();
    };

    if (accessToken === null) {
      // Prompt the user to select a Google Account and ask for consent to share their data
      // when establishing a new session.
      tokenClient.requestAccessToken({prompt: 'consent'});
    } else {
      // Skip display of account chooser and consent dialog for an existing session.
      tokenClient.requestAccessToken({prompt: ''});
    }
  }

  /**
   *  Sign out the user upon button click.
   */
  function handleSignoutClick() {
    if (accessToken) {
      accessToken = null;
      google.accounts.oauth2.revoke(accessToken);
      document.getElementById('content').innerText = '';
      document.getElementById('authorize_button').innerText = 'Authorize';
      document.getElementById('signout_button').style.visibility = 'hidden';
    }
  }

  /**
   *  Create and render a Picker object for searching images.
   */
  function createPicker() {
    const view = new google.picker.View(google.picker.ViewId.DOCS);
    view.setMimeTypes('image/png,image/jpeg,image/jpg');
    const picker = new google.picker.PickerBuilder()
        .enableFeature(google.picker.Feature.NAV_HIDDEN)
        .enableFeature(google.picker.Feature.MULTISELECT_ENABLED)
        .setDeveloperKey(API_KEY)
        .setAppId(APP_ID)
        .setOAuthToken(accessToken)
        .addView(view)
        .addView(new google.picker.DocsUploadView())
        .setCallback(pickerCallback)
        .build();
    picker.setVisible(true);
  }

  /**
   * Displays the file details of the user's selection.
   * @param {object} data - Containers the user selection from the picker
   */
  function pickerCallback(data) {
    if (data.action === google.picker.Action.PICKED) {
      document.getElementById('content').innerText = JSON.stringify(data, null, 2);
    }
  }
</script>
<script async defer src="https://apis.google.com/js/api.js" onload="gapiLoaded()"></script>
<script async defer src="https://accounts.google.com/gsi/client" onload="gisLoaded()"></script>
</body>
</html>

appsmithorg / appsmith