Google Sheets

Google Sheets

The Google Sheets API data source creates a table (or tables) in your Panoply database consisting of spreadsheet data collected from one or more Google Sheets spreadsheets and metadata added by Panoply.

To use the Google Sheets API data source:

  1. Click Connect, select the Google account you want to connect to, and then click Accept.
  2. Click Next, and select one or more spreadsheets to import.
  3. (Optional) Click Show to expand the Advanced fields, and adjust as necessary. Pay particular attention to the Destination and Primary Key fields.
    • The default destination table is sheets_{file name}_{sheet name} based on the source content.
    • You can configure a primary key.
  4. Click Collect to begin the data ingestion.

For each spreadsheet file, Panoply opens each sheet and imports the values row-by-row. Spreadsheets must include headers. This is because the first row of the spreadsheet is converted into column names in the target database table. Empty spreadsheet rows are not imported.

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.