Connect Amazon Ads API To Google Sheets [Tutorial]

How to connect the Amazon API to Google Sheets

Amazon Ads offers a range of products and information to help you achieve your advertising goals, for registered sellers, vendors, book vendors, Kindle Direct Publishing (KDP) authors, app developers, and/or agencies (refer to each product’s page for eligibility criteria). We can use the Amazon Ads API to import data from Amazon Ads into Google Sheets. In this tutorial, we’ll show you how to connect the Amazon Ads API to Google Sheets in 9 steps:

Install the Apipheny add-on
Create a Login with Amazon application
Apply for permission to access the API – Application approval may take up to 72 hours
Assign API access to your LwA application
Create an authorization grant
Obtain the access token
Choose an Amazon Ads API endpoint URL
Enter your Amazon Ads API request into Apipheny
Run the Amazon Ads 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 a Login with Amazon application

Click the Login with Amazon tab:

Click the Create a New Security Profile button:

Creating a new security profile

Complete the fields and click the Save button:

Saving the new security profile

The security profile is now displayed in the list:

After creating a new profile, you’re redirected to the Login with Amazon page.

This page includes a table that lists your security profile name and OAuth2 credentials. Locate the name of the security profile you created earlier, and click Show Client ID and Client Secret.

Note these values, as they are essential credentials for accessing the Amazon Ads API. The client ID is passed in all requests to the API, and both the client ID and client secret are used to generate access tokens for the API. These values can be retrieved from this panel at any time.

Step 3.) Apply for permission to access the API – Application approval may take up to 72 hours

Access the link: https://advertising.amazon.com/partner-network/register-api?ref_=a20m_us_api_drctad
Complete the form and then click the Submit button for review:

You will receive a confirmation message:

Confirmation message for Amazon Ads API registration form

You should receive an email at the address you used to log in above to confirm receipt of your application.

Review of your application may take up to 72 hours. Once your application has been reviewed, you will receive two emails:

Technical support
You will receive an invitation from jira@amzn-clicks.atlassian.net to access technical support for the API through our Jira service desk. Please accept this invitation shortly after it is received in order to facilitate any future support requests.

Application status
If your application has been approved, read about the next step in the onboarding process: Assign API access to your LwA application. If your application is not approved, you will receive information about how to resolve the issue.

Step 4.) Assign API access to your LwA application

If your request for API access is approved by Amazon, you’ll receive an email from Amazon Ads. Click the link in this email to open a web page where you can assign API access to the Login with Amazon (LwA) application you created in Step 2.

Important

Before clicking on the link, it’s important that you log out of all Amazon user accounts (including your personal shopping account) except the Amazon account you used to apply for access in step 2. If anyone clicks on the link while logged into the wrong account, the link will be invalidated and will need to be reset by the Amazon API support team.

After clicking the link, you’ll see a message reminding you to create a client application through Login with Amazon as described in Step 2 of this onboarding walkthrough. If you already completed Step 2, click Continue to see a list of the LwA applications associated with the logged-in account.

Step 5.) Create an authorization grant

To retrieve an authorization code, create an authorization URL that enables a user account to grant authorization to your client application.

Log in to the Amazon Developer console using your Amazon Developer account and select Login with Amazon from the top menu.

Choose the security profile you used to apply for API access, move your mouse to the “gear” icon under Manage on the right side of the screen, and select Web Settings from the menu.

From the Web Settings panel, click Edit. Add a valid address in the Allowed Return URLs field and click Save.

Login with Amazon implements a different authorization URL prefix for each region. Select the URL for your region from the table below.

Region	URL prefix
North America (NA)	https://www.amazon.com/ap/oa
Europe (EU)	https://eu.account.amazon.com/ap/oa
Far East (FE)	https://apac.account.amazon.com/ap/oa

An authorization code retrieved from any of these URLs can be used to access the advertising API in any region.

Important

If this authorization grant is for the Data Provider API, recall that you created a separate DSP account for each region. You are required to repeat this process three times, once for each of the regions described above.

To grant access to your own Amazon Ads data and to generate an authorization code in the North America (NA) region, replace YOUR_LWA_CLIENT_ID and YOUR_RETURN_URL in the URL below with your values:

https://www.amazon.com/ap/oa?client_id=YOUR_LWA_CLIENT_ID&scope=advertising::campaign_management&response_type=code&redirect_uri=YOUR_RETURN_URL

The authorization URL has the following query parameters:

Parameter	Description
YOUR_LWA_CLIENT_ID	The Client ID associated with your Login with Amazon client application. For information on locating your client ID, see Create a Login with Amazon application.
scope	The OAuth 2.0 permission scope used to limit the application’s access to an advertiser’s account. For the DSP, Sponsored Brands, Sponsored Display, Sponsored Products, and Amazon Attribution APIs, set scope to advertising::campaign_management. For the Data Provider API, set scope to advertising::audiences.
response_type	The type of response. Always set to code.
YOUR_RETURN_URL	The value from the Allowed Return URLs field of your Login with Amazon security profile, as set above.

After you replaced the required values, copy the link in a browser’s address bar and tap Enter.

Sign in using an Amazon user account with access to the Amazon Ads accounts you want to manage through the API. This account may not be the same as the Amazon Developer account you used to create the LwA client.

You will be redirected to a consent form listing the specific data and services included in the authorization grant. To grant access, select Allow.

You will be redirected to the redirect_uri that you specified previously, with query parameters appended to the URL. Copy the address from your browser’s address bar, and note the value of the code parameter:

The code is valid for only 5 minutes. Make sure you use it to obtain a token quickly.

Step 6.) Obtain the access token

To retrieve access and refresh tokens using cURL, write the following command in command prompt (for Windows OS) or in a terminal (for Mac OS):

curl -X POST –data "grant_type=authorization_code&code=AUTH_CODE&redirect_uri=YOUR_RETURN_URL&client_id=YOUR_CLIENT_ID&client_secret=YOUR_SECRET_KEY" https://api.amazon.com/auth/o2/token

Don’t forget to replace the following values:

code	The authorization code retrieved in Step 5. Note this code expires after 5 minutes. A new code may be generated by repeating the same steps.
redirect_uri	One of the values in the Allowed Return URLs field in your Login with Amazon account.
client_id	The Client ID of your Login with Amazon account, obtained at Step 2.)
client_secret	The Client Secret of your Login with Amazon account, obtained at Step 2.)

Step 7.) Choose an Amazon Ads API Endpoint URL

In this section, we’ll show you how to browse the Amazon Ads 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 8.

First, open the Amazon Ads API documentation page: https://advertising.amazon.com/API/docs/en-us/reference/api-overview

The menu on the left, under Resources menu item, contains a list of endpoint’s categories:

If we click the Accounts category then a list of subcategories will be displayed: Profiles, Manager accounts, Portfolios, Portfolio budget usage, Invoices, Account budgets and Test accounts:

An example endpoint is GET /v2/profiles from the Profiles subcategory, which gets a list of profiles. The URL for this endpoint is:

https://advertising-api.amazon.com/v2/profiles

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 https://advertising-api.amazon.com/v2 (for North America), https://advertising-api-eu.amazon.com/v2 (for Europe) or https://advertising-api-fe.amazon.com/v2 (for Far East) base domains. All endpoints should be appended to these base URLs.

Step 8.) Enter your Amazon Ads API request into Apipheny

Now, to get your Amazon Ads 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 Ads API endpoint. For this example, we are using the “GET” method.

API URL: In Step 7, we explained how you can find the Amazon Ads API endpoint that you need. Now copy your complete Amazon Ads 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 Stores endpoint, which retrieves a list of stores for a given advertiser.

The URL for this specific endpoint is:

https://advertising-api.amazon.com/v2/stores

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

Header 1

Key:
Authorization

Value:
Bearer your_token

---

Header 2

Key:
Amazon-Advertising-API-Scope

Value:
scope

Header 3
Key:
Amazon-Advertising-API-ClientId

Value:
your_client_id

Header 4
Key:
Accept

Value:
application/json

Make sure to replace:

your_token with a valid API access token in the format Bearer access_token, the one obtained at Step 6.). An access token is only valid for one hour
your_client_id with the client ID related to a Login with Amazon application
scope with the Amazon Ads profile ID

Amazon Ads API request entered in the Apipheny add-on

Step 9.) Run the Amazon Ads 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 Ads data will be imported into your Google Sheets. Congratulations!