How to connect Google Sheets as a datasource

Good news! You can now connect Google Sheets spreadsheets as data source in your crystal project using Google user’s account.

In order to configure Google Sheets as a datasource, you will need to enable the Google Drive and Google Sheets APIs and to create a Service Account on GCP console. All the required configurations steps are explained in details in the following sections of this article.

Let’s get started!

Google API configuration

Sign in the Google Cloud platform and go to the GCP console. Select the related GCP Project and then on the left sidebar menu of the GCP Console select APIs and Services and then Library.

In the opened menu search for Google Sheets and select the Google Sheets API.

Click on Enable button to activate the Google Sheets API for the current project.

Once you activated the Google Sheets API, be back to the search, look for the Google Drive and select the Google Drive API.

Click on Enable button to activate the Google Drive API for the current project.

Now, when the required APIs are enabled, it is needed to create a service account and connect it. Let’s see how you can do it on the GCP Console.

Create service account

Login into the GCP Console and be sure to select the correct project. From the sidebar menu, select APIs and services and then select Credentials.

From the top menu, select Create Credentials and then choose option Service Account.

In the opened form fill in the Service account name, then click on Create and continue and continue the setup skipping all the optional steps.

Now find the Service Account you just created in the list of service accounts and open its link.

Then go to the Keys tab.

Click on Add Key and then select Create new key.

In the opened menu select the JSON format and then click Create. This will automatically download the JSON service account file needed for the following setup with crystal.

Data source configuration on crystal Self-Service console

Once the previous steps on Google Cloud Platform are performed, it is time to connect Google Sheets to your crystal project. To do so, open Self-service console and click on Data Source Management.

Click on Connect new data source at the bottom of the screen and in the opened section choose Google Sheets.

In order to fill in the fields in the opened menu, you will need the service account file downloaded previously. Open the JSON file and put the information inside of the form, then click Test and connect.

After the datasource has been successfully connected, you will need to manually share your spreadsheets with the service account's email.

To do so, open every Google Sheet you would like to connect to crystal project, click on Share and enter the client email which you can find in the JSON file.

“Viewer” permissions are enough grant for allowing crystal to access the spreadsheets

Once it is done, the spreadsheets you shared with client email will be showed up in the collection of tables in the first step of topic creation.

Google Sheets Oauth Authentication

You can connect your Google Sheets by simply signing-in with your Google account.

To do that, select Google Sheets in the Add new data source menu:

A new window will prompt with two options: Manual Authentication or Oauth Authentication. Select the second one, then click on Next:

Sign-up with your Google account to connect your Google Sheets. A new window will prompt-up, allowing you to select both Date (dd/MM/yyy) and Locale (local time zone format).

You can also set your Data source name. Therefore, click on Test and connect to proceed: