DuckDB GSheets

🚧 WARNING - Experimental 🚧 Here be dragons

A DuckDB extension for reading and writing Google Sheets with SQL.

Note: This project is not affliated with Google or DuckDB, it is a community extension maintained by Evidence.

Install

INSTALL gsheets FROM community;
LOAD gsheets;

The latest version of DuckDB (currently 1.1.3) is supported.

Usage

Authenticate

-- Authenticate with Google Account in the browser (default)
CREATE SECRET (TYPE gsheet);

-- OR create a secret with your Google API access token (boring, see below guide)
CREATE SECRET (
    TYPE gsheet, 
    PROVIDER access_token, 
    TOKEN '<your_token>'
);

Read

-- Read a spreadsheet by full URL
FROM read_gsheet('https://docs.google.com/spreadsheets/d/11QdEasMWbETbFVxry-SsD8jVcdYIT1zBQszcF84MdE8/edit');

-- Read a spreadsheet by full URL, implicitly
FROM 'https://docs.google.com/spreadsheets/d/11QdEasMWbETbFVxry-SsD8jVcdYIT1zBQszcF84MdE8/edit';

-- Read a spreadsheet by spreadsheet id
FROM read_gsheet('11QdEasMWbETbFVxry-SsD8jVcdYIT1zBQszcF84MdE8');

-- Read a spreadsheet with no header row
SELECT * FROM read_gsheet('11QdEasMWbETbFVxry-SsD8jVcdYIT1zBQszcF84MdE8', headers=false);

-- Read a sheet other than the first sheet using the sheet name
SELECT * FROM read_gsheet('11QdEasMWbETbFVxry-SsD8jVcdYIT1zBQszcF84MdE8', sheet='Sheet2');

-- Read a sheet other than the first sheet using the sheet id in the URL
SELECT * FROM read_gsheet('https://docs.google.com/spreadsheets/d/11QdEasMWbETbFVxry-SsD8jVcdYIT1zBQszcF84MdE8/edit?gid=644613997#gid=644613997');

Write

-- Write a spreadsheet from a table by spreadsheet id
COPY <table_name> TO '11QdEasMWbETbFVxry-SsD8jVcdYIT1zBQszcF84MdE8' (FORMAT gsheet);

-- Write a spreadsheet from a table by full URL
COPY <table_name> TO 'https://docs.google.com/spreadsheets/d/11QdEasMWbETbFVxry-SsD8jVcdYIT1zBQszcF84MdE8/edit?usp=sharing' (FORMAT gsheet);

-- Write a spreadsheet to a specific sheet using the sheet id in the URL
COPY <table_name> TO 'https://docs.google.com/spreadsheets/d/11QdEasMWbETbFVxry-SsD8jVcdYIT1zBQszcF84MdE8/edit?gid=1295634987#gid=1295634987' (FORMAT gsheet);

Getting a Google API Access Token

To connect DuckDB to Google Sheets via an access token, you’ll need to create a Service Account through the Google API, and use it to generate an access token:

Navigate to the Google API Console.
Create a new project.
Search for the Google Sheets API and enable it.
In the left-hand navigation, go to the Credentials tab.
Click + Create Credentials and select Service Account.
Name the Service Account and assign it the Owner role for your project. Click Done to save.
From the Service Accounts page, click on the Service Account you just created.
Go to the Keys tab, then click Add Key > Create New Key.
Choose JSON, then click Create. The JSON file will download automatically.
Download and install the gcloud CLI.
Run the following command to login to the gcloud CLI with the Service Account using the newly created JSON file
```
gcloud auth activate-service-account --key-file /path/to/key/file
```

Run the following command to generate an access token:

gcloud auth print-access-token --scopes=https://www.googleapis.com/auth/spreadsheets

Open your Google Sheet and share it with the Service Account email.
Run DuckDB and load the extension

This token will periodically expire - you can re-run the above command again to generate a new one.

Limitations / Known Issues

Google Sheets has a limit of 1,000,000 cells per spreadsheet.
Reading sheets where data does not start in A1 is not yet supported.
Writing data to a sheet starting from a cell other than A1 is not yet supported.
Sheets must already exist to COPY TO them.

Support

If you are having problems, find a bug, or have an idea for an improvement, please file an issue on GitHub.