Datacenter supports datasets of type Google Sheets, allowing you to pull data from existing workbooks in your Google Sheets data sheets. This is particularly useful for scenarios when a user needs to pull data from existing tables stored in data sheets format, for example when working directly with Google Sheets projects or with imported Excel files.

Follow these steps to configure a Google Sheets dataset:

  1. Create a new dataset of type Google Sheets, specifying a name and (optionally) a prefix to use to identify the dataset and its values:

    image-20250210-131358.png

  2. Click Add. The dataset is added to the list, and it has an initial WARNING status as no endpoints have been added yet:

    image-20250210-131908.png

  3. Click on the sheetname endpoint in the Dataset Configuration panel:

    image-20250210-132113.png

  4. Enter the spreadsheets ID of the file you want to pull data from, in the SpreadsheetId field of the Endpoint Fixed Options section on the right panel:

    image-20250210-132204.png

Info: The Google Sheet spreadsheets ID can be found on the spreadsheet URL. It is the alphanumeric string of letters and numbers that comes after the “/d/” portion of the URL:

image-20250210-134710.png

Another option is to use an App Script custom function that uses the getActiveSpreadsheet method of SpreadsheetApp, returning the id of the spreadsheet using the getId()method:

function GET_CURRENT_SHEET_ID () {
return SpreadsheetApp.getActiveSpreadsheet().getId()
}

  1. Back to the Dataset Configuration panel, double click on the Sheet Values endpoint. This adds an endpoint card in the Dataset view.

    image-20250210-132537.png

  2. Finally, click on the endpoint dataset card.

    image-20250210-132800.png

  3. Select the sheet name corresponding to the Google Sheet tab your data is store into:

    image-20250210-133317.png

    Due to the dataset’s pre-selected script GoogleSheets.js, the entries in the first row of the sheet are interpreted as headers and are automatically used to define the keys of the extracted values. For example, when working with the following sheet:

    image-20250210-133728.png

    The (flattened) dataset output in Datacenter is:

    image-20250210-134152.png

    If, however, the GoogleSheets.js script is removed form the dataset processing options, then the raw (flattened) dataset output in Datacenter looks like this:

    image-20250210-134121.png