How to use the Amazon Line Item Importer for Google Sheets

Overview

Built for Google Sheets powered by Tiller, the Amazon Line Item Importer by Tiller Labs allows you to download & import a CSV file of your order history from Amazon to accurately categorize your Amazon spending.

Instead of seeing a single charge from Amazon.com in your Tiller Sheet, this workflow:

  • Creates new rows for each item purchased
  • Pulls in each product’s name and details
  • Shows exactly what you paid for it

For example, after running the workflow, instead of seeing a single charge like this:

You will see discrete line-items in your Tiller Sheet, like this:

You can then accurately categorize and track each item you purchased:

What is Tiller Labs?

The Amazon Line Item Importer add-on is published by Tiller Labs, a playground for the team at Tiller to build experimental spreadsheet templates, add-ons, and workflows.

Besides being free, Tiller Labs projects are different from Tiller’s core templates and automation service, because they are supported exclusively here in the Tiller Community and aren’t tested to the same high standards as our core product.

As with all good experiments, Tiller Labs projects may inspire and delight, but also might go awry. If you’re open to adventure, come play in our lab.

Who Is This For?

This add-on will save time and improve accuracy when tracking how money was spent for personal-finance & small-business users who regularly buy from Amazon.

Tiller Amazon Workflows is designed to integrate with Tiller financial spreadsheets.

What Does It Do?

Amazon provides line-item purchase history via its Items Order History Report, a CSV file. Historically, the challenge has been how to efficiently & accurately integrate this order data into a personal-finance spreadsheet.

The Amazon Line Item Importer helps by:

  • Itemizing Amazon orders into discrete line-items for each item purchased to facilitate accurate budget categorization
  • Importing & mapping Amazon’s purchase fields into Tiller financial spreadsheets
  • Tracking Amazon purchases, preventing the import of duplicates
  • Storing searchable order metadata in the Transactions sheet
  • Providing one-click filtering of uncategorized Amazon line items

How Does It Work?

Tiller Amazon Line Item Importer adds the ability to discretely categorize your purchase line items.

If you use Tiller, your Transactions sheet will already have (negative) charges from your credit card for your Amazon orders. Therefore, your cashflow already accurately reflects your Amazon purchases due to the credit card charges from Tiller feeds.

So, Tiller Amazon Workflows will import your transaction line items so they are cashflow-neutral.

The magic of the add-on is that you will see discrete purchases that can be categorized without clumsy splits or time spent cross-referencing the Amazon site.

To add cash-flow neutral rows, the add-on creates two types of line items:

  • Amazon Items are negative charges against your balance for individual purchases. They should be categorized manually for discrete tracking.
  • Amazon Orders are positive credits to your Transactions sheet that comprise one or more purchases. Order rows will be categorized as “Amazon” by the add-on and should approximately* offset the credit card charges pulled into your sheet by Tiller’s feeds. (The negative credit card charges should also be categorized as “Amazon” and will offset/balance with the order rows.)

Here is an instructive example:

If you add up the amounts of all the new rows inserted into your sheet by the add-on— the green and red rows— they will sum to $0.

What Are the Steps?

Download and Install the Add-on

The add-on is available in the Chrome store for download. During install, you will need to accept the add-on’s scopes request.

Prepare Your Sheet

The first time you open the add-on, it will check:

  • If the active Google Sheets spreadsheet is compatible with the add-on
  • If the spreadsheet’s Transactions sheet contains a column named “Metadata”

If the spreadsheet is compatible and the required column is missing, the add-on will ask permission to add it.

Next, be sure you have categorized the existing Amazon charges in your Transactions sheet (i.e. the credit card charges inserted by Tiller Feeds) with the category “Amazon”. This step is important to ensuring any new [Amazon Order] rows (added by the add-on) will offset properly with your credit card charges.

Import Your Transactions

To import transactions from Amazon into your spreadsheet:

  1. Log into your Amazon account
  2. Navigate to the Order History Reports page
  3. Choose the Items Report Type & select a date range, then click Request Report
  4. Now open your Tiller compatible spreadsheet
  5. Open the add-on by going to the Add-on menu and navigating to “Amazon Line Item Importer/Open sidebar”
  6. Click “Import Line Items” on the main menu
  7. Click the “Upload Line Items” button
  8. Navigate to the CSV file you downloaded from Amazon
  9. After the add-on scans the CSV file for new purchases not already in your Transactions sheet, click “Add to Transactions”

Complete the Categorization Process

  • Confirm that all Amazon order charges to your credit card— i.e. those added by Tiller’s feeds, not the Amazon Workflows add-on— are categorized as “Amazon”
  • Manually categorize all Amazon purchase line items with the “[Amazon Item]” prefix to their corresponding budget categories (e.g. Household, Kids, Pets, etc.)

Frequently Asked Questions (FAQ)

Are Tiller Labs solutions supported by Tiller?

Tiller Labs solutions are not officially supported by Tiller. They are supported by the Tiller community. Tiller may provide limited informal support in the community.

What if I import some of the same Amazon purchases twice?

The add-on stores enough data in the Metadata column to uniquely identify your Amazon purchases. If you import the same CSV file twice or import two CSV files with some overlap, the add-on will ignore purchases that have already been imported.

Can I delete the rows marked [Amazon Order]?

Deleting the (positive) [Amazon Order] rows will affect your cashflow. As documented above, without the order rows, your Transactions sheet will double count purchases— once in the credit card charge (from Tiller Feeds) and a second time in the purchase line items from the add-on. The orders rows compensate for double counting and render the import cashflow neutral.

Why doesn’t my Amazon category sum to exactly zero?

The orders amounts will only approximately balance with the credit card charges because Amazon does not publish precise tax and shipping values in the Items report used by the add-on. (Amazon’s Orders report does include precise tax and shipping values, but lacks the line-item purchase information.) Most users will end up with a slightly negative balance in the “Amazon” category representing the cost— tax and shipping— of purchasing from Amazon.

What is the “Metadata” column for in the Transactions sheet? Can I delete it?

No. The add-on uses the metadata in the “Metadata” column to uniquely identify purchases. The column is required to avoid inserting duplicate purchases.

Will this add-on work for non-US accounts?

Short answer: probably not. We have only just released this add-on and testing has focused on Amazon reporting for US accounts. Feel free to share details on where the process breaks down in the feedback thread and we may be able to build new importer rules.

Will Tiller Amazon Workflows help with my Amazon digital orders?

No. Amazon does not include digital orders in its Items report.

How should I handle a returned item using this workflow?

First, you will not see returned purchases in the Items report used by the Amazon Line Item Importer. Amazon’s Order History Reports page provides a separate Returns report type, but it is not currently compatible with the add-on.

By way of example, if you bought a lawnmower from Amazon then imported your purchases with the add-on, your Transactions sheet would look something like this:

Date Description Amount Category Source
6/1/2019 Amzn Mktp Us*m68n167z1 Amamzn.com/billwa -$200.00 Amazon Tiller feeds
6/1/2019 [Amazon Order] #118-6130598-5743452 (Tiller offset for 1 item) $200.00 Amazon Add-on
6/1/2019 [Amazon Item] Craftsman 140cc 3-in-1 Gas Powered Lawn Mower -$200.00 Landscaping Add-on

You can see in the transactions above, the “Amazon” category nets to zero and the Landscaping category shows $200 of spend. So far so good.

Now, Let’s show the Transactions sheet after Amazon processes the return.

Date Description Amount Category Source
6/1/2019 Amzn Mktp Us*m68n167z1 Amamzn.com/billwa -$200.00 Amazon Tiller feeds
6/1/2019 [Amazon Order] #118-6130598-5743452 (Tiller offset for 1 item) $200.00 Amazon Add-on
6/1/2019 [Amazon Item] Craftsman 140cc 3-in-1 Gas Powered Lawn Mower -$200.00 Landscaping Add-on
6/10/2019 Amzn Mktp Us*m70n182z6 Amamzn.com/billwa $200.00 Amazon Tiller feeds

After the return, the good news is that our net cashflow is back to zero (as if we never made the purchase). The bad news is that the new spend is inaccurate at a category level since the “Amazon” category shows a +$200 favorability and Landscaping shows -$200 of spending.

To correct for this category inaccuracy for single-line-item orders, we recommend you search on the Amazon Order ID in your Transactions sheet, then delete all rows added by the add-on (i.e. the [Amazon Item] and [Amazon Order] rows). You can see that this would leave just the two Tiller-feeds credit card charges which correctly offset to $0 in the “Amazon” category.

Things are slightly more complicated when a single item is returned from a multi-item order. In this case, you would delete the Landscaping line item and reduce— not delete — the [Amazon Order] by the Landscaping [Amazon Item] amount.

Will Tiller Amazon Workflows help with my Amazon digital orders?

No. Amazon does not include digital orders in its Items report.

How does the add-on use the requested scopes?

“See, edit, create, and delete your spreadsheets in Google Drive”
https://www.googleapis.com/auth/spreadsheets
Allows access to the Google Sheets v4 API to execute batch changes to spreadsheets

“Display and run third-party web content in prompts and sidebars inside Google applications”
https://www.googleapis.com/auth/script.container.ui
Implements the sidebar user interface

“Connect to an external service”
https://www.googleapis.com/auth/script.external_request
Fetches the datasource definition file that tells the add-on how to validate, parse and remap fields

“Allow this application to run when you are not present”
https://www.googleapis.com/auth/script.scriptapp
Loads user-interface code & html assets stored in the add-on

1 Like