Edit

Set up your Google Sheets connection

This article outlines the steps to create a Google Sheets connection.

Supported authentication types

The Google Sheets connector supports the following authentication types for copy and Dataflow Gen2 respectively.

Authentication type Copy Dataflow Gen2
Organizational account n/a √

Set up your connection for Dataflow Gen2

You can connect Dataflow Gen2 in Microsoft Fabric to Google Sheets using Power Query connectors. Follow these steps to create your connection:

  1. Check capabilities to make sure your scenario is supported.
  2. Complete prerequisites for Google Sheets.
  3. Get data in Fabric.
  4. Connect to Google Sheets data.
  5. Check limitations and considerations for any current restrictions.

Capabilities

  • Import

Prerequisites

Before you can use the Google Sheets connector, you must have a Google account and have access to the Google Sheet you're trying to connect to.

Get data

To get data in Data Factory:

  1. On the left side of Data Factory, select Workspaces.

  2. From your Data Factory workspace, select New > Dataflow Gen2 to create a new dataflow.

    Screenshot showing the workspace where you choose to create a new dataflow.

  3. In Power Query, either select Get data in the ribbon or select Get data from another source in the current view.

    Screenshot showing the Power Query workspace with the Get data option emphasized.

  4. In the Choose data source page, use Search to search for the name of the connector, or select View more to the right of the connector list to see all the connectors available in Data Factory.

    Screenshot of the Data Factory Choose data source page with the search box and the view more selection emphasized.

  5. If you choose to view more connectors, you can still use Search to search for the name of the connector, or choose a category to see a list of connectors associated with that category.

    Screenshot of the Data Factory Choose data source page displayed after selecting view more, with the list of connectors.

Connect to Google Sheets data

To connect to Google Sheets data:

  1. Select Google Sheets from the Power Query Connect to data source page.

  2. In the Google Sheets dialog, enter the URL of the Google Sheet you want to connect to. You can copy this URL from your browser address bar when viewing the sheet.

  3. Select Sign in to sign in with your Google account.

  4. In the Google sign-in window that appears, provide your credentials and allow Power BI to access your Google account.

  5. Once you successfully sign in, select Next.

  6. In the Choose data page, select the sheets you want to load, and then select Transform data to transform the data in Power Query Editor.

Limitations and considerations

This section describes any limitations or considerations of the Google Sheets connector.

Shared drive support

This connector does support connecting to shared drives.

Multiple connections

This connector uses a different ResourcePath for every Google Sheet URL. You'll need to authenticate to every new resource path and URL, but you might not need to sign into Google multiple times if the previous sessions remain active.

Understanding URL parsing

The connector first checks for the signature of the URL, ensuring it starts with https://docs.google.com/spreadsheets/d/. The connector then parses the Google Spreadsheet ID from the URL to include in the Google Sheets API call. The rest of the URL isn't used. Each Google Sheet connection is tied to the submitted URL, which will act as the ResourcePath.

Set up your connection in a pipeline

Data Factory in Microsoft Fabric doesn't currently support Google Sheets in pipelines.

Related content

  • Last updated on