Skip to content

Google Sheets Data Source Integration Tutorial

In this tutorial, we will go trough all necessary steps to set up a Google Drive job.

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 drive that you want to use as a storage for reprots from CxReports and share it with the email of the Google Service Account that you have created.

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"

Setting up a Google Drive job

  1. Navigate to "Jobs" section within "Workspace Configuration"
  2. Go through the wizard until you reach "Delivery" section. For more information, consult Emails section
  3. Select "Add Google Drive"
  4. From the dropdown select "Google Account", the one you have previously created
  5. Add "Folder Id" - you can find it by navigating to the folder within Google Drive that you want to use and copy the ID from the url. Example: https://drive.google.com/drive/u/0/folders/1lcVUmXDIXRgcA00UNeLJyJhPXkPYKMMg
  6. Optionally, add the "Subfolder Path" - this option will create a subfolder within the Google Drive folder. For example, you can use {[new Date()]:dt;YYYYMMMdd} to create a subfolder with a current date