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:

user needs to authorize access

Clicking the Authorize button launches Google Authentication:

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.

service authorisation

On authorization the data is rendered from our Google Sheet in the application window and the user can submit new data:

comment data returned from Google Sheet

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:
    Sheet document ID
  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.

34 Comments


  1. Thanks for this excellent quickstart. I was able to reproduce your example after following your instructions. Now, I would like to convert an app that I created to view a bubble chart that uses HtmlService which I wrote about here – http://www.wyzpubs.com/wp/2015/09/17/advanced-charts-and-appsheet-apps/. I am wondering if all that is required is for me to host the index.html, Javascript.html and Stylesheet.html files on my website to be able to get the chart to render.

    Reply

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

      Reply

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

    Reply

    1. 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 ;)

      Reply

  3. Hi martin,

    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?

    Reply

    1. Hi – is this with a particular browser? There could be an issue with Safari and how the callback is made

      Reply

      1. Hi, Martin,

        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.
        Error: origin_mismatch
        Application: Apps Authenticated INSERT with Apps Script using Execution API”

        wondering what i miss from your guide

        Thanks!

        Reply

        1. Sounds like an issue with step 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.

          Reply

  4. 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:
    {
    “error”: {
    “code”: 403,
    “message”: “The request was blocked. Quota configuration not found for the requesting project.”,
    “status”: “PERMISSION_DENIED”,
    “details”: [
    {
    “@type”: “type.googleapis.com/google.rpc.QuotaFailure”,
    “violations”: [
    {
    “subject”: “project:1064435504394”,
    “description”: “The request was blocked. Quota configuration not found for the requesting project.”
    }
    ]
    }
    ]
    }
    }

    Do you have any idea how I can fix this?

    Reply

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

      Reply

  5. Hello,

    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:
    {
    “error”: {
    “code”: 401,
    “message”: “The request does not have valid authentication credentials.”,
    “status”: “UNAUTHENTICATED”
    }
    }

    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,

    Reply

  6. 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?

    Reply

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

        Reply

          1. That’s it! Published the google spreadsheet script with a new version and it worked! :D 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?


          2. Hi Einar – yes new version required every time you update the script
            Thanks
            Martin


  7. Hi Martin,

    I followed your example but am also running in a:
    Error calling API:
    {
    “error”: {
    “code”: 404,
    “message”: “Requested entity was not found.”,
    “status”: “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…

    Hoera:
    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

    Reply

  8. Hi.

    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…

    Any ideas?

    Reply

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

      Works fine.

      Thank the lord :) :) thanks

      Reply

      1. 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???)

        Reply

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

          Reply

  9. Hi Martin, thanks for the tuto. I have a quick question about the Authorized JavaScript origins. What should I use as URI for a web app hosted on a Google Site and deployed from Google Script that calls another Google Script deployed as API?

    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”.

    Reply

      1. Sorry, I am a bit lost… I think the issue is only related to the wrong URI used as “Authorized JavaScript origins” in credentials.

        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?

        Reply

  10. 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.

    Reply

  11. 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!

    Reply

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

      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *