Connecting to Google Sheets

Contents[Hide]

1. Overview

Google Sheets are online spreadsheets which you can create and edit for free. Just go to https://docs.google.com/spreadsheets/ and sign in to your Google/Gmail account to begin creating spreadsheets.

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. Developers Console Project

You need to create or use an existing project in the Google Developers Console as a first step.

Go to https://console.developers.google.com/project and sign in to your Google/Gmail account.

Once signed in, you'll see a list of existing projects (if there are any) or you can click Create Project to create a new one.

Create a new project
Create a new project

In the New Project dialog, enter a name for your project and then click Create.

Enter the project name
Enter the project name

2.2. Enable the Drive API

Make sure you are logged into the Developers Console and your project is selected as the current project.

Click APIs in the left navigation under APIs & auth to see the API Library.

Click the Drive API link under Google Apps APIs.

API Library
API Library

Click Enable API.

Click Enable API
Click Enable API

2.3. Service Account Credentials

This section shows you how to generate Service account credentials which will allow Dundas BI to access your Google spreadsheets.

In the Google Developers Console, click Credentials in the left navigation under APIs & auth. On the right, click Add credentials and select the Service account option.

Add Service account credentials
Add Service account credentials

Select the P12 option and click Create to generate a certificate file. This file will be needed later to set up a data connector in Dundas BI.

Create a P12 key
Create a P12 key

The New public/private key pair dialog is displayed and shows the password for the private key. Copy and paste this password into Notepad for later use.

Also download the generated certificate file which has the extension p12. Then close the dialog.

P12 key file and password is generated
P12 key file and password is generated

Copy and paste the Service account email address into Notepad for later use. You'll need to enter this email address when setting up the data connector in Dundas BI.

Service account email address
Service account email address

Click on the Service account email address link to see more details about this service account including its Client ID. This ID may be required in the Authorization step which is described next.

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 in to 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 in Notepad and select the Can View option. Then click Done.

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.

Set the Data Provider dropdown to Google Sheets.

Enter the Service Account E-Mail Address which you recorded into Notepad previously.

Clock Choose File and select the P12 certificate file which you downloaded (most likely in your Downloads folder).

Enter the service account email address and choose the certificate file
Enter the service account email address and choose the certificate file

Enter the password for the certificate file which you also recorded into Notepad previously.

If you are using the Delegate domain-wide authority method, enter the email address of the account that created the spreadsheets in the User Account E-mail Address field. Otherwise, leave it blank.

Click the Spreadsheet dropdown and select the one you want.

Click Submit to create the data connector and begin discovery.

Enter the password and select the spreadsheet
Enter the password and select the spreadsheet

You can now visualize your spreadsheet.

Create a new dashboard using the Blank template. Go to Explore and locate your newly created data connector. Expand it and drag a sheet to the dashboard canvas.

View your Google Sheets data on a dashboard
View your Google Sheets data on a dashboard

4. See also

 

 

Dundas Data Visualization, Inc.
500-250 Ferrand Drive
Toronto, ON, Canada
M3C 3G8

North America: 1.800.463.1492
International: 1.416.467.5100

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