How to create Google Spredsheet automatically updates data

Tracking data on spreadsheets is a fairly simple task, but remembering to update data every day, week or month may be a problem. Why manually update while you can use very simple automation tools?

Below is a step-by-step guide to creating updated Google spreadsheets and tracking data. The article uses a lot of Twitter followers, but the code can vary depending on the data you need to get and store.

Create functions that automatically save spreadsheet data

Step 1: Create a Google spreadsheet

Go to Google Drive and create a white sheet and name the title.

Create a new Google Spreadsheet spreadsheet

Step 2: Add the first recipe

Formulas in spreadsheets can do more than just calculations. They can export data from the URL. You may want to track more than one page, not only your own but also your opponent, so all will be set up for easy adjustment.

Add formulas on the spreadsheet

The first column is the list of Twitter accounts you want to track, the second column will be the recipe for the Twitter Web Intent page. Instead of complex HTML pages, full URLs can be seen, Intent Web pages designed for developers want to put the Twitter module on their pages. The format is http://twitter.com/intent/user?screen_name=TWITTERHANDLE so the formula for the spreadsheet will be

= CONCAT) 'https://twitter.com/intent/user?screnn_name-',A2)

Click drag the rest of the table if the data is more than one line.

Step 3: Write a spreadsheet function

Ideally, use the Twitter API to pull data. However, using the Twitter API or other APIs requires authentication, you will have to set up OAuth2 for the spreadsheet. If you are interested, Google has an Oauth2 library for Google Apps Script.

In this tutorial, we will use an easier way to get followers data from the Web Intent. The Google Sheet IMPORTXML function allows exporting HTML with an XPath query. XPath is a fairly complex language, but SelectorGadget will allow pointers and clicks to find certain data on HTML pages.

Use XPath query

This is what SelectorGadget displays for XPath queries:

// * [contains (concat ('', @class, ''), concat ('', 'count', ''))] // * [contains (concat '', @class, ''), concat ('', 'alternate-context', ''))]

However, because the Google Sheets function uses two quotation marks to close the value, you'll need to change all of the double quotes of XPath to a single quote, then enter the following formula into the followers column.

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

Then click and drag and drop the rest of the column.

Step 4: Automatically save the data retrieved

Use this formula for the rest of column C and the values ​​will automatically fill in every account you follow. However, these values ​​will not be saved but will change each time the spreadsheet is opened.

To keep the transaction history, copy and paste the value manually into another column. Instead, create a new function.

1. Find the first blank column.
2. Paste the column label with the date of data retrieval
3. Copy the value from column C to the first blank.

Step 5: Write a function to store data

To save the data, we need to create a spreadsheet function. Go to Tools > Script Editor to create the function, you will see the default function myFunction.

Write a function to save data

Step 6: Save the date

Users will also want to know when data is retrieved and stored. So add the current date when the function runs in the first line of the first blank column. The following command will help set the value of row 1 of the first column without data to the current date and time.

sheet (1, numColumns + 1) .setValue (newDate ());

Save the current date and time

Then do the same with the remaining cells in the column with the latest data. The function code will be:

Code to save the date and time

Step 7: Function to save Twitter followers

Change to storeFollowers () and use the code below.

Save Twitter followers

The image below shows what each line will do, then save it, name the script.

The function to save Twitter followers

Step 8: Schedule the function to run automatically

Click on the clock icon to open the Current Project's Triggers dialog box , then click Click here to add one now. You will set it to run whenever manual open (From spreadsheet) or in Time-driven schedule - hourly, daily, monthly.

Schedule the function to run automatically