Log every Gmail receipt in a spreadsheet, automatically

What it does

Every morning, a short script looks through your Gmail for receipts and invoices from the last day, pulls out who sent it, when, what it was for and how much it cost, and adds one row per receipt to a Google Sheet. You set it up once. After that you do nothing.

Everything here is free with any Google account. There is nothing to install and nothing to buy.

Before you start

You need a Google account, and it is free. Everything here is Google Forms, Sheets, Docs, Drive and Gmail, and all of them come with any Google account. If you can open Gmail you already have one.
You need a computer for this, not a phone. The script editor does not work properly on a phone browser. Once it is set up it runs on Google's servers, so your computer can be closed.
About twenty minutes. Most of it is pasting.
A Gmail account with some receipts in it.

The build, step by step

Make a new spreadsheet.
Go to sheets.new. A blank Google Sheet opens. Give it a name in the top left, something like Receipts log.
You should see: an empty spreadsheet with the name you just typed at the top left.
Open the script editor.
In that spreadsheet's menu bar, click Extensions, then Apps Script.
You should see: a new browser tab open, headed Apps Script, with a small amount of code already in it that begins function myFunction().

If there is no Extensions menu: you are looking at a Google Doc or a Form rather than a Sheet. This step has to be done from the spreadsheet.

Delete everything in the editor and paste this in.

Select all the code that is there and delete it, so the page is empty. Then paste the following in its place.

function logReceipts() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0];
  var query = 'subject:(receipt OR invoice OR "your order") newer_than:1d';
  var threads = GmailApp.search(query, 0, 50);

  for (var i = 0; i < threads.length; i++) {
    var messages = threads[i].getMessages();
    for (var j = 0; j < messages.length; j++) {
      var message = messages[j];
      var amount = message.getPlainBody().match(/\$[0-9,]+\.[0-9]{2}/);
      sheet.appendRow([
        message.getDate(),
        message.getFrom(),
        message.getSubject(),
        amount ? amount[0] : ''
      ]);
    }
  }
}

You should see: the code sitting in the editor with no red marks against it.

Change the search words to match your own receipts.
Look at the line beginning var query. The words inside the brackets are what Gmail searches subject lines for. Open your Gmail, look at two or three real receipts, and see what their subjects actually say. If yours tend to say Payment received, add that. Separate each one with OR, and put quotation marks around anything containing a space.
Getting this wrong is the usual reason nothing appears later. A search that matches none of your email finds nothing, and the script cannot tell you that — it just adds no rows.
Save it.
Click the save icon in the toolbar above the code — it looks like a floppy disk.
You should see: the tab at the top of the code change from Untitled project to whatever name you are asked for, or simply stop showing an unsaved marker.
Run it once, by hand.
Click Run in the toolbar.
A permissions screen will appear, and it looks alarming. It is not an error and you have not broken anything. This happens the first time only. Google is asking whether you allow your own script to read your Gmail and write to your Sheet. Click Review permissions, choose your Google account, and you will then see a warning that Google has not verified this app — that is because the app is you, written five minutes ago. Click Advanced at the bottom left, then the link that goes to your project anyway, then Allow.

You should see: a panel at the bottom of the editor saying the execution started and then finished.
Go and look at your spreadsheet.
Switch back to the spreadsheet tab.
You should see: one row for each receipt found, with the date, who sent it, the subject line, and the amount.

If it is still empty: the search did not match anything. Go back to step 4. Try widening it to a single common word first, just to prove the script works, then narrow it down.
Set it to run every morning.
Back in the Apps Script tab, click the clock icon in the left-hand sidebar. This is the Triggers page — a trigger is simply a rule that runs your script at a time you choose, without you being there. Click Add Trigger in the bottom right. Leave the function set to logReceipts. For the event source choose Time-driven, then a Day timer, then pick an hour. Click Save.
You should see: your new trigger listed on that page, naming the function and the time.

What to watch for

Do not run it by hand once the trigger is on. The search looks back one day and the trigger runs once a day, so each receipt is caught once. Run it manually as well and you will log the same receipt twice. If you do end up with duplicates, they are safe to delete by hand — nothing depends on them.
The amount is found by looking for a dollar figure in the body. If a receipt writes its total another way, that cell will be blank while the rest of the row is still correct. The row is still worth having.
It reads the last day only. It will not go back and collect receipts from before you set it up.

What it costs

Nothing. Google Sheets, Gmail and Apps Script are free with an ordinary Google account. Apps Script has daily limits far above what this uses — it reads a few dozen emails a day, where the free allowance is thousands.

See the other builds → All builds