How to create an automatically updating Google spreadsheet

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 Facebook followers" in this example, but the code can be easily updated to fetch and store other data as well.

[13 killer Chrome apps to replace your desktop software and 10 great Google tools you need in your business workflow]

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

Add your first formula

Spreadsheet formulas can do more than math calculations; they can also extract data from an API such as Facebook's. 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 Facebook accounts you want to track. In the second column, create a formula to find number of likes from the API using this format:

https://graph.facebook.com/accountID (replace that with the account ID or name)

To do so, your column B formula needs to add the URL root  "https://graph.facebook.com/" to the account name from column A, like so:

=CONCAT("https://graph.facebook.com/",A2)

and so on for the other rows.

Write a spreadsheet function

Now that you've got your properly formatted data-request URL, it's time to go fetch the data by writing a spreadsheet function. In Google Spreadsheet, you do that inside the sheet's associated script page.

Select Tools --> Script editor ... (continued on next slide)

Use the script editor

... and choose Spreadsheet under "Create script for" header. You'll get a page of code that includes a couple of default functions: readRows() and onOpen().

Create a new blank function, calling it anything you wish. Here I'll call it facebookLikes with a single argument, the URL:

function facebookLikes(url) {

}

Fetch data

What we're looking for is the number of likes in this returned JSON data that will look something like the data at left.

Google's developer documentation shows that fetching data uses

UrlFetchApp.fetch(url);

so we'll create a variable holding that

var result = UrlFetchApp.fetch(url);

and then parse the JSON result.

Parse JSON

We can extract the number of likes from the JSON with this code:

var json = response.getContentText();
var data = JSON.parse(json);
var likes = data.likes;
return likes;

Use your function in a spreadsheet cell

In cell C2, use your custom facebookLikes function on your URL in cell B2 with this formula:

=facebookLikes(B2)

and you should see the number of likes appear after you hit Enter.

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.

Write a function to store data

To store data, we need to find the first empty column. Do this by setting a variable name for the active spreadsheet, getting the data range in that existing sheet with the getDataRange() command and then creating variables to hold the number of columns and number of rows. You can see the start of a storeLikes() function at left.

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.

Test your function

You can test your function in the Script editor by going to the Run menu and choosing your function from the drop-down list.

Schedule your function to auto-run

Last piece: Schedule your new store-the-data function to run. Click on the clock icon and choose "Add a new trigger." 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 or weekly. 

Auto-running monthly

Unfortunately, Google Spreadsheet's automated triggers don't include  a "monthly" option. To get around that, write a separate function that runs daily, and use that function to check the current date and run your other function if the day of the month is 1 (see code at left). Finally, set up a new trigger to run this check-what-day-it-is function daily.

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

Originally published on Computerworld| Click here to read the original story.

googlespreadsheetthumb.jpg