Are you tired of manually updating data in your spreadsheets? Well, we have some simple automation tricks that will save you time and effort. In this article, we will guide you through the process of creating a Google Sheets spreadsheet that automatically updates by retrieving data from the web. So grab a cup of coffee, sit back, and let’s dive in!
Step 1: Create a Google Sheets Spreadsheet
The first step is to head over to Google Drive or Google Sheets and create a new blank spreadsheet. If you don’t have a Google account yet, don’t worry, it’s free and easy to sign up. Once you have your account set up, give your spreadsheet a catchy title, and you’re good to go!
Step 2: Find the “XPath” for the Web Data
Google Sheets requires an “XPath” selector to extract specific data from a web page. Luckily, there’s a simple tool called SelectorGadget that makes it easy to find the XPath. Install the SelectorGadget Chrome browser extension, activate it, and click on the section of the web page you want to retrieve. The selected section will be highlighted in green. Make sure to select only the desired part and exclude any unwanted sections.
Next, click on the XPath selector that appears at the bottom right. This will display the XPath version, which will look something like this:
//*[contains(concat( ' ', @class, ' ' ), concat( ' ', 'myforecast-current-lrg', ' ' ))]If your XPath contains double quotes, make sure to replace them with single quotes to avoid any issues.
Step 3: Add Your Formula
Spreadsheet formulas can do more than just mathematical calculations; they can also extract data from web pages. For a Google spreadsheet, the function to read a part of a web page is:
=IMPORTXML("URL", "XPath selector")In our example, we will be using the National Weather Service page for Needham, MA and the temperature selector. So our formula will look like this:
=IMPORTXML("https://forecast.weather.gov/MapClick.php?lat=42.2803&lon=-71.2345", "//*[contains(concat( ' ', @class, ' ' ), concat( ' ', 'myforecast-current-lrg', ' ' ))]")In my spreadsheet, I’ll list the location I’m monitoring in the first column, making it easy to add more locations in the future. I’ll put the formula in the second column.
I also want to include the date and time in column C. We’ll take care of that in a moment.
Step 4: Write a Function to Retrieve and Store the Data
By default, the spreadsheet is set up so that the temperature is not saved; it will change every time you open the spreadsheet. To keep historical data, you would need to manually copy and paste the values into another cell each time, which is not very practical. Instead, let’s create a new function to: 1) find the first empty row in the spreadsheet, and 2) copy the value from cell B2 to another empty cell for storage.
To store the data, we need to create a custom spreadsheet function. Go to Extensions > Apps Script to create functions for your spreadsheet.
A default function called myFunction will appear.
Rename this function to storeTemperature() (or any name you prefer) 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 and add it to the 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 the active spreadsheet, followed by a variable that stores the number of the first empty row in that spreadsheet.
To read or write data in a Sheets cell, that cell needs to be a range object. Fortunately, it’s easy to convert a cell location in a spreadsheet into a cell range object using the getRange() method. In this example, I’ve named my spreadsheet object sheet, so the code would be sheet.getRange(). The getRange method accepts various formats for identifying a cell location, such as getRange(3, 5) for row 3, column 5, or getRange("B" + 2) for the conventional spreadsheet cell syntax like B2.
With that in mind, I can create a variable that holds the cell range for cell B2, which is located at row 2 and column 2, where I know my current temperature is stored, using sheet.getRange(2, 2). Then, I read the value from that cell using getValue() as shown in the code above.
Next, in this code block, I find the first empty row in column B, where I want to add the new temperature, and store that cell as a range object. Then, I simply set the value of that cell with the temperature I just stored.
The last few lines do something similar for storing the current date and time, as well as the hardcoded location.
There are ways to make this code more compact, but I’ve shared a detailed version to make it easier to understand what’s going on. In the last line, I simplified the code by combining getRange and setValue in a single line, instead of creating a separate variable to hold the cell range. Use the syntax that works best for you.
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 everything goes well, you’ll have a new row of data with the current temperature.
Step 5: Schedule the Automatic Execution of Your Function
Last but not least, you need to schedule the automatic execution of your function. To do this, click on the clock icon on the left to open the dashboard for your script, which displays the triggers for your current project (there won’t be any yet).
Click on “Create a new trigger,” and a menu will appear.
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 to run every hour, every day, every week, every month, or other options.
And there you have it! An automatically updating spreadsheet that collects and stores data on its own.
This article was originally published in April 2017 and updated in February 2023.
Check out Crawlan.com for more tips and tricks!
Don’t forget to visit Crawlan.com for more exciting articles and insights.
