Connecting to Google Sheets

Contents[Hide]

1. Overview

Google Sheets are spreadsheets that you can create and edit online.

This article shows you how to set up a data connector in Dundas BI to load data from a Google spreadsheet via the Google Drive API.

Using Google Sheets
Using Google Sheets

2. Setup

2.1. Create or choose a project

Go to https://console.cloud.google.com/ and sign into your Google account.

Once signed in, you can create or choose an existing project to use. The current project is often listed at the top of the screen and you can click to see a list of projects and switch between them or to create a new one.

The cloud resource manager page should also list your projects and allow you create a new one.

Create a new project
Create a new project

2.2. Enable the Drive and Sheets APIs

Click to access the navigation menu from the top-left corner, hover over APIs and services and select Library.

API Library
API Library

Ensure the correct project is selected at the top of the page, then search for and click Google Drive API.

Click Enable.

Click Enable
Click Enable

Return to the library, then search for and click Google Sheets API.

Click Enable.

Click Enable
Click Enable

2.3. Service account credentials

Service account credentials allow Dundas BI to access your data from Google.

To create credentials, from the navigation menu, hover over APIs and services and select click Credentials. On the Credentials page, click Create credentials and select the Service account option.

Add service account credentials
Add service account credentials

Enter a name and role for your service account.

When done, record the service account's e-mail address, for example by opening Notepad on your computer and pasting it.

Service account email address
Service account email address

Next, add a key to your service account. For example, click to edit the new service account, go to the Keys tab if applicable, and click Add Key. Choose a JSON key type.

When finished, a certificate file should be downloaded. This file will be needed later to set up a data connector in Dundas BI.

2.3.1. P12 file type

If you require the P12 file type instead of the default JSON, you will require some additional information.

A popup will appear displaying the password for the private key. Copy and paste this with the service account's e-mail address for later use.

2.4. Authorization

Now that the credentials have been generated, they must also be authorized to access the spreadsheet data. There are a couple of ways to proceed depending on the type of account used to create the spreadsheets.

If you used a Gmail account to create your spreadsheets, first sign into Google Sheets. Open a spreadsheet that you want to connect to from Dundas BI. Click the Share button in the top right corner.

Enter the Service account email address which you recorded previously (e.g., in Notepad), or it may be automatically suggested if you've already used it. Customize the permission level to Viewer and uncheck the option to notify if preferred, then click Share or Send.

Share this spreadsheet with the Service account email address
Share this spreadsheet with the Service account email address

If the spreadsheets you want to connect to were created with a Google Apps domain account, your Google Apps domain administrator must perform the authorization. The steps are generally described here: Delegate domain-wide authority to your service account. You'll need the Client ID for the service account (see previous section) and also make sure the API Scopes field includes the following:

https://docs.google.com/feeds,https://spreadsheets.google.com/feeds

3. Data connector

Log on to Dundas BI and create a new data connector.

Enter a Name for the connector, then set the Data Provider dropdown to Google Sheets.

Click Choose File and select the certificate file which you downloaded.

Choose the certificate file
Choose the certificate file

  • If the certificate file is of a P12 file type, you now have to provide the Service Account E-Mail Address and Password For the .P12 File that you recorded previously.
  • If you are using the Delegate domain-wide authority authorization method, click to expand the Advanced options to enter the User Account E-mail Address for the account that created the spreadsheets. Otherwise, leave this field blank.
Type in the title of your Spreadsheet, or select from the popup that appears below this field.
 
In version 8.0.2 or later, you can include the wildcard characters * or ? in the spreadsheet title to refer to multiple spreadsheets with the same structure. This will access their combined data from the same data connector, like in the example shown for Excel.

Click the Submit button at the bottom of the dialog to save and check in the data connector and automatically discover the data's structure.

4. See also

Dundas Data Visualization, Inc.
400-15 Gervais Drive
Toronto, ON, Canada
M3C 1Y8

North America: 1.800.463.1492
International: 1.416.467.5100

Dundas Support Hours:
Phone: 9am-6pm, ET, Mon-Fri
Email: 7am-6pm, ET, Mon-Fri