Contact UsGet started

Creating a Google Sheets connection

Written by
Updated at September 10, 2024

Warning

Before creating a connection, learn about the limitations.

To create a Google Sheets connection:

  1. Open the page for creating a new connection.

  2. Under Files and services, select the Google Sheets connection.

  3. Add files depending on access permissions:

    1. Click + Add file and specify the link to the file. To do this, in the Google Sheets access settings, select Anyone with the link and copy the link to the sheet you need from your browser's address bar. If you copy the link from the Google Sheets sharing window, it will lead to the first sheet of the spreadsheet.
    2. Click Add. If the file contains multiple sheets, specify the ones you want to upload in the Add sheets window. You cannot select empty sheets. Enable the Sheets option to select all available sheets. Each sheet is uploaded to a separate table. Click Add.
    3. The information about the added file and the uploaded data will be displayed at the center of the screen.
    1. At the top of the screen, click Log in with Google.
    2. Select the account to switch to the DataLens application.
    3. Grant DataLens a permission to access your Google account. Click Continue.
    4. Click + Add file and specify the link to the file.
    5. Click Add. If the file contains multiple sheets, specify the ones you want to upload in the Add sheets window. You cannot select empty sheets. Enable the Sheets option to select all available sheets. Each sheet is uploaded to a separate table. Click Add.
    6. The information about the added file and the uploaded data will be displayed at the center of the screen.

    To sign out of your Google account, click . Click Sign out. The sheets from the files with restricted access will no longer be displayed.

    Note

    To properly identify the data types, select Format → Numbers in the Google Sheets menu and set the column cell format.

  4. Set the Show column headers option to Yes or No.

  5. (Optional) Enable automatic updates for the spreadsheet data. To do this, in the top-right corner, select Update automatically. The spreadsheet data will be updated once in 30 minutes or less often. To update the data manually, click Update data.

  6. Click Create connection.

  7. Select a workbook to save your connection to or create a new one. If using legacy folder navigation, select a folder to save the connection to. Click Create.

  8. Enter a name for the connection and click Create.

You can add, rename, and delete files in the connection.

What do I do if a Google Sheets spreadsheet is not working properly?

If a Google Sheets spreadsheet contains empty cells, DataLens may incorrectly display column headers and miss the first several rows.

To fix this, you need to fill empty cells in your Google Sheets spreadsheet with any appropriate values, e.g., unavailable, none, unknown.

To quickly populate empty cells in a Google Sheets spreadsheet:
1. Highlight the entire Google Sheets sheet.
2. In the spreadsheet menu, select Data → Create a filter.
3. In the right-hand corner of the first column's top cell, click the filter icon.
4. Select Clear and then select (Blanks).
5. In the column's top cell, enter the appropriate value, e.g., 0, unavailable, none, or any other.
6. Drag the cell down to fill all the empty cells in the column with the entered value.
7. Click the filter icon again and click Select all.
8. Repeat all the steps starting with Step 3 for the other columns in the spreadsheet.

Steps to follow in case of the "Document is not supported" error

This error occurs when trying to create a connection to an XLSX table from Google Sheets.

To fix it, save the table in Google Sheets format: in the table menu, select File → Save as Google table.

Limitations

When creating a connection to Google Sheets, keep in mind the following limitations:

  • Maximum number of Google Sheets sheets per connection: 10. When creating a connection, all sheets will be loaded, but you can only select ten of them to work with at the same time.

  • Maximum size per sheet: 200 MB.

    Note

    Technically, you cannot check the size of an individual sheet in the connection. If the limit is exceeded, you will get a message saying the data could not be loaded.

  • Maximum number of columns per sheet: 300.

  • Minimum number of rows per table: 2. In a table with one row, you cannot specify column types because you cannot tell a header from a data row.

Previous
Creating a connection to Yandex Documents
Next
Creating a Yandex Query connection