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.gs file 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.