Auto email invoices to Xero from Gmail with PDF
We are going to show you how to automatically take an email with an invoice on it and send it to the Files feature in Xero, ready for an invoice to be created.
// Update 1 March 2017 //
This system used to work, but since Google have changed a few things on their APIs it no longer functions. It doesn’t carry enough value for me right now to update it, and I don’t know if there is a similar solution elsewhere currently.
// UPDATE March 10th, 2015 //
For some reason (as pointed out by Paul in the comments) the PDF is now not recognised by Xero. As I have yet to look into it, Paul kindly pointed out an article with a script that does work, and doesn’t need a Google Sheet to embed it. Check that out:
// End Update //
It’s been bugging me for a while that I have been paying ShoeBoxed for a service that essentially holds copies of receipts for me. Note that they do more than that, by having a human read and interpret the receipt / invoice for you too, however it’s hit and miss and more often than not they get the simple things wrong. For example I have a supplier that consistently turns up in Xero with three variations of their company name. On balance we have decided it is creating more work than it should be for the $40/month.
One awesome thing Shoeboxed does is take an invoice that is embedded into an email, and make it a PDF or image. Xero requires a PDF or image file, and will ignore the content of the email. This is a problem, as we cannot simply forward on the invoice email to Xero without the attachment.
So I set about hacking a solution in place with Gmail, Google Drive, Google Scripts and Xero.
1) Set up two labels in Gmail, one for emails to be processed, and one for processed.
2) Create a new spreadsheet in Google Drive to house the Google Script. Install the script and add a trigger.
3) sit back and enjoy the awesomeness of email invoices being converted to PDF and sent in to Xero ready for processing.
First up, set up a label in Gmail called ‘#Receipts’ – note this is case sensitive, so get that right.
Then create a filter to send any email you have that is an invoice into that label. So, we have our project management tool Basecamp send us emails every month, there is no PDF attachment, which Xero requires, so we cant just send on the email.
You can also do this with an email that has the PDF attachment, we are dealing with those too.
Secondly, set up a label called #Xero – this will be where all the documents that are sent to Xero are kept after processing.
Login to Xero, go to Files and pick up your unique email address.
You will need this to update the Google Script in a moment.
Go back to Gmail, and create a filter: “to: [YOUR UNIQUE EMAIL]@xerofiles.com”. Replace my example with your real email address. Then add a filter to this that adds the label “#Xero” to these emails. This results in all the emails we generate being tagged with #Xero for easy reference.
Open Google Drive and create a new Spreadsheet somewhere safe. Call it ‘Gmail Scripts’ for reference. Note that this will be a holder for the Gmail script, nothing more (seems a little odd but Gmail doesn’t have scripts directly).
Go to Tools > Script Editor
Then delete all the code that comes up, and copy and paste the below script into the window.
Look for the line that says
and replace [YOUR UNIQUE EMAIL]@xerofiles.com with your email address from earlier.
Hit Save, then Push the play button. With any luck, nothing will happen, so it’s running great.
Finally, go to Resources > Triggers and add a trigger to make it fire once an hour.
Now, once you have an email labelled with #Receipts your script will pick it up,and process it.
What it actually does:
- Finds all emails tagged with #Receipts
- Convert the body of the email into a PDF
- Collect all attachments on the email
- Create a new email to your Xero address
- Add the new PDF & existing attachments on to the new email
- Set subject to “Invoice sent to Xero : [Original Subject] [Original Date]
- Send to the unique Xero address, from you
I hope you find this useful!
Hat tip to Zachary Yates on StackOverflow for doing the majority of the leg work with the Google Script.