Skip to main content

Importing data from Google Sheets

Important

Please note that this feature is currently not supported for Holistics 4.0. See note 1 in Feature Comparison docs for more information.

Introduction​

Holistics 3.0 allows you to import data from Google Sheets into your data warehouse for BI & reporting purposes. In this tutorial, we will show you step-by-step on how to do that.

High-Level Approach​

The general workflow to import data from Google Sheets is:

  1. Prepare the Data Warehouse in which Holistics has been granted additional WRITE permission to import data.
  2. Create an Import Model that links to the Google Spreadsheet
  3. Grant Holistics permission to connect to your Google account.
  4. Configure sync settings
  5. Save & Finish.

Behind the scene, Holistics connects through Google Sheets API, download the data and load into a table in the data warehouse. That table is then exposed as a data model in the Holistics modeling layer.

Step-by-step Instructions​

1. Prepare your Data Warehouse​

You should already connected to your Data Warehouse. Make sure your datawarehouse also have WRITE permission.

Then go to Modeling and make sure the data warehouse is selected.

  • First, click on Create Data model, choose Data Import and select Google Spreadsheets.

  • Then, connect to Google Spreadsheet by following these steps:
    • Paste the URL of your Google Spreadsheet into the Google Source URL box.
    • Click Validate and select the spreadsheet you want to connect.
    • Now, you can preview the structure of the destination table before proceeding.

3. Grant Holistics permission to connect to your Google account​

If this is the first time you connect to a spreadsheet, you will be prompted to grant Holistics permission to connect to your Google Account.

The popup will show only one time since one Holistics account can only link to one Google account for all imports.

Note

Please make sure that Holistics's popup is allowed in your browser for the authentication to work.

✍If you want to link to a new Google account:

  • First, you need to remove Holistics app in your old Google account. Visit this page for Remove third-party account access.
  • Then just paste the URL and click Validate again. The pop-up will show to ask you to reconnect.
Note

Please note that the old imports might fail if the new account does not have permission to access them.

4. Advanced Settings​

From Advanced Settings you can modify the destination table from Destination Settings, and control how column types will be cast from Sync Configuration. Please visit the dedicated page for more details.

  • Destination Settings:
COMMON GOT-CHAS
  • Make sure that you've set the Destination Table to a schema with WRITE access. If you've changed it, remember to click "Apply"
  • Right now, Holistics forces normal text for the Destination Table Name, that's why you must ensure the target table name is lowercase

  • Sync Configuration:

Holistics could map your data to one of the Generic Data Types first (Whole Number, Decimal, TrueFalse, Date, DataTime, Text) by default. Remember to select the suitable data types in the Sync Configuration section before starting loading your data.

Finally, click Create. Now you have your Google Sheets data in Holistics for you to analyze, merge with your database data, and visualize on your dashboards.

Other Notes​

Prepare your Google Sheets​

Formatting requirements:

  • Remove extra headers and footers.
  • The sheet needs to be in a tabular format, have a header row with column names
  • The sheet can not have any rows with non-empty cells after the actual data rows end. The typical example of this is a "Totals" row at the end, summing up each column.
  • Ensure the correction of data structure by cleaning values that don't match the data type specified.
  • Resolve cell errors before importing the data.
  • Change the decimal separator. If your Google Sheet uses a comma (,) for the decimal separator, data may not accurately be brought into Holistics. The issue is Holistics expects the decimal separator to be a period (.). By opening your Google Sheet and changing the locale of your spreadsheet (File > Spreadsheet settings) to United States, for example, it will change the decimal separator to a period.

Date formatting in Google Sheets​

In order to successfully import dates to Holistics, the date formats need to be either:

  • YYYY-MM-dd
  • MM-dd-YYYY

If you are using specific Date formats in Google Sheets that are not formatted in a way that works with Holistics, there are two approaches to deal with this case:

  1. Change the date format of your date column to YYYY-MM-dd or MM-dd-YYYY from your Google Sheet file.
  2. Save your date column as String and when each time query you need to cast that value to Date with the right format to be read by Holistics.

Refresh source​

After adding new columns or removing any column to the imported Google Sheet, you need to re-validate the sheet's link following these steps:

  1. Click on the Data modeling tab and go to the model with the imported Google Sheet
  2. Click on the Manage button on the right panel and Re-validate the URL. Note that this will also revert all of the column names and data types to default.

Refresh the Data Warehouse​

When you create a new schema in your Data Warehouse to store Google Sheet import, remember to refresh the Data Warehouse that allows choosing this new schema as a destination for your Google Sheet data.

You can follow the instruction below:

  1. Go to Tools, choose Data Manager
  2. Select the data source
  3. Click Refresh

Limitation​

  • Currently, Holistics doesn't support incremental/upsert mode for Google Spreadsheet.

    You will need to either use Full/Append import mode or if you want still to do incremental/upsert import, you could do it via other external tools like Fivetran or Stitch.

  • There is currently no way to import several tabs at once. If your Google Sheet has multiple sheets/ tabs, we recommend importing each tab individually.

Troubleshooting​

ERROR: "failedPrecondition: this operation is not supported for this document"​

When I import a Google Spreadsheet or export to Google Spreadsheet, I encounter this error: failedPrecondition: this operation is not supported for this document?

This error happens normally because the file was saved in .xlsx extension and you need to converted it to Google Sheet format by choosing the option "Save as Google Sheets".

To avoid causing any harm to our data, I want to grant Holistics READ-ONLY access to the database but still be able to import data to it​

Our recommended course of action is to create a designated schema in this database replica that does allow for WRITE access so you can specify that Holistics writes the Google Sheet to only this schema.

If your team gives WRITE access only for this schema and keeps other schema access as READ-ONLY, Holistics will only be allowed to import tables and store transform model tables in this schema, and the operation fails if it attempts to select any other schema for the table destination.

Otherwise, your team needs to set up their own Extract-Load (EL) on your end to make this Google Sheet available in your database for querying.

(Redshift Only) I get the error 'stl_load_errors' when loading the sheet into the database​

The most likely reason is that your original Google Sheet data is not clean and some value does not match the data type specified. However, the exact error will need to be observed from the stl_load_errors table.

For more details, you can also run this simple query on your end:

SELECT * FROM stl_load_errors;

Please help to check and correct the data on your end then try to create a plain new import model.

Let us know what you think about this document :)