Apps Script connecting to GCS

March 29, 2023 - 3 minutes read - 446 words

I’m building a Google Sheet that interacts with Google Cloud Storage (GCS) objects using Apps Script.

I Googled but found few examples of such integrations beyond out-of-band solutions (e.g. Python solutions) that interact with Google services and program Google Sheets using its library.

In my case, I’m going to bind a Google Sheet to a specific Google Cloud project and my Google (User) account has owner access to the Google Cloud Storage bucket and its objects.

For any Apps Script code to authenticate to a Google Cloud service, you’ll need to:

Configure OAuth consent screen
Use an OAuth client ID

NOTE When you associate an Apps Script with a Google Cloud project, an OAuth Client ID called Apps Script is automatically creatd in the project’s credentials.

While you’re updating the Apps Scripts Project Setting, enable|check the checkbox

Show “appsscript.json” manifest file in editor

You will need to update this manifest and add an oauthScopes key with the appropriate values. You can lookup Google’s OAuth 2.0 Scopes for Google APIs. You’ll need to add auth/script.external_request (which curiously is not listed). In my case, to use Google Cloud Storage, the appsscript.json manifest:

{
  "timeZone": "America/Los_Angeles",
  "exceptionLogging": "STACKDRIVER",
  "runtimeVersion": "V8",
  "dependencies": {},
  "oauthScopes": [
    "https://www.googleapis.com/auth/script.external_request",
    "https://www.googleapis.com/auth/devstorage.read_write"
  ]
}

NOTE I think it’s not possible to use Google API Client Library for Javascript aka “gapi” from App Script.

I began by adding Google’s apps-script-oauth2 library to the Apps Script but learned that it is not necessary.

Instead, it’s possible to use ScriptApp.getOauthToken:

const authorization = (() => {
    const token = ScriptApp.getOAuthToken();
    return `Bearer ${token}`;
})();

Google’s APIs Explorer is excellent and Google Cloud Storage JSON API includes the services methods. We’re going to use Objects:list.

To use it we need:

GET https://storage.googleapis.com/storage/v1/b/{bucket}/o

Apps Script includes UrlFetchApp and it is described in the Apps Script documentation External APIs.

const endpoint = "https://storage.googleapis.com/storage/v1";
const bucket = "[YOUR-BUCKET]";
const url = `${endpoint}/b/${bucket}/o`;

const response = UrlFetchApp.fetch(url,{
  "method": "GET",
  "headers":{
    "Authorization": authorization,
    "Accept": "application/json",
  },
  "contentType": "application/json",
  "muteHttpExceptions": true,
});

const code = response.getResponseCode();
const text = response.getContentText();

switch (code) {
  case 200:
    // Sucess ...
    break;
  default:
    // Otherwise ...
}

In the above response is of type HTTPResponse. If muteHttpException is false (default), you will want to try {...} catch (err) {...} the call to URLFetchApp.fetch. With muteHttpException: true, we can (as shown) switch on response.GetResponseCode().

The next step is to use Objects:get to get Cloud Storage object content and add this to a sheet.

To use it we need:

GET https://storage.googleapis.com/storage/v1/b/{bucket}/o/{object}

But note, as-is the method returns the object’s metdata. To GET the object’s content, you must add a query parameter alt with the value mediaL

GET https://storage.googleapis.com/storage/v1/b/{bucket}/o/{object}?alt=media

That’s all!