Trigger Google Apps Script from Coda

Hi @Hannah_Strong - As @Scott_Collier-Weir alluded to Apps Script has a REST API you can use to execute a script remotely:

However you can’t easily create a generic Pack for this, since the execute endpoint requires that you authenticate with:

  • The same Google Cloud project used by the script.
  • The unique set of scopes that the script requires.

The OAuth2 support in Packs doesn’t allow users to swap in different a client ID in secret or a dynamic set of scopes.

You can however build a one-off Pack that works with that exact Apps Script project, that meets the criteria above and calls the execute endpoint. It’s not a complicated API, it’s just that Google OAuth is a bit involved.

Scott’s idea of turning your Apps Script into a web app (using goGet or doPost) could work. The only limitation there is that you’d have to deploy the web app to run as you, meaning that all the spreadsheets would be created under a your account. You can’t pass individual authorization in a web app call, that’s what the previously mentioned execute API is for.

I think you would still need to create a Pack for that use case though, to send the request to the web app URL, but it would be a much simpler Pack. (I’m seeing if I can add this feature to my existing Apps Script Pack)

A last and even simpler idea may be to publish the Apps Script as a web app (either running as you or running as the end user) and have a Coda button use the OpenWindow() formula to open it in the browser. The user could sign in if required, and then it could generate the spreadsheet and perhaps even display a link to it. It’s not quite as seamless as it all being in the Coda doc, but I think it could be workable.