I’m looking for a way to generate a Google Sheet from Coda data with the push of a button inside Coda itself. I understand how to set up a Google Apps Script project that creates a Google Sheet from Coda data using the Coda API, but the intended users are not tech savvy so I need them to be able to run this script or otherwise trigger the creation of the Google Sheet from within Coda itself. Any ideas on how to go about this?
1 Like
It’s possible. I’ve looked at doing it a couple times and every time thought “nahhhhh not worth the huge effort and lift”
Not as straight forward as a road as one would hope
@Eric_Koleda can tell you more
WAIT! I just thought of something. While you can trigger an apps script via a post call (which you could build a button for)
You could more easily spin up a doPost() in your apps script and deploy it to act as an incoming webhook catcher which THEN itself runs the apps script code. . .
Then you wouldn’t even need to build a pack. See any issues with that @Eric_Koleda ??
1 Like
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.
Thank you @Scott_Collier-Weir and @Eric_Koleda ! I was hoping there was an easier answer that would make it faster to deal with OAuth2 since I don’t have a ton of experience with it, but it sounds like there isn’t. I’ll dive into this more next week when I have fewer things on my plate and can really think about the best way to proceed.
Ya, Google OAuth2 is a real journey unfortunately. I did just add a TriggerWebApp action to my Apps Script Pack, if you can go that route.
2 Likes
I do this in many of my current Coda solutions. You can easily bypass oAuth in the Google webhook listener by deploying the service for anonymous access. Use a special token passed between Coda and the webhook listener and you are free to send all sorts of data and instructions to Google apps script that has pervasive access to perform pretty much anything as “you” or whicher account the doPost() is running within.
Hi @Bill_French !
I do the same as you but it still asks me to renew oAuth manually inside Apps scripts every week (not a Google Workspace account and unverified app). The script do use scopes accessing user data.
Can I do something except trying to switch to API executable deployment and submit my pack as a client for app verification ? I’m a bit afraid of this process 
Or maybe subscribing to a Google Workspace account for just this one account the script will be used with…
Thank you for any insight
It’s one option; I do not do this. My webhook systems are simple endpoints.
I typically build these types of services in Workspace accounts but I also have some in my everyday gmail account. I encounter no such re-authorization tasks to sustain these services. I have one that I use with Airtable that has been running reliably since 2019 - not a single change or any maintenance required.
Thank you for your explanation.
Must be one of my scopes… If you have time, could you list the scopes used on your everyday gmail account that do not trigger re-authorization ?
Here are mine:
https://www.googleapis.com/auth/script.send_mail
https://www.googleapis.com/auth/spreadsheets
https://www.googleapis.com/auth/drive
https://www.googleapis.com/auth/script.external_request
https://www.googleapis.com/auth/drive.activity.readonly
1 Like
Yeah - this one has only two scopes and it’s in my regular gmail account running without re-authentication since 2019:
The web service is configured like this - nothing fancy. Just works.
2 Likes
Thank you Bill, this gives me hope I might be able to split the script in two to at least allow the most critical part to not trigger reauth
How do I call doPost()? I have deployed my solution when I create a button with a URL I get this error “Script function not found: doGet”
On the button, there is an optional parameter called METHOD
Make sure to pass that a string POST value on Codas side.
Aside from that, ensure the app script is deployed as a web-app that has its setting as accessible to everyone. Let me know if you need any further help!