In this post, I demonstrate how to Scrape data from a Website and import it to a Google Sheet. I used JavaScript and Reg Expressions to accomplish the task.
How to Video:
Video Notes:
- Reg Expression Tool: https://regex101.com/
- Apps Script (Script Editor) is now located under tab ‘Extensions’ instead of ‘Tools’ on Google Sheets
- Check out another Scraping Website Post. Click Here
Code in Video:
function mainFunction()
{
// *************************************************************************
// CODE WITH CURT 2/14/2021
// The code below will eventually not bring back the desired results.
// Over time Web sites get Updated and Upgraded losing the original HTML and CSS tags that
// are searched in the RegExp statements.
// *************************************************************************
clearRecords();
var url = "https://www.premierleague.com/tables";
var str = UrlFetchApp.fetch(url).getContentText();
const mainRegex = /<td class=\"team\" scope=\"row\">([\s\S]*?)<\/td>/gi;
var results = str.match(mainRegex);
const teamNamePass1 = /<span class=\"long\">([\s\S]*?)<\/span>/gi;
const teamNamePass2 = /(?<=<span class=\"long\">).*?(?=<\/span>)/gi;
const teamLogoPass = /(?<=img class=\"badge-image badge-image--25\" src=\").*?(?=\")/gi;
for(var i = 0; i < 20; i++)
{
Logger.log('content: ' + results[i]);
var team1NameString = results[i].match(teamNamePass1);
Logger.log('content: ' + team1NameString[0]);
var team2NameString = team1NameString[0].match(teamNamePass2);
Logger.log('content: ' + team2NameString[0]);
var team1Logo = results[i].match(teamLogoPass);
Logger.log('content: ' + team1Logo[0]);
addRecord(i+1, team2NameString[0], team1Logo[0]);
}
}
function clearRecords()
{
var ss= SpreadsheetApp.getActiveSpreadsheet();
var tableSheet = ss.getSheetByName("TABLE");
tableSheet.getRange("A1:D20").clear();
var images = tableSheet.getImages();
for (var i = 0; i < images.length; i++)
{
var img = images[i];
img.remove();
}
}
function addRecord(count, team, logo) {
var ss= SpreadsheetApp.getActiveSpreadsheet();
var tableSheet = ss.getSheetByName("TABLE");
var currentRow = tableSheet.getLastRow();
var nextRow = currentRow + 1;
tableSheet.setRowHeight(nextRow, 30);
tableSheet.getRange(nextRow,1).setValue(count);
tableSheet.insertImage(logo, 2, nextRow);
tableSheet.getRange(nextRow,3).setValue(team);
tableSheet.getRange(nextRow,4).setValue(new Date());
}
Related Posts
![Back Up Google Drive Files](https://codewithcurt.com/wp-content/uploads/2023/04/Back-Google-Drive-Files-WS-150x150.png)
![Embed Chart Google Sheets](https://codewithcurt.com/wp-content/uploads/2022/11/Embed-Chart-GS-WS-150x150.png)
![Search Google Drive Google Sheets](https://codewithcurt.com/wp-content/uploads/2022/11/Search-Google-Drive-GS-WS-150x150.png)
![Create Custom Menu Google Sheets](https://codewithcurt.com/wp-content/uploads/2022/10/Create-Custom-Menu-GS-WS-150x150.png)
![Get and Set Value Google Sheets](https://codewithcurt.com/wp-content/uploads/2022/07/Get-and-Set-Value-GS-WS-150x150.png)
![Create Data Validation Google Sheets](https://codewithcurt.com/wp-content/uploads/2022/04/Create-Data-Validation-GS-WS-150x150.png)
![Stock Trend Analysis Google Sheets](https://codewithcurt.com/wp-content/uploads/2022/04/Stock-Trend-Analysis-GS-WS-150x150.png)
![Scrape Amazon Website Googe Sheets](https://codewithcurt.com/wp-content/uploads/2022/03/Scrape-Amazon-Website-GS-WS-150x150.png)
![Alert Popups on Google Sheets](https://codewithcurt.com/wp-content/uploads/2022/03/Alert-Popups-on-GS-WS-150x150.png)
![Combine Multiple Spreadsheets Google Sheets](https://codewithcurt.com/wp-content/uploads/2022/02/Combine-Multiple-Spreadsheets-GS-WS-150x150.png)
![Last Row and Column Google Sheets](https://codewithcurt.com/wp-content/uploads/2022/04/Last-Row-and-Column-GS-WS-150x150.png)
![Time and Distance Map Google Sheets](https://codewithcurt.com/wp-content/uploads/2022/02/Time-and-Distance-GS-WS-150x150.png)
![Create Dependent Dropdown Google Sheets](https://codewithcurt.com/wp-content/uploads/2022/01/Create-Dependent-Dropdown-GS-WS-150x150.png)
![Call Rest API Google Sheets](https://codewithcurt.com/wp-content/uploads/2022/01/Call-Rest-API-GS-WS-150x150.png)
![Google Doc From Apps Script](https://codewithcurt.com/wp-content/uploads/2021/12/Google-Doc-From-Apps-Script-WS-150x150.png)
![How to Clear, Delete, Insert, and Replace Row Google Sheets](https://codewithcurt.com/wp-content/uploads/2021/12/How-to-Clear-Delete-Insert-and-Replace-Row-GS-WS-150x150.png)
![Email Last Update Google Sheet](https://codewithcurt.com/wp-content/uploads/2021/12/Email-Last-Update-GS-WS-150x150.png)
![JSON Web Service Google Sheets](https://codewithcurt.com/wp-content/uploads/2021/11/Web-Service-GS-WS-150x150.png)
![Query Function Google Sheets](https://codewithcurt.com/wp-content/uploads/2021/02/Query-Function-WS-150x150.png)
![Copy Row Google Sheet to Google Sheet](https://codewithcurt.com/wp-content/uploads/2021/02/Copy-Row-Sheet-to-Sheet-WS-150x150.png)