| title | hide_title |
|---|---|
DuckDB GSheets |
true |
🚧 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 gsheets FROM community;
LOAD gsheets;The latest version of DuckDB (currently 1.1.3) is supported.
-- 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 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 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);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.
- 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.
If you are having problems, find a bug, or have an idea for an improvement, please file an issue on GitHub.