If you work with excel a lot and are tired of copying and pasting all the time, you can set up a self-updating spreadsheet in Google Docs. Here’s how:
Create a Google spreadsheet
- Head to your Google Drive and create a spreadsheet
Add your first formula
- You can do more than just math calculations. For example, you can extract data from URLs
- In the first column put the Twitter accounts you want to track
- In the second column put the following twitter formula: https://twitter.com/intent/user?screen_name=TWITTERHANDLE
Write a spreadsheet function
- Google Sheet’s IMPORTXML function lets you extract specific HTML using XPath queries
- SelectorGadget makes it easier to finding specific data on an HTML page
- SelectorGadget shows for an XPath query: //*[contains(concat( ” “, @class, ” ” ), concat( ” “, “count”, ” ” ))]//*[contains(concat( ” “, @class, ” ” ), concat( ” “, “alternate-context”, ” ” ))]
- Change all the double quotes into single quotes so it is compatible with Google sheets
Auto-save fetched data
- Use the formula above for the rest of the column and the values will begin to automatically fill in for the other accounts you’re tracking
- The values won’t save because they will constantly be updating. If you want to save the data you must copy and paste in another sheet
Write a function to store data
- Head to Tools > Script Editor to create functions for the spreadsheet
- You will see a default pop up called myFunction which will let you write specific functions to store data
Save the date
- So you don’t confuse the data, you will want to put the date that the function runs in row 1 of your first empty column
- Loop through the rest of the cells in the column with your latest data by using the for loop below
Function to store Twitter followers
- Use the code below to store your Twitter followers and change the function to storeFollowers()
Schedule your function to auto-run
- Schedule your new store-the-data function to run by clicking on the clock icon
- Go to the link to “Click here to add one now”. This will allow you to set your function to run whenever the spreadsheet is opened manually or on an automated schedule, which you can customize.
