Connecting to Google Sheets
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.
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.
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.
Ensure the correct project is selected at the top of the page, then search for and click Google Drive API.
Click Enable.
Return to the library, then search for and click Google Sheets API.
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.
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.
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.
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.
- 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.
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.