Convert PDF to Google Sheets

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.

A Quick Look: How to Convert PDF to Google Sheets

The best tip is to convert PDF to Google Sheets using Google drive. Here’s how:

  1. Upload your file to Google Drive
  2. Open your Drive and locate the file
  3. Click on the file and choice open with
  4. Select Google Docs
  5. Copy the data into Google Docs
  6. Paste onto your spreadsheet in Google Sheets

Why Convert PDFs to Google Sheets?

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

  1. When you need to edit data or use any functions that Google Sheets provides you in order to interpret your data.
  2. It saves you the time and energy of having to type out the data manually.
  3. 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.

Examples 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.

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.

PDF to Convert to Google Sheets

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

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

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 might be a bit of a long process and require 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:

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

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

  1. Click on Open With, followed by Google Docs.
  2. This will open the PDF in Google Docs.
  3. 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.
    Tab separated data from PDF
  4. Press CTRL+H to open the Find and Replace Dialog box.
  5. In the input box next to Find, type a space character (simply by pressing the space bar on the keyboard).
  6. In the input box next to Replace with, type a comma character ‘,’.
    Replace space with comma
  7. Press the Replace all button. This will replace all the space characters with commas.
  8. Close the dialog box.
  9. We now have a comma-separated file.
    Comma separated data
  10. 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:
    Replace comma with space
  11. Download the file as a Plaintext file (.txt) by navigating to File —> Download —> Plaintext (.txt)
    Download as Plain text

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

  1. Once the plaintext version of the file is downloaded, open it in notepad.
    Open in Notepad
  2. Navigate to File->Save As.
  3. 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.
    Save the file as CSV
  4. 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

  1. In your browser, open Google Sheets by typing sheets.Google.com in the browser location bar.
  2. Navigate to File->Open (or just press the shortcut CTRL+O).
  3. This will open the ‘Open a file’ dialog box.
    Open a file dialog box
  4. Select the Upload tab and press the button that says, ‘Select a file from your device’.
    Select Upload a file option
  5. Double-click on the CSV version of your file from its appropriate folder.
    Select the CSV file you want to open
  6. This will now load the contents of your table into Google Sheets.
    CSV file data opens in 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.

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

Even though Google Sheets does not provide an easy way to convert 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 file. 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:

  1. Open the PDF file in Google Drive
  2. Click on the button on top that says ‘Open with’.
  3. In the dropdown menu that appears, select the ‘+ Connect more apps’ option.
    Click on Connect More Apps
  4. This will open the Google Workspace Marketplace, from where you can find the apps you need.
  5. In the search bar on top, type ‘PDF table extractor’.
    Search for PDF table extractor
  6. Select the PDF Table ExtractorApp from the app options that appear.
    Select the PDF Table Extractor App
  7. Click on the Install button.
    Click on Install
  8. You will be asked for permission to install PDF Tables Extractor. Click Continue.
    Click on Continue
  9. You will then be asked to confirm if you want to allow PDF Extractor to add itself to Google Drive. Click Allow.
    Click on Allow
  10. 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 on Allow again
  11. Click Allow once more.
    Click on Allow Once More
  12. You should now see a message at the bottom left of the browser window that says ‘PDF Tables Extractor has been connected’.
  13. Once again, click on the button on top that says ‘Open with’.
    Click on Open with
  14. From the dropdown menu that appears, this time select PDF table extractor.
    click on pdf Table extractor
  15. The application should now open.
  16. On the left side of the window, click on the button that says ‘Open file from Google drive’.
    Click on Open file from Google Drive
  17. Select the PDF file that you need to convert, press Select and wait for the file to get loaded into the application.
    Select the file you want to open
  18. 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.
    File uploaded and stop button disabled
  19. Once the file has been loaded into the application, click on the button that says ‘Start, Extract Tables’.
    Click on Start Extract Tables
  20. 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.
    Processing to CSV file completed
  21. You will also see the HTML version of the file in an HTML table format below.
    HTML version of the table
  22. You can now go ahead and copy this table, after which you can paste it into Google Sheets directly.
    Copy the table into Google Sheets

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

PDF Extra

Check Price on Amazon

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

Check Price on Amazon

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:

  1. 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
  2. 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.

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 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:

Spreadsheet Expert at Productivity Spot | + posts

Sumit is a Google Sheets and Microsoft Excel Expert. He provides spreadsheet training to corporates and has been awarded the prestigious Excel MVP award by Microsoft for his contributions in sharing his Excel knowledge and helping people.

Popular Posts
You May Also Like