Google Sheets

Google Sheets

This document describes the Google Sheets data source. Continue reading to learn more about:

  1. Collecting - what should you know about adding the data source.
  2. Data Dictionary - what data is available and how it is structured.

Collecting

To configure this data source and collect Google Sheets data:

  1. From the Data Sources menu, click Add Data Source. Add data source
  2. Search for Google Sheets, then select that data source.
  3. Click Login and follow Google’s authorization process to allow Panoply to access Google Sheets data.
    • WARNING: Google requires the logged-in user to have permissions to the data. If the permissions are not in place, some of the data will not be available.
  4. Select the Google Sheets files from which to collect data.
    • For each spreadsheet file, Panoply collects each sheet as a unique table.
    • For each spreadsheet file, Panoply opens each sheet and collects the values row by row. Empty spreadsheet rows are not imported.
    • Spreadsheets must include headers. This is because the first row of the spreadsheet is converted into column names in the target tables. See our Google Sheets demo file with properly formatted sample data.
    • WARNING: The first row (row 1) of your spreadsheet must be a complete set of column headers. If your file includes some columns without headers, the data ingestion process may exclude some of your data or fail. Be sure each of your columns has a header.
  5. (Optional) Set the Advanced Options.
    • We recommend not changing advanced options unless you are an experienced Panoply user.
    • Destination - Panoply selects a default destination. These are the tables where data is stored. The default is sheets_{file name}_{sheet name}.
    • Primary Key - Panoply does NOT select a default primary key for this data source. If you plan on uploading this file multiple times, and want to update existing rows instead of append new rows, then enter the column name that will be the primary key. Note that this primary key will be used across all the files selected.
    • Truncate - Many users find this feature useful with this data source. Use truncate to delete any data collected previously, and then add new data to the same destination table(s) based on a new collection. This is useful when you don’t have a primary key and do not want to append rows to an existing data set.
  6. Click Collect.
    • The data source appears grayed out while the collection runs.
    • You may add additional data sources while this collection runs.
    • You can monitor this collection from the Jobs page or the Data Sources page.
    • After a successful collection, navigate to the Tables page to review the data results.

Data Dictionary

Because Google Sheets data comes from a spreadsheet file Panoply cannot provide a data dictionary. But Panoply does automate the data schema for the collected data. This is the useful information to know about the Panoply automations:

  • The default format for destination table names is sheets_{file name}_{sheet name}.
  • Panoply does NOT select a default primary key for this data source. If you plan on uploading this file multiple times, and want to update existing rows instead of append new rows, then enter the column name that will be the primary key. Note that this primary key will be used across all the files selected.
  • A column in a table uses the same data type for all values in that column. Panoply automatically chooses the data type for each column based on the available values. This is important to note for this data source. If even one value in a column has text, then the entire column is considered data type Text.
    • For example, the following combination of values in a single column will be data type Number:
      • 10000
      • 10,000
      • 10.10
    • For example, the following combination of values in a single column will be data type Text:
      • 10000
      • 10,000
      • 10.10
      • 10000x
  • Regarding data types, values using commas as a decimal place (such as β€œ12,45”) can be imported as data type Number with some restrictions.
    • The β€œlocation” of the Google Sheet determines if β€œ12,45” is a number or a text. See the discussion of decimal point and comma and the Google Sheets API documentation on ValueRenderOption.
    • Someone in the United States, and using the United States version of Google Sheets, enters β€œ12,45” into a Google Sheet cell then Google will automatically format that value as a Text. Even if you manually change the cell format to Number, Google will treat it as a Text when added to Panoply.
    • Someone in the France, and using the French version of Google Sheets, enters β€œ12,45” into a Google Sheet cell then Google will automatically format that value as a Number.
  • The following metadata columns are added to the destination table(s):
    • id - Formatted as a GUID, such as 2cd570d1-a11d-4593-9d29-9e2488f0ccc2.
    • __updatetime - Formatted as a datetime, such as 2018-06-26T01:26:14.695Z.
    • __senttime - Formatted as a datetime, such as 2018-06-26T01:26:14.695Z.
    • __tablename - Formatted as {file name}_{sheet name}, such as Sales Metrics_2018Q2.
Getting started is easy! Get all your data in one place in minutes.
Try Free