Google Apps Scripts and REST API: from a Data Scientist programmer perspective

5 min readMar 22, 2023

The number of AI applications and programming languages that one needs to know to stay competent has drastically increased. It was so much easier when one just needed to know python, sci-kit learn, pandas, tensorflow, and pytorch. But, over the past year it is important to know a lot of other programs, sdks, REST APIs, and programming languages for app creation, deployment, and presentation of results. And, if you are lucky enough to be able to focus on model development, instead of proving your speed and competence in Data Analytics and Consulting, you need to know how to deploy the model using various front-end programs like Google Apigee, Azure, streamlit, Javascript/html packages, or the new Python/html packages. Over the past month I explored the different model deployment packages and methods, including Google Apigee, Azure, and Google Scripts with Javascript/html.

Google Apps Scripts is a combination of Javascript and html, the Google scripts scripting language (.gs) is not 100% Javascript; it is based on javascript but it includes Visual Basic (VBA) like commands that allow one to interact with the Google spreadsheet. Google Scripts can easily be used with Google’s APIs, like Google fit or Google analytics REST API. Eventhough Google Scripts have been around for years, I found that it was a great way to practice using REST API services and javascript/html at the same time. The diagram below is the communication process with Google, such that the data is retrieved; the 2-step authorization is already coded for you (the developer) and you simply just need to insert your ClientID and ClientSecret. You as the developer need to write the POST request to get the correct data/scopes for the user (see step 4 below).

How to output Google API data to a Google Spreadsheet

Steps to output Google Fit API step data to a Google Spreadsheet: 0. go to Google Drive, 1. create/open a Google spreadsheet, 2. click on Extensions tab, 3. click on Apps Script. A new window will open for coding in .gs and .html. You can code directly in javascript, push the Run button and see a result in the console. Warning: Do not make the mistake of thinking like a programmer who wants to see every step execute in sequence in javascript, and just view the desired output in the Google Spreadsheet. Google uses an OAuth2 protocol that hides approval interactions with the REST API, the .gs programming language uses the spreadsheet as a graphical user interface to interact/approve Google authentication. I made the mistake of trying to write javascript/html code in a sequential manner, to launch the OAuth2 without using the googlesheet. It was a good learning lesson, but I essentially re-coded functionality that already existed in OAuth2; there was very little information describing how Google Apps Script works on a global level and just looking at the OAuth2 code on GitHub does not tell you how it works.

I found that in the “world of REST API and Apps”, no one tells you the global steps that one needs to perform. The instructions are often given in incremental pieces, and a simple task can often appear long and confusing. So, below are the global steps of how to stream Google API data to a Google Sheet:

Configure the Google Apps Script (create a .gs file, add the OAuth2 library)
Configure your Google API account (go to https://console.cloud.google.com/, create a project, obtain the Client ID and ClientSecret),
Copy-paste an example Google Script template in your .gs file for basic functionality (the template includes: (onOpen) the GUI menu that will appear as a tab item in your GoogleSheet, the OAuth2 functions); you only need to type your user information into these function.,
Add your functions to manipulate the received data,
Run the Google Script (there will be no output in the console),
Return to the Google Sheet and use the App (click on the tab item ‘Google Fit’ in the GoogleSheet — click on Authorize and follow the prompts, click on the tab item ‘Google Fit’ in the GoogleSheet — click on Get Data),
Watch the data stream to the Google Sheet!

Step 1: Configure the Google Apps Script

Create a .gs file : click on the + sign next to Files — select Script. Just for extra information, the first function in a .gs script is executed when you push the Run button. Leave the script blank for now.

Add the OAuth2 library: copy-paste the Script ID 1B7FSrk5Zi6L1rSxxTDgDEUsPzlukDsi4KGuTMorsTQHhGBzBkMun4iDF for the OAuth2 library. I found the Script ID for the OAuth2 library from reference [1] and I also looked it up on a library app site (https://scrviz.web.app). https://scrviz.web.app was a great tool to look for other library IDs; to search for library IDs click on Filter menu (do not check Hireable) — Filters — Manifest filters (check Interlocked) — click on Libraries (it looks like the database/hamburger symbol with 3 stacked circles) — click on the library that you want (like OAuth2, OAuth1, Cheerio, etc).

Step 2: Configure your Google API account

If you have never touched your Google cloud console account, I recommend following the directions at https://developers.google.com/fit/rest/v1/get-started?hl=en to set up a project account. In general, you need to go to https://console.cloud.google.com/ — create a New project — select APIs & Services and then Credentials —get the Client ID and ClientSecret by clicking ‘Download OAuth client’. Also, under Authorized redirect URIs I put the URL to the GoogleSheet.

Step 3: Copy-paste an example Google Script template

The Google github page of samples (https://github.com/googleworkspace/apps-script-samples) can be a helpful and fast way to setup your project. I used the spreadsheet script example, and verified it using a script from a great blog post (https://ithoughthecamewithyou.com/post/export-google-fit-daily-steps-to-a-google-sheet).

Step 4: Add your functions to manipulate the received data

I added two functions to the .gs file, one that takes a time interval in which I want to see data (selectDATA_interval1 or selectDATA_interval0) and another that is the REST POST request to the Google Fit API (getData).

Step 5: Run the Google Script

Push the Run button at the top of your Google Apps Script; there will be no output in the console.

Step 6: Return to the Google Sheet and use the App

In the first row of the Google Sheet, for three columns, type: bucketDate, steps, length. In step 4 during the unpack step, we specify to put the data under three columns that are named bucketDate, steps, and length. Click on the tab item ‘Google Fit’ in the GoogleSheet — click on Authorize and follow the prompts. Next, click on the tab item ‘Google Fit’ in the GoogleSheet — click on Get Data. Watch the data stream to the Google Sheet!