Categories
Code

Search Books with Goodreads API and Google Apps Script

The Goodreads API helps you query the entire database of books on the Goodreads website. You can find the ratings of books, fetch book reviews, search books by author or even publish your own reviews. This example shows how to connect to the GoodReads website through Google Apps Script, find books by title, parse the XML results as JSON and write the results in a Google Spreadsheet.

You can also extend the code to insert the thumbnail of the book image in a spreadsheet cell using the IMAGE function.

To get started, go to the Goodreads.com account and create a key. All Rest API methods will require you to register for a developer key.

Goodreads will return the response in XML format (see below) and we can use the XML service of Google Apps Script to easily parse this XML response.

Here’s the complete example. Remember to replace the API key with your own.


function GoodReads() {
  var search = "Taj Mahal";
  var books = searchBooks_(search);  

  // Write Data to Google Spreadsheet.
  var sheet = SpreadsheetApp.getActiveSheet();
  books.forEach(function(book) {
    sheet.appendRow([book.title, book.author, book.rating, book.url]);
  });
}

function searchBooks_(query) {
  
  var baseUrl = "https://www.goodreads.com/book/show/",
      apiUrl = "https://www.goodreads.com/search/index.xml",
      apiKey = "ctrlq.org",
      searchResults = [],
      payload = {
        q: query,
        key: apiKey
      },
      params = {
        method: "GET",
        payload: payload,
        muteHttpExceptions: true
      };
  
  var response = UrlFetchApp.fetch(apiUrl, params);
  
  // API Connection Successful
  if (response.getResponseCode() === 200) {
    
    // Parse XML Response
    var xml = XmlService.parse(response.getContentText());
    var results = xml.getRootElement().getChildren('search')[0];
    
    // Save the result in JSON format
    results.getChild('results').getChildren().forEach(function(result) {
      result.getChildren('best_book').forEach(function(book) {
        searchResults.push({
          title: book.getChild('title').getText(),
          author: book.getChild('author').getChild('name').getText(),
          thumbnail: book.getChild('image_url').getText(),
          rating: result.getChild("average_rating").getText(),
          url: baseUrl + result.getChild("id").getText()
        });
      });
    });
    
  }

  return searchResults;
  
}
Categories
Code

Get the Amazon Sales Rank by ASIN in Google Spreadsheet

Given the Amazon ASIN number, the getAmazonSalesRank() method with return the overall Sales Rank of the item using the Amazon Product Advertising API. You’ll need to enter your own Amazon AWS keys and associate ID before making the API calls.

This can also be converted into a custom function for Google Spreadsheets where you can enter the product ASINs in one column the latest sales rank is displayed in another column. It is however recommended that you either use the Cache Service or store the results in Property Service to avoid making too many calls to the Amazon API.

function getAmazonSalesRank(asin) {

  try {

    var method = "GET",
        uri    = "/onca/xml",
        host   = "ecs.amazonaws.com",
        public_key    = "YOUR_PUBLIC_KEY",
        private_key   = "YOUR_PRIVATE_KEY",
        associate_tag = "YOUR_AMAZON_ASSOCIATE_ID";

    var params = {
      Service        : "AWSECommerceService",
      Version        : "2011-08-01",
      AssociateTag   : associate_tag,
      Operation      : "ItemLookup",
      ItemId         : asin,
      Timestamp      : new Date().toISOString(),
      AWSAccessKeyId : public_key,
      ResponseGroup  : "SalesRank"
    };

    var canonicalized_query = Object.keys(params).sort();

    canonicalized_query = canonicalized_query.map(function(key){
      return key + "=" + encodeURIComponent(params[key]);
    });

    var string_to_sign = method + "\n" + host + "\n" + uri + "\n" + canonicalized_query.join("&");

    var signature = Utilities.base64Encode
    (Utilities.computeHmacSha256Signature(string_to_sign, private_key));

    var request = "http://" + host + uri + "?" 
                     + canonicalized_query.join("&") + "&Signature=" + encodeURIComponent(signature);

    var response = UrlFetchApp.fetch(request, {muteHttpExceptions: true}).getContentText();

    var elems = XmlService.parse(response).getDescendants();

    for(var i=0; i

		
Categories
Code

Parse XML Response of Amazon API with Google Scripts

The Amazon API returns the response in XML format with the ItemAttributes node containing the bulk of the information about the product including the title, author’s name, list price, physical dimensions and so on.


  B000A2XB9U
  
    Rockin' the Corps
    014381273229
    
      1999
      USD
      $19.99
    
  

The XMLService of Google Apps Script can be used to parse the response group as shown below:


function parseAmazonXML(xml){

  var obj = {};
  var xmlData = XmlService.parse(xml);
  
  var element = xmlData.getElement();
  var items = element.getElement("Items").getElements("Item");
  
  for (var i = 0; i 

		
Categories
Code

Get Book Details from Amazon with Google Apps Script

Google Apps Script can connect to the Amazon Product Advertising API to get the product details, price and availability of any book (by ISBN) or any other item listed on the Amazon website (by ASIN). You would need to sign-up for a free AWS (Amazon Web Services) account and also key in your Amazon Associate tag, the AWS Access ID (or SubscriptionID) and the AWS Secret Access key.

The script is for Amazon US (region set to com) but it would work for other Amazon country website as well though your Associate Tag may be different for different Amazon Website. See the Amazon Price Tracker to see the code in action.

function AmazonAPI(isbn) {
  
  var region = "com",
      method = "GET",
      uri    = "/onca/xml",
      host   = "ecs.amazonaws." + region;

  var private_key   = "AWS Secret Access Id",
      public_key    = "AWS Access Key",
      associate_tag = "labnol-20";
  
  var params = {
    Service        : "AWSECommerceService",
    Version        : "2011-08-01",
    AssociateTag   : associate_tag,
    Operation      : "ItemLookup",
    SearchIndex    : "Books",
    ItemId         : isbn,
    Timestamp      : new Date().toISOString(),
    AWSAccessKeyId : public_key,
    IdType         : "ISBN",
    ResponseGroup  : "ItemAttributes"
  };
  
  var canonicalized_query = Object.keys(params).sort();
  
  canonicalized_query = canonicalized_query.map(function(key){
    return key +"="+encodeURIComponent(params[key]);
  });
  
  var string_to_sign = method + "\n" + host + "\n" + uri + "\n" + canonicalized_query.join("&");
  
  var signature = Utilities.base64Encode
                     (Utilities.computeHmacSha256Signature(string_to_sign, private_key));
  
  var request   = "http://" + host + uri + "?" + canonicalized_query.join("&") 
                     + "&Signature=" + encodeURIComponent(signature);
  
  var response  = UrlFetchApp.fetch(request);
  
  return XmlService.parse(response.getContentText());
  
}

function getBookInfo(){

  var isbn = "xyz"; // Put the 10 or 13 digital ISBN here
  
  var o = {};
  var response = AmazonAPI(isbn);
  var a = response.getDescendants();
  
  for(var i=0;i

		
Categories
Code

Create RSS Feeds from Amazon Listings

If an Amazon Category page does not offer RSS feeds, you can use PHP to easily create one. The PHP snippet below will download a page from the Amazon website and converts the products into an RSS feed so you can easily track when new items are added to that category.

The RSS feed will have the product name, the title, the permalink (URL) and the thumbnail image.

';
  $head .= '';
  $head .= '';
  $head .= 'Amazon ';
  $head .= 'http://www.amazon.com';
  $head .= 'Amazon RSS Feed';
   
  $url = "http://www.amazon.com/Best-Sellers-Kindle-Store/zgbs/digital-text/";
   
  $text = file_get_html($url); 

  foreach ($text->find("div.zg_item_compact") as $class) {
    foreach ($class->find('strong.price') as $price) {
      if ($price->plaintext == "Free") {
              $rssfeed .= '';
              foreach ($class->find("div.zg_title a") as $book) { 
                preg_match("/\/dp\/(.*)\/ref/", $book->href, $matches);                
                $ASIN  = trim($matches[1]);
                $url   = "http://www.amazon.com/dp/" . $ASIN . "/?tag=ctrlqorg-20";
                $img   = "http://images.amazon.com/images/P/" . $ASIN . ".01.LZZZZZZZ.jpg";
                $title = htmlentities(trim($book->plaintext));                
                $rssfeed .= '' . $title . '';
                $rssfeed .= '' . $url . '';
                $rssfeed .= '' . $url . '';
                $rssfeed .= '';
              }
              foreach ($class->find("div.zg_byline a") as $author) {
                  $bio  = "http://www.amazon.com" . $author->href . "/?tag=publisherapi-20";
                  $name = htmlentities(trim($author->plaintext));
                  $rssfeed .= 'By ' . $name . '';
              }
              $rssfeed .= '';
              $rssfeed .= '' . $now . '';
              $rssfeed .= '';
          }
      }
  }

  $footer  = '';
  $rssfeed = $head . $rssfeed . $footer;
  $fh      = fopen("amazon.rss", "w");

  fwrite($fh, $rssfeed);
  fclose($fh);

?>
Categories
Code

Web Scraping Amazon with PHP

There are two ways to get the price of any product listed on the Amazon website. You can either use the official Amazon API or you can perform web scraping.

Scraping essentially means that your script will download the web page from Amazon, parse the HTML and extract the price. This method is preferred when the Product Advertising API doesn’t have the prices for a particular product or when the price offered by the API is different from what is available on the Amazon website.

(Prezzo|Precio|Price|Prix Amazon|Preis):?\<\/b\>([^\<]+)/i';

    /* Return the price */

    if (preg_match($regex, $html, $price)) {
        $price = number_format((float)($price[2]/100), 2, '.', '');
        echo "The price for amazon.com/dp/$amazonISIN is $price";
    } else {
        echo "Sorry, the item is out-of-stock on Amazon";
    }

?>

All you have to do is specify the 10 digital Amazon ISIN code for a product and the PHP script will get the price for you.

Categories
Code

Use the Amazon API to Get Product Prices

You can use the Amazon Advertising API (AWS) to fetch the current prices, item description, customer reviews, product images, quantity available and other details of any product listed on various Amazon websites.

The getAmazonPrice() method takes the region (like “com” for US or “es” for Spain) and the 10-digit ASIN of any product. The response is returned as JSON that the Amazon Price Tracker can easily parse with Google Scripts. You cannot however use this technique for fetching prices of Kindle Books as the Amazon API doesn’t support them yet.

 "ItemLookup",
		"ItemId" => $asin,
		"IncludeReviewsSummary" => False,
		"ResponseGroup" => "Medium,OfferSummary",
	));

	$item = $xml->Items->Item;
	$title = htmlentities((string) $item->ItemAttributes->Title);
	$url = htmlentities((string) $item->DetailPageURL);
	$image = htmlentities((string) $item->MediumImage->URL);
	$price = htmlentities((string) $item->OfferSummary->LowestNewPrice->Amount);
	$code = htmlentities((string) $item->OfferSummary->LowestNewPrice->CurrencyCode);
	$qty = htmlentities((string) $item->OfferSummary->TotalNew);

	if ($qty !== "0") {
		$response = array(
			"code" => $code,
			"price" => number_format((float) ($price / 100), 2, '.', ''),
			"image" => $image,
			"url" => $url,
			"title" => $title
		);
	}

	return $response;
}

function getPage($url) {

	$curl = curl_init($url);
	curl_setopt($curl, CURLOPT_FAILONERROR, true);
	curl_setopt($curl, CURLOPT_FOLLOWLOCATION, true);
	curl_setopt($curl, CURLOPT_RETURNTRANSFER, true);
	curl_setopt($curl, CURLOPT_SSL_VERIFYHOST, false);
	curl_setopt($curl, CURLOPT_SSL_VERIFYPEER, false);
	$html = curl_exec($curl);
	curl_close($curl);
	return $html;
}

function aws_signed_request($region, $params) {

	$public_key = "PUBLIC_KEY";
	$private_key = "PRIVATE_KEY";

	$method = "GET";
	$host = "ecs.amazonaws." . $region;
	$host = "webservices.amazon." . $region;
	$uri = "/onca/xml";

	$params["Service"] = "AWSECommerceService";
	$params["AssociateTag"] = "affiliate-20"; // Put your Affiliate Code here
	$params["AWSAccessKeyId"] = $public_key;
	$params["Timestamp"] = gmdate("Y-m-d\TH:i:s\Z");
	$params["Version"] = "2011-08-01";

	ksort($params);

	$canonicalized_query = array();
	foreach ($params as $param => $value) {
		$param = str_replace("%7E", "~", rawurlencode($param));
		$value = str_replace("%7E", "~", rawurlencode($value));
		$canonicalized_query[] = $param . "=" . $value;
	}

	$canonicalized_query = implode("&", $canonicalized_query);

	$string_to_sign = $method . "\n" . $host . "\n" . $uri . "\n" . $canonicalized_query;
	$signature = base64_encode(hash_hmac("sha256", $string_to_sign, $private_key, True));
	$signature = str_replace("%7E", "~", rawurlencode($signature));

	$request = "http://" . $host . $uri . "?" . $canonicalized_query . "&Signature=" . $signature;
	$response = getPage($request);

	var_dump($response);

	$pxml = @simplexml_load_string($response);
	if ($pxml === False) {
		return False;// no xml
	} else {
		return $pxml;
	}
}

?>

You’ll have to get your public and private access keys from the AWS Management Console and replace them in the PHP snippet. You also need to put your Amazon Affiliate ID for the country specific Amazon Affiliate program. For instance, the id for Amazon US would be different from Amazon UK though you can use the same Access Keys with the code.

Categories
Code

Import Amazon RSS Feeds into Google Sheets

This Google Script fetches and parse the XML feed for the Amazon Best Sellers (books) list and imports the list into the current active Google Spreadsheet.

It uses the Google Feeds API to load the Amaozn XML feed as JSON and parses the JSON results using the built-in Utilities.jsonParse method of Apps Script.

function parseAmazon() {

  var url = "http://www.amazon.com/gp/rss/bestsellers/books?num=10&tag=ctrlq-20";
  
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  sheet.clear();
  
  var response = UrlFetchApp.fetch("https://ajax.googleapis.com/ajax/services/feed/load?v=1.0&q=" + encodeURIComponent(url));
  var amazon = Utilities.jsonParse(response.getContentText());

  Logger.log(amazon.responseData.feed.entries[0].publishedDate);
  
  var headerNames = ["Book Name", "Amazon URL"];
  var headRange = ss.getActiveSheet().getRange("A1:B1");
  headRange.setValues([headerNames]);
  headRange.setHorizontalAlignment("center");
  headRange.setFontWeight("bold");

  for(var i=0; i