Connecting to Google Sheets
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.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.
In the New Project dialog, enter a name for your project and then click Create.
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.
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.
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.
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.
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.
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.
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).
If the certificate file is of a P12 file type, you now have to provide the client credential information that you generated previously:
- Paste the Service Account E-Mail Address that you recorded previously.
- 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.
4. See also