I have been trying (with little success) to have a google cloud function be triggered via an http request from a google sheet (google apps script) and it seemingly won't work. A few important things:
- The function should only run if the user comes from my organization
- The user should not have to be invited to the GCP project
I know this can be done very easily in google colabs and Python. The following script will let a user in my organization who is not in the GCP project trigger the cloud function:
import requests
import google.auth
from google.auth.transport.requests import Request
from google.colab import auth
credentials, project_id = google.auth.default()
request = Request()
credentials.refresh(request=request)
GCF_URL = ''https://project_location-project_id.cloudfunctions.net/name-of-your-funciton''
resp = requests.get(GCF_URL, headers={'Authorization': f'Bearer {credentials.id_token}'})
This will work and trigger the cloud function for any users inside my organization but does not work for my personal email for example.
Now, I would like to replicate this behvaiour inside a google apps script such that an end user with access to that sheet can trigger the cloud function as long as they are a member of my organization.
I have tried some things I have seen online such as this example:
function callExternalUrl() {
var url = 'https://project_location-project_id.cloudfunctions.net/name-of-your-funciton';
var oauthToken = ScriptApp.getOAuthToken(); // get the user who's logged into Google Sheet's OAuth token
const data = {
oauthToken, // 1. add the oauth token to the payload
activeUser: param.user // 2. this here is important as it adds the userinfo.email scope to the token
// any other data you need to send to the Cloud Function can be added here
};
var options = {
'method' : 'get', // or post, depending on how you set up your Cloud Function
'contentType': 'application/json',
// Convert the JavaScript object to a JSON string.
'payload' : JSON.stringify(data)
};
const response = UrlFetchApp.fetch(url, options);
Logger.log('Response Code: ' + response.getResponseCode());
}
This gives a 403 error but if I change it up so that it gives the OAuth on the correct format like this:
function callExternalUrl() {
var url = 'https://project_location-project_id.cloudfunctions.net/name-of-your-funciton';
var oauthToken = ScriptApp.getOAuthToken(); // get the user who's logged into Google Sheet's OAuth token
var response = UrlFetchApp.fetch(url, {
headers: {
Authorization: 'Bearer ' + oauthToken
}
});
// const response = UrlFetchApp.fetch(url, options);
Logger.log('Response Code: ' + response.getResponseCode());
}
I get a 401 (i.e. the authorization failed). Now, it seems that I simply have to get the correct authentication from the users to send in this request for it to work. I have seen this github repo that focuses on getting OAuth2 from google apps scripts (https://github.com/gsuitedevs/apps-script-oauth2), but I can't seem to get that to work either, it would have to be adapted to cloud in some way I am unaware of.
I have read
Securely calling a Google Cloud Function via a Google Apps Script
which is very similar but it did not seem to get to the root of the problem, any input on how to make this process possible?
