The ability to convert a PDF to Google Sheets is an incredibly useful skill, and we will cover three different methods in this tutorial.
One of the most common formats for distributing reports is the PDF format. They usually have tables that contain important data for analysis:
However, copying the table directly into Google Sheets doesn't work:
Is there still a way to convert your PDF to Google Sheets? Of course! In fact, you have three choices. Scroll down to learn these three methods and choose which one you think is the best for you.
Unfortunately, this task is not as simple as pressing Copy + Paste.
PDF stands for portable document format, which means that it carries instructions for how to display a sequence of characters on a screen. This makes the PDF format versatile and useable for many different operating systems, but it also makes it difficult to easily transfer PDF data (text, charts, figures) into systems that do not have a PDF reader.
To convert a PDF to Google Sheets, the computer must extract the data from the instructions that the PDF contains. Data extraction takes place when one converts a PDF to Google Docs, which allows the computer to turn the PDF file into a format that Google Drive programs can understand.
Another option is to convert PDF to an Excel file, which you can then copy paste into your Google Sheets. We will show you how to convert PDF to Google Docs and then to Google Sheets because it does not require and outside converter.
From there, one can easily copy and paste between Google Docs and Google Sheets because they are similar systems. However, this disconnect between the PDF file format and the format that Google Sheets can recognize prevents the simple copy paste method of converting PDF to Google Sheets.
Lido is a new spreadsheet that automates tasks and is fully compatible with Google Sheets. It has two built-in features that Google Sheets does not for converting PDFs into Google Sheets.
You can sign up for Lido for free: https://www.lido.app/go/signup
Lido's PDF conversion features are paid but there is a 14 day free-trial. This is the most accurate method for extracting table data from PDFs to sheets based on our testing.
You will see this modal to upload your PDF. Choose a file from your desktop, or drag and drop it into the modal.
On the next screen, you will see your PDF. If there are multiple pages in your PDF, you'll see them all in the left section.
Use the boundary box to select the section of the PDF that you want to extract into your sheet.
Then, click Extract data.
That's it! Your PDF data will now be converted to spreadsheet format.
If your PDFs are already in a Google Drive folder and you want to avoid drawing the extraction box every time, you can use Lido's =EXTRACTTABLESFROMPDF() formula instead. This will only work if your PDF data is tabular, but can save substantial time if you have many PDFs to convert.
^^^
formula
=EXTRACTTABLESFROMPDF()
Given a Google Drive file link, this table will automatically extract all of the tables in the file and return them in your spreadsheet.
^^^
The syntax is for the formula is:
=EXTRACTTABLESFROMPDF(<google-drive-credential>, file_link, output_cell).
To set up the formula, you will need to click + Add Credential to connect to your google drive
Then, select a PDF from your Google drive that you want to extract data from:
And then finally, choose an output_cell in your spreadsheet where you want the extracted data to be returned. For example, if we choose B1 as our output cell, then the extracted data will populate as such starting in cell B1:
Configure your formula then click Run action to perform the PDF data extraction.
This will run the formula and all of your data will appear in a range starting from the output_cell location that you selected.
There are a few things that Lido will allow you to do from here. If you want to export it into a Google Sheet, you can easily to so by clicking File -> Export to Excel file.
Alternatively, if the PDF to spreadsheet conversion is only one part of your workflow, then you might be able to automate additional parts with Lido as well. Learn more about how to automatically send emails from Lido with your converted PDF data.
This first method will first convert your PDF to Google Docs, from where you can copy and paste the document into your spreadsheet. Due to the incompatibility between PDF and Google Sheets, you may not perfectly duplicate the table that is in the PDF.
Alternatively, you can just drag-and-drop the file to Google Drive to upload it.
You have now copied the table!
The conversion process is not perfect, but it significantly reduces the work you need to do in transferring data.
Another method involves changing a PDF file to a file format that can be imported directly to Google Sheets. One such format is the comma-separated values (CSV) format. There are online converters you can use to transform PDF to CSV file such as convertio.co.
Insert your PDF document into the PDF converter and click convert. Most PDF converters allow you to choose a format, so make sure that you choose CSV.
After converting the PDF document, download the resulting file to your computer. We will need this new file in the next steps.
A box labeled Import file will appear. You can drag the comma separated value file to it or click Select a file from your device and look for it.
This will be the file that you created in step 1 and downloaded to your computer.
Once the CSV file is uploaded, settings will appear before importing the data. We recommend setting the parameters for the Import location to Insert new sheet(s) and the separator type to Detect automatically.
The table is now uploaded!
Again, the format may not reflect what it originally looks like, but you can easily format the data to your liking without having to manually enter it.
You can easily import data from PDF to Google Sheetsin just a few clicks. Simply follow the steps below:
1. In your Google Drive, go to the upper-left corner then click New.
2. Click File upload, then select your file.
3. Open the PDF file.
4. Click Open with, then select Google Docs.
5. The PDF file is converted to Google Docs.
6. Copy the table from Google Docs and paste it into your Google Sheets spreadsheet.
Related Articles
