Google Sheets and CSVs
  • 26 Jul 2022
  • 5 Minutes to read
  • Contributors
  • Dark
    Light

Google Sheets and CSVs

  • Dark
    Light

Overview

Two of the most popular sources of data to include in a database are Google Sheets and CSV files. Preset supports both of these data sources, as described below.


Connect a Google Sheet

In order to connect a Google Sheet, you will need Administrator permissions on your workspace. Let's start by learing how to include data from a publicly-shared Google Sheet in Preset.

This is a Google Sheet that has the permission setting of Anyone with the link. To learn how to connect Preset to a privately-shared restricted Google Sheet, please see Google Sheets (Private) Connection.

Set up the Database

In the Toolbar, hover your cursor over the plus + icon, then Data, and then select Connect Google Sheet.

New_Connect_Google_Sheet

The Connect a database window appears.

In the Display Name field, enter a meaningful name for the new connection to your public Google Sheet. In this example, we simply used the default Google Sheets.

In the Type of Google Sheets Allowed field, retain the default selection of Publicly shared sheets only.

GSheet_Public_Enter_Display_Name1

Copy the Google Sheet Link

For this walkthrough, we are going to use a Google Sheet that contains data about Netflix titles. You can view the sample Netflix GSheet here external-link_10x10.

Set access permissions

Before continuing, make sure that the Google sheet has its permission set for anyone with a link.

Public Google Sheets Privacy

Users will only be able to access a public google sheet if you provide them with the exact sharable URL. Public google sheets won't appear on search engine results, etc.

Copy the permalink to your clipboard

testgif1.gif

Connect Google Sheet as a Table

Our next step is to connect the Google Sheet as a table to the database.

Return to Preset and, in the Google Sheet name field, enter a name for a table — in this example, we called our table "Netflix Data".

In the URL field, paste the Google Sheet URL from your clipboard.

To create additional tables, then select + Add Sheet and repeat the process.

When done, select Connect.

GSheet_Public_Add_URL1

Step 3 of 3 will appear. At this stage, you can configure Advanced options, if needed. To learn more, see Advanced Connection Settings.

GSheet_Public_Advanced_Step_3_of_3

To finalize the connection, select Finish.

In order to create charts, though, one last step is required: add a dataset from your newly-created Google Sheet database.

Add a Dataset to Preset

Start by navigating to Datasets. From the Data screen, select the Datasets tab.

GSheets_Public_Navigate_to_Datasets

Then select + Dataset.

GSheet_Public_Select_Add_Dataset

The Add dataset panel appears.

In the Database field, select the database name that you defined earlier in the Display Name field.

In the Schema field, select a schema (if applicable).

In the See Table Schema field, select a table that you created in Step 3 above.

Select Add.

Great, you are now ready to start creating charts using your Google Sheet data!

GSheet_Public_Add_Dataset


Upload a CSV File

In addition to adding Google Sheet data to a database, you can add comma-separated value based data (CSV file). In order to do so, you will need Administrator or Primary Contributor permissions on your workspace.

Let's see how this is done.

Connect a Database and Enable Uploads

The first step is to connect a database to contain the uploaded CSV file.

Be sure to add a database!

Please note that a CSV cannot be uploaded to one of the sample databases provided in your Preset account, so be sure to add your own database to make use of the CSV upload feature!

In order for the database to accept the CSV, you need to enable data uploads. Start by browsing to the Databases screen and then select the Edit pencil icon for your database.

Edit_Database

The Edit database panel appears. Select the Advanced tab and then expand the Security panel.

In the Schemas Allowed for CSV Upload field, enter the name(s) of any schemas in your database that should be available for CSV data uploads. If more than one, seperate each by a comma.

In the Allow data upload field, select the checkbox.

Lastly, select Finish.

Enable_Data_Upload

To confirm, in the Databases screen under the CSV upload column header, ensure that a checkmark is displayed for your database row entry.

In the example below, CSV data upload has been enabled for the "BigQuery - Financial" database:

CSV_Upload_Confirm

Configure the CSV Upload

In the Toolbar, select Upload file to database and then, in the sub-menu, select Upload CSV.

New_Upload_Options

Alternatively, you can hover your cursor over the plus + icon, then Data, and then select Upload CSV to database from any screen in Preset.

New_Upload_a_CSV

The CSV to Database configuration screen appears.

In the Table Name field, enter a memorable name for the table to be created from the CSV data.

In the CSV File* field, select Choose File and select the CSV file on your local or network system.

In the Database field, select the database to use.

In the Schema field, select the schema associated with the selected database.

In the Delimiter* field, specify the symbol used to delimit the data. For a whitespace, use \s+.

In the Table Exists* field, select what to do if a table already exists: Fail (do nothing), Replace (drop and recreate table), or Append (insert data).

In the Header Row field, enter 0 if your CSV data already has a header row or leave empty if there is no header row present.

In the Index Column field, enter the column to use as row labels for the dataframe. Leave empty if there is no index column.

In the Mangle Duplicate Columns field, select the checkbox to specify duplicate columns as "X.0, X.1".

In the Skip Initial Space field, select to skil spaces after the delimiter.

In the Skip Rows field, enter the number of rows to skip at the start of the file.

In the Rows to Read field, enter the number of rows to read.

In the Skip Blank Lines field, select to skip blank lines instead of interpreting them as NaN values.

In the Parse Dates field, enter a comma-separated list of columns that should be parsed as dates.

In the Infer Datetime Format field, select to use Pandas to automatically interpret the datetime format.

In the Decimal Character field, enter a character to interpret as a decimal point.

In the Dataframe Index field, select to write the dataframe index as a column,

In the Column Label(s) field, enter one or more column labels for index columns. If none are provided and if Dataframe Index is true, then index names are used.

Null Values: List of values that should be treated as null, in JSON format.

When done, select Save.

*: Required

CSV_Upload1

After selecting Save, you will be taken to the Datasets screen where your newly-loaded CSV and associated dataset appear.

CSV_Uploaded

Visualize the CSV Data

From here, you can select the dataset to launch it in the Explore page and proceed with configuring a chart.

Visualize_CSV_Data1


Was this article helpful?