Using Google Sheets and Google Apps Script to Work with APIs

One of the coolest things about working with Google Apps Script is the overall versatility of the Google Apps ecosystem. I’ve written pretty extensively about how to use Google Apps Script to do a lot of more business focused things, like send an email with Google Forms submissions, sending an attachment with Google Forms submission, and linking Google Forms so that they create Google Calendar events.

In this post, I’m going to look at a more robust application of Google Apps Script that will allow us to use Apps Script to retrieve data from an external API, parse that data, and append the parsed data to a Google Spreadsheet. At that point, we can use the Google Spreadsheet as a backend to serve the data as a CSV file or create dynamic charts using the Google Charts service.

Although the following code is my own implementation, I used the examples found at the Bionic Teaching blog as a jumping off point.

function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet(); //get active spreadsheet
  var sheet = ss.getSheetByName('data'); //get sheet by name from active spreadsheet
  
  
  var apiKey = 'Your API Key'; //apiKey for forecast.io weather api
  var long = "-78.395602"; 
  var lat =  "37.3013648";    
  var url = 'https://api.forecast.io/forecast/' + apiKey +"/" + lat +"," + long; //api endpoint as a string 
  
  var response = UrlFetchApp.fetch(url); // get api endpoint
  var json = response.getContentText(); // get the response content as text
  var data = JSON.parse(json); //parse text into json
  
  Logger.log(data); //log data to logger to check
  
  var stats=[]; //create empty array to hold data points
  
  var date = new Date(); //create new date for timestamp
  
  //The following lines push the parsed json into empty stats array
    stats.push(date);//timestamp 
    stats.push(data.currently.temperature); //temp
    stats.push(data.currently.dewPoint); //dewPoint
    stats.push(data.currently.visibility); //visibility
    
  //append the stats array to the active sheet 
  sheet.appendRow(stats)
  
}

This script is basically just a service that connects to the Forecast.io Weather API and returns, among other things, the current temperature, dew point, and visibility for a location specified by longitude and latitude coordinates. You can check out the docs for the Forecast.io API here.

After testing this a few times to make sure it worked, I set it on a trigger to run every hour, so now my spreadsheet has grown quite large. Since the script automatically adds data every hour, I thought it would be cool to attach this spreadsheet to the Google Charts service to create a visualization that automatically updates. I’ve embedded both below:

 

There are lots of cool applications for this technique, and if nothing else it makes connecting to a simple API and storing some data much easier than trying to use another backend language. I’m going to work on building out some more advanced applications for this and write some additional posts. I’d really like to publish the spreadsheet as a CSV and access the data from other programs, but I’ll need to find a way to work around the CORS rules.

8 thoughts on “Using Google Sheets and Google Apps Script to Work with APIs”

  1. Tom says:

    Nicely done. It’s such fun stuff and such low overhead.

    1. admin says:

      Yes, it is. Thanks for the starting point! I tried publishing the sheet as CSV so that I could load it on another site with d3 to do some more advanced data viz but couldn’t get around CORS settings. I’m going to play around with setting the headers of urlFetch to see if I can make it work. Now that would be cool 🙂

  2. Clair Roegge says:

    You can also attach a script function to an image or drawing within a spreadsheet; the function will execute when a user clicks on the image or drawing. To learn more, see Images and Drawings in Google Sheets .

    1. admin says:

      Thanks for the additional info, Clair. The Google Apps are pretty amazing.

  3. kb says:

    Hi May I know what’s data.currently. for?

    1. admin says:

      Thanks for asking. data.currently is a JSON node that has a few key/value pairs that describe the current weather.

  4. Bob says:

    This is great. Do you know anything about using Apps Script to access an external API (i.e. using token call, etc)?

    1. BrownBearWhatDoYouSee says:

      The UrlFetchApp class should give you access to HTTP headers for auth tokens, so I would say that all of what you need should be available via that class. If that’s not enough detail for you, can you post and example of what you’re trying to do for me to look at.

      Thanks for reading,
      Jeff

Leave a Reply

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