Skip to main content
Version: v2

Connecting to Google Sheets

Before starting

Before you begin, learn how to create a data source

Step 1: Enable Google Sheets API

Please go to the Google Sheets API page and activate the API in the project you want to use

enable_gsheet_api

Step 2: Prepare Service Account

Canner Enterprise uses Service Account (Service Account) Authentication to operate Google Cloud APIs, so it is necessary to prepare Service Account (Service Account)

Please refer to Google Cloud Authenticating as a service account > Creating a service account section

  1. Go to Google Cloud Create Service Account page
  2. Select the project
  3. Start to create a service account (Service Account); enter the information like the following gsheet_create_sa
  4. Skip the second and third steps, click Finish directly, then return to the list screen, click the service account just created gsheet_sa_info
  5. Click Key, click Add Key, select the JSON type gsheet_download_sa_json
  6. The downloaded JSON content will be as follows gsheet_sa_json

Step 3: Authorize Google Sheets to Service Account

  1. Go to the Service Account page created in the previous step and copy the email address.gsheet_sa_info
  2. Go to the Google Sheet that you want to link to Canner Enterprise, click "Share", and paste "Email Address", add it as a new user, and give it the role of "Viewer." gsheet_auth_sa
  3. After completing the settings, the following figure will appear, one more Service Account user gsheet_auth_sa_done

Step 4: Create Data Sources

You can create a data source through the following two operations. The first is to click the "+" button on the sidebar Data Source or click the Create a Data Source button on the Overview page to create.

1_gsheets

Step 5: Set connection information

In the pop-up window form, fill in and set the connection information, and click Submit to send. The setting details of relevant connection fields are as follows.

3_gsheets

  • Name: database name, for display, can be modified later
  • Data Source Type: database type, please select Google Sheet
  • Google Sheet ID: Please fill in the Spreadsheet ID of Google Sheet
Get Spreadsheet ID of Google Sheet

In the URL of Google Sheets, please copy the URL fragment framed in red in the figure below gsheet_sheet_id

  • Encoded Credentials: Key content of Service Account
How to fill in the key content of the Service Account
  1. Click the pencil icon to the right of the input connect_gsheet_click_convert
  2. After pasting the JSON content in Step2 in the pop-up window, click Convert to return to the form and continue to fill in other fields 4_gsheets

Step 6: Complete the build

After Submitting, the S3 data source will show up in the sidebar in a few moments, and you can click to enter the data source details page.

5_gsheets