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.
- Find the first blank column.
- Paste the column label with the date of data retrieval
- 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
You should read it
- 10 tips and tricks in Google Spreadsheet
- How to Make a Shared Spreadsheet
- Google Docs & Spreadsheets: games or tools
- Complete guide to Excel 2016 (Part 9): Working with multiple spreadsheets
- Instructions to send email automatically in Google Sheet with Google Script
- Use Pivot Table in the Google Docs Spreadsheet
- How to print an Excel spreadsheet in a page
- How to Use Google Spreadsheets
May be interested
- How to download Microsoft Office version completely free?in addition to paying a fee to be able to install microsoft office on a computer, users can also use microsoft office suite completely for free with a variety of programs.
- How to create a Google Form form on Google Drivegoogle form on google drive form will help users create a table to collect information, comments, votes, or contact information of a group of subjects.
- Powerful support utilities for Google Slidesgoogle slides is google's online silde presentation service, with the ability to create, edit content and slide shows like on microsoft powerpoint.
- Trick to create a copy of text for recipients when sharing a Google Drive filejust once to try this trick, you'll realize its great benefit when sharing files with colleagues and friends via google drive.
- How to register to download Office 365 for free for students / teachersmicrosoft provides free office 365 office tools for students and teachers. users just need to enter a valid email address provided by the school and register to use the free office 365 suite from microsoft.
- Instructions for creating interactive charts in Excel with INDEX functionexcel makes it easy to create clear, concise charts from your raw data. however, sometimes the graphs create static and not interesting. so the following article will introduce a simple method to help you turn static excel charts into dynamic charts.