How To Make A Table In Google Sheets

Have you ever encountered an interesting dataset as you browse the web, and wanted to analyze it further in a spreadsheet? Perhaps you want to include this data into a custom report, or use it to drive calculations in your spreadsheet model. If so, you have come to the right place. In this article, I will walk you through a couple of approaches to quickly take a dataset found on the Internet and convert it into a table in Google Sheets.

We will explore two public datasets:

• List of ISO country codes: [all.csv]
• Covid-19 dataset published by Our World In Data available here: [owid-covid-data.csv]

Method 1 — Import Directly Into Sheets (for small files)

The first step is to download `all.csv` and `owid-covid-data.csv` to your computer using the links above.

Google sheets has a built in feature to import common file formats. Choose File → Import, navigate to your Downloads folder and choose `all.csv`.

Import a small file into Sheets

While the above method works well for relatively small files, you run into issues if you try it on a larger file. In this case, `owid-covid-data.csv` is approximately 35 MB in size and has about 127,000 rows.

Method 2 — Create Table Using Dataprep and BigQuery (for large files)

To avoid the above issue, you can load the data into BigQuery using Dataprep, then use the BigQuery connector for Google Sheets to bring the data in.

The following video demonstrates how to accomplish that. The high level steps are the following:

1. Create a Dataprep flow and upload the CSV file
2. Create a recipe to filter and summarize the data (optional)
3. Define the output as a BigQuery table
4. Run a job to populate the BigQuery table
5. Use the BigQuery connector to pull the results into Google Sheets

In conclusion, it is possible to create a table in Google Sheets from many common file formats. Depending on the size of the file, you can perform a direct import, or use Dataprep and BigQuery to create a data pipeline into Sheets.