Trigger PubSub messages on Google Sheets save actions

1
votes

I am trying to automate a Google Sheet import as soon as someone has committed their changes to Google Sheet's version control (and not just edited any cell like the onEdit event seems to trigger, I need it committed).

While polling is an option, I'd rather really have Google Sheets send out a message to PubSub. Now PubSub requires the authentication JSON and such and I haven't seen any integration with Google Sheets that integrates this concept, which surprised me.

I searched on the internet for triggers in Google Sheet and some way to automate code to connect to external resources. Apparently, the Google Drive Push Notification API seems to be the way to go. I'd really like to keep everything in my Google Cloud space so I went for a Google Cloud Function with an HTTP(S) endpoint. I already started working out the Function and PubSub channel when I went back to the documentation to read up on how to send a call to the HTTP endpoint.

Bad luck. Seems you need to register the domain to prove you own it and wishing I could, I can not prove that I own cloudfunctions.net. So there went my plan.

It seems very not-Google like to not integrate its Cloud SDK on Google Sheets triggers since they do offer a Sheets API using Cloud Service Accounts.

So my conclusion is I have two options:

1) I am able to send an HTTP callback on a onEdit() function but only if it's my own domain and I seem to require to set up an environment just for that.

2) I would have to poll the last version of the Google Sheets commit compared to the latest version to trigger it myself.

Am I overlooking something very simple or are these my only options?

1
eventsgoogle-apps-scripttriggersgoogle-cloud-functionsgoogle-cloud-pubsub

1 Answers

1
votes

Cloud functions count as an AppEngine Standard Endpoint as described here and here, and so do not require domain verification. You can use a cloud function using a Cloud Pub/Sub trigger freely- you don't even need to explicitly set up a subscription.

Edit: I didn't understand the OPs question correctly, they want to prove their ownership of a cloud function to the Google Drive Push Notification API, not Cloud Pub/Sub push. This should be possible through HTML tag verification as described here. In whatever framework you are using for your web server, you should be able to set the appropriate HTML meta tag on the response.