Connect Dashboard to a Google Drive Spreadsheet

26 September 2017

In this blog post, we share a sample project that connects BI Dashboard to a Google Drive spreadsheet. If you'd benefit from such a scenario, download the sample and use the instructions below to connect to your own data. And we’d love to see if you’re interested in such functionality to become native to our Dashboard: leave your comments and fill out the survey at the end.

Why a Sample Project, Not Built-In Support

If you consider SaaS Dashboard solutions, their list of supported sources typically includes cloud-based spreadsheets – those stored in Google Drive, Dropbox, or OneDrive. There’s a balance to that combination, with both the dashboard and its data being hosted by third parties in the cloud. That cloud-based mix of storage and UX lets you completely avoid building an app in its classic sense.

On the other hand, DevExpress Dashboard has been designed with focus on application development – where you are in control of how your data is handled and where you integrate your dashboard into an application that goes beyond the boundaries of data analysis alone. Thus far, Cloud-Stored Spreadsheet support never made it to the top of our TO-DO list. We do get questions about this every now and then, so we can start to learn about our customers’ tasks, and want to continue doing this. And as this blog post shows, our Dashboard is flexible enough to support those kinds of data sources, should the feature make the top spot during our next planning.

Meanwhile, we hope to help customers who want Google Drive spreadsheet access by providing them with this "starter" project.

What’s in the Project

The video below guides you through the user experience. You first sign into your Google Account, allow the application to view the files in your Google Drive, select the required spreadsheet file, then a sheet, table, or a named range within it. Once connection has been established, you build your dashboard as you’d do with any other data source type.

Download, Run, Modify

Start by downloading the project from the DevExpress Code Central:

Right-click and set the MvcDashboard project in the solution as a StartUp Project and run the application. You can connect to your own Google spreadsheets and analyze their data by using the Web Dashboard.

In our version of the application, the authentication works through our test Google API Console project that allows data access from localhost. Consider the following authentication-related changes you'll want to make in a production app.

Obtain and Specify Your "Client ID"

To integrate Google Sign-In into your web application, follow the Google Sign-In for Websites guide, which results in generating your own “Client ID” and “Client Secret” values.

Once you've obtained the values, open Web.config in the MvcDashboard project and specify GoogleClientID and GoogleClientSecret within the <appSettings> section.

<!-- ... -->
    <!-- ... -->
    <add key="GoogleClientID" value="ClientIDValue" />
    <add key="GoogleClientSecret" value="ClientSecretValue" />

Enable the Google Drive API

To interact with the Drive API, you need to enable the Drive API service for your app. The following guide describes how to do that:

Set Redirect URI

When you deploy the project to a web server, you'll need to use the Google Site Verification API to verify the authenticated user as the domain owner. Once that's done, the app can have Google Sign-in service redirect back to your web site. To launch the application on your domain / site, open Web.config in the MvcDashboard project. Then change the RedirectUri value within the <appSettings> section to your domain / site instead of localhost. Remember that our implementation requires the "/Home/GoogleRedirectCode" string to follow the production domain name to make sure that the correct action method is invoked.

<!-- ... -->
    <!-- ... -->
    <add key="RedirectUri" value="" />

Your Thoughts about Using Dashboard with Cloud-Hosted Spreadsheets

Please take a moment of your time to indicate your preferred cloud-based spreadsheet provider for the DevExpress Dashboard. And if you’ve got more to say on the matter, do use the comments section below.

Free DevExpress Products - Get Your Copy Today

The following free DevExpress product offers remain available. Should you have any questions about the free offers below, please submit a ticket via the DevExpress Support Center at your convenience. We'll be happy to follow-up.
No Comments

Please login or register to post comments.