Google recently announced a new Google Apps Script Service called Execution API. The pitch:
Have you ever wanted a server API that modifies cells in a Google Sheet, to execute a Google Apps Script app from outside of Google Apps, or a way to use Apps Script as an API platform? Today, we’re excited to announce you can do all that and more with the Google Apps Script Execution API. The Execution API allows developers to execute scripts from any client (browser, server, mobile, or any device). You provide the authorization, and the Execution API will run your script.
Getting data into Google Sheets from external sources has been a long held interest of mine. Whilst the Execution API can do much more than just this in this post I want to focus on how you can setup the Execution API to create an authenticated pipeline. Before I go into this it’s worth reminding ourselves of the other ways you can get data into Google Sheets, in particular, publishing a script as a web app.
Publishing a GET/POST endpoint
By publishing your script as a web app you expose an endpoint that can handle POST and GET requests. I’ve a couple of posts that illustrate how you can use this, the most recent being Google Sheets as a Database – INSERT with Apps Script using POST/GET methods (with ajax example). Publishing a GET/POST endpoint does have several limitations, in particular, you need to publish the app to execute as you and allow access to anyone even anonymously. This doesn’t mean you can’t create full featured applications as you can use the HTMLService which will also expand the number of ways the app can run including as the user accessing the app. A limitation of publishing a standalone app in this way, which catches out a lot of new Apps Scripters, is you cannot <iframe> Apps Script web apps in other sites other than Google Sites.
This is where Execution API comes in. Lets consider the following scenario:
Company X needs to integrate a form on their CMS that allows authorised users to submit data to a Google Sheet and see data entered by other authorised users. As part of the data entry the email address of who is submitting the data needs to be included.
The key word in this scenario is ‘authorised’, essentially we want to enable authenticated access to a Google Sheets from a 3rd party source.
Getting your head around the Execution API documentation
Like all good developers lets start with the official documentation. Looking at the ‘Using the Execution API’ there are a couple of things to note:
To use the API, you must supply a valid OAuth token that covers all the scopes used by the script (not just the ones used by the called function).
The link to ‘valid OAuth token’ is just a list of the OAuth scopes, so how do we get the token? Moving on to the ‘General procedure’ it says:
5. In the application code, generate an OAuth access token for the API call. This is not a token the API itself uses, but rather one the script requires. The token must be built using the Client ID and the scopes from the script (in the editor, under File > Project properties > Scopes). This also requires prompting the user to authorize the script. Note that the Google client libraries, while not strictly necessary, can greatly assist in handling OAuth for the application.
This guide is based on documentation published by Google [source 1, source 2 & source 3] and licensed under the Creative Commons Attribution 3.0 License, and code samples are licensed under the Apache 2.0 License.
Here is an example application which which solves our example use case. After the page loads the user needs to authorize access:
Clicking the Authorize button launches Google Authentication:
The user is prompted if they would like the application to have access to their services defined in the target script scope. This is a one-time-only prompt and to revoke access the user needs to remove from their account dashboard under Apps connected to your account.
On authorization the data is rendered from our Google Sheet in the application window and the user can submit new data:
Notes and limitation
The key thing to note here is this app is running based on the permissions of the user. In my example to allow anyone to enter a comment the source Google Sheet has to be shared to everyone with edit access but not publically searchable. The link to the Google Sheet is never exposed (security by obscurity). In a enterprise setting you can limit who the sheet is shared with to prevent unauthorised access. Here is how this example has been put together:
Create a Target Project for the Apps Script Execution API
Before you can begin any of the quickstarts, you will need a target Apps Script for the API to call. Follow these steps to create a simple script and enable it for API access.
Step 1: Create the script
- Open this Google Sheet and File > Make a copy and copy your new Sheet document id from your browser address bar:
- Share the Google Sheet with users you would like to be able to add data.
- Open the Apps Script editor and create a blank project.
- Click on the “Untitled Project” title to rename the script. Name the script “Apps Authenticated INSERT with Apps Script using Execution API” and click OK.
- Replace the contents of the
Code.gsfile with the following code replacing <INSERT_SPREADSHEET_DOC_ID> with the id of your new sheet from step 1:
- Save the project by selecting File > Save.
Step 2: Publish the script for execution and enable the Google Apps Script Execution API
- In the code editor, select Publish > Deploy as API executable.
- In the dialog that opens, leave the Version as “New” and enter “Target-v1” into the text box. Click Deploy.
- Open your target Apps Script in the editor and select Resources > Developers Console Project.
- In the dialog that opens, click on the blue link (that starts with “Apps Authenticated INSERT with Apps Script using Execution API”) at the top to open the console project associated with your script.
- In the sidebar on the left, select APIs & auth, then APIs. In the search bar under the API library tab, enter “Google Apps Script Execution API”. Click the same name in the list that appears. In the new tab that opens, click Enable API.
- Back on the Credentials tab, click the Add credentials button and select OAuth 2.0 client ID.
- Select the application type Web application.
https://mashe.hawksey.info. You can leave the Authorized redirect URIs field blank.
- Click the Create button.
- Take note of the client ID in the resulting dialog. You will need it in a later step.
Step 3: Set up the sample
Create a file named
quickstart.html and copy in the following code:
Replace the placeholder
<ENTER_YOUR_CLIENT_ID> in the copied code with the client ID you generated in Step 1. Be sure to also replace the placeholder
<ENTER_YOUR_SCRIPT_ID_HERE> with the script ID of your target script. Upload the quickstart.html to your web server (if you don’t have one you can run a localhost).
Step 4: Run the sample
Navigate to the page with your quickstart.html example.
The first time you run the sample, it will prompt you to authorize access:
- Click the Authorize button to open the authorization window.
If you are not already logged into your Google account, you will be prompted to log in. If you are logged into multiple Google accounts, you will be asked to select one account to use for the authorization.
- Click the Accept button.
After the initial user authorization, calls to
gapi.auth.authorize that use
immediate:true mode will obtain an auth token without user interaction.
Join the conversation
Bellave S Jayaram
Yes should be possible. The important thing to remember is Execution API needs to authenticate against at least one scope so to see the output the user needs to authenticate first. You can of course use a similar trick and make the source sheet accessible to anyone with link (in your scenario view only should work) so once they login the result can render.
Bellave S Jayaram
I am trying this but having no luck getting the POST to work. I posted a question here – http://stackoverflow.com/questions/32920443/why-does-my-apps-script-deployed-as-api-executable-return-permission-denied.
Is there anything special about the project properties for the script that would cause this?
Hard to say without seeing the code
Hi Martin, working the example and stuck at Step 1: Create the script, make a copy. The spreadsheet opens in “View Only” mode and the option “Make a copy…” is greyed out. I tried “Signing In” but that did not work either.
Hi Martin, I did not realize the “Spreadsheet” was totally generic, meaning, with no Apps Script attached to it. So I manually created your headers in another spreadsheet and Create and Read seems to be working. Do you have nice succinct functions for Update and Delete? I have not used GAS in a while, but I think with this new Execution API, I will bring it back into my toolkit 😉
do you know, how to do the same thing for users who do not have a Google Account?
Authorize script with my account, and use my token to run Apps script?
I found an example for VBA http://ramblings.mcpher.com/Home/excelquirks/goinggas/exap/authentication
but nothing for web app.
I’d be looking to publish as a Apps Script webapp where you can setup a script to execute as yourself even anonymously https://developers.google.com/apps-script/guides/web
I was trying to comment on the GET/POST example but unfortunately the comment section is closed. I was trying to apply your example but it seemed like its not working under domain accounts. i keep getting the following
“XMLHttpRequest cannot load . No ‘Access-Control-Allow-Origin’ header is present on the requested resource.” error on the console. Any ideas?
Hi – is this with a particular browser? There could be an issue with Safari and how the callback is made
Thanks for the sample.
i have following the procedure and i did create my sample
but after i authurize i get the following error msg
“400. That’s an error.
Application: Apps Authenticated INSERT with Apps Script using Execution API”
wondering what i miss from your guide
Sounds like an issue with step 8
Thanks for this very useful post!
I have two questions.
First, I suppose the script ID can be copied the same way you copy the document ID from the browser address bar in “Step 1: Create the script”, right?
Second, after authorizing access I get the the error
Error calling API:
“message”: “The request was blocked. Quota configuration not found for the requesting project.”,
“description”: “The request was blocked. Quota configuration not found for the requesting project.”
Do you have any idea how I can fix this?
1. Script ID is the project key of the script to be executed.
To find the project key, open the project in the script editor, then select **File > Project properties**. (string)
2. I’ve got the same error 403 @PERMISSION_DENIED” , becauce of wrong Client ID. Check it once more,
Thank you so much for this very helpful tutorial.
I have some difficulties to execute your example.
Here is my exemple : http://oxyjm.fr/indemniflight/quickstart.html
I get this error :
Error calling API:
“message”: “The request does not have valid authentication credentials.”,
I have complete the following part of code with that :
var CLIENT_ID = ”;
var SCRIPT_ID = ”;
CLIENT_ID from the Credentials of Google Developers Console
SCRIPT_ID from “Apps Authenticated INSERT with Apps Script using Execution API” File > Project Propertie > Project Key
Do you have idea where come frome the error ?
Thank you for your help,
Finally a solution with authentication! Thanks! 🙂 However, I’ve got a problem.
I’ve changed the script to write to Sheet1 and read from Sheet2, but I get the strangest error: When adding new data the correct Sheet2 is read, but just reloading the page reads Sheet1!
All I’ve done is add one more sheet to the spreadsheet and one more variable to the script. What am I missing?
Hi Einar – you should be able to change the getData apps script function to specify Sheet2 e.g. var sheet = doc.getSheetByName(SHEET_NAME);
could be rewritten as
var sheet = doc.getSheetByName(‘Sheet2’);
See line 54 in https://gist.github.com/mhawksey/833d0e10c64bbe2b1f30#file-code-gs-L54
Thanks, that’s exactly what I’ve done, but it only displays Sheet2 when I post new information. Just loading the page displays Sheet1. Is it a cache problem? Locking problem?
You might need to update the version number of the published script
That’s it! Published the google spreadsheet script with a new version and it worked! 😀 Thank you, Martin!
Now I can keep input in one sheet and query or do some math in the other sheet and present it to the user! This is gold! And securely!
Do I have to create a new version every time I update the script?
Hi Einar – yes new version required every time you update the script
Again, thank you! 🙂
I followed your example but am also running in a:
Error calling API:
“message”: “Requested entity was not found.”,
In the spreadsheet I entered manually some data
I get request for authentication and after I log in …the (sample ) values from the Google Spreadsheet appear on my webpage.
When I then fill in a name and a comment and hit send I get the error from above…
I removed the created oath credentials there where 2 and then I removed the Google Apps Script Execution API..
Then I reopened the Developpers console project clicked on the blue link and searched for the “Google Apps Script Execution API” when enabeled I mentioned it was best to create oath for this which I did. I ended up this time with one credential which worked this time.
One more thing “project=project-id-zvnpdzqnxzjafxrqale” isn’t found in the “Selecta project” drop down list
change : ‘devMode’: false // Optional.
Ive spent about 20 hours or so working on getting OAuth tokens to pull data from Google Spreadsheets using the Execution API.
Ive followed the tutorial which lists the folders under the root of my google drive. No Problems.
Now after 10 hours or so , I am completely stumped.
If I add the scope “https://www.googleapis.com/auth/spreadsheets” to the request for the *token* the request for the drive root folders fails. I mean without adding any more code at all to the Apps Script (so the script still only requires the scope for drive access).
If I add code to access a spreadsheet to the Apps Script, the script then asks for this scope (spreadsheets as above). If you can imagine, I have looked everywhere I can think of.
Im using a Web Application access token.
Ive looked into maybe needing a Service Account Key, but apparently this doesnt work with the execution API.
Ive added the Drive API to the API project in the API console
Ive filled in the required parts of the Drive API
All the same.
Since, as I said above, the access token becomes invalid *just when I add the scope to the token request* (before it is needed in any way), the only solution I can imagine that makes sense is to use a Service account key…
Just packing up… It occoured to me the other possible point of failure was the library Im using picking up more than one scope in the list of scopes…
Ive just included the spreadsheet scope, and deleted the drive List under root folders function (leaving only the spreadsheest scope necessary by the script..)
Thank the lord 🙂 🙂 thanks
Glad you figured it out and left a note – scopes can be tricky (wondering if library scopes get picked up in the parent script File > Properties???)
it was purely a syntax issue for the specific widget I was using to apply for the o auth token.
also I discovered that using https to communicate with the Google servers I’d a requirement, else your project will cease to issue valid tokens after about five days or so
In other words, I created an Web App that should run under the ExecutiveUser ID and send information to an API running under my userID on a Google Site.
I hope have been cleared enough…
You can find your example here http://www.idoops.io/list but it’s not working because of an “origin_mismatch”.
I think this is an issue with data coming back as JSON rather than JSONP. I’ll try an update the script but in the meantime if you are feeling brave an example of wrapping the result in JSONP is in the Tools > Script editor of this similar example https://docs.google.com/spreadsheets/d/1RhnLFl_EXcjuzJA-Z_cmHsWrjZW4YqS3TpFTWw89-PY/edit
In my case, I put “http://www.idoops.io” under credentials. Now, if you try to authorize the Web App at the bottom page http://www.idoops.io/list, you get an “Error: origin_mismatch”. No?
Hi again, I found the solution here “http://stackoverflow.com/questions/30030046/google-apps-script-origin-mismatch-error-in-google-site/38354336#38354336”
Simply, just open the request details in the error alert and get the origin url after “origin=”.
The url origin looks as “https://n-zkews3wjaef6a-0lu-script.googleusercontent.com” when you want to link one web app developed in Google Script to an API also based in G Script.
This is awesome! I am using googles html service to upload files and store the data info and google drive link in a spreadsheet right now. Could this method be used to upload files to google drive? I would like to use the Picker API if possible, I imagine it is possible combining these two. Thanks!
should be possible – Apps Script does have limited quotas on payload (100MB/day) but if you are only anticipating small files I think it will work
When I try to use the form from a different Google account, I get the following error:
Error calling API:
“message”: “Requested entity was not found.”,
It works perfectly from the Google account I used to create the form and the ‘pushing’ document. I am using codepen.io to host the test page.
hard to say as it could be a number of things. One thing already mentioned is changing
I should have scoured the comments more thoroughly. Thanks so much for responding; the solution worked!
I’m sort of new to all this, so I really appreciate you commenting personally. Started going through your stuff on Github (most of it I can only understand superficially) and its awesome. This is the third of your tutorials I’ve read.
Comments are closed.