Skip to content

Google Sheets Data Source Integration Tutorial

In this tutorial, we will go trough all necessary steps to enable Google Sheets to be used as a data source.

Setting up a Google Service Account

  1. Go to Google Cloud Console and sign in with your Google account. If you do not have a Google Cloud project, create one by clicking on "Select a project" and then "New Project".
  2. Click on "APIs & Services" in the sidebar, search for "Google Sheets API" and enable it. Do the same thing for "Google Drive API". Return to the homepage after this step.
  3. Under "IAM & Admin" in the sidebar, select "Service Accounts" and click on "+ Create service account" to initiate the service account creation process. Fill out the form and create the account (make sure to select proper roles)
  4. Account that you have created will appear in the table on "Service Accounts" dashboard, select the account and navigate to "Keys" tab, select "Add Key" followed by "Create new key" option, the "Key type" should be "JSON". Selecting "Create" will create the key and will prompt you to download the json file. Make sure to download it as you will need this later.

Warning

Navigate to a google sheet that you want to use as a data source for CxReports and share it with the email of the Google Service Account that you have created. Row 1 of the sheet that you want to use should have names of the headers that you want in the Data Table in the CxReports.

Setting up Google Service Account in CxReports

  1. After logging in, navigate to "Google Drive" option under "Workspace Configuration" in the navigation menu and click on "+ Add New Service Account"
  2. Fill out the form with necessary information:
    • Name: Name of the Google Service Account within the CxReports environment
    • Email: Email of the Google Service Account that you have created in the first step of this tutorial
    • Scopes: Enable the scopes (both Google Sheets and Google Drive will be enabled/disabled simultaneously)
    • Key (JSON): Copy and Paste the contents of the JSON key that you have downloaded during the first step of this tutorial
  3. Select "Save"

Creating Google Sheet Data Source

  1. Navigate to the report in which you want to use a Google Sheet as a Data Source
  2. In the navigation bar on top, select "Report" tab, navigate to "Data Sources" and select "+ Add new data source"
  3. Fill out the form with necessary information:
    • Name: Name of the data source
    • Display Name: The display name of the data source
    • Type: Select "Google Sheets"
    • Google Account: From the dropdown, select the Google Service Account that you have created in CxReports
    • Sheet ID: Add the ID of the google sheet that you want to use (the location of the Google Sheet ID is highlighted in this example https://docs.google.com/spreadsheets/j/3p7m1L034M8-GgKQzcOtinkpBreia1gIbBR9nKHTh3vL/edit?gid=0#gid=0)
    • Range: Add the sheet range in the format of sheet_name!columns_range (for example, Sheet1!A:Z)
  4. Select "Save"
  5. Drag & drop "Data Table" component from the component bar to the report page
  6. Select the data source that you have created under "Data source" option in the "Configuration" tab of the component and click "Generate columns"