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:
- Install your connector
- Configure Google
- Connect your accounts
- Create content
Note: This connector required a change to the Yellowfin user interface. It is compatible with Yellowfin versions 9 and 8.0.10. Due to a change in Google’s authentication methods, the previous connector will not work with any versions of Yellowfin.
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
- Go to Yellowfin Marketplace and login:
- 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
- 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.
- From the Yellowfin burger bun
The Plugin Management dialog box will be displayed:
If you have already installed any other plugins or connectors, they will be listed here menu in the top left corner, click on Administration, then Plugin Management
- Click on the Add button to display the New Plugin dialog box
- 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:
- 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.
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.
- Go to console.developers.google.com to open the Google APIs console
- Click on the Credentials link on the left
- 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
- Type a name for the project, such as ‘Yellowfin’, and optionally, edit the provided project ID, then click on the Create button
- On the Credentials page, click on the +CREATE CREDENTIALS link at the top of the page, then click on OAuth client ID
- 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
- From the Application type dropdown, select Desktop app, and enter any additional information required
- Click on the Create button
The OAuth client created confirmation box will appear, displaying your client ID and your client secret
- Click on OK in the bottom right corner
Enable Google Sheets API
- 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
- In the search bar, type google sheets api
- From the search results, click on Google Sheets API
- 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
- In Yellowfin, click on the yellow Create button
Now, you will see the Google Sheets connecter listed under Or choose a platform and select Data Source from the list
- 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.
Set incorrect data to null
Yes — Any data that’s inconsistent with the defined data type will appear as null.
No — Yellowfin will display the data.
Number of rows used to define the data type
Set the number of rows for Yellowfin to scan to define the column data type. Yellowfin determines this based on the majority of records (for example, if there are six metrics and four dimensions, Yellowfin would define the data type as a metric). By default, this is set to 10.
Select any Google Sheets files that you expect to use within Yellowfin. To create a view from a Google Sheet tab source, it must be selected in this field. This selection can be edited at any time through the Admin Data Sources. If you’re upgrading from a previous Google Sheets connector, select any files that it used.
If a Google Sheet is removed from this data source or from the drive that the data source connects to, any reports and views associated with the Google Sheet will stop displaying data entirely. Yellowfin periodically checks for updates to any Google Sheets selected and updates any related data within Yellowfin accordingly.
The maximum number of Google Sheets that can be connected is the quota of request per minute Google assigns to your account by default is 40. This number can be increased by submitting an application to Google. Alternatively, you can create additional connectors, but be aware of the overhead of polling a large number of files.
- Provide a name and a description for your connector if these fields are empty
- Enter your client ID and client secret from your Google APIs console in the relevant fields
- Click on the Generate Authorize URL button
- Click on the Authorize Google Sheets button
- Choose your Google account (the one you used to create the Client ID and Client Secret fields)
- 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
- 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
- Ensure each checkbox is checked, then click on the Continue button (you may need to scroll down)
Google will display a code
- 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).
- Copy the code, then switch back to Yellowfin
- Paste the code into the 3. Enter PIN field
- Click on the Validate Pin button
- Scroll down the dialog box to see the other pre-filled fields
- Check the default values are consistent with your needs (see the table before these steps for further information), and adjust as required
- 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!
- 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
- From the Select Table dropdown list, click on a Google Sheet tab to use as a view
- In View Name, enter a name for your view, and if you’d like to add a description, enter it into View Description
- 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.
- From the Yellowfin burger bun
- Click on the Data Sources section at the top to expand it, where you should see your Google Sheets data source
- Click on the Google Sheets data source to open it
- Click on Connection Settings, then scroll down to see the list of currently selected Google sheets
- 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)
- Use your mouse in conjunction with your keyboard's multi-select key to select any additional files
- 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.
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
Column names are decided from the first row of data in Google Sheets. You can rename columns later in Yellowfin; however, the first line in a Google Sheets sheet cannot be blank. Columns without data in the header row are not exposed to the API.
Yellowfin hierarchies have been disabled for the Google Sheets connector.
Filter Entry Style options are limited for Google Sheets connectors, with filter values manually entered.
New columns in connected sheets
A Yellowfin view contains a list of all columns from a Google Sheet. New columns will appear in the list, but you must add new columns manually for it to appear at report level.
Google Sheets tab names
Avoid using symbols (-,.*\$%) in the names of your Google Sheets tabs, as this causes issues with the API.
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.