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 Analytics data.

In the API Manager, click Credentials in the left navigation. On the right, click Create credentials and select the Service account key option.

Add Service account credentials
Add Service account credentials

Set the Service account dropdown to New service account. Then enter the Name for your service account.

Click Create to generate a certificate file. This file will be needed later to set up a data connector in Dundas BI.

Create a key
Create a key

2.3.1. P12 file type

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

The Service account created dialog is displayed and shows the password for the private key. Copy and paste this password into Notepad for later use.

Close the dialog to see the list of service account keys which includes the one you just created. 

Click Manage service accounts in the top-right corner.

Click Manage service accounts
Click Manage service accounts

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

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.

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

Choose the certificate file
Choose the certificate file

If the certificate file is of a P12 file type, you now have to provide the client credential information that you generated previously:

  1. Paste the Service Account E-Mail Address that you recorded previously.
  2. Enter the private key password for the certificate file.

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.

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