[Note: originally written Pre-Covid-19 and posted on reddit; updated with minor edits]
Since getting a Google Home mini, I've loved how it's freed me from having to go to my phone or computer for everyday things, simply by talking. But one missing thing has been the ability to add items to my Amazon Shopping cart by voice. When you have kids/baby/generalhouseholdchaos.... there's rarely time (or motivation at the end of the day) to log on to Amazon to buy those diapers/batteries/toilet paper/detergent/socks/etc (definitely a first-world problem, and I'm aware of Subscribe-and-save but prefer to only buy when I need it).
So, here's how I did it using IFTTT!
My requirements:
Easy, hands-free way to add something to my Amazon cart by voice.
Don't need order to be placed, just put together. I just need assistance adding things to my cart for later review and ordering
Automated alert or "push" of my shopping cart to me. As if I had a personal assistant who added things to my cart then let me know about it when ready, at the right time of the day (e.g. after kids are in bed)
Only support Repeat Purchases. I realized I only need to order things I've bought before on Amazon. These are things I don't need to read reviews for, or price shop. Just want to buy it again.
What you'll need:
A Google Home or Google Assistant enabled Device
Your IFTTT account
Ability to code some Google Apps Script (like JS)
Your Amazon account
Your email or your phone number (for sending you a link to your shopping cart)
Let's do it! Step 1: Set up a Google Sheets spreadsheet to capture your requests You need Sheets to capture the items you request to GH (you'll see later how we use IFTTT to do so).
Create a new Sheet (e.g., "amazoncartlist") where you'd like it (e.g.,"/Google" folder) and set these first column cells of this worksheet (creatively named "Sheet1") as your header for easy reference:
Date - Date/time when your request is made
item - name of the thing you're adding
ASIN - Amazon standard identification number. Each product on Amazon has one.
Bought - This flag will indicate status of whether this item has been bought or not. You'll see later.
Now you have a worksheet, called "amazoncartlist" that will operate as a "message queue" from GH/IFTTT (more later).
Step 2: Add a worksheet to the Sheet to "look up" the Amazon ASIN
In order to add items to an Amazon Cart, we'll need to reference them by ASIN. Luckily, for all the items you've bought in the past, the ASINs are readily available in your Order History. We're going to download this file, and add it to another worksheet in this Sheet to use as a "lookup" file.
In your browser, login to your Amazon account, and follow instructions to create an Order History Report.
Download this file, then copy and paste its contents into a second worksheet of your original Google Sheets spreadsheet. Name this second worksheet "orderhistory".
If you don't want to copy all of it, you don't have to (I did because of laziness). You really just need the Title and the ASIN columns. You'll end up with a long list of your past order history, and it'll look a little like this:
You only need Title and ASIN fields copied over. Now we have a lookup table that can use "Title" (name of the item you're requesting) to lookup its ASIN!
Step 3: Code App Script to lookup ASIN of items added to the "amazoncartlist" worksheet
Now the cool part. We are going to code Apps Script, Google's JS-style language, into the Sheet, to automatically lookup and populate ASIN value of any item that is added to the first worksheet (we had named it Sheet1).
In your Google Sheet, go to Tools > Script Editor
In the script editor, paste in the following variable declarations and function definition for addASIN(), then Save it in the Script Editor:
var DATE_COLUMN = 1;
var ITEM_COLUMN = 2;
var ASIN_COLUMN = 3;
var BOUGHT_COLUMN = 4;
var TOTAL_COLUMNS = 4;
function addASIN(e) {
var lr = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1').getLastRow();
var itemRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1').getRange(lr, DATE_COLUMN, 1, TOTAL_COLUMNS);
var rowValues = itemRange.getValues();
var itemCell = rowValues[0][ITEM_COLUMN - 1];
var asinCell = rowValues[0][ASIN_COLUMN - 1];
var boughtCell = rowValues[0][BOUGHT_COLUMN - 1];
// If item name is command to clear list, set entire bought column to 1
if (itemCell == "clearaboveitems") {
var boughtColumn = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1').getRange(2, BOUGHT_COLUMN, lr - 1, 1);
boughtColumn.setValue(1);
} else {
// else lookup ASIN and set value in column
itemRange.getCell(1, ASIN_COLUMN).setFormula('=query(orderhistory!2:391, \"select E where lower(C) like \'%\"&lower(B' + itemRange.getRow() + ')&\"%\' limit 1\")');
}
}Sorry for the hacky code (I'm one of those coders!). To briefly explain what we're doing here:
Load the bottom-most row and its cell values (we assume this is the most recently added item)
Read the Item cell value and if it equals "clearaboveitems", set the entire Bought column values to "1" (more on this later)....
Otherwise, do a lookup query on the "orderhistory" worksheet (remember our ASIN lookup worksheet?) to find a match and its corresponding ASIN value
NOTE: Technically, we're actually setting a Formula in the ASIN cell of the lastmost row to perform this query:
=query(orderhistory!2:391,"select E where lower(C) like '%"&lower(B3)&"%' limit 1")
Step 4: Set up Trigger for your code to run whenever the sheet is updated
Now we want this ASIN lookup logic to happen whenever an item is requested and added to your Sheet. Fortunately, we can point and click to make this happen without any code:
In Script Editor, Go to Edit > Current Project Triggers
In the resulting tab, click Add Trigger
Choose the function to run: addASIN
Choose the deployment: Head
Select Event Source: From spreadsheet
Select Event Type: On Change
Save it!
Now, whenever your Google Sheet is changed (manually or by IFTTT), your function addASIN() will run automatically! Step 5: Set up an IFTTT Trigger for Google Assistant to add a row to your Google Sheet Now that we have a mechanism that will automatically read rows in Sheets and lookup ASINs, we'll need to hook it up to IFTTT, where we'll create an Applet:
"If [Google Assistant phrase trigger] Then [Google Sheets Add Row action]"
In your IFTTT, create a new applet using a Google Assistant Trigger: "Say a Phrase with Text Ingredient"
Fill it out accordingly (note your phrase might trigger native Google to say it doesn't support Amazon - so try to find wording to avoid conflicts). Save the Trigger.
Set up the applet's Action using Google Sheets: "Add Row to Spreadsheet", fill out rows accordingly and Save.
Your Trigger configuration might look like this:
and your Action configuration will look like this:
Step 6: You should probably test all this out at this point.
Validate this works. You should at this point be able to say something to Google Home with a text ingredient, trigger your IFTTT applet, see it add a new row with your text ingredient in your Google Sheet, and auto-populate its corresponding ASIN value.
If it works, give yourself a high-five and continue.
Step 7: Code App Script to automatically send you a link to your Amazon cart
Almost there. Now that we have some logic, we still need a way for your cool new service to "push" the shopping cart to you, to remind you to review and submit your order. Because you are lazy and forgetful.
Go back to your Google Sheet > Tools > Script Editor so we can revise the code
Add this new function constructBasket() to your code and Save it:
function constructBasket(e) {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1');
var startRow = 2; // First row of data to process (ignore headers)
var numRows = sheet.getLastRow() - 1; // Number of rows to process
// Fetch the range of cells
var dataRange = sheet.getRange(startRow, DATE_COLUMN, numRows, TOTAL_COLUMNS);
// Fetch values for each row in the Range.
var data = dataRange.getValues();
// setup array for items to add to cart
var asinArray = [];
// Parse through data to find items to add
for (var i = 0; i < data.length; ++i) {
var row = data[i];
var asin = row[ASIN_COLUMN - 1]; // ASIN column
var asinBought = row[BOUGHT_COLUMN - 1]
if (!asinBought && asin != '#N/A') { // Prevents adding already bought items and rows with valid ASIN
asinArray.push(asin); // add asin to list
}
}
// only send if we have anything
if (asinArray.length > 0) {
var amazonLink = 'http://www.amazon.com/gp/aws/cart/add.html?';
// Construct Amazon link with list of ASINs
for (var j = 0; j < asinArray.length; j++) {
var asinNumerator = j+1;
amazonLink += 'ASIN.' + asinNumerator + '=' + asinArray[j] + '&Quantity.' + asinNumerator + '=1&';
}
// send text via email address to my phone
var emailAddress = '9175555555@tmomail.net'; // replace with your own email or mobile text address
var subject = 'Your cart';
var message = 'Your Amazon cart:\n' + amazonLink;
MailApp.sendEmail(emailAddress, subject, message);
}
}
Again, very hacky code. Roughly, it:
Loads rows of your sheet and adds them into an Array, only if their "Bought" column value is 0 (not already ordered) and they have valid ASIN values
For these unbought items with valid ASIN values, we construct an Amazon Add-to-Cart URL, appending each item to the URL per documented structure (Note: Depending what country you live in, your Amazon URL/domain might differ)
Send the constructed Add-to-Cart URL via email to your desired email address
Note: Cool assistants like JARVIS would never email and would text you. So in my code, I use the known T-mobile email workaround to send free text messages directly to my T-Mobile phone. You can do the same for other mobile operators as I'm sure there are similar methods
Step 8: Configure Trigger for your constructBasket logic Now you have code that sends you a convenient 1-click link that instantly adds all items you requested into your Amazon shopping cart. We need to configure when it executes. I chose to make this Time-based, as in, Run every day in the evening:
In Script Editor, go to Edit > Current Project Triggers (again)
Create a New Trigger and configure it:
Choose the function to run: constructBasket
Choose the deployment: Head
Select Event Source: Time-driven
Select type of Time based Trigger: Day Timer
Select Time of Day: 9-10 PM (when I'm the most brain-dead)
Save it!
Voila! Now, each evening at your set-time, this logic will construct a cart link of the items you requested and send it to your phone!
Step 9: Add a voice command to "clear out" your cart
You don't want the same items being added and sent to you each and every night. This is where the "Bought" column in your Google Sheet comes in. Now, we can't actually (rather, I don't have the energy to figure out how to) detect item order status, but we can still make it easy to mark items as "Bought" so that they aren't repeatedly sent.
We're going to repeat Step 5 above, and create a new IFTTT applet (Google Assistant trigger, Google Sheets Add Row action).
Configure it so that for its action, it adds a row to your same Google sheet but with a hardcoded string "clearaboveitems" instead of text ingredient (important as our App Script code looks for this specific word)
Your Trigger will look like this:
Your Action will look like this:
Once you save this, you can say your command "Clear my Amazon cart", which will trigger IFTTT to add this special command "clearaboveitems" to your Sheet. Your App Script code will run on this Change event, and mark all rows above with the value of "1" in the Bought column, so they will be ignored in future logic.
WOOHOO! YOU DID IT!
This is how I did it. While it's pretty hacky, it generally works for me and definitely has made part of my life easier. Some caveats and notes:
You could improve the item ASIN lookup query. Right now, it does a basic string match, so requires you to say the right words to be matched in the Order History worksheet. What if you coded in Natural Language Processing or some fuzzy matching to improve match rates?
IFTTT and Sheets has some ~2k row limit before it creates another Google Sheet. So that'll happen if you really ever hit that limit
I think the Google Home -> IFTTT -> Sheets -> App Script logic processing can be pretty powerful and used in a lot of scenarios, especially when App Script code can do so much (e.g., can make POST requests, parse JSON/XML)
I wrote this after already having built it, so had to recreate the steps from memory. Please let me know if I missed anything.