Google Sheets

This plugin allows you to interact with Google Sheets documents, that are commonly named spreadsheets.

Here’s a summary of the key features:

• Read and write data: You can import data from Google Sheets into Dataiku as datasets and vice versa.
• Populate project with datasets per sheet: This plugin offers a macro to import all sheets from a Google Sheet document and create separate datasets for each sheet in your project.

Plugin Information

How To Set-Up

Dataiku supports connecting to Google Sheets using a Service Account or OAuth2.

Use a service account if:

• your Dataiku users don’t have direct access to the resources in Google Sheets
• you don’t need resources access filtering per user

Use OAuth2 if:

• your Dataiku users have access to your Google Sheets resources
• you do not want your users to access resources via Dataiku in Google Sheets which they do not have permission for

More information about connecting to Google Sheets is available here.

How To Use

You need to install the plugin in Dataiku DSS. Go to the Administration > Plugins page. The plugin requires the installation of a code environment.

First, identify the Google Sheet Document ID in its URL. It’s the part between “d/” and “/”. For instance, if the URL is https://docs.google.com/spreadsheets/d/4RMEq[…]mHUz4/edit#gid=0 the document ID is 4RMEq[…]mHUz4.

Example – Create a Google Sheets datasets

• In Dataiku, go to “+Dataset” and choose “Google Sheets” -> “Google Sheets document.”
• Select your authentication method (Single Sign On or Service Account).
• Enter the document ID. The list of available sheets should update, you can select the ones you need.
• Click “Test & Get schema” to get a preview.
• Click on “Create” to get your Google Sheet Document in your flow

Example – Write or Append to a Google sheet

• In your flow, select a source dataset and choose the recipe “Google Sheets” -> “Google Sheets – Write / append to a sheet”
• Select your authentication method (Single Sign On or Service Account).
• Enter the document ID. The list of available sheets should update, you can select the one where you want to write or append your DSS Dataset.
• Pick the write mode:
  • Append: Adds new data to the end of the sheet.
  • Overwrite: Replaces existing sheet content.
• Click Run and see the results in your Google Sheet Document.

Example – Import Google Sheets with macro

• In your project go to “Macros” -> “Import GoogleSheets”
• Select your authentication method (Single Sign On or Service Account).
• Enter the document ID. The list of available sheets should update, you can select the one where you want to write or append your DSS Dataset.
• Select your sheets or leave blank to import them all.
• Run the macro
• Your Google sheets are available as datasets in your flow, within a dedicated zone.

More usage information is available here.

Known limitations

This plugin does not handle Excel files stored in Google Sheets. These files can be recognized by the green .XLSX tab visible on Google Sheets’ web interface.

In order to import this type of file into Dataiku, please use the Google Drive plugin instead.