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.

How to create Google Spredsheet automatically updates data Picture 1
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.

How to create Google Spredsheet automatically updates data Picture 2
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.

How to create Google Spredsheet automatically updates data Picture 3
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.

How to create Google Spredsheet automatically updates data Picture 4
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.

How to create Google Spredsheet automatically updates data Picture 5
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 ());

How to create Google Spredsheet automatically updates data Picture 6
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:

How to create Google Spredsheet automatically updates data Picture 7
Code to save the date and time

Step 7: Function to save Twitter followers

Change to storeFollowers () and use the code below.

How to create Google Spredsheet automatically updates data Picture 8
Save Twitter followers

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

How to create Google Spredsheet automatically updates data Picture 9
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.

How to create Google Spredsheet automatically updates data Picture 10
Schedule the function to run automatically

4 ★ | 1 Vote

May be interested

  • How to prevent Microsoft from automatically downloading Windows 10 updates?How to prevent Microsoft from automatically downloading Windows 10 updates?
    according to the information recently posted, computers that are using the old version of windows operating system that microsoft also supports will automatically download from 3.5 to 6gb of windows 10 driver installation data. this is automatic, whether you join the upgrade program or not, the windows 10 installer is downloaded silently.
  • Why shouldn't auto-update automatically be updated in Chrome?Why shouldn't auto-update automatically be updated in Chrome?
    there is an easy way to turn off automatic updates, but you can do it in several ways, like stopping the google update service, which is responsible for handling automatic updates, for example. here is why you shouldn't do it.
  • How to Automatically Report on ExcelHow to Automatically Report on Excel
    today's tipsmake will guide you how to automatically report data on microsoft excel. this article also talks about how to query and create reports from external sources (mysql, postgres, oracle, etc.) right on your spreadsheet using the excel plug-in to link your spreadsheet to the source. that data. as for data stored on excel spreadsheets, we will use macro commands to create and export reports into many different file types with just one keystroke. luckily, excel has this feature built in, so you won't need to create your own algorithm.
  • Google Chrome 10: BreakthroughGoogle Chrome 10: Breakthrough
    if you are using chrome, you should automatically update the new version automatically. below, let's take a look at the highlights from the stable release of google chrome 10.
  • Trick to automatically delete data when closing Chrome, Firefox browserTrick to automatically delete data when closing Chrome, Firefox browser
    if you share a computer and don't want others to know what you've done, manual deletion will take a lot of time, now you really want the data to be deleted automatically when exiting the browser. this article will help you do that.
  • How to create graphs, charts in Google SheetsHow to create graphs, charts in Google Sheets
    google sheets also features a graphical representation, showing the data that are included so that users can change the chart.
  • How to Turn Off Google Chrome Updates CompletelyHow to Turn Off Google Chrome Updates Completely
    today's tipsmake will show you how to prevent google chrome from automatically updating on windows, mac, iphone and android platforms. note that stopping updating google chrome puts your device and other devices and systems at risk of hacking or cyberattacks.
  • How to create links to download files directly from Google DriveHow to create links to download files directly from Google Drive
    when you want to share and download data on google drive, users can create links and share them with others so they can download data without providing an account.
  • Why should you never turn off automatic application updates on your phone?Why should you never turn off automatic application updates on your phone?
    most if not all new android phones and iphones have automatic app updates enabled in the google play store and app store respectively. you may want to disable these updates if you are on a limited data plan.
  • Instructions to send email automatically in Google Sheet with Google ScriptInstructions to send email automatically in Google Sheet with Google Script
    google script is a powerful tool used to automate many tasks in google such as sheets and docs. and now the ability to automatically send emails makes it a more powerful tool.