When you’re importing invoices to QuickBooks Online Advanced, the most time-consuming step is the first one. Before you can import your invoices, you need all the data in one place. Compiling invoices into an accurate and organized list is time-consuming and requires a lot of manual copy and paste to get the numbers together. Invoice to Sheet can speed up this process, moving data from your email to a spreadsheet in just a few clicks.
Never again will you need to slog through compiling all of your PDFs into a folder.
No more opening them one by one and extracting numbers line by line with copy-paste.
Just open the PDF in Invoice to Sheet, extract your data, and check the numbers.
Let the add-on do the work 💪.
Four simple steps:
- Find your invoices in Gmail
- Open Invoice to Sheet and extract your invoice content into a spreadsheet
- Prepare your data for import
- Import your spreadsheet into QuickBooks
Check your QuickBooks version. This tutorial is for customers of QuickBooks Online Advanced, not QuickBooks Desktop
Step 1: Track down your invoices in Gmail
Invoice to Sheet works from inside Gmail, extracting PDF attachments from your emails. Get started by finding the invoices you’d like to import.
Are you struggling to find your invoices even though you KNOW they’re in there...somewhere? We’ve all been there. Sure, finding that email after sifting through 200 results is rewarding, but why not make it easier on yourself? Save time with advanced Gmail search. If you’re not very familiar with the tool, we’ve written an in-depth guide to show you how here.
What to do when your invoices are stored in multiple locations
Email is great, isn’t it? I can’t remember the last time I received a printed invoice. Still, I’m sure you have invoices hanging around somewhere besides your Gmail inbox.
Getting invoices into your email so they can be extracted with Invoice to Sheet is easy. Just email them to yourself!
Need to navigate a labyrinth of Drive folders to find them? A couple tips and tricks can speed up your search. Check out this article with time saving tips to help you find invoices in Drive more quickly.
Oh, and if you actually have printed invoices, just say no to the scanner and take a picture with your phone. 🤓
Step 2: Use Invoice to Sheet to extract structured data from your invoices
Our goal is to import the data from all of our invoices into QuickBooks in the most efficient way possible.
To do this, we’re using Invoice to Sheet to extract the valuable information from each invoice and compile it into a single spreadsheet. We’re then going to import the contents of that spreadsheet into QuickBooks.
To use Invoice to Sheet we need to have it installed first. Click here to install Invoice to Sheet from the Google Workspace Marketplace.
Let’s run through the process to extract data from an invoice.
Find an email with a PDF invoice attached.
Open Invoice to Sheet from the Google Workspace Add-on Sidebar in Gmail.
Follow the prompts to extract data from your invoice.
Once extracted, use the list of data to review what the add-on found and correct any issues. Invoice to Sheet uses Google’s Document AI. It’s not perfect...yet. 🦾🤖
When you’ve finished, scroll back to the top and select a destination for your data.
Follow the prompts to send your data to a spreadsheet. Once it’s finished, open the spreadsheet, and let's take a look at what we’ve got.
There it is, all of your data extracted from a PDF: like magic. 🧙♂️
Invoice to Sheet has now moved all the data from your Invoice, importing the data you just reviewed as a new row in the specified spreadsheet.
Now repeat this process for every invoice you’d like to extract data from and import to QuickBooks Online.
Step 3: Preparing your spreadsheet for import into QuickBooks Online
We need to organize the data and columns in our spreadsheet before we can import it into QuickBooks.
The two main objectives of this step are to:
- Organize the data we’re importing to ensure it maps correctly in QuickBooks
- Remove unwanted data from our spreadsheet
Download a template CSV from QuickBooks
While this step is not required, using the file format in this QuickBooks template will save you time formatting your spreadsheet.
- Click here to download the CSV template from QuickBooks
- Click here to view the template in Google Sheets and make a copy
Don’t be confused by the sample file, which shows multiple rows for each invoice- this is intended to demonstrate invoices with line item level detail, which is optional.
Either adjust your spreadsheet file or fill in the template with the data you’ve extracted using Invoice to Sheet.
Using the template can help reduce errors during the import process. QuickBooks will allow you to define a mapping for data in your spreadsheet to data in QuickBooks, so the exact naming of your columns is up to you.
Next, remove data from your spreadsheet that you don’t want to be imported to QuickBooks. This will make it easier to accurately organize the rest of your data.
Here’s an example of the most basic possible import for 10 invoices:
Required fields for your data to import successfully to QuickBooks Online
To ensure our data maps correctly in QuickBooks, we must take a few steps to ensure we have the 5 following required fields. Check each of them for consistency and accuracy.
Invoice Number: Use the invoice number to assign a unique identifier to each invoice. This invoice number must be unique and not match any invoice already existing in QuickBooks.
Want your own invoice numbers to be imported? Turn on "Custom transaction numbers" in Accounts and Settings, or your invoice numbers will be replaced by standard QuickBooks invoice numbers.
Customer: The customer must always be spelled consistently across your spreadsheet, and QuickBooks to avoid duplication.
Make sure to confirm whether or not you need to include a title like LLC or INC. During the import process, you’ll get a chance to ask QuickBooks to create new customers for any that haven’t yet been set up in the system (like if this is the first invoice from them).
Invoice Date: This date is for the invoice itself. This is not the payment due date.
Due Date: The date payment is due for this invoice.
Item Amount: If you are importing an invoice without line items, use the Item Amount as the total amount for the invoice.
Invoice to Sheet is currently limited to extracting the invoice total amount and won’t include line item level extraction, but we plan to enable this as soon as the data quality is improved from Google’s DocumentAI.
If you wish to include line item level details like quantity or product names in your import, add one line in your spreadsheet for each line item, and match them all to the same Invoice Number and customer. When importing, QuickBooks Online will import all of the data under a single invoice.
What about taxes? If you have taxable items, include a Tax Rate column and enter the tax rate name or percentage.
Similar to line items, Invoice to Sheet is currently limited in its ability to extract tax information. For now, the add-on will include a field to indicate whether or not the invoice contains taxes (with possible values Yes or No). Tax rate details will be included in the future once the accuracy of this information is improved from Google’s DocumentAI.
What else? We won’t go into too much detail beyond these requirements. QuickBooks Online is a very flexible tool and supports many different custom fields for your invoices. Because everyone does their invoices a little bit differently, it’s best to make your own decision about what data you do and don’t include.
You can import a maximum of 100 invoices at a time and 1,000 row limit per spreadsheet.
Step 4: Importing your invoices into QuickBooks Online
Now that you’ve got your invoice extracted and organized in an import file, follow these instructions to import invoices into QuickBooks
With your finished spreadsheet, open it in Google Sheets and download it as a CSV file from the file menu.
Head over to QBO and open the ‘Import data’ tool from the settings menu in the top right corner.
Select Invoices from the Import Data menu
Select your CSV file to upload into QuickBooks
Make sure to check ‘Add new customers to QuickBooks’ if your invoices are from new customers. If the names listed on your invoice don’t exist or don’t match what’s in QuickBooks, you will receive the following error:
Map your spreadsheet columns to QuickBooks data fields.
Make sure to select the date format to match your data!
At this point, QuickBooks will show you the outcome of your import. If the numbers look good, press the Complete Import button to finish the process. If something doesn’t look right, select ‘Previous’ to make changes.
Your import is now complete
And that's that! Quickbooks will finish by confirming all of the new information imported to QuickBooks.
Excited to give it a try? Install Invoice to Sheet today and import invoices to QuickBooks more quickly. Speed up your invoice management workflow, and liberate the data trapped in your PDF files.