Import Amazon Seller Data to Google Sheets

In this guide, we’ll walk through how to import Amazon Seller data directly into Google Sheets, using the API Connector add-on for Sheets.

Connecting to Amazon used to be an extremely complicated process that required setting up AWS Identity and Access Management (IAM) and authenticating via AWS Signature Version 4. Thankfully, starting October 2023, Amazon greatly simplified their API such that neither of the above are now required (info). However, their process is still a bit convoluted, and much of the documentation online (both on and off their website) is out of date. This guide will attempt to demystify the process and walk through how to connect and get Amazon Seller data in Sheets!

Contents

• Before You Begin
• Part 1: Create an Amazon App
• Part 2: Get your Amazon Access Token
• Part 3: Pull Amazon Seller Data into Sheets
• Part 4: Get Order Item Data
• Part 5: Handle Pagination
• Part 6: API Documentation

Before You Begin

Click here to install the API Connector add-on from the Google Marketplace.

Part 1: Create an Amazon App

1. Log into Amazon Seller Central: https://sellercentral.amazon.com/home
2. From the left hand menu, click on Apps and Services > Develop Apps (or navigate there directly: https://sellercentral.amazon.com/sellingpartner/developerconsole)
   
3. If you haven't already completed a Developer Profile for your selling account, choose Proceed to Developer Profile, which will open a form asking for your contact information and how you intend to use the API. A few notes to get immediate access:
   • From the Data Access dropdown, choose Private Developer: I build application(s) that integrate my own company with Amazon Services APIs.
   • Do not select any of the "restricted" roles (if you select restricted roles, you will need to provide additional rationales and receive approval from Amazon). For more information about Role definitions, refer to Roles in the Selling Partner API.
4. Once your developer profile is ready, click +Add new app client
   
   
5. You will be prompted to give your app a name (any name will do), select the API type (should be SP API), and select all the roles you'd like your app to have access to.
   
6. At the bottom of the page you'll be asked "Will you delegate access to PII to another developer's application?" You'll need to provide additional information if you select Yes. For simplicity, we'll select No here. Click Save and exit.
   
7. You will now see your new app on the list. Under LWA credentials, click View
   
   
8. You'll be presented with your Client identifier (aka "Client ID") and Client secret. Copy these and keep them handy as we'll need both of these values shortly.
   
9. We still need to authorize the credentials. Back on the list of apps, click Authorize
   
   
10. Find your app and click Authorize App
    
11. You'll now see your refresh token. Click Copy
    
12. We now have everything we need! Gather your client ID, client secret, and refresh token and proceed to the next section.
    

Part 2: Get Your Amazon Access Token

To get your access token, set up a request as follows:

• Application: Custom
• Method: POST
• Request URL: https://api.amazon.com/auth/o2/token
• Request body: {"grant_type":"refresh_token","refresh_token":"your_refresh_token","client_id":"your_client_id","client_secret":"your_client_secret"}

Substitute in your own refresh token, client ID, and client secret, and set your data destination to a tab called AccessToken. The whole request should look like this:

Note the value in the access_token field. We can use this to get data from the Amazon Seller API. It will expire in 3600 seconds (1 hour), but we can run this request whenever we need a new token. If you are running these requests on a schedule, set the request order such that the access token request always runs first.

Part 3: Get Amazon Seller Data in Sheets

To create requests, we'll need to plug in 2 region-specific values:

• Your API base domain, which is available from here.
• Your marketplace ID, which is available here.

You can run requests to any endpoints shown in the documentation, but for this example, we'll fetch orders from a store using the US marketplace. Substitute in the base domain and marketplace ID for your own region. The token header references a cell, so that you don't need to update the value every time you refresh your token.

• Application: Custom
• Method: GET
• Request URL: https://sellingpartnerapi-na.amazon.com/orders/v0/orders?MarketplaceIds=ATVPDKIKX0DER&CreatedAfter=2024-01-01
• Headers
  • Key = x-amz-access-token, Value = +++AccessToken!A2+++
    

Note that the example response above contains no order data, since my Amazon Seller account isn't used for selling. If you're in the same situation and want to test out some endpoints, you can fetch a sample response by running a request to Amazon's Sandbox API. You'll use the same access token.

• Application: Custom
• Method: GET
• Request URL: https://sandbox.sellingpartnerapi-na.amazon.com/orders/v0/orders?MarketplaceIds=ATVPDKIKX0DER&CreatedAfter=TEST_CASE_200
• Headers
  • Key = x-amz-access-token, Value = +++AccessToken!A2+++
    
    

Part 4: Get Order Item Data

The main /orders endpoint returns order summary data only. To get detailed data about the items within the order, Amazon provides additional endpoints like these

• /orders/v0/orders/{orderId} //(return the order that you specify)
• /orders/v0/orders/{orderId}/orderItems //(get detailed order item info)

Since each URL can accept only a single order ID, you'll need to run a multi-query request that cycles through your list of order IDs. Here's one approach:

1. Run the order data request as described above. Name the destination tab Data.
2. The response sheet will contain a table of order data, with order IDs listed in a column titled payload.Orders.AmazonOrderId
   
3. Create a new request that references this list of order IDs. Substitute in your own base domain as well as the location of the cells containing your order IDs:
   • Application: Custom
   • Method: GET
   • Request URL: https://sellingpartnerapi-na.amazon.com/orders/v0/orders/+++Data!C2:C20+++
   • Headers
     • Key = x-amz-access-token, Value = +++AccessToken!A2+++
4. This request will cycle through your list of order IDs, fetching SKUs and other data for each one. Just be aware that Sheets can run requests for only about 6 minutes before timing out, so this method will not work for very large sets of data.

Part 5: Handle Pagination

1. The Amazon Seller API only returns 100 records per page.
   
   
2. To get more results, we can use pagination handling to loop through multiple pages of data.
   • Pagination type: cursor
   • Next token parameter: NextToken
   • Next token path: NextToken 
   • Run until: choose when to stop fetching data
     
     

Part 6: API Documentation

Official API documentation: https://developer-docs.amazon.com/sp-api/docs/welcome

Orders documentation: https://developer-docs.amazon.com/sp-api/docs/orders-api-v0-reference