Discussion – 


Automatically update Google spreadsheets

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

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.

Sources: http://www.computerworld.com/article/2469616/business-intelligence/business-intelligence-79661-how-to-create-an-automatically-updating-spreadsheet.html?idg_eid=e290755adaadde6a2cea6c780b67c726&email_SHA1_lc=7092c1becdb531c1596d6de4c21ca97f262d14b5&cid=cw_nlt_computerworld_dailynews_2017-04-29&utm_source=Sailthru&utm_medium=email&utm_campaign=Computerworld%20First%20Look%202017-04-29&utm_term=computerworld_dailynews#slide9

You May Also Like