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

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.

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.

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.