API: Google Sheets

This requires Admin or Data Admin privileges for implementation.

Please Note: If you do not have the correct privileges to access Data Hub you will receive the following upon your login attempt. If you get this message and think it is an error please see your organization's iDashboards Administrator for assistance as Support is unable to give you access.

This article will outline the process required to create an API connection to Google Sheets, as well as how to turn that Google Sheets data into a data set that can be used to build charts/dashboards. 

Connecting to Google Sheets – Creating a Data Set

Creating an ETL Job  – Creating a New Chart

Connecting to Google Sheets

1. Log into iDashboards Data Hub and select System then API Accounts
   
2. This shows the API Account options as well as any existing API connections. Click Add Account
   
3. Now select Google Sheets, Next then Login
   
4. Log into your Google account and follow the steps below: Open a new tab and go to Google Developer Account, it is the same as Google Analytics, or any other Google API. In the Google API Dashboard, click Enable APIs and Services
5. Search for Google Sheets API and Google Drive API, click ENABLE for each
6. From the left hand navigation select Create Credentials
   
7. Open Create Credentials dropdown and choose OAuth client ID
8. Under Application type choose Web Application
   • Name: iDashboards Data Hub (or other descriptive name)
   • Authorized redirect URIs: Use the Redirect URL value from the Add Account box in Data Hub.
   • Unless this URL includes a top level domain (.com, .org, etc) you will need to use localhost in place of your server/PC name.
   1. 1. 
9. Click Create, A box will open with the Client ID and Client Secret copy and paste those values into the appropriate field, in  Add Account box in iDashboards Data Hub.
   1. 1. 
10. Click Log in and choose your account in the new window.   
11. Click ALLOW
    1. 1.  
12. You will now see the Account Created Successfully window. Close this and go back to Data Hub.
    1. 1. 
13. You will now see the newly created Google Sheets account available
    1. 1. 

Connecting to Google Sheets

Creating an ETL Job  –  Creating a New Chart

Creating a Data Set

1. You completed adding your Google Sheets API connection. You are now ready to create a new data set with the ETL tool. Select a New Data Set
   
2. Select Cloud API and choose Google Sheets
3. In the next window, select the sheet you would like to use and choose either:
   • the entire sheet
   • a named range within the sheet
   • or a custom range you can define through the cell range
     
4. Once you  selected the data range you want to use, you will have the option to define the data type for each column or use the detected type. Make any necessary changes and click Save.
   
5. You will see a summary of your Data Set along with options to:
   • Add Parameters
   • or Define Column Names
     
6. After making any changes, click Save As, name your data set, and then click Close. Your Data Set is now available in Data Hub, however, in order to make it available as a Data Source for a Dashboard/Chart/Picklist/Form, you will need to format it utilizing an ETL job.

Connecting to Google Sheets

Creating a Data Set  – Creating a New Chart

Creating an ETL Job

1. From the Data Hub Lobby at the top of the screen click: ETL, OR New ETL Job.
2. In the ETL canvas, click the E in the upper left hand corner and drag and drop it anywhere on the canvas to start a data extract.
   
   
3. Hover over the Extract Data Tasj and click the gear (or double click on the box). This will open a window where you can select the data set you created.
4. Click Open and an Extract Data box will open and click Save.
   1. 
5. Create a Load Data into Table. by dragging and dropping the L box anywhere onto the canvas.
   
6. Hover over the Extract Data Task, select the arrow and connect it to the middle of the Load Data into Table.
7. Hover over the Load Task and click the gear (or double click the Load Task) to configure the Load and save the data to a table within your database.
   1. 
8. Click Create a Target Table from Input
9. Select your datastore, and for Schema select dbo
   
10. Name the table and click Create
    1. 1. 
11. Verify your details in the next window and click Save.
    1. 1. 
12. Now you can run your ETL job by clicking the play button in the upper right hand corner. Once that is complete, click Save or Save As to save this ETL job for future use. Your Google Sheets data is now available as a data source that can be used to create a chart in the Builder Application.

Connecting to Google Sheets 

Creating a Data Set – Creating an ETL Job

Creating a New Chart

1. Log into the Builder Application, select Chart then the (+).
2. In Chart Designer, in Data Set find and select your Datastore from the list
3. Then select the table you created in the ETL and click Next
   
   
4. Now, select the columns you want to use and click Next.
   
5. Choose your Chart Type
6. Then, Edit/Select Chart Properties and click Save.
7. You will now see your new chart with Google Sheets data.

Connecting to Google Sheets – Creating a Data Set

Creating an ETL Job  – Creating a New Chart