A SQLite extension which loads a Google Sheet as a virtual table.
Tested Platform
Getting Started
Setup Google Cloud
Create a Project
- Log in to the Google Cloud console.
- Go to the Manage resources page.
- On the Select organization drop-down list at the top of the page, select the organization resource in which you want to create a project.
- Click Create Project.
- In the New Project window that appears, enter a project name, say
libgsqlite
, and select a billing account as applicable. - Enter the parent organization or folder resource in the Location box.
- When you’re finished entering new project details, click Create.
Enable Google Sheets API for the Project
- Go to the API Library page.
- From the projects list, select the project you just created.
- In the API Library, select Google Sheets API.
- On the API page, click Enable.
Setup Google OAuth Consent Screen
- Go to the OAuth consent screen page.
- Select Internal as User Type, then click Create
- Add required information like an app name (
libgsqlite
) and support email address. - Click Save and Continue.
- Click Add or Remove Scopes.
- On the dialog that appears, select the scope
.../auth/spreadsheets.readonly
(See all your Google Sheets spreadsheets) and click Update. - Click Save and Continue.
- Click Back to Dashboard.
Create a Credential
- Go to the Credentials page.
- Click Create Credentials → OAuth Client ID.
- Select Desktop app as Application Type.
- Type
libgsqlite
as Name. - Click Download JSON to save your Client ID and Client Secret locally.
Create a Sample Spreadsheet
- Go to sheet.new to create a new spreadsheet, then copy and paste following data.
Employee Number | First Name | Last Name | Department |
---|---|---|---|
1 | Christine | Haas | A00 |
2 | Michael | Thompson | B01 |
3 | Sally | Kwan | C01 |
4 | John | Beyer | E01 |
5 | Irving | Stern | D11 |
6 | Eva | Pulaski | E01 |
- Copy the URL of the spreadsheet.
Query the Spreadsheet with SQLite
- Setup required environment variables with the credential:
$ export LIBGSQLITE_GOOGLE_CLIENT_ID=... # client_id property in the downloaded JSON $ export LIBGSQLITE_GOOGLE_CLIENT_SECRET=... # client_secret property
- Launch SQLite:
- Load the extension:
.load libgsqlite # or "gsqlite" on Windows
If you get
Error: unknown command or invalid arguments: "load". Enter ".help" for help
, your SQLite is not capable for loading an extension. For macOS, install it withbrew install sqlite3
, and use it. - Create a virtual table for your spreadsheet by providing
ID
(url of the spreadsheet),SHEET
(sheet name), andRANGE
for module arguments. All three arguments are mandatory. You’ll be navigated to Google OAuth consent screen to get a secret to access the spreadsheet. You can create multiple virtual tables from different spreadsheets.CREATE VIRTUAL TABLE employees USING gsqlite( ID 'https://docs.google.com/spreadsheets/d/...', -- your spreadsheet URL SHEET 'Sheet1', -- name of the sheet RANGE 'A2:D7' -- range to fetch );
- Go back to your terminal, and run a query as usual:
.mode column .headers on SELECT * FROM employees; SELECT * FROM employees WHERE D LIKE 'E%';
Please read CONTRIBUTING for more detail.
An article, Extending SQLite with Rust to support Excel files as virtual tables | Sergey Khabibullin, and its companion repository x2bool/xlite, for great write up and inspiration.
- The extension will load the spreadsheet only once while creating a virtual table. If you want to pick up recent changes, drop the table and create it again.
INSERT
,UPDATE
andDELETE
statements won’t be implemented. Welcome PRs.
The extension is intended for use in personal, not-shared, environment. The Google Cloud secret will be cached for 59 minutes under the temporary directory (See std::env::temp_dir
) with fixed name access_token.json
for your convenience. Note that, as described at the doc, creating a file or directory with a fixed or predictable name may result in “insecure temporary file” security vulnerability.
The extension never send your data to any server.
This extension is released under the MIT License. See LICENSE for details.
Leave A Comment