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 […]

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:

http://numernet.com/xero/automatically-send-receipts-gmail-xero/.

// 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.

Overview Steps: 

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.

Labels:

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.

Xero:

Login to Xero, go to Files and pick up your unique email address.

finding your Xero email address

Login to Xero and click the folder icon in the top left toolbar area, your unique email address is shown below.

You will need this to update the Google Script in a moment.

Go back to Gmail, and create a filter: “to: @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.

Google Drive:

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 @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.

Trigger:

Finally, go to Resources > Triggers and add a trigger to make it fire once an hour.

Set up a trigger to make this run every hour.

Set up a trigger to make this run every hour.

Now, once you have an email labelled with #Receipts your script will pick it up,and process it.

What it actually does:

  1. Finds all emails tagged with #Receipts
  2. Convert the body of the email into a PDF
  3. Collect all attachments on the email
  4. Create a new email to your Xero address
  5. Add the new PDF & existing attachments on to the new email
  6. Set subject to “Invoice sent to Xero :  
  7. 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.

 

Duncan Isaksen-Loxton

Educated as a web developer, with over 20 years of internet based work and experience, Duncan is a Google Workspace Certified Collaboration Engineer and a WordPress expert.

12 Comments

Why not just use Receipt Bank at $20 per month that WILL accept a straight email and then send it to Files in Xero as well as store it in Receipt Bank?
I have had 100% accuracy with Receipt Bank since I have been using it. Awesome product!

Ed, good point! 2 Reasons, 1 I didn’t know about it, and 2 this is a bit geeky, so it appeals to me! I will check it out, thanks!

Hi. Looks like a great script, keen to give it a try!

Just noticed though, that:
(a) I think you’ve got the script blocks pasted in the wrong way around
(b) I don’t see any code to remove the email’s label and apply the “Processed” label. Is it in there?

Anyway, thanks for sharing the script – I’m gonna have to learn more about Google scripting – so powerful!

@Simon thanks for the comment.

1) Yes, thanks – I have fixed that!

2) It doesn’t need one. Once it sends to the unique Xero address Gmail applies the #Xero label, because that is set up to filter all email TO your Xero address.

The script simply removes #Receipts once its done, and gmail does the rest.

Hi Guys,

This is awesome, can we put this script in “public” folder, shared with everyone within our organisation?

If then the users setup the labels, will the script run or does this need scripts to be configured per user?

Cheers

@Willem I believe you have to configure it per user. Each user needs to verify that they allow it to run.

Even if it were an add on in Google Chrome the user would need to install and run it, so no way around it I believe.

Hi Duncan, Thanks very much for this script. It’s exactly what I was looking for. However, I have a strange problem I hope you can help me with. When Gmail sends these files, Xero gives me an error message that reads, “Sorry, the file you have tried to upload is not supported.” If I open these PDFs in Acrobat or Preview they open perfectly. If I then close them and send them to Xero again using Mac Mail (rather than being forwarded by Gmail), Xero recognises the file and it appears in my Files folder as expected. Is this a bug you’re familiar with and is there anything you can suggest I do to fix this?

Hey Paul,

Thanks for reaching out. I have seen this to and as yet I don’t have an answer! I assume Xero (or Google pdf generation) have made some changes on their side to the doc validation, because this is a new issue.

I get the same result, I can save it to my machine and then resend it works fine. The mime type is correct RFC wise, so right now I don’t have an answer. Sorry!

Hello all,
the link in your March 10th 2015 update is not working anymore. Any other solution out there?

Thanks

Hi there, trying to get this script working but as soon as I save and Run the script following the steps above I get the following popup at the top:

ReferenceError: “DocsList” is not defined. (line 16, file “Google Script”)

This was amazing until I read the last comment. Duncan, thanks for the effort in sharing this – have you cracked the issue you were having?

Marco, I haven’t had a chance to do this again yet, so it doesn’t work right now. I have maybe three invoices per month and it just doesn’t warrant the time to update for me. Sorry!

Comments are closed.

Login
Log in below to access your courses.
Log In With Google
Forgot Password
Enter your email address or username and we’ll send you instructions to reset your password.