Completed Project
https://docs.google.com/spreadsheets/d/1JbF7ptNc6XgcEwxpoFU2RkJFKYefiPKxyglOUavbsy4/edit?usp=drivesdk (Set to View Only for security reasons)
Tools Needed
- Scraper API
- Google Sheets
- Google Apps Script
Guide
- Create or sign into your account with ScraperAPI. Head over to your dashboard and grab your ScraperAPI API key. ScraperAPI’s free plan affords you 5,000 calls for your first seven days on registration. Afterwards, yo get 1,000 calls on your free plan.
- Open up a new Google Sheets file. Mine’s named: Amazon Google Sheets Data. For convenience and forward compatibility with my script, i recommend that you strucure your G-Sheets as indicated in the picture. However, you can always make adjustments to your code if you'd rather not.
- For clarity, our column headers are: S/N, Page ID, Product Description, Product URL, Images, Rating, Reviews, and Price.
- For ease of use, the search key is expected in the B2 cell. Let's code up the script:
function mainFunction()
//Attach this function to a trigger of sorts in your Google Sheets
{
clearRecords();
var ss = SpreadsheetApp.getActiveSpreadsheet();
var tableSheet = ss.getSheetByName(‘TABLE’);
var settingSheet = ss.getSheetByName(‘SETTINGS’);
var searchValue = tableSheet.getRange(1,2).getValue();
var API_KEY = xxxxx;
var url = “http://api.scraperapi.com”;
url += “?api_key=”+api_key
url += “&autoparse=true”;
url += “&url=https://www.amazon.com/s?k=”+searchValue;
// fetch data from API
var response = UrlFetchApp.fetch(url);
var json = response.getContentText();
var data = JSON.parse(json);
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].price;
var reviews = results[i].total_reviews;
if (stars == null)
{
stars = 0;
}
if(reviews == null)
{
reviews = 0;
}
addRecord(item_count, 1, name, 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(2000);
var url = “http://api.scraperapi.com”;
url += “?api_key=”+api_key
url += “&autoparse=true”;
url += “&url=”+pages[x];
function clearRecords()
{
var ss= SpreadsheetApp.getActiveSpreadsheet();
var tableSheet = ss.getSheetByName(‘TABLE’);
tableSheet.gtRange(‘A4:H1000’).clear();
}
function addRecord(coun, 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]);
6 Afterwards, head over to your the sheets. Type in your search query into the designated cell. For us, that'd be Cell B2. Here's what our output looks like for a search query of 'Medieval Torces'.
Top comments (0)