Google Sheets as a Database – Authenticated INSERT with Apps Script using Execution API

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.

Execution API

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.

Further down the Using the Execution API page there is getSheetNames example which includes a ‘target script’ and example application scripts for different platforms. If like me to jumped to these examples you’ll still be missing one very important step, generating an OAuth access token. The issue is the client examples on this page skip the critical token generation. Personally I think it would be clearer if the documentation included the complete quickstart project, which in fairness is linked to from the overview page but can be easily missed. So making use of the fact that the documentation is published under Creative Commons CC-BY 3.0 here’s my version of creating an Execution API/JavaScript application.

Creating an Execution API/JavaScript 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

1. Open this Google Sheet and File > Make a copy and copy your new Sheet document id from your browser address bar:
   
2. Share the Google Sheet with users you would like to be able to add data.
3. Open the Apps Script editor and create a blank project.
4. 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.
5. 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:
6. Save the project by selecting File > Save.

Step 2: Publish the script for execution and enable the Google Apps Script Execution API

1. In the code editor, select Publish > Deploy as API executable.
2. In the dialog that opens, leave the Version as “New” and enter “Target-v1” into the text box. Click Deploy.
3. Open your target Apps Script in the editor and select Resources > Developers Console Project.
4. 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.
5. 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.
6. Back on the Credentials tab, click the Add credentials button and select OAuth 2.0 client ID.
7. Select the application type Web application.
8. In the Authorized JavaScript origins field, enter the URL of the domain where you’ll be hosting your application. Note: no paths, trailing slash or wildcards – for the example showed you I entered https://mashe.hawksey.info. You can leave the Authorized redirect URIs field blank.
9. Click the Create button.
10. 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:

1. 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.
    
2. 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.

Summary

This example has shown you how to setup a Execution API/JavaScript application. One of the main features of the Execution API is you can create your own REST interfaces for Google Services and achieve easy integration with one of the main client libraries or handling with your own POST requests. I’ve previously described Google Apps Script as the Authentic{ated} Playground, well that playground has just got a lot bigger.