Never manually type invoices again (Google Sheets edition)

In this example, we will work on invoices in an image format. Specifically png. If your invoices are already in png, then you’re good to go. Otherwise, convert it to png.

Here are my sample invoices:

Invoice 1:

Invoice 2:

Put the png invoices in a folder in Google Drive. Doesn’t matter where but ensure that only invoices to be processed are included in this folder and nothing else.

In Google Sheets, create sheets named header and items. We will separate the invoice header and item details in these sheets.

In the header sheet, put the columns that you want to extract. Put them starting from row 1 column A. I suggest you at least include invoice_number and invoice_date but still, your choice.

Here are the columns I’ve used for the header sheet.

• invoice_number
• invoice_date
• vendor_name
• due_date
• total_price
• bank_account_number
• bank_name

Take note, you can put any fields you want to extract from your invoice here (except for the items, of course. We have a separate sheet for that). The Apps Script that we will implement will ignore the field if it’s not present in the invoice. For example, if an invoice doesn’t have a bank_name included in it, but you want to extract the bank_name for other invoices, it’s best that you include it.

Another note, we will use an AI to extract details from the invoice, if you’re wondering what kind of magic we will use here. So no, we will not use some OCR system here. We will use Google’s AI, Gemini.

Example (header sheet):

For items sheet, here are the columns I’ve used:

• invoice_number
• invoice_date
• item_description
• item_quantity
• item_price

Take note that I’ve included invoice_number and invoice_date here, again. Why is that? These two would be our identifier that a particular item is included in this invoice_number and invoice_date. Thus, in a table relationship term, one header can have one or more items or we can say that they have a one-to-many relationship.

Now, to create an Apps Script, click Extensions > Apps Script.

You will see a screen like this. This is a Code.gs file where we can put Apps Script codes.

Go to this Github link. You will see a Code.gs file there. Copy the codes in that file and replace everything in your Code.gs.

Your Code.gs file will now look something like this.

There’s one more file you need to copy. Create an index.html file.

Name it index.

You now have a new index.html file.

Back to the same Github link. Copy the code in index.html file and paste it in the index.html file you’ve created. Take note to replace the existing code and save.

Now, go back to your Google Sheets and refresh the page. You will now see an Invoice menu on the top.

Click it then Extract.

Click OK when prompted by Authorization required

Login to your Google account. Take note that the title of your Apps Script project will show here.

Click Advanced.

Click Go to <project-name> (unsafe).

Take note that the above is fine even if it says not verified or unsafe. It’s your apps script, so it’s fine.

Read the permissions that you will allow (might not be the same as the screenshot) before proceeding. Then click Allow.

Now, try to click Invoice > Extract again.

If all is well, you will see this sidebar on the right side of your Google Sheets

To get your Google Gemini API Key, go to ai.google.dev/aistudio, then sign in using your Google Account. You can use any Google Account. It doesn’t have to be the same as what you’ve been using so far.

When prompted, just check what’s necessary and click Continue.

Then get your API here.

Click Copy but before doing that, read first.

When you refresh the page, you will see that it’s Free of charge, at least at the time of writing. Thus, you might want to read more about this API. But if you did not setup any credit card on this Google Account, you’re safe even if it becomes paid in the future. But knowing Google, they will always have a free tier as long as you use it within limits.

The above images or instructions might change at the time that you’re reading this. All we did was just get an API key. So if the instructions changed, just follow where you can get an API key.

Now, go back to your Google Sheets, on the sidebar, paste your copied API Key.

Then the Google Drive Folder ID. That’s easy. Remember that you put your png images in a Google Drive folder? Go to that folder, then in the URL, there you will find the folder ID.

Copy that and paste it here.

And the moment of truth! You can now click START.

You will get this if something went wrong. It will show at the bottom right of the page.

There are two obvious reasons that this might happen.

One, your API Key is wrong. Try to copy/paste again. And two, the folder ID is wrong. Try to double check that.

Other than that, you’re on your own. Lol. There should be an error log in the console. If you know what I meant by console, then you probably can debug it on your own. If not, hmm… maybe contact me? Or you can always try to read the instructions again. It’s possible that you might’ve just missed a thing or two.

Anyways, I hope that all is well for you and it worked! I forgot to mention, after you click START, you will get a loading spinner.

This will not stop until you get a Done message or as mentioned above Something went wrong.

And if you’re curious, here’s the result for my sample invoices.

Header:

Items:

Your mileage may vary. Take note that our goal here is to extract texts from invoice, not to get rid of our job. What I’m trying to say is, this solution is not perfect. You still need to check and correct mistakes. This is still early AI after all but daanngg, it’s pretty good. Imagine if you are actually processing so much invoices like these in a day.

Take note that there are Apps Script limitations. You can’t just run this script forever with hundreds or thousands of invoices. I will not discuss Apps Script’s limitations here. Plus, because we are complying with Google Gemini’s limit for free usage, we are only hitting the API endpoint, at least 3 seconds at a time (change this accordingly, it’s in the code). Thus, it might take a while to process the invoices, so be patient. As long as the loading spinner is spinning, it’s processing.

One last thing, you might want to check the AI prompt I’ve provided in the Code.gs file. Modify it however you want.