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.