Blog post banner

Use the Amazon Product API with Gridarrow

April 12, 2018

The Amazon Product Advertising API is a world-class web service used by millions of customers every day.

It gives access to much of the same data that is used by Amazon including the items for sale, customer reviews and seller reviews. You can search for items by keyword, similarity to other products, or by navigating product categories.

What is Gridarrow?

Gridarrow is the missing server for Excel. It connects just about any data source to Excel with simple Python scripts.
Why is it awesome?
  • It's real-time, so as soon as the data is updated in the Gridarrow App it's updated in Excel, no need to poll or refresh.
  • It's multi-user so your colleagues can connect to the same data at the same time.
  • It simplifies your spreadsheets by moving data retrieval and pre-processing into Python.
Request Demo

Amazon in Excel

There is a really nice Python client available on Github, making integrating this API with Excel a breeze using Gridarrow.

Here’s a very simple example where we’ve used the API to search for similar items to the game Jenga!

Amazon prices for similar items

Scheduling updates

The Amazon Product API isn’t exactly what we’d call a highly dynamic “real-time” data source - but that doesn’t mean the data doesn’t change! Prices are updated, reviews get added, and new products are released. If you check the Python code in the example below you can see that we’ve used the @arrow.on_schedule annotation to make our data get updated every 3 hours between Monday and Friday.

But before you jump into it, remember to:

 1 2 3 4 5 6 7 8 910111213141516171819202122232425
import os
from amazon.api import AmazonAPI
from gridarrow import Arrow

arrow = Arrow(__name__)

amazon = AmazonAPI(os.environ['AMAZON_ACCESS_KEY'],
                   os.environ['AMAZON_SECRET_KEY'],
                   os.environ['AMAZON_ASSOC_TAG'])

item_id = os.environ['ITEM_IDS']

@arrow.on_schedule("0 */3 * * MON-FRI", timezone='US/Eastern', initialize=True)
def on_schedule(_):
    products = amazon.similarity_lookup(ItemId=item_id)

    if item_id in arrow.grids:
        arrow.grids[item_id].close()

    grid = arrow.grid(item_id, description=f"Similar items for {item_id}",
                      rows=len(products),
                      columns=['ASIN', 'Description', 'UPC', 'EAN', 'ISBN', 'Price', 'Currency'])

    for i, p in enumerate(products):
        grid[i, grid.columns] = [p.asin, p.title, p.upc, p.ean, p.isbn, *p.price_and_currency]