Introducing Flights: agent-native data pipelines in MotherDuckJoin the livestream

Skip to main content

Google Sheets

Google Sheets is a hosted spreadsheet application for collaborative tabular data.

Google Sheets can be queried from MotherDuck in two ways:

  • Use read_csv() with the Google Sheets /export?format=csv URL. This works well for server-side reads in MotherDuck and for views that should reflect the current sheet contents.
  • Use the community duckdb-gsheets extension when you need its Google Sheets-specific features.

Query a sheet with read_csv()​

For a public Google Sheet, use the CSV export URL:

SELECT *
FROM read_csv(
    'https://docs.google.com/spreadsheets/d/<sheet_id>/export?format=csv&gid=<tab_id>',
    MD_RUN = REMOTE
);

The sheet_id is the value between /d/ and /edit in the Google Sheet URL. The gid identifies the worksheet tab.

When connected to MotherDuck, read_csv() can read the HTTPS URL server side. MD_RUN = REMOTE makes the execution location explicit, although non-local HTTPS reads are remote by default.

Create a view or table​

Create a view when you want queries to reflect the current Google Sheet contents:

CREATE OR REPLACE VIEW my_database.main.google_sheet AS
SELECT *
FROM read_csv(
    'https://docs.google.com/spreadsheets/d/<sheet_id>/export?format=csv&gid=<tab_id>',
    MD_RUN = REMOTE
);

Create a table when you want to snapshot the sheet into MotherDuck:

CREATE OR REPLACE TABLE my_database.main.google_sheet_snapshot AS
SELECT *
FROM read_csv(
    'https://docs.google.com/spreadsheets/d/<sheet_id>/export?format=csv&gid=<tab_id>',
    MD_RUN = REMOTE
);

Authenticate to a private sheet​

For private sheets, create an HTTP secret with an OAuth bearer token that has access to the sheet. Store it in MotherDuck if the query needs to run server side from future sessions or scheduled jobs:

CREATE SECRET google_sheets_http IN MOTHERDUCK (
    TYPE HTTP,
    SCOPE 'https://docs.google.com',
    EXTRA_HTTP_HEADERS MAP {
        'Authorization': 'Bearer <google_oauth_access_token>'
    }
);

The bearer token must come from a Google identity or service account that can read the spreadsheet. See the DuckDB HTTP authentication documentation for additional httpfs authentication options.

Use the Google Sheets extension​

The community Google Sheets extension can read sheets with read_gsheet():

INSTALL gsheets FROM community;
LOAD gsheets;

CREATE SECRET (TYPE gsheet);

SELECT *
FROM read_gsheet('https://docs.google.com/spreadsheets/d/<sheet_id>/edit');

This workflow may require browser interactivity unless you configure an API access token. See Using Excel and Google Sheets Data in MotherDuck for a longer walkthrough.

Related content​