Automating invoice and statement matching with Google Sheets and PDFs

Getting Started
1

Hi Everyone.

I am trying to help automate a process at work.

We create one statement at the end of the month which lists the following:

  • Customer name
  • invoice numbers
  • Date
  • Debits
  • Credits

At the end of the month, two of my colleagues sit there and make sure that the info on the statement matches the information on the invoices.

All documents are PDF’s.

I have successfully managed to pull the information from the statement and the individual invoices and put them in a Google Sheets spreadsheet. One sheet is named “Statement” and the other “Invoices”

The idea Is to make sure that all the information matches i.e. the company name, invoices number etc. If it does then it will email the customer with all the attachments. If there is a mismatch, then this will be flagged.

There is probably a very simple solution, but I am having trouble.

Thanks in advance.

2

Hey Stuart,

will the people be checking the sheet it self by any chance? Cause you can do the comparison natively and don’t need to complicate it with external automations.

Otherwise - List Rows module to list all entries in the second sheet, followed by a Search Rows module that checks if the data exists in the first sheet should be a good starting point.

Also, if the invoices are being created from a different data set than the statements, is it not possible to do the sync between the two sets earlier?

3

Hi. Thanks for your reply.

The invoices are created first using a standalone system. The invoices are saved in a folder on the server, and a copy is printed. Someone who deals with the accounts will log the invoices onto the accounting software, in this case, SAGE.

At the end of the month, two people will cross-reference the statement and check that all the invoices have been saved, that they have the correct customer, invoice number, and the correct amount.

They will then create an email with the statement and all the invoices as attachments. It takes them both around 2 hours each, depending on if they get distracted.

We are a small company with only four employees and work in an old-fashioned way. We write down what customers have taken, then at the end of the month, we create the invoices (usually 200+). The end of the month is very hectic, but the owner is set in his ways.

Ultimately, my plan is to get a system to check that all the invoices have been saved, that the customer, invoice number, date, and debit/credits match. If so, create a link for the customer to download their statement and associated invoices. Create an email and send it.

I may be being a bit ambitious given that I have only been using make.com for just over a week.

Kind regards
Stuart