Connecting to Google Sheets

Sisense enables easy and quick access to databases, tables, and views contained in Google Sheets databases.

Note:

  • If you are connecting to a Google service remotely, and the address of Sisense is something other than localhost, Google requires that you connect using the OAuth 2.0 protocol. See Google Authentication in Linux for more information.
  • To authenticate with your GSuite account (Linux only):
    • Navigate to https://console.cloud.google.com/marketplace/product/google/sheets.googleapis.com, select the app, and enable it.
    • Do the same for Google Drive: https://console.cloud.google.com/marketplace/product/google/drive.googleapis.com.
  • To work with Google Sheets, you must have a professional Google account, since Google's API is only available to business accounts.

For the list of supported connectors, see Data Source Connectors.


To import data from your Google Sheets:

  1. In the Data page, open an ElastiCube model or create a new ElastiCube model.
  2. In the Model Editor, click . The Add Data dialog box is displayed.
  3. Click Google Sheets. The Google Sheets Connect area is displayed.
  4. Click Google and enter your Google Sheets credentials.

  5. Once you have logged in, click Next. The Add Data area is displayed with a list of available spreadsheets for your account.

    8-5v7googlesheetsthumb0300.png

    Note:

    The loading time of the list of available documents is expected to take longer than usual because an additional scan of the shared documents is run.

  6. Select the relevant spreadsheet and click Done. A list of tabs included the sheet are displayed.
  7. Select the relevant tabs within the sheet and click Done. All the selected tables are added to your schema.
  8. Enter the following details:
    • Culture: Select the culture for your spreadsheet. This defines settings such as the format of the date and time or delimiter (decimal or comma). To change the default culture, see Changing the Default Culture.
    • First Row Contains Field Names: Select to specify table column names based on the header in the first row of the spreadsheet.
    • Ignore rows that start with: Specify rows to ignore that start with a specific symbol, value or letter.
    • Static Range: This enables you to select a specific range of data in the sheet. Data needs to be in a table structure, starting at the top-left cell of the range, with field names as the first row.
      Enter two cells, each with a leading $ sign and a colon as a delimiter. Press Enter to preview the selection. For example, for a static range between cells A1 and E10, type in A1:E10.
  9. Click Done. The relevant data is added to your model's schema.

Note:

When connecting to Google Sheets, it's best practice to use Quick Access because it uses your existing Google connection token (Google imposes of limit of 50 connection tokens).


Out-of-Memory Issues

When building an ElastiCube with this connector, you might receive an "out of memory" error. To add more memory, see Troubleshooting Performance Issues.

Limitations

  • Table field headers (column titles) must be strings for full backwards compatibility.