Automate Quickbooks Entries with Google Sheets

?? Everything you didn't realize you wanted to know about automating data entry into Quickbooks Online using Google Sheets, grit and a third party app.

?? Or: a reasonable case for a simpler small business accounting software suite.

Here's the Google Sheet we'll be working from (modified with dummy data). Feel free to make a copy and edit.

Small Business Data Entry Problem

Our small business has used Quickbooks for all it's bookkeeping for at least a couple decades. Over the last several years as I've overseen our finances and record keeping, I've been able to improve a number of processes that has saved time and reduced errors.

One of the latest has been the ability to automate bill and invoice entries that previously took more manual entry because of how we use Quickbooks' classing and categories.

I'll walk through a bill entry example to illustrate the savings.

Billing Example

We purchase many of our goods and supplies from Sysco. A typical invoice looks like this:

Unfortunately, Quickbooks' built-in importer is neither smart enough to successfully read this information and categorize it nor smooth enough to save substantial time were it accurate.

When I attempted to import a bill from a PDF, it simply lumped all the amounts together and seemingly chose an account at random to apply it toward (Dues & Subscriptions in this case).

Now, the data entry for something like this isn't terrible. Especially when you're seasoned at quickly creating a bill and attaching a PDF for records.

However, we have sub-categories for our Cost of Goods such that almost every line of an invoice we receive needs to go towards a different category.

This substantially increases the data entry time, tedium and the chance of mistakes.

TransactionPro

I found a decent service to help with this. TransactionPro is a paid app built to import and/or export data from Quickbooks. Using a CSV file, I can now upload one file that then creates multiple bills or invoices which are all properly categorized.

I'm a bit perturbed that this approach requires an add-on service, but the amount of time it saves far outweighs the yearly price tag.

When importing data, there are a host of options in TransactionPro. The only ones that I've needed to use so far have been Bills and Invoices:

Cleaning Data

I took a Data Analysis Certification Course by Google last year, and it affirmed my own experience: data cleaning is often the most tedious, difficult part. Once done well, though, everything else is smooth.

I decided a few months ago to invest the upfront time to get some templates prepared so that our data cleaning and upload process was

1. Fast - I wasn't interested in saving a few seconds, but if I could substantially reduce the time sink, this would be a worthy investment.
2. Accurate - Human entry is a weak point. I needed a good way for the raw data to go straight into Quickbooks rather than pass through me or someone else.
3. Simple - Just like speed, this needs to be relatively simple so that the learning curve is small and able to be passed on in the future.

TransactionPro has a huge Excel worksheet with templates available for all the data types it supports.

The header row of each sheet list the categories available for entry. And at the bottom, sheets are available for every type of file (Bill shown in pic):

Okay great. Now we need to

1. Get information from Sysco into one of the Bill templates.
2. Upload the template to TransactionPro
3. Rejoice while TransactionPro makes all the bills in Quickbooks Online automatically.

Download CSV

From Sysco, we need our invoice data in CSV format. I can now download a week's worth of invoices across multiple locations into one CSV file rather than inputting one at a time.

Curiously, I have to increase the column width of column A where the invoice numbers are truncated in order for it to be copied properly in the following steps. Weird quirks ftw ??

Data Matching

Now, Sysco doesn't care much about Quickbooks' account categories so we will have to match up the columns on our CSV with the appropriate columns in our TransactionPro template.

I opted to create a Google Sheet to automate this process.

I simply expand the columns on the downloaded CSV, copy all the cells with data and paste them into my Google Sheet.

Google Sheet Setup

I made a worksheet with several sheet tabs at the bottom for organization.

The Sheet Tab breakdown:

1. Instructions are notes to myself - especially useful in the beginning as I was ironing out the kinks.
2. Data is where I paste in the raw data from the CSV file
3. Automation is where I setup the named category headers from TransactionPro that I'll be using on the bills and grab the info from the Data tab (more on this in a second).
4. Paste for Export is essentially a copy of the Automation tab without the formulas (again, more on that detail in a moment).
5. SyscoItems is a list of all the items' item numbers paired with the cost of goods sub categories we have created.
6. Locations is a list of our delivery locations paired with their customer codes from the invoices. This is for class tracking in Quickbooks.

The first part is easy: copy and paste everything from the CSV file into the Data sheet. After that, though, I had to spend some time setting everything else up so that it would pull the right info from the right places and be available to throw into TransactionPro.

Sub-Category and Class Tracking

Before we get to the Automation Sheet itself, let's look at SyscoItems and Locations. I needed a pairing of the item number (which I had from the Sysco CSV download) and the cost of goods sub-category (which we set up in Quickbooks).

So, I just made a running list of every single unique item number and manually selected the category. A little work up front, but after the first couple weeks, most of the items were in my little database and matched to our categories. When a new one crops up, I add it to the list and it's all good.

Pro Tip: You can also see from the screenshot above that I create Named Ranges for important ranges. This makes it a lot easier and cleaner when referencing these ranges in formulas for automation.

The same was done for Locations. Each of our locations has a "Ship to" customer code that I paired on the Locations tab in the Locations Named Range.

Now that those two pieces were set up, I had all the information I needed to properly clean all the data and prepare for export in the Automation Sheet.

Data Automation

Using TransactionPro's template coupled with knowledge of how we were entering these bills already in Quickbooks, I setup the Automation sheet's header row 1 with the proper categories.

Our huge data set from the Data sheet is, like all good things, set as a Named Range for easy formula reference.

The very first column is the RefNumber (and yes, everything has to be spelled, spaced and capitalized exactly as it appears on that TransactionPro template because...you know, Quickbooks...more on that in the following section)

Using this formula, we can check if there's data in the cell in the Data Sheet, bring it into this cell, and leave this cell blank if not. Dragging that formula all the way down will pull all the reference numbers (invoice numbers in my case) into the Automation tab.

=IF(ISBLANK(Data!A2),"",Data!A2)        

Then on the Vendor column, I check if anything is in the RefNumber column and if there is, simply input the vendor name: in this case "Sysco Jackson Vendor". This should be the exact name of the vendor in Quickbooks.

=IF(ISTEXT(A2),"","Sysco Jackson Vendor")        

For TxnDate, DueDate, LineNumber, LineDesc, LineQty, and LineUnitPrice, I use the same type formula to test for data and then grab the proper cell's data from the Data sheet. Nothing fancy here.

For the actual items themselves (LineItem) and the location (LineClass), we need to lookup the info from those two named ranges we created: Locations and SyscoItems.

Thankfully, VLOOKUP is here to help!

Using another named range (ItemFromInvoice) which is simply the column where all the item numbers appear on the original data CSV, we can look up the proper cost of goods category from our created database of items (SyscoItems).

=iferror(VLOOKUP(ItemFromInvoice,SyscoItems,3,0),"")        

Then we do the same exact thing for the location class using the named range in the data CSV where all the location codes are (ShipTo) and the Locations named range we created on the Locations sheet.

=iferror(VLOOKUP(ShipTo,Locations,2,0),"")        

Tax: Always a Pain

So, a few of the items on our invoice aren't cost of goods and have tax amounts. That needs to be entered appropriately too.

At the bottom of the Automation sheet, I grab the unique invoice numbers from the rows above so that I can then total the tax amounts from each invoice rather than having multiple lines per invoice with small tax amounts.

=UNIQUE(A2:A116)        

Using the methods above, I grabbed the appropriate information, applied the Taxes - Sales Tax account and the Location. Then, using the formula below, I summed the tax amount from the original data.

=if(isblank(A117),"",SUMIF(InvoiceNumber,"="&A117,Tax))        

This allows us to sum the whole Tax range (which is simply the tax column in the original data) where the invoice number is equal to that row's unique invoice number.

Open up that sample Google Sheet to poke around the tabs and look at the formulas if the above section didn't quite sink in.

Quickbooks Quirks

*VERY IMPORTANT* Quickbooks is not nice when it comes to typos.

• The accounts have to be entered exactly as they appear on the templates.
• The categories have to be entered exactly as you have them setup in Quickbooks. (You should go to Chart of Accounts and/or Products and Services to see exactly how your accounts appear in Quickbooks).
• Random errors still occur. I encountered a peculiar error where one of our accounts was "Cost of Goods Sold:Paper/Plastic" and it would not import because of the slash in the name. Renaming it in Quickbooks with a space solved this.
• If no account is matched on import, TransactionPro will create a new account based on what you've entered. It is possible to inadvertently create duplicate accounts that are not properly classified for your income statement.
• If you notice an "Uncategorized Expense" on your income statement, that's what happened. Click into it to find the offending account. Go to Products and Services and select the correct expense account for that category. ??

• Strangely, once you make this change, it will only apply to future imports. To get rid of the uncategorized expense, you'll have to make a journal entry debiting the cost of goods and crediting the uncategorized expense. ??

• Quickbooks has a real issue with formatting. You may have to explicitly create the date format of your date columns for it to recognize them as dates.

• Same goes for LineQty and LineUnitPrice...and any number for that matter. It has flagged my number columns as being non-numeric a few times, and I've had to go in and explicitly declare that these columns are indeed numbers before exporting.

• Lastly, when importing, it will import everything into the Item Details section of the bill. There is not a way to have it put into Category details.

Export CSV

Are we done yet, already!? ??

Remember, setup is everything. Getting all this lined up nicely at the onset will reduce errors going forward, and create a clean system for use in the future.

We've got our data in the Automation tab nicely organized with the proper headers, but it is riddled with our formulas.

Copy everything and Paste Values Only into a new Sheet (Paste for Export).

**Don't forget the tax amounts at the very bottom of the Automation Sheet. ??

Then, make sure to sort A->Z by the RefNumber column before exporting. TransactionPro or, more likely, Quickbooks is not smart enough to match up everything by RefNumber if they aren't grouped in subsequent rows. It'll create separate invoices with those lone tax amounts on them. ????

Now make sure you're on the Paste for Export Sheet and select File-> Download -> Comma Separated Values

Import to TransactionPro

This is the easiest part and as long as everything is setup properly, it shouldn't take but a few seconds.

After opening the app from within Quickbooks Online, you'll be prompted to choose an import type (Bill for our purposes here). And then we'll upload our newly minted CSV file.

TransactionPro matches up the headers with the Quickbooks fields, and if you've copied things over from their template as we did, there should be no issues here.

You'll be asked to confirm that all is well within a classic Bootstrap Modal. ??

And hopefully, you'll see a nice imported successfully alert!

Thanks for Reading!

I've created a few of these workflows for several of our internal billing and invoicing needs.

You can do this too. ??

As with many things, it seems more intimidating on the front end than it really is. Once you've set things up for your company and gone through the process of cleaning and importing data a couple times, it's a breeze.

I hope you found this helpful, and please do reach out if you'd like to see more examples and content like this! ??