How to trigger a Google Cloud Function using a Google Sheets Trigger?

1
votes

I am trying to trigger a Google cloud function using an HTTP request from a Google Apps Script connected to Google Sheets.

The cloud function (tested with a dummy request on GCP, this works) essentially gets the latest entry of the sheet as submitted by users using the Appsheet front-end. I would like it to be triggered whenever a new submission occurs. As such, I am using a onChange(e) trigger to send the request. This is the code I have so far in the appscript.

function onChange(e) {
  Logger.log('called');
  var url = "https://MY_REGION-MY_PROJECT.cloudfunctions.net/MY_CLOUD_FUNCTION";
  const token = ScriptApp.getIdentityToken();
  const options = {
    headers: {'Authorization': 'Bearer ' + token}
  }
  const response = UrlFetchApp.fetch(url, options);
  Logger.log('Response Code: ' + response.getResponseCode());
}

The stackdriver logs give me the following error when a new Appsheet submission is made (thus calling onChange(e)):

Exception: Request failed for https://MY_REGION-MY_PROJECT.cloudfunctions.net returned code 401. Truncated server response: 
<html><head>
<meta http-equiv="content-type" content="text/html;charset=utf-8">
<title>401 Unauthorized</title>
</head>
<body text=#000000 bgcolor... (use muteHttpExceptions option to examine full response)
    at onChange(Code:9:32)

I have looked at and followed the solutions and comments here and here, but they didn't help me much; that is, I have made sure that the app script is connected to my project, re-deployed the cloud function, and added openid to the scope to the manifest.

Given that it is still not working, I would like some guidance on this. Am I still missing something here?

3
httpgoogle-apps-scriptgoogle-sheetsgoogle-cloud-platformtriggers

3 Answers

2
votes

I found a temporary fix; hard code the authentication token into the App Script. The authentication token is given by running gcloud auth print-identity-token in the Google Cloud Shell script. Copy the output and replace the corresponding line in onChange(e) method.

function onChange(e) {
  Logger.log('called');
  var url = "https://MY_REGION-MY_PROJECT.cloudfunctions.net/MY_CLOUD_FUNCTION";
  const token = ScriptApp.getIdentityToken(); //<-- Replace this line!
  const options = {
    headers: {'Authorization': 'Bearer ' + token}
  }
  const response = UrlFetchApp.fetch(url, options);
  Logger.log('Response Code: ' + response.getResponseCode());
}

There seems to be a bug in the API which doesn't get value of the token when the ScriptApp.getIdentityToken() is called. I've brought this up in the issue tracker. Hopefully, it is fixed soon.

Note that hardcoding the authentication token may only be a temporary solution as that token can change. Be sure to regularly update it in the App Script if you want to continue to use this method.

2
votes

Pivoted from Bhairav's answer, we don't have to manually copy and paste the identity token. Here is how we can call the getIdentityToken() function

You should be able to call ScriptApp.getIdentityToken(); without any problem

0
votes

A few thoughts:

GAS Triggers Don't Listen for Programmatic Changes

Neither onChange, nor onEdit triggers are invoked by programmatic access to the Sheet. (Reference). I've tried to build stuff like this in the past, and been thwarted.

How I've Handled This

I've handled this by creating a dedicated tab where other apps, which have write access to the sheet, programmatically add stringified objects to a cell. Then, I have a GAS function that, each minute, looks for new activities (the stringified objects), then does stuff based on what's inside the parsed activity object. To use this approach, you'll need to:

  • build the "activities handling" function into your Script
  • set up a time-based trigger (I do it once a minute) to look for those activities, then handle them

It's not a perfect solution, but it works well for me.