Scrape Amazon Website to Google Sheets

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:

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

Get and Set Value Google Sheets How to Get and Set Values on Google Sheets using Apps Script - In this post, I demonstrate how to Get and Set Values using Google Apps Script on Google Sheets.
Web App Report Google Sheets How to Create Web App Report from Google Sheets - In this post, I demonstrate how to take a Web App and display an HTML report with input filters using Google Sheets. Also included in this video is the use of the QUERY formula for Google Sheets.
Create Data Validation Google Sheets How to Create Data Validation on Drop Down using Google Apps Script - In this post, I demonstrate How to Create Data Validation Drop-down List Using Google Apps Script on Google Sheets.
Stock Trend Analysis Google Sheets Create Stock Trend Analyzer on Google Sheets - In this video, I demonstrate how to analyze stock using the GOOGLEFINANACE Function on Google Sheets with Google Apps Script.
Alert Popups on Google Sheets Create Alert Popups on Google Sheets - In this post, I demonstrate how to create 3 different types of alert popups using Google Apps Script on Google Sheets.
Combine Multiple Spreadsheets Google Sheets Combine Multiple Spreadsheets into One Google Sheet - In this post, I demonstrate how to Combine Multiple Spreadsheets into one Sheet using Google Apps Script.
Last Row and Column Google Sheets How to Get Next Open Row and Column using Google Apps Script - In this post, I demonstrate how to get the next open row and column using Google Apps Script on Google Sheets.
Time and Distance Map Google Sheets How to Get Map Time and Distance using Google Apps Script - In this post, I demonstrate how to get the Map Time and Distance between locations using Google Apps Script using the Map Class.
Create Dependent Dropdown Google Sheets How to Create Dependent Dropdown on Google Sheets - In this post, I demonstrate how to Create a Dependent Dropdown list on Google Sheets using Google Apps Script.
Call Rest API Google Sheets How to Call Rest API on Google Sheets - In this post, I demonstrate how to call a Rest API using Google Apps Script and Displaying the data on a Google Sheet.
Google Doc From Apps Script How to Create Google Doc from Apps Script - In this post, I demonstrate how to create a Google Doc using Google Apps Script and save it in Google Drive. In this example, I use the data from a Google Sheet.
How to Clear, Delete, Insert, and Replace Row Google Sheets How to Clear, Delete, Insert, and Replace Row using Google Apps Script - In this post, I demonstrate how to clear, delete, insert, and replace rows using Google Apps Script on Google Sheets.
Email Last Update Google Sheet Email Last Update on Google Sheet - In this post, I demonstrate how to email the last update made on a google sheet. This is a way to monitor any changes made on Google Sheets
JSON Web Service Google Sheets How to Call JSON Web Service to Populate Google Sheet - In this post, I demonstrate how to Call a JSON Web Service and Populate a Google Sheet with a table of raw data.
Scrape Website Google Sheet Scrape Website Data to Google Sheets - 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.
Query Function Google Sheets Create Report Builder on Google Sheets using QUERY Function - In this post, I demonstrate how to use the QUERY function and Google Apps Script and build multiple report views from a list.
Copy Row Google Sheet to Google Sheet Copy Row from Sheet to Sheet using Apps Script on Google Sheets - In this post, I show how to copy a row of data from one sheet to another using Google Apps Script. This video also shows how to search a column on Google Sheets.
Updated Date Column Google Sheet Create Updated Date Column on Google Sheets - In this post, I demonstrate how to create an Updated Date Column on a Google Sheet. I use the onEdit(e) Google Apps Script function to accomplish the task.
How to Create Google Sheets Button to Run Apps Script - In this post, I show how to create a Button in Google Sheets as well as how to call a Googe Apps Script Function with the Button.