Tracking primary information in a spreadsheet is fairly easy — it’s remembering to replace your information every day, week, or month that may be an issue. But why replace manually when you possibly can reap the benefits of some easy automation instruments?Here’s a step-by-step information to creating an mechanically updating Google Sheet that each scrapes information from the online and mechanically updates the spreadsheet. We’ll use “daily local temperature at lunchtime” on this instance, however the code could be simply tweaked to fetch and retailer different information as effectively.1. Create a Google Sheets spreadsheetHead to Google Drive or Google Sheets and create a brand new clean spreadsheet. (If you don’t but have a Google account, you possibly can join there.) IDG/Sharon Machlis
First, begin a brand new clean spreadsheet. (Click picture to enlarge it.)
Title the sheet something you need, and also you’re prepared to start. (The default can be “Untitled spreadsheet,” however you possibly can click on on that to vary it to one thing that may higher determine it in your record of paperwork.)2. Find the “XPath” for the a part of the online web page you need to scrapeGoogle Sheets wants an “XPath” selector to pluck only a portion of knowledge from an internet web page. Fortunately, there’s an easy-to-use point-and-click device that helps you just do that: SelectorGadget. (There’s a less-than-2-minute video on the positioning demonstrating the way it works.)Install the SelectorGadget Chrome browser extension, activate it by clicking the browser device icon, after which click on on the part of the web page you need to scrape. That part will flip inexperienced. The trick is to be sure to’re solely choosing that. Other areas of the web page could also be inexperienced or yellow, too. If they’re, click on on those that you simply need to take away out of your choice. In the picture under, I visited the National Weather Service web page for Needham, MA, the situation of Foundry company headquarters. (Foundry is Computerworld’s guardian firm.)This web page was simple: I clicked the temperature, and it was the one merchandise chosen. If others had proven up as inexperienced or yellow, I might have clicked these to show them off so as to be certain my XPath was selecting solely what I would like. IDG/Sharon Machlis
Selecting the temperature on the National Weather Service web page for Needham, MA. (Click picture to enlarge it.)
In the underside 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 XPath, and the XPath model will present up trying one thing like this: IDG/Sharon Machlis
Grabbing the XPath selector. (Click picture to enlarge it.)
XPath is usually extra difficult 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 components. Unfortunately, the selector itself additionally has double citation marks. That will trigger issues, so in case your XPath consists of 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 formulaSpreadsheet formulation can do greater than math calculations; they will additionally extract information from net pages. For a Google Sheet, the operate to learn a part of an internet web page is:=ImportXML(“URL”, “XPath selector”)For my National Weather Service Needham, MA web 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 record the situation I’m monitoring. This will make it simple so as to add extra locations to my sheet sooner or later. I’ll put the components within the second column. IDG/Sharon Machlis
Inserting the components within the spreadsheet. (Click picture to enlarge it.)
And I need the date and time in column C. We’ll deal with that in a second.4. Write a operate to fetch and retailer informationThe means the spreadsheet is at the moment arrange, the temperature gained’t be saved; it’ll change every time you open the sheet.To preserve historic information because the sheet’s at the moment designed, I’d want to repeat and paste values manually into one other cell every time I opened it. That’s not very scalable! Instead, let’s create a brand new operate to 1) Find 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 information, we’ll have to create a spreadsheet operate. Head to Extensions > Apps Script to create capabilities for the spreadsheet. IDG/Sharon Machlis
Launch Apps Script to create a operate. (Click picture to enlarge it.)
You’ll see a default operate pop up referred to as myFunction. IDG/Sharon Machlis
Starting a brand new mission in Apps Script. (Click picture to enlarge it.)
Change that operate identify to storeTemperature() (or no matter you may 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 B2var currentTempCell = sheet.getRange(2, 2);var currentTemp = currentTempCell.getValue();// Find first empty cell in column B and add that valuevar newTempCell = sheet.getRange(“B” + firstEmptyRow);newTempCell.setValue(currentTemp)// Get the present date and time add it to similar row column Cvar presentDate = Date(); var newDateCell = sheet.getRange(“C” + firstEmptyRow);newDateCell.setValue(presentDate)// Add place identify in column A, at the moment arduous codedsheet.getRange(“A” + firstEmptyRow).setValue(“Needham, MA”)}The first line of code creates a variable for no matter spreadsheet is energetic, adopted by a variable holding the variety of that sheet’s first empty row.In order to learn or write information in a Sheets cell, that cell must be a variety object. Fortunately, it’s simple to show a spreadsheet’s cell location right into a cell vary object together with yourSheetObject.getRange(). In this instance, I referred to as my sheet object sheet (you possibly can 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 typical 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.Value() as you possibly can see within the above code.Next in that code block, I discover the primary empty row in column B, the place I need 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 last 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 happening. In the final line, I tightened it up a bit by combining getRange and setValue in a single line of code, as an alternative of making a separate variable holding the cell vary. Use whichever syntax you like. IDG/Sharon Machlis
The full Lunchtime Temps mission. (Click picture to enlarge it.)
Save this by clicking the floppy disk icon above the operate code. You’ll most likely need to change the default mission identify to one thing else. I named mine “Lunchtime Temps.”You can check your script by clicking the Run button to see what occurs within the spreadsheet. If all goes effectively, you’ll have a brand new row of knowledge with the present temperature.5. Schedule your operate to auto-runLast 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 mission’s triggers. (There gained’t be any but.) You could also be requested to authorize this in your Google account first. IDG/Sharon Machlis
There gained’t be any triggers on your script till you set them up. (Click picture to enlarge it.)
Click on create a brand new set off, and a menu pops up. IDG/Sharon Machlis
Adding a set off for the Lunchtime Temps mission. (Click picture to enlarge it.)
If you need your spreadsheet to replace on a selected schedule, change the occasion supply from “From spreadsheet” to “Time-driven” after which choose whether or not you need it working hourly, day by day, weekly, month-to-month, or different choices.Then, voila! A self-updating spreadsheet that collects and shops information mechanically.This article was initially revealed in April 2017 and up to date in February 2023.
Copyright © 2023 IDG Communications, Inc.