Overview

Using Google Sheets is a productive and simple way for teams to collaborate via spreadsheet data. With a Yellowfin connector, you can connect your Google Sheets data to extend its usefulness using the Yellowfin range of data analytics tools.

While Yellowfin is connected to your Google Sheets data, it automatically updates at regular intervals to keep your source data and your Yellowfin data synchronized. It’s visible in Yellowfin as a data source. From the data source, you can create views, then reports, charts, dashboards and more.

To connect Yellowfin to Google Sheets:

1. Install your connector
2. Configure Google
3. Connect your accounts
4. Create content

Install the Google Sheets connector

Installing a connector to use with Yellowfin requires access to Yellowfin and to Yellowfin Marketplace. To install a connector:

• download the connector from Yellowfin Marketplace; and,
• upload the connector to Yellowfin.

Each of these steps are described in the sections below.

Download a connector from Yellowfin Marketplace

1. Go to Yellowfin Marketplace and login:
   http://www.yellowfinbi.com/YFWebsite-Marketplace-77991
   
   
2. Scroll through the list of connectors until you find the free Google Sheets Connector, then click on the Read More link to expand its details
3. Click on the Download button to download the connector.

Upload the connector to Yellowfin

When adding a new connector to Yellowfin, ensure that your Yellowfin user has enough access to complete the steps below. Their role will need some of the checkboxes checked under the Data Sources & Views role in the Yellowfin admin console. The steps below presume the user is logged in to Yellowfin with the correct access level.

1. From the Yellowfin burger bun  menu in the top left corner, click on Administration, then Plugin Management
   The Plugin Management dialog box will be displayed:
   
   If you have already installed any other plugins or connectors, they will be listed here
2. Click on the Add button to display the New Plugin dialog box
   
   
   
3. Enter a name and a description, which will be used to list the connector on the Plugin Management page, then upload the connector that you downloaded from Yellowfin Marketplace
   Yellowfin will process the connector and display its contents:
   
4. Click on the Submit button to add the connector to your Yellowfin instance
   

Now that you’ve installed the Google Sheets connector, you’re ready to configure your Google credentials to securely connect with Yellowfin.

Configure Google

Yellowfin can only display Google Sheets data via the connector after some strict security checks are made. You will therefore need a Google client ID that has the Google Sheets API enabled. The process is covered in the steps below. Once complete, you will use these credentials to set up a new data source in Yellowfin via the connector.

Create Google Client ID

The instructions below are not related to Yellowfin software. They are provided here only to cover the basics of the configuration you’ll need to use Google Sheets in Yellowfin. If you need further help, please refer to any instructions provided by the third-party software provider.

1. Go to console.developers.google.com to open the Google APIs console
2. Click on the Credentials link on the left
3. At the top left of the screen, click on Select a project, to display a dialog box, then click on NEW PROJECT in the top right corner of the dialog box
4. Type a name for the project, such as ‘Yellowfin’, and optionally, edit the provided project ID, then click on the Create button
5. On the Credentials page, click on the +CREATE CREDENTIALS link at the top of the page, then click on OAuth client ID
6. If you’re prompted to provide further information on the OAuth consent window, click on the CONFIGURE CONSENT SCREEN button and provide the requested information, then click on the various buttons, including the final BACK TO DASHBOARD button, then repeat step 5
7. From the Application type dropdown, select Desktop app, and enter any additional information required
8. Click on the Create button
   The OAuth client created confirmation box will appear, displaying your client ID and your client secret
9. Click on OK in the bottom right corner

Enable Google Sheets API

1. From the Google APIs console, click on the Library link on the left, and if you’re prompted to enable the API library, enable it
2. In the search bar, type google sheets api
3. From the search results, click on Google Sheets API
4. Click on the Enable button to enable the API

Connect your accounts

Now that your Google APIs console has been configured and your Google Sheets connector is installed within Yellowfin, you can connect the two. To complete these steps, you will:

• create a new data source; and,
• create a secure connection between Google and Yellowfin.

Create a new data source

1. In Yellowfin, click on the yellow Create button  and select Data Source from the list
   
   

   Now, you will see the Google Sheets connecter listed under Or choose a platform
   

   
2. Click on the Google Sheets connector
   

Identify your connection

The fields in the New Connection dialog box should be set to match your requirements. The table below describes how they work. The first set of fields relate to authenticating with Google. Once that’s complete, you’ll need to make some decisions for the following fields.

1. Provide a name and a description for your connector if these fields are empty
   
   
2. Enter your client ID and client secret from your Google APIs console in the relevant fields
3. Click on the Generate Authorize URL button
4. Click on the Authorize Google Sheets button
5. Choose your Google account (the one you used to create the Client ID and Client Secret fields)
6. You may get a verification warning message on the app.
   
   Note: To remove this warning screen, submit your data usage and server host to Google for review
7. If the message above appears, click on the Advanced link and any further confirmation links (there may be three or four) to get to the confirmation dialog box below
   
   
8. Ensure each checkbox is checked, then click on the Continue button (you may need to scroll down)
   Google will display a code
   
9. If the validation fails, your Yellowfin session may have timed out and you’ll need to log in and create this connection again (it’s much faster the second time around).
10. Copy the code, then switch back to Yellowfin
11. Paste the code into the 3. Enter PIN field
12. Click on the Validate Pin button
13. Scroll down the dialog box to see the other pre-filled fields
    
    
14. Check the default values are consistent with your needs (see the table before these steps for further information), and adjust as required
15. Use your mouse and the Ctrl (PC keyboard) or Command (Mac keyboard) key to select multiple files from the list displayed in the Include File(s) field, which displays the list of files available in your Google Drive
    IMPORTANT: save time later by making sure you select the right files here: if you think you need to use a file, include it in your selection!
    
16. Click on the Create View button
    Yellowfin will process your selection into a data source and display the next step, where you can create a single-table view
    

Create content in Yellowfin

Once you’ve successfully connected Yellowfin to any Google Sheets in your Google account and selected the files to be used as a data source, you’re ready to create a view. The spreadsheets you selected in the previous screen will be used to populate a list that contains each tab from each spreadsheet (in the format of ‘sheet name – tab name’). A tab effectively works as a table within a view, presuming your content is prepared accordingly.

Due to the nature of third-party connectors, a view created from a third-party connector data source can hold a single table only. In this step, you can create your first single-table view. If you need additional views from this data source, you can use the yellow Create button  to create additional single-tables views. If you wish to use data from more than one Google Sheet tab, you can use a sub query to pull the data into a single report.

Once you’ve chosen which Google Sheets tab to use as a table in your view and provided the view with a name, Yellowfin will process the view and display the Report Builder.

Create a View

1. From the Select Table dropdown list, click on a Google Sheet tab to use as a view
2. In View Name, enter a name for your view, and if you’d like to add a description, enter it into View Description
   
3. Click on the Create Analysis button

Yellowfin will process the view and display the Report Builder, where you can build reports, charts and more. See the wiki page on building reports for more information.

Add more sheets later on

In a perfect world, you'll be able to see all the Google sheets you need to access. Alas, a perfect world doesn't consider new data in new Google sheets, or accidentally omitted sheets during the initial setup of a new data source. You can add more Google sheets at any time via the Data Sources section of the Admin Console.

1. From the Yellowfin burger bun  menu in the top left corner, click on Administration, then Admin Console to display the Yellowfin administration options
   
2. Click on the Data Sources section at the top to expand it, where you should see your Google Sheets data source
   
3. Click on the Google Sheets data source to open it
   
4. Click on Connection Settings, then scroll down to see the list of currently selected Google sheets
   
5. Hold down your keyboard's multi-select key (eg Ctrl on a PC keyboard or Command on a Mac keyboard) to ensure your currently selected files remain selected (if you don't hold down the key, the current selection will be lost as soon as you select a file)
6. Use your mouse in conjunction with your keyboard's multi-select key to select any additional files
   
   
7. When you have selected all relevant files, scroll back to the top of the window and click on the Save button

The Google sheets you selected from the file list will now be available within Yellowfin. If you wish to check, reopen the connection and check the file list.

Additional info

Limitations of the Google Sheets API

The Google Sheets connector is dependent on the Google Sheets API which is controlled by Google. Google may at any time change how their API works which may cause the Connector to stop working.

For the latest information on limitations, visit the Google Sheets developer site. https://developers.google.com/sheets

Access Refresh Schedule

Google Sheets connection parameters include the Access Token and Refresh Token. This is because Google Sheets access tokens are only valid for a one-hour period, and subsequently need to be refreshed periodically to maintain a connection. Yellowfin automatically refreshes the tokens as a background task, every 50 minutes, ensuring that the connection is always valid.