How to read data from Google Sheets

DP Team -

Google Sheets worksheets can be used the same way as any other data source. Once your Google Sheets account is connected, you can load a worksheet as a table and combine it with data read from other data sources.

The Google Sheets connector is one of our most popular one. The following are some example use cases:

  • Migrating data from Google Sheets
    Data read from Google Sheets can be written to any of our connectors. For example Amazon S3 or an SQL database. An example real-life scenario is people manually adding data to Google Sheets which is then read by a scheduled pipeline which then writes it to Amazon S3 as a CSV. The CSV files can then be processed further by custom scripts.
  • Combining data in Google Sheets with data from other sources
    Once added to a pipeline, a Google Sheets worksheet works exactly the same way as an SQL table or a CSV file. Our connector hides all differences. A real-life use case would be joining a worksheet with Google Analytics data and writing the result to Amazon DynamoDB
  • Data inspection
    Read data from Google Sheets and run queries on it using the pipeline builder widgets or Spark SQL. This allows sophisticated queries to be run which would not be supported by Google Sheets otherwise.

How to connect your Google Sheets account

  • Go to Connections
  • Select Google Sheets
  • Click "Grant permission" button
  • Follow instructions by Google
  • When you are redirected to Data Pipelines, ensure your Google account shows in the list. You may connect more than one account.

Once you have connected your account, you can start adding Google Sheets

  • Click "Add Google Sheet" button
  • Enter a name for the sheet (can be anything)
  • Select the account you have just connected
  • Paste in the spreadsheet ID or full spreadsheet URL. (ensure the spreadsheet is accessible by the Google account you selected)

You can now access the spreadsheet from your pipelines. To add it to a pipeline:

  • In pipeline builder view, under Load Datasets, click Google Sheets
  • Select the spreadsheet
  • Enter the worksheet name you want to load (the worksheet must exist in the spreadsheet you selected)
  • Click Load

Using Google Sheets with Data Pipelines is a convenient way to give non-technical users the ability to work with data inside your cloud environment.