Google Sheets power tips: Create an automatically updating spreadsheet
Tired of finding, copying, and pasting data into spreadsheets? With just a few lines of code, you can set up a self-updating spreadsheet in Google Sheets that fetches and stores data for you.
img.feature-img
width: 100%;
height: auto;
everything possible / Shutterstock
Tracking basic data in a spreadsheet is pretty simple — it’s remembering to update your data each day, week, or month that can be a problem. But why update manually when you can take advantage of some simple automation tools?
Here’s a step-by-step guide to creating an automatically updating Google Sheet that both scrapes data from the web and automatically updates the spreadsheet. We’ll use “daily local temperature at lunchtime” in this example, but the code can be easily tweaked to fetch and store other data as well.
1. Create a Google Sheets spreadsheet
Head to Google Drive or Google Sheets and create a new blank spreadsheet. (If you don’t yet have a Google account, you can sign up there.)
IDG/Sharon Machlis
First, start a new blank spreadsheet. (Click image to enlarge it.)
Title the sheet anything you want, and you’re ready to begin. (The default will be “Untitled spreadsheet,” but you can click on that to change it to something that will better identify it in your list of documents.)
2. Find the “XPath” for the part of the web page you want to scrape
Google Sheets needs an “XPath” selector to pluck just a portion of data from a web page. Fortunately, there’s an easy-to-use point-and-click tool that helps you do just that: SelectorGadget. (There’s a less-than-2-minute video on the site demonstrating how it works.)
Install the SelectorGadget Chrome browser extension, activate it by clicking the browser tool icon, and then click on the section of the page you want to scrape. That section will turn green. The trick is to make sure you’re only selecting that. Other areas of the page may be green or yellow, too. If they are, click on the ones that you want to remove from your selection.
In the image below, I visited the National Weather Service page for Needham, MA, the location of Foundry corporate headquarters. (Foundry is Computerworld’s parent company.)
This page was easy: I clicked the temperature, and it was the only item selected. If others had shown up as green or yellow, I would have clicked those to turn them off in order to make sure my XPath was choosing only what I need.
IDG/Sharon Machlis
Selecting the temperature on the National Weather Service page for Needham, MA. (Click image to enlarge it.)
In the bottom right, a selector appears; but that .myforecast-current-lrg is a css selector. I need XPath, which is an option all the way at the bottom right. Click on XPath, and the XPath version will show up looking something like this:
IDG/Sharon Machlis
Grabbing the XPath selector. (Click image to enlarge it.)
XPath is often more complicated than CSS, but you don’t need to worry about what all the expressions mean. The one key point is that the XPath selector will end up surrounded by double quotation marks in your Google Sheets formula. Unfortunately, the selector itself also has double quotation marks. That will cause problems, so if your XPath includes double quotation marks, change them all to single quotes manually.
The XPath selector I’ll use in my Google Sheet is now
//*[contains(concat( ‘ ‘, @class, ‘ ‘ ), concat( ‘ ‘, ‘myforecast-current-lrg’, ‘ ‘ ))]
3. Add your formula
Spreadsheet formulas can do more than math calculations; they can also extract data from web pages. For a Google Sheet, the function to read part of a web page is:
=ImportXML(“URL”, “XPath selector”)
For my National Weather Service Needham, MA page and temperature selector, that’s
=IMPORTXML(“https://forecast.weather.gov/MapClick.php?lat=42.2803&lon=-71.2345”, “//*[contains(concat( ‘ ‘, @class, ‘ ‘ ), concat( ‘ ‘, ‘myforecast-current-lrg’, ‘ ‘ ))]”)
In my spreadsheet’s first column, I’ll list the location I’m tracking. This will make it easy to add additional places to my sheet in the future. I’ll put the formula in the second column.
IDG/Sharon Machlis
Inserting the formula in the spreadsheet. (Click image to enlarge it.)
And I want the date and time in column C. We’ll take care of that in a moment.
4. Write a function to fetch and store data
The way the spreadsheet is currently set up, the temperature won’t be saved; it will change each time you open the sheet.
To keep historical data as the sheet’s currently designed, I’d need to copy and paste values manually into another cell each time I opened it. That’s not very scalable! Instead, let’s create a new function to 1) Find the first empty row in the sheet and 2) copy the value from cell B2 into another empty cell for storage.
To store data, we’ll need to create a spreadsheet function. Head to Extensions > Apps Script to create functions for the spreadsheet.
IDG/Sharon Machlis
Launch Apps Script to create a function. (Click image to enlarge it.)
You’ll see a default function pop up called myFunction.
IDG/Sharon Machlis
Starting a new project in Apps Script. (Click image to enlarge it.)
Change that function name to storeTemperature() (or whatever you might like to call it), and use the following code:
function storeTemperature()
var sheet = SpreadsheetApp.getActiveSheet();
var firstEmptyRow = sheet.getLastRow() + 1;
// Get current temperature from cell B2
var currentTempCell = sheet.getRange(2, 2);
var currentTemp = currentTempCell.getValue();
// Find first empty cell in column B and add that value
var newTempCell = sheet.getRange(“B” + firstEmptyRow);
newTempCell.setValue(currentTemp)
// Get the current date and time add it to same row column C
var currentDate = Date();
var newDateCell = sheet.getRange(“C” + firstEmptyRow);
newDateCell.setValue(currentDate)
// Add place name in column A, currently hard coded
sheet.getRange(“A” + firstEmptyRow).setValue(“Needham, MA”)
The first line of code creates a variable for whatever spreadsheet is active, followed by a variable holding the number of that sheet’s first empty row.
In order to read or write data in a Sheets cell, that cell needs to be a range object. Fortunately, it’s easy to turn a spreadsheet’s cell location into a cell range object with yourSheetObject.getRange(). In this example, I called my sheet object sheet (you can call it anything), so the code is sheet.getRange(). getRange takes several formats to identify a cell location, including getRange(3,5) for row 3, column 5, or getRange(“B ” + 2) for conventional spreadsheet cell syntax like B2.
With that in mind, I can create a variable holding the cell range for cell B2, which is row 2 and column 2, where I know my current temperature sits, using sheet.getRange(2, 2). I then read that value with get.Value() as you can see in the above code.
Next in that code block, I find the first empty row in column B, where I want to add the new temperature, and save that cell as a range object. Then it’s simply a matter of setting that cell’s value with the temperature I just stored.
The final rows do something similar to store the current date and time as well as the hard-coded location.
There are ways to make this code more compact, but I’m sharing a verbose version to make it easier to see what’s going on. In the last line, I tightened it up a bit by combining getRange and setValue in a single line of code, instead of creating a separate variable holding the cell range. Use whichever syntax you prefer.
IDG/Sharon Machlis
The full Lunchtime Temps project. (Click image to enlarge it.)
Save this by clicking the floppy disk icon above the function code. You’ll probably want to change the default project name to something else. I named mine “Lunchtime Temps.”
You can test your script by clicking the Run button to see what happens in the spreadsheet. If all goes well, you’ll have a new row of data with the current temperature.
5. Schedule your function to auto-run
Last piece: Schedule your function to run automatically. To do this, click on the clock icon on the left to open your script dashboard that shows your current project’s triggers. (There won’t be any yet.) You may be asked to authorize this in your Google account first.
IDG/Sharon Machlis
There won’t be any triggers for your script until you set them up. (Click image to enlarge it.)
Click on create a new trigger, and a menu pops up.
IDG/Sharon Machlis
Adding a trigger for the Lunchtime Temps project. (Click image to enlarge it.)
If you want your spreadsheet to update on a specific schedule, change the event source from “From spreadsheet” to “Time-driven” and then select whether you want it running hourly, daily, weekly, monthly, or other options.
Then, voila! A self-updating spreadsheet that collects and stores data automatically.
This article was originally published in April 2017 and updated in February 2023.
2023-02-10 09:00:03 Google Sheets power tips: Create an automatically updating spreadsheet
Original from www.computerworld.com
The modern workplace has no shortage of software solutions for facilitating, assessing, and archiving data. Google Sheets is one of the most popular and powerful tools for crunching numbers, organizing, and sharing documents. With a few simple tricks, even basic users of Google Sheets can leverage its flexibility and efficiency. This article will teach you how to create an automatically updating spreadsheet that can automatically recalculate and regenerate values while at the same time enabling integrated access by multiple users.
One of the biggest advantages of using Google Sheets is the ability to automate functions and regular tasks. To set up an automatically updating spreadsheet, start by entering the data that you want to calculate in the appropriate cells and then enter the desired formula. To make this automatic, select Tools > Script Editor and in the new script, add the line:
SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange(‘A1:C3’).autoFill(SpreadsheetApp.getActiveSheet().getRange(‘A1:C3’), SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES);
This line of Script Editor code tells Google Sheets to automatically refill content in cells A1 through C3 when new data is entered. It’s important to note that new data must be entered in the same format that is already entered in the adjacent cell for autoFill to work correctly.
Google Sheets also allows you to enable integration so that multiple users can simultaneously collaborate on the spreadsheet. To enable integration, start by clicking File in the menu bar and selecting “Share”. Then enter the desired user’s email address to send out a share link. Users who receive the email can click on the link to access the spreadsheet simultaneously.
By applying these simple power tips, even basic users of Google Sheets can take advantage of its automation tools to quickly create an automatically updating spreadsheet. What’s more, users can take advantage of integration capabilities to make the spreadsheet accessible to multiple users simultaneously, further enhancing its flexibility and efficiency.
###
Google Sheets is a powerful software solution for managing and sharing documents, thanks in part to its automation features. This article showed you how to create an automatically updating spreadsheet that can keep track of changes and enable multiple users to simultaneously access and collaborate on the document. With these easy power tips, you can use Google Sheets to get the most out of its autoFill functionality, integration capabilities, and more.