Simple web scraping with Google Sheets
Our goal, as discussed, is to get data into a spreadsheet application which can be used to perform analysis. In the last topic, we looked at scraping data from PDFs with Tabula.
If a table of data has been published on a web page, sometimes you can scrape it directly into your spreadsheet application. Here we are going to look at an example with Google Sheets.
Take a look at this page, which shows current and historical tender notices for the state owned entity (SOE) CIPC. http://www.cipc.co.za/index.php/tenders-and-supplier-database-form/
It doesn't look like it, but the list of tenders on this page is published as a table. If we want to import this list - for example to count how many tenders were issued in different months of the year - we can do it directly from within Google Sheets. First of all, we open up a new spreadsheet.
Put your cursor in cell A1 (for more information about how to navigate spreadsheets, see our Data Pipeline module). Now type the following formula. =IMPORTHTML("http://www.cipc.co.za/index.php/tenders-and-supplier-database-form/", "table", 1)
Let's pause for a second to look at that formula.
The equals sign at the start tells Google Sheets that this cell is a formula, not a string of text and numbers (like a paragraph). A formula is an instruction to do something, for example =sum(A2:E2) tells Google Sheets to add the values in column 2, rows A to E together. =average(A2:E2) would look for the arithmetic mean of the same numbers.
In our formula, =importhtml tells Google Sheets to load the webpage inside the brackets ("http://www.cipc.co.za/index.php/tenders-and-supplier-database-form/"). It's important to notice the quote marks around the website URL.
Each element of the formula is separate by commas. The second element tells Google Sheets to load a table from the webpage, and the final element - the 1 - which table to load. For humans, reading the formula backwards makes more sense:
"Load the first table you find on http://www.cipc.co.za/index.php/tenders-and-supplier-database-form. It's in HTML"
Working with imported data
After you've entered the formula, your spreadsheet should look like this.
We can tidy this up by making the columns a little wider. Just put your cursor on the line between A and B at the top of the sheet, for example, and drag it manually to the right. You can also double click this line to automatically resize the column.
Now we have a table of all the latest tenders from CIPC, in a machine readable format. Because this table is reloaded from the original website every time we refresh our sheet, you might want to copy the data onto a fresh tab to store a copy for working on. Just press CTRL + A to select the whole table, and click the + icon in the bottom left to add a new tab to your spreadsheet. In Cell A1 on the new sheet, press CTRL + SHIFT + V. This is the command to paste values only, in other words, it takes the characters in each cell and not the formula.
Now your spreadsheet is ready for work.
Last updated
