Monitoring fundamental knowledge in a spreadsheet is fairly easy — it’s remembering to replace your knowledge every day, week, or month that may be an issue. However why replace manually when you may make the most of some easy automation instruments?

Right here’s a step-by-step information to creating an mechanically updating Google Sheet that each scrapes knowledge from the net and mechanically updates the spreadsheet. We’ll use “every day native temperature at lunchtime” on this instance, however the code will be simply tweaked to fetch and retailer different knowledge as nicely.

1. Create a Google Sheets spreadsheet

Head to Google Drive or Google Sheets and create a brand new clean spreadsheet. (For those who don’t but have a Google account, you may enroll there.)

IDG/Sharon Machlis

Title the sheet something you need, and also you’re prepared to start. (The default will likely be “Untitled spreadsheet,” however you may click on on that to vary it to one thing that may higher determine it in your checklist of paperwork.)

2. Discover the “XPath” for the a part of the net web page you wish to scrape.

Google Sheets wants an “XPath” selector to pluck only a portion of knowledge from an internet web page. Fortuitously, there’s an easy-to-use point-and-click software that helps you do exactly that: SelectorGadget. (There’s a less-than-2-minute video on the positioning demonstrating the way it works.)

Set up the SelectorGadget Chrome browser extension, activate it by clicking the browser software icon, after which click on on the part of the web page you wish to scrape. That part will flip inexperienced. The trick is to ensure you’re solely choosing that. Different areas of the web page could also be inexperienced or yellow, too. If they’re, click on on those that you just wish to take away out of your choice.

Within the picture beneath, I visited the Nationwide Climate Service web page for Needham, MA, the situation of Foundry company headquarters. (Foundry is Computerworld’s father or mother firm.)

This web page was straightforward: I clicked the temperature, and it was the one merchandise chosen. If others had proven up as inexperienced or yellow, I’d have clicked these to show them off as a way to be certain my XPath was selecting solely what I would like.

IDG/Sharon Machlis

Within the backside proper, a selector seems; however that .myforecast-current-lrg is a css selector. I would like XPath, which is an choice all the best way on the backside proper. Click on on XPath, and the XPath model will present up wanting one thing like this:

IDG/Sharon Machlis

XPath is commonly extra sophisticated than CSS, however you don’t want to fret about what all of the expressions imply. The one key level is that the XPath selector will find yourself surrounded by double citation marks in your Google Sheets system. Sadly, the selector itself additionally has double citation marks. That can trigger issues, so in case your XPath contains double citation marks, change all of them 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 system

Spreadsheet formulation can do greater than math calculations; they’ll additionally extract knowledge from internet pages. For a Google Sheet, the operate to learn a part of an internet web page is:

=ImportXML("URL", "XPath selector")

For my Nationwide Climate Service Needham, MA web page and temperature selector, that’s

=IMPORTXML("https://forecast.climate.gov/MapClick.php?lat=42.2803&lon=-71.2345", "//*[contains(concat( ' ', @class, ' ' ), concat( ' ', 'myforecast-current-lrg', ' ' ))]")

In my spreadsheet’s first column, I’ll checklist the situation I’m monitoring. It will make it straightforward so as to add further locations to my sheet sooner or later. I’ll put the system within the second column.

IDG/Sharon Machlis

And I would like the date and time in column C. We’ll deal with that in a second.

4. Write a operate to fetch and retailer knowledge

The way in which the spreadsheet is presently arrange, the temperature received’t be saved; it’ll change every time you open the sheet.

To maintain historic knowledge because the sheet’s presently designed, I’d want to repeat and paste values manually into one other cell every time I opened it. That’s not very scalable! As an alternative, let’s create a brand new operate to 1) Discover the primary empty row within the sheet and a couple of) copy the worth from cell B2 into one other empty cell for storage.

To retailer knowledge, we’ll have to create a spreadsheet operate. Head to Extensions > Apps Script to create features for the spreadsheet.

IDG/Sharon Machlis

You’ll see a default operate pop up known as myFunction.

IDG/Sharon Machlis

Change that operate title to storeTemperature() (or no matter you would possibly wish to name it), and use the next code:

operate storeTemperature() {
var sheet = SpreadsheetApp.getActiveSheet();
var firstEmptyRow = sheet.getLastRow() + 1;
// Get present temperature from cell B2
var currentTempCell = sheet.getRange(2, 2);
var currentTemp = currentTempCell.getValue();
// Discover first empty cell in column B and add that worth
var newTempCell = sheet.getRange("B" + firstEmptyRow);
newTempCell.setValue(currentTemp)
// Get the present date and time add it to similar row column C
var currentDate = Date(); 
var newDateCell = sheet.getRange("C" + firstEmptyRow);
newDateCell.setValue(currentDate)
// Add place title in column A, presently arduous coded
sheet.getRange("A" + firstEmptyRow).setValue("Needham, MA")
}

The primary line of code creates a variable for no matter spreadsheet is lively, adopted by a variable holding the variety of that sheet’s first empty row.

With the intention to learn or write knowledge in a Sheets cell, that cell must be a vary object. Fortuitously, it’s straightforward to show a spreadsheet’s cell location right into a cell vary object with yourSheetObject.getRange(). On this instance, I known as my sheet object sheet (you may name it something), so the code is sheet.getRange(). getRange takes a number of codecs to determine a cell location, together with getRange(3,5) for row 3, column 5, or getRange("B " + 2) for standard spreadsheet cell syntax like B2.

With that in thoughts, I can create a variable holding the cell vary for cell B2, which is row 2 and column 2, the place I do know my present temperature sits, utilizing sheet.getRange(2, 2). I then learn that worth with get.Worth() as you may see within the above code.

Subsequent in that code block, I discover the primary empty row in column B, the place I wish to add the brand new temperature, and save that cell as a variety object. Then it’s merely a matter of setting that cell’s worth with the temperature I simply saved.

The ultimate rows do one thing much like retailer the present date and time in addition to the hard-coded location.

There are methods to make this code extra compact, however I’m sharing a verbose model to make it simpler to see what’s occurring. Within the final line, I tightened it up a bit by combining getRange and setValue in a single line of code, as a substitute of making a separate variable holding the cell vary. Use whichever syntax you favor.

IDG/Sharon Machlis

Save this by clicking the floppy disk icon above the operate code. You’ll in all probability wish to change the default venture title to one thing else. I named mine “Lunchtime Temps.”

You may check your script by clicking the Run button to see what occurs within the spreadsheet. If all goes nicely, you’ll have a brand new row of knowledge with the present temperature.

5. Schedule your operate to auto-run

Final piece: Schedule your operate to run mechanically. To do that, click on on the clock icon on the left to open your script dashboard that exhibits your present venture’s triggers. (There received’t be any but.) You could be requested to authorize this in your Google account first.

IDG/Sharon Machlis

Click on on create a brand new set off, and a menu pops up.

IDG/Sharon Machlis

If you need your spreadsheet to replace on a particular schedule, change the occasion supply from “From spreadsheet” to “Time-driven” after which choose whether or not you need it operating hourly, every day, weekly, month-to-month, or different choices.

Then, voila! A self-updating spreadsheet that collects and shops knowledge mechanically.

This text was initially printed in April 2017 and up to date in February 2023.