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.
Nicely done. It’s such fun stuff and such low overhead.
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 🙂
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 .
Thanks for the additional info, Clair. The Google Apps are pretty amazing.
Hi May I know what’s data.currently. for?
Thanks for asking. data.currently is a JSON node that has a few key/value pairs that describe the current weather.
This is great. Do you know anything about using Apps Script to access an external API (i.e. using token call, etc)?
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