Connect Amazon Selling Partner API (SP-API) To Google Sheets

How to connect the Amazon SP API to Google Sheets

SP-API is a suite of REST-based APIs that provides Amazon selling partners programmatic access to their Amazon Seller Central account data. We can use the Amazon SP API to import data from Amazon Seller Central into Google Sheets. In this tutorial, we’ll show you how to connect the Amazon SP API to Google Sheets in 8 steps:

Install the Apipheny add-on
Create and configure your IAM policies and entities
Registering your application
Authorizing Selling Partner API applications
Request a Login with Amazon access token
Choose a Amazon SP API endpoint
Enter your Amazon SP API request into Apipheny
Run the Amazon SP API Request in Your Google Sheet

Step 1.) Install and open the Apipheny add-on for Google Sheets

Apipheny is an API connector for Google Sheets. You can use Apipheny to connect your Google Sheets to unlimited API data sources, make unlimited API requests, and more. There is a 30 day free trial included.

1.) Install Apipheny by opening the following link on desktop and then clicking the Install button in the Google Marketplace: https://apipheny.io/install

2.) After you’ve installed Apipheny, open a Google Sheet and then click on the Extensions dropdown in the menu at the top.

In the dropdown list you should see Apipheny. Click Apipheny then click Import API to open the Apipheny sidebar in your Google Sheet.

Tip: you can open a new Google Sheet by entering this URL in your browser: sheet.new

Step 2.) Create and configure your IAM policies and entities

a) Create an AWS account

You need an AWS account because the Selling Partner API (Amazon Shipping API) security model uses AWS authentication credentials. If you’re not already an AWS customer, you can create a free AWS account. See AWS Free Tier.

b) Create an IAM policy

This IAM policy defines the permissions required to make calls to the Selling Partner API. Attach this policy to the IAM User that you create in c) section.

Use the following procedure to create an IAM policy

Sign in to the AWS Management Console, and then open the IAM console at console.aws.amazon.com/iam.
From the left navigation pane, select Policies.

3. If this is your first-time choosing Policies, the Welcome to Managed Policies page appears. Choose Get Started.

4. Select Create policy.

5. Choose the JSON tab.

6. Paste the following code into the text box (replacing the existing code), and then choose Next: Tags.

7. Add tags to your IAM policy – optional, then click the Create policy button.

8. Your IAM policy will be created and displayed on the screen:

The Policies section with the new created policy

c) Create an IAM user

Create an IAM user to get AWS keys to authenticate calls to the Selling Partner API. We recommend creating a new IAM user exclusively for this purpose.

To create an IAM user

If you are not already signed in, sign into the AWS Management Console and open the IAM console at console.aws.amazon.com/iam.
In the navigation pane at left, click Users and then click Add user.

3. Enter a user name for the new user then click Next. We recommend you follow the naming convention AmazonShipping_<Developer/ShipperName>. Example: ApiphenyIAMUser

4. On the Set Permissions page, click on Attach existing policies directly

5. Search your newly created policy, select it and then click Next:

6. On the Add tags (optional) page, add optional tags if you want them, else, click Create user.

7. Your new IAM user is displayed.

The Users section with the newly created user

8. To create an access key, click the username of your IAM user, then go to the Security credentials tab and click the Create access key button:

On the next screen choose Third-party service, check the Confirmation box and click the Next button:

Set a description for your key and then click the Create access key button:

Setting a description for the access key

Your access key is now displayed on the screen. Click the Download .csv file button to save it on safe location, then click the Done button:

The access key is displayed on the screen

To locate the User IAM ARN, please use navigation pane at left and click on Users. Click on the newly created user to get the IAM ARN required during App creation process

Step 3.) Registering your application

Sign into Seller Central using the credentials that you used to register as a developer.
In the Partner Network menu, click Develop Apps.

The Developer Central page appears.

Click Add new app client.

The App registration page appears.

Complete the form.

Note. If you are registering a public application, a Sellers check box and a Vendors check box appear after you choose the API type. Select Sellers, Vendors, or both, depending on the type of selling partner your application is for. The list of roles for which you can apply vary depending on your selection.

Step 4.) Authorizing Selling Partner API applications

To self-authorize your application (seller application)

Sign into Seller Central using the credentials that you used to register as a developer.
In the Partner Network menu, click Develop Apps.

The Developer Central page appears.

For the application that you want to authorize, click the arrow next to Edit App, and then click Authorize.

The Authorize application page appears.

Note. If your selling partner account is merged with accounts in other regions, you will see an Authorize app button for each of your merged accounts.

Click Authorize app for each selling partner account that you want your application to access. The Marketplaces column indicates the marketplaces in which an account is active.

A Login with Amazon (LWA) refresh token appears with every Authorize app button that you click.

Note. If you click an Authorize app button multiple times, a new refresh token is generated each time. Generating a new refresh token does not invalidate previous refresh tokens.

Save a refresh token for each selling partner account that you authorized your application to access. Later you can exchange these refresh tokens for access tokens. When you call a Selling Partner API to access a selling partner account, include the access token that corresponds to that account.
To authorize your application to access a different selling partner account, click sign in to that account at the bottom of the page.

A Seller Central sign-in page appears.

The Authorize application page appears.

Click Authorize app to get a refresh token.

Step 5.) Request a Login with Amazon access token

A Login with Amazon (LWA) access token authorizes your application to take certain actions on behalf of a selling partner. An LWA access token expires one hour after it is issued.

To request an LWA access token, make a secure HTTP POST to the LWA authentication server.

Open a command prompt window (for Windows OS), or a terminal (for Mac OS) and write the following command:

curl -X POST https://api.amazon.com/auth/o2/token --header "Content-Type=application/x-www-form-urlencoded;charset=UTF-8" --data "grant_type=client_credentials&scope=sellingpartnerapi::notifications&client_id=YOUR_CLIENT_ID&client_secret=YOUR_CLIENT_SECRET"

Don’t forget to replace:

YOUR_CLIENT_ID – with a real client ID;

YOUR_CLIENT_SECRET – with a real client secret.

You will get a response in this format containing your access token:

HTTP/l.l 200 OK
Content-Type: application/json;charset UTF-8
Cache-Control: no-store
Pragma:no-cache
{
  "access_token":"token_value",
  "token_type":"bearer",
  "expires_in":3600,
  "refresh_token":"Atzr|IQEBLzAtAhRPpMJxdwVz2Nn6f2y-tpJX2DeXEXAMPLE"
}

token_value will be replaced with a real token.

Save it to a safe location because you’ll need it later.

Step 6.) Choose a Amazon SP API endpoint

In this section, we’ll show you how to browse the Amazon SP API documentation to find an endpoint that retrieves the information you need. If you already know your API URL, or you want to use the same example URL as us, just skip to Step 7.

First, open the Amazon SP API documentation page: https://developer-docs.amazon.com/sp-api/docs

Scroll down the left menu until you find a list of APIs: A+ Content API, Authorization API, Easy Ship API, Fulfillment By Amazon (Fba), Feeds Api, Finances Api, Fulfillment Inbound Api, Fulfillment Outbound Api, Listings, Merchant Fulfillment Api, Messaging Api, Notifications Api, Orders Api, Product Fees Api, Product Pricing Api, Replenishment Api, Reports Api, Sales Api, Sellers Api, Services Api, Shipment Invoicing Api, Shipping Api, Solicitations Api, Tokens Api, Uploads Api, Vendor Direct Fulfillment Apis, Vendor Retail Procurement Apis.

If we choose the the Orders API and click on the Orders API v0 reference link, then a list of its endpoints will be displayed on the main section of the window:

An example endpoint is getOrders, which returns orders created or updated during the time frame indicated by the specified parameters. The URL for this endpoint is:

https://sellingpartnerapi-na.amazon.com/orders/v0/orders

Each endpoint’s documentation contains a descriptive title, the HTTP method (GET) and URL, a paragraph describing the endpoint’s purpose, the query parameters, as well as an example response:

All API calls to Amazon API should be made to the https://sellingpartnerapi-na.amazon.com (for North America), https://sellingpartnerapi-eu.amazon.com (for Europe) or https://sellingpartnerapi-fe.amazon.co (for Far East) base domains. All endpoints should be appended to these base URLs.

Step 7.) Enter your SP-API request into Apipheny

Now, to get your Amazon SP data in your Google Sheet, go back to your Google Sheet and make sure that you’ve installed the Apipheny add-on and you have it opened on the Import tab. In the Import tab of Apipheny, enter the following details into the add-on:

Method: At the top of the Apipheny sidebar, select the HTTP method (GET, POST, PUT, PATCH or DELETE) required by your Amazon SP API endpoint. For this example, we are using the “PUT” method.

API URL: In Step 6, we explained how you can find the Amazon SP API endpoint that you need. Now copy your complete Amazon SP API URL into the Apipheny add-on, in the field that says API URL Path, followed by any POST parameters required for your query (if applicable).

For this example, we are using the PUT /fba/inbound/v0/shipments/{shipmentId} endpoint, which updates or removes items from the inbound shipment identified by the specified shipment identifier. The URL for this specific endpoint is:

https://sellingpartnerapi-na.amazon.com/fba/inbound/v0/shipments/shipmentId1/preorder/confirm?MarketplaceId=marketID&NeedByDate=DATE

Don’t forget to replace shipmentId1, marketID and DATE with real values.

Headers: In the Headers section of the Apipheny add-on, add four rows with the following keys and values:

Header 1 Key:
x-amz-access-token
Header 1 Value:
your_token

Header 2 Key:
x-amz-date
Header 2 Value:
DATE

Header 3 Key:
user-agent
Header 3 Value:
userValue

Replace these with real values in your Headers section:

your_token	The LWA access token.
DATE	The date and time of your request.
userValue	Your application name and version number, platform, and programming language. These help Amazon diagnose and fix problems you might encounter with the service. See Include a User-Agent header in all requests.

Amazon SP API request entered in the Apipheny add-on

Sign your request

The Selling Partner API uses the AWS Signature Version 4 Signing Process for authenticating requests. When you send HTTP requests to the Selling Partner API, you sign the requests so that Amazon can identify who sent them. You sign requests using your AWS access keys, which consists of an access key ID and a secret access key. Amazon recommends using the AWS Security Token Service (AWS STS) to request temporary AWS access keys to sign your requests.

You need to learn how to sign HTTP requests only when you manually create them. When you use the one of the AWS SDKs to calculate signatures for you, the SDK automatically signs the requests with the AWS access key that you specify when you configure it.

The following example shows what a request might look like after you’ve added the signing information to it using the Authorization header:

PUT /fba/inbound/v0/shipments/shipmentId1/preorder/confirm?MarketplaceId=ATVPDKIKX0DER&NeedByDate=2020-10-10HTTP/1.1
Authorization: AWS4-HMAC-SHA256 Credential=AKIAIHV6HIXXXXXXX/20201022/us-east-1/execute-api/aws4_request, SignedHeaders=host;user-agent;x-amz-access-token,
Signature=5d672d79c15b13162d9279b0855cfba6789a8edb4c82c400e06b5924aEXAMPLE
host: sellingpartnerapi-na.amazon.com
user-agent: My Selling Tool/2.0 (Language=Java/1.8.0.221;
Platform=Windows/10)
x-amz-access-token: Atza|IQEBLjAsAhRmHjNgHpi0U-Dme37rR6CuUpSREXAMPLE
x-amz-date: 20190430T123600Z

Step 8.) Run the Amazon SP API Request in Your Google Sheet

The last step is to click the Run button at the bottom of the Apipheny add-on and then your Amazon SP data will be imported into your Google Sheets.