How to create an automatically updating Google spreadsheet

Tired of finding, copying and pasting data into spreadsheets? With just a few lines of code, you can set up a self-updating spreadsheet in Google Docs that fetches and stores data for you

Google sheet formula
Credit: Screenshot of Google Sheets
Let your spreadsheet do the work

Tracking basic data in a spreadsheet is pretty simple -- it's remembering to update your data each day, week or month that can be a problem. But why update manually when you can take advantage of some simple automation tools?

Here's a step-by-step guide to creating an automatically updating Google spreadsheet to track data. We'll use "number of Twitter followers" in this example, but the code can be easily tweaked to fetch and store other data as well.

 

Create a new Google spreadsheet
Credit: Screenshot from Google Sheets
Create a Google spreadsheet

Head to Google Drive and create a blank spreadsheet (if you don't yet have a Google account, you can sign up there). Title the sheet anything you want, and you're ready to begin. (The default will be "Untitled spreadsheet," but you can click on that to change it to something that will better identify it in your list of documents.)

Google Sheet formula
Credit: Screenshot of Google Sheets
Add your first formula

Spreadsheet formulas can do more than math calculations; they can also extract data from URLs. Since you may want to track more than one page -- not only yours but competitors' -- we'll set it up to be easily scalable.

In the first column, list the Twitter accounts you want to track. The second column will be a formula for a Twitter Web Intent page.

What's a Twitter Web intent page? Instead of the more HTML-complex profile page, which you might find at a URL like https://twitter.com/Computerworld, Web Intent pages are designed for developers who want to put Twitter modules on their pages. The format is https://twitter.com/intent/user?screen_name=TWITTERHANDLE, so the spreadsheet formula should be:

=CONCAT("https://twitter.com/intent/user?screen_name=",A2)

Click and drag that down the rest of your URL column if you've got more than one row of data.

Using SelectorGadget to find an XPath query
Credit: Screenshot of SelectorGadget in action
Write a spreadsheet function

Ideally, it would be best to use the Twitter API to pull in this data. However, to use the Twitter API, or any other API that requires authorization for use, you'd need to set up OAuth2 authorization for your spreadsheet. That's a bit outside the scope of this tutorial; but if you're interested, Google has an Oauth2 library for Google Apps Script.

Since I don't have space to include these instructions, I'll take an easier way out and extract number of followers from the Web Intent page. Google Sheet's IMPORTXML function lets you extract specific HTML using XPath queries.

XPath is a fairly complex language to learn, but SelectorGadget makes it easy to point-and-click your way to finding XPath for specific data on an HTML page.

This is what SelectorGadget shows for an XPath query: //*[contains(concat( " ", @class, " " ), concat( " ", "count", " " ))]//*[contains(concat( " ", @class, " " ), concat( " ", "alternate-context", " " ))]. However, because the Google Sheets function uses double quotation marks to enclose arguments, you'll first need to change all of the XPath double quotes to single quotes, and then enter a formula like this for your followers column:

=ImportXML(B2, "//dl[(((count(preceding-sibling::*) + 1) = 1) and parent::*)]//*[contains(concat( ' ', @class, ' ' ), concat( ' ', 'alternate-context', ' ' ))]")

Again, click-and-drag that down the rest of your column.

Google sheet formula
Credit: Screenshot of Google Sheets
Auto-save fetched data

Use this formula in the rest of column C, and values will automatically fill in for other accounts you're tracking. However, those values won't be saved; they'll change each time you open the sheet.

To keep historical data as the sheet's currently designed, you'd need to copy and paste values manually into another column or spreadsheet. What fun is that? Instead, let's create a new function to 1) Find the first empty column, 2) label the column with the date of data extraction, and 3) copy the value from column C into that first empty cell.

Google Sheets default function
Credit: Screenshot of Google Sheets
Write a function to store data

To store data, we'll need to create a spreadsheet function. Head to Tools > Script Editor to create functions for the spreadsheet. You'll see a default function pop up called myFunction.

Full function code
Credit: Sharon Machlis
Save the date

You'll want to know when the data was fetched and stored. So, put the current date that the function runs in row 1 of your first empty column. This command:

sheet(1,numColumns + 1).setValue(new Date());

will set the value of row 1 of the first column without any data -- cell 1, numColumns + 1 -- to the current date and time.

Then loop through the rest of the cells in the column with your latest data by using the for loop at left.

Full function code:

function storeFollowers(){
    var sheet = SpreadsheetApp.getActiveSheet();
    var datarange = sheet.getDataRange();
    var numRows = datarange.getNumRows();
    var numColumns = datarange.getNumColumns();
    var nextColumn = numColumns + 1;
    sheet.getRange(1, nextColumn).setValue(new Date());
    for (var i=2; i <= numRows; i++){
      var numLikes = sheet.getRange(i, 3).getValue();
      sheet.getRange(i, numColumns + 1).setValue(numLikes);
    }
}
storeFollowers() function
Credit: Sharon Machlis
Function to store Twitter followers

Change that to storeFollowers() and use the following code


var sheet = SpreadsheetApp.getActiveSheet();
var datarange = sheet.getDataRange();
var numRows = datarange.getNumRows();
var numColumns = datarange.getNumColumns();
var nextColumn = numColumns + 1;

Comments in the image show what each line is doing.

Save this -- you'll be asked to name your script project something.

Schedule your function to auto-run

Last piece: Schedule your new store-the-data function to run. Click on the clock icon to show your current project's triggers, and then the link to "Click here to add one now". You'll be able to set your function to run whenever the spreadsheet is opened manually (choose "From spreadsheet" as the trigger event) or on an automated schedule (select "Time-driven" as the event) -- hourly, daily, weekly or monthly. You may be asked to authorize this in your Google account.

Then, voila! A self-updating spreadsheet that collects and stores data automatically.