Convert PDF to Google Sheets

Sumit

Last updated November 7, 2023

The world of data sharing and saving changed when Adobe invented the PDF format. It became a common thing to work with data received in the form of PDF documents.

You might have a scanned copy of important data that you need to process in Google Sheets, or you may want to work on a dataset received from someone or downloaded from a website in PDF form. In which case, you’ll need to learn how to convert PDF to Google Sheets.

Unfortunately, there is no way to directly open a PDF in Google Sheets or convert one to a format that can be processed in a Google worksheet. This is because PDF documents can be extracted into many file formats, such as images, text, tables, and more, making data extraction difficult.

There are, however, ways to indirectly get your data into a Google Sheet.

In this tutorial, we will discuss different ways to convert PDF files to Google Sheets format. We will also give you some tips and tricks to handle PDF to Google Sheets parsing rules.

This Article Covers:

A Quick Look at the Steps to Convert PDF to Google Sheets

Here’s how to convert PDF files to Google Sheets using Google drive:

Upload your file to Google Drive
Open your Drive and locate the file
Click on the file and choose Open with
Select Google Docs
Copy the data using Ctrl + C in Google Docs
Paste into your spreadsheet in Google Sheets

Methods of Converting PDF to Google Sheets

At this point, there are largely two ways to convert PDF to Google Sheets format:

By using other Google applications like Google Drive and Google Docs.
By using third-party applications or online tools to automatically convert PDF data.

Let us look at each of these methods one by one.

Related reading: How to Convert PDF to Word on Mac [5 Easy Ways]

Using Google Docs to Convert PDF to Google Sheets

Let’s say you have the following PDF file saved somewhere on your computer’s hard disk.

If you want to open this file in Google Sheets, here’s what you have to do:

Go to your Google Drive
Navigate to New > File Upload.
You should now see a File Upload dialog box from where you can select the folder you want to fetch your PDF file.
Double-click the name of your PDF file.
Your file should start uploading and you can see this from a box at the bottom right corner of your browser window.
Once the file is done uploading, the box will say ‘1 upload complete’.
Click on the name of your PDF file.
The file will open in a popup within the same tab.
Click on Open With, followed by Google Docs.
This will open the PDF in Google Docs as a text document.
Select the part of the PDF document that you want to work with and copy it (by pressing the keyboard shortcut CTRL+C).
Open a Google Sheets document, select the cell where you want the contents of the PDF document to be placed, and press CTRL+V to paste.

Your PDF file’s table contents should now be visible in Google Sheets, and you can subsequently work on this data as required. This is the best method to convert PDF to Google Sheets free. Google Docs is very versatile and can also convert word to PDF.

When using this method, there are a few important points to note, though:

If the data in a PDF file is stored as plaintext rather than in the form of a table with rows and columns, this method might not work.
When you paste data from a PDF into Google Sheets, you might also bring in hidden table cells or characters.

If the data is in plain text, then you might need first to convert the data into CSV format.

How to Insert PDF into Google Sheets by Converting it to CSV Format

This is a longer process for converting PDF files and requires a little more effort on your part.

Here are the steps you need to follow in case you come across such a PDF file:

Use Google Drive to upload the File:

Go to your Google Drive
Navigate to New->File Upload
You should now see a File Upload dialog box from where you can select the folder you want to fetch your PDF file.
Double-click the name of your PDF file.
Your file should start uploading, and you can see this from a box at the bottom right corner of your browser window.
Once the file is done uploading, the box will say, ‘1 upload complete’.
Click on the name of your PDF document.
The file will open in a popup in the same browser tab.

Use Google Sheets to Save the File as a Plaintext File (.txt)

Click on Open With, followed by Google Docs.
This will open the PDF in Google Docs.
If you see the contents of the tables in the form of plaintext, then you will need to convert the contents into a comma-separated format. Notice that the contents of each cell are separated by a space. We need to take advantage of this and convert all space characters to commas.
Press CTRL+H to open the Find and Replace Dialog box.
In the input box next to Find, type a space character (simply by pressing the space bar on the keyboard).
In the input box next to Replace with, type a comma character ‘,’.
Press the Replace all button. This will replace all the space characters with commas.
Close the dialog box.
We now have a comma-separated file.
In our sample file, we might need to make a few adjustments since the file already contained values separated by commas in the last column of the table. This might get treated as a separate cell when converted into Google Sheets. So for our convenience, we removed the commas between the last three values (all belonging to the last column) of each row. We replaced the commas with space, as shown below:
Download the file as a Plaintext file (.txt) by navigating to File —> Download —> Plaintext (.txt)

Use Notepad to Save the File as a Comma Separated Value File (.csv)

Once the plaintext version of the file is downloaded, open it in notepad.
Navigate to File->Save As.
In the Save As dialog box, type the name of the file, followed by .csv, since we want to save the file in comma-separated value format.
Click Save.

Now all that’s left to do is open the CSV file in Google Sheets

Use Google Sheets to Open the CSV File

In your browser, open Google Sheets by typing sheets.Google.com in the browser location bar.
Navigate to File->Open (or just press the shortcut CTRL+O).
This will open the ‘Open a file’ dialog box.
Select the Upload tab and press the button that says, ‘Select a file from your device’.
Double-click on the CSV version of your file from its appropriate folder.
This will now load the contents of your table into Google Sheets.

You will notice every value that you separated with commas now occupies individual cells in Google Sheets. You can now work on the contents of the file and process the data as you need to.

Note: You might need to do a little more work to clean up the data by removing extra blank cells, removing hidden or illegible characters, formatting date values, etc. You’ll also have to add back any hyperlinks from the original text if it had any.

Using Third-Party Applications / Online Tools to Convert PDF to Google Sheets

Even though Google Sheets does not provide an easy way to convert multiple PDF files to a format that Google Sheets understands, there are several applications and online tools available to get this done quickly and easily. Some may use parsing rules to directly convert PDF to Google Sheets.

In this section, we will discuss how to add PDF to Google Sheets using some of these tools.

Using Google Workspace Marketplace (PDF Tables Extractor) to Convert PDF to Google Sheets

The PDF Tables Extractor is a Google application designed to extract tables from a PDF document. It extracts all tables in the file, making sure it retains the row and column structure of the tables.

It then converts the file into a CSV format that you can directly edit. It also displays the extracted (and edited) table in the form of an HTML table within the browser. You can easily copy this table into Google Sheets directly.

You can use this app to extract tables from PDF files both on your Google Drive and well as your local hard disk.

Here are the steps you need to follow to use the PDF Tables Extractor to convert your PDF file to Google Sheets:

Open the PDF file in Google Drive
Click on the button on top that says ‘Open with’.
In the dropdown menu that appears, select the ‘+ Connect more apps’ option.
This will open the Google Workspace Marketplace, from where you can find the apps you need.
In the search bar on top, type ‘PDF table extractor’.
Select the PDF Table ExtractorApp from the app options that appear.
Click on the Install button.
You will be asked for permission to install PDF Tables Extractor. Click Continue.
You will then be asked to confirm if you want to allow PDF Extractor to add itself to Google Drive. Click Allow.
You will now be asked to confirm if you want to view and manage Google Drive files and folders that you have opened or created with this app. Click Allow.
Click Allow once more.
You should now see a message at the bottom left of the browser window that says ‘PDF Tables Extractor has been connected’.
Once again, click on the button on top that says ‘Open with’.
From the dropdown menu that appears, this time select PDF table extractor.
The application should now open.
On the left side of the window, click on the button that says ‘Open file from Google drive’.
Select the PDF document that you need to convert, press Select and wait for the file to get loaded into the application.
You will see the name of the file on top of the application window when it is done loading. The ‘Stop’ button will also get disabled.
Once the file has been loaded into the application, click on the button that says ‘Start, Extract Tables’.
This will convert your PDF file to CSV format. When processing is done, the Stop button will again become disabled, and you will see the word ‘Completed’ appear in green next to the Stop button.
You will also see the HTML version of the file in an HTML table format below.
You can now go ahead and copy this table, after which you can paste it into Google Sheets directly.

Using Other Tools to Convert to PDF to Google Sheets

There are plenty of tools online that can convert PDF to Excel files. All you have to do is upload them and click Convert. Then you can open the Excel files in Google Sheets easily by uploading them to your Google Drive.

One example of a free online tool that can do this is Online2PDF.com

This is a free site but can run into issues when working with larger files, so you may want to consider paid software instead. Here are our two favorite programs for high-quality PDF conversion that are compatible with spreadsheets.

PDF Extra

A high-quality PDF suite that rivals Adobe Acrobat with a single lifetime price. It creates accurate copies into Excel files that open easily in Google Sheets.

PDF Converter Ultimate

PDf Converter Ultimate is a cheaper alternative. While it isn’t quite as accurate as PDF extra. It is still much better than any free option available on the web.

Automating PDF to Google Sheets Conversion

To automate the conversion process for PDFs to Google Sheets, you will need to use one of the following:

Webhooks – These are HTTP callbacks defined by the user to trigger an event in a web page. They can facilitate many things on the page, including automating processes like converting PDFs to Google Sheets
API – This interface lets you modify your spreadsheet more freely in Google Sheets. It can perform functions like creating sheets, modifying formats, managing connected sheets, and in this case as well, automating the conversion of PDF to Google Sheets.

You can also use a third-party app with this specific feature.

Why Convert PDFs to Google Sheets?

There are a number of reasons why you would want to import multiple PDF files to Google Sheets:

When you need to edit data or use any functions that Google Sheets provides you in order to interpret your data.
It saves you the time and energy of having to type out the data manually.
Converting PDF to Google Sheets is also a good way of avoiding errors since there are functions in Google Sheets that can detect and eliminate errors easily.

Converting PDFs to Google Sheets is an intermediate skill. We’d highly recommend completing a Google Sheets Course to help you wrap your head around skills such as this. Below is our favorite one from Udemy.

The Complete Google Sheets Course: Beginner to Advanced!

PDF Conversion Metrics

There are a lot of ways to convert PDFs to Google Sheets, so to find the best way for you, it is important to know how each performs using PDF conversion metrics. The following are the ways to measure the performances of these methods:

Accuracy

The accuracy of the conversion method is determined by how well the method is able to extract data to Google Sheets and how fast the data extraction and data transfer process is.

Ease of Use

It is essential to consider how easy it is to use the conversion method when evaluating it. It is better to have a more straightforward method to save time and speed up the learning process.

Language Functionality

It may be necessary to consider if the method supports multiple languages, especially if you are working internationally. Some methods used to upload PDFs to Google Sheets may not work with specific languages, which would make them redundant in those situations.

Software Integration

It is advantageous if your method can support a wide range of software other than only Google Sheets. This will determine how well it fits into your workflow or if more unnecessary steps need to be taken.

Frequently Asked Questions

Can Google Sheets Pull Data from a PDF?

You can’t pull data from PDF to Google Sheets, at least not on its own. Most of the time, you’ll need to use another program or software like Google drive. You can also insert PDF into Google Sheets and save it as a plain text file. However, even then, you’ll still need Notepad to convert the file into a CSV file before reopening it.

Can You Scan a PDF and Convert it to Google Sheets?

You can scan a PDF and convert it to Google Sheets by making it readable using Google Docs and then copy and paste it into your spreadsheet. You can also use a third-party app with features for scanning multiple PDF files or images. Using Online2PDF is also a good way to embed PDF in Google Sheets if you are using a scanned document.

Conclusion

A quick Google search will give you a number of other third-party app options. You can select the platform you are most comfortable with and you can convert PDF to Google Sheets in a jiffy.

We hope this tutorial was helpful for you. You can also check out how to convert excel to Google Sheets.

Related: