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