This post may contain affiliate links, please check out our disclosure policy here about affiliate links.
In this video, I demonstrate how to Scrape Amazon Website to Google Sheets using software called ScraperAPI.
How to Video:
Video Notes:
- ScraperAPI Documentation, API Key, and Website
- Check out another Scraping Website Post. Click Here
Code in Video:
function mainFunction()
{
clearRecords();
var ss= SpreadsheetApp.getActiveSpreadsheet();
var tableSheet = ss.getSheetByName("TABLE");
var settingsSheet = ss.getSheetByName("SETTINGS");
var searchValue = tableSheet.getRange(1,2).getValue();
var api_key = settingsSheet.getRange(1,2).getValue();
var url = "http://api.scraperapi.com";
url += "?api_key="+api_key;
url += "&autoparse=true";
url += "&url=https://www.amazon.com/s?k="+searchValue;
Logger.log('url: ' + url);
//CALL API
var response = UrlFetchApp.fetch(url);
var json = response.getContentText();
var data = JSON.parse(json);
Logger.log('content: ' + data);
Logger.log('results: ' + data.results.length);
var item_count = 1;
for (var i = 0; i < data.results.length; i++)
{
var results = data.results;
var name = results[i].name;
var image = results[i].image;
var list_url = results[i].url;
var stars = results[i].stars;
var price = results[i].price;
var reviews = results[i].total_reviews;
if(stars == null)
{
stars = 0;
}
if(reviews == null)
{
reviews = 0;
}
addRecord(item_count, 1, name, image, list_url, stars, reviews, price);
item_count++;
}
var pages = data.pagination;
if(pages.length > 0)
{
for (var x = 0; x < pages.length; x++)
{
Utilities.sleep(5000);
var url = "http://api.scraperapi.com";
url += "?api_key="+api_key;
url += "&autoparse=true";
url += "&url="+pages[x];
//CALL API
var response = UrlFetchApp.fetch(url);
var json = response.getContentText();
var data = JSON.parse(json);
for (var i = 0; i < data.results.length; i++)
{
var results = data.results;
var name = results[i].name;
var image = results[i].image;
var list_url = results[i].url;
var stars = results[i].stars;
var price = results[i].price;
var reviews = results[i].total_reviews;
if(stars == null)
{
stars = 0;
}
if(reviews == null)
{
reviews = 0;
}
addRecord(item_count, x+2, name, image, list_url, stars, reviews, price);
item_count++;
}
}
}
}
function clearRecords()
{
var ss= SpreadsheetApp.getActiveSpreadsheet();
var tableSheet = ss.getSheetByName("TABLE");
tableSheet.getRange("A4:H1000").clear();
}
function addRecord(count, page, name, image, list_url, stars, reviews, price) {
var ss= SpreadsheetApp.getActiveSpreadsheet();
var tableSheet = ss.getSheetByName("TABLE");
tableSheet.appendRow([count,
page,
name,
image,
list_url,
stars,
reviews,
price]);
}
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)
![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)
![Scrape Website Google Sheet](https://codewithcurt.com/wp-content/uploads/2021/02/Scrape-Website-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)