https://github.com/0x6b/libgsqlite Skip to content Toggle navigation Sign up * Product + Actions Automate any workflow + Packages Host and manage packages + Security Find and fix vulnerabilities + Codespaces Instant dev environments + Copilot Write better code with AI + Code review Manage code changes + Issues Plan and track work + Discussions Collaborate outside of code + Explore + All features + Documentation + GitHub Skills + Blog * Solutions + For + Enterprise + Teams + Startups + Education + By Solution + CI/CD & Automation + DevOps + DevSecOps + Case Studies + Customer Stories + Resources * Open Source + GitHub Sponsors Fund open source developers + The ReadME Project GitHub community articles + Repositories + Topics + Trending + Collections * Pricing [ ] * # In this repository All GitHub | Jump to | * No suggested jump to results * # In this repository All GitHub | Jump to | * # In this user All GitHub | Jump to | * # In this repository All GitHub | Jump to | Sign in Sign up {{ message }} 0x6b / libgsqlite Public forked from x2bool/xlite * Notifications * Fork 40 * Star 266 A SQLite extension which loads a Google Sheet as a virtual table. License MIT license 266 stars 40 forks Star Notifications * Code * Issues 1 * Pull requests 0 * Actions * Security * Insights More * Code * Issues * Pull requests * Actions * Security * Insights 0x6b/libgsqlite This commit does not belong to any branch on this repository, and may belong to a fork outside of the repository. master Switch branches/tags [ ] Branches Tags Could not load branches Nothing to show {{ refName }} default View all branches Could not load tags Nothing to show {{ refName }} default View all tags Name already in use A tag already exists with the provided branch name. Many Git commands accept both tag and branch names, so creating this branch may cause unexpected behavior. Are you sure you want to create this branch? Cancel Create 1 branch 1 tag Code * Local * Codespaces * Clone HTTPS GitHub CLI [https://github.com/0] Use Git or checkout with SVN using the web URL. [gh repo clone 0x6b/l] Work fast with our official CLI. Learn more. * Open with GitHub Desktop * Download ZIP Sign In Required Please sign in to use Codespaces. Launching GitHub Desktop If nothing happens, download GitHub Desktop and try again. Launching GitHub Desktop If nothing happens, download GitHub Desktop and try again. Launching Xcode If nothing happens, download Xcode and try again. Launching Visual Studio Code Your codespace will open once ready. There was a problem preparing your codespace, please try again. This branch is 19 commits ahead, 17 commits behind x2bool:master. Latest commit @0x6b 0x6b Merge pull request #4 from 0x6b/update-license ... 1cf74de Oct 21, 2022 Merge pull request #4 from 0x6b/update-license fix: update license 1cf74de Git stats * 37 commits Files Permalink Failed to load latest commit information. Type Name Latest commit message Commit time .github/workflows build: disable 32-bit builds on push August 8, 2022 23:27 api chore: cargo fmt August 30, 2022 10:38 lib chore: cargo fmt August 30, 2022 10:38 .gitignore build: add lock file to the repo August 30, 2022 18:25 CONTRIBUTING.md docs: small updates August 4, 2022 00:13 Cargo.lock build: add lock file to the repo August 30, 2022 18:25 Cargo.toml initial commit as libgsqlite August 3, 2022 17:54 LICENSE fix: update license October 21, 2022 11:35 README.md docs: small updates August 4, 2022 00:13 View code [ ] libgsqlite Tested Platform Getting Started Setup Google Cloud Create a Project Enable Google Sheets API for the Project Setup Google OAuth Consent Screen Create a Credential Create a Sample Spreadsheet Query the Spreadsheet with SQLite Contributing Acknowledgements Limitations Security Privacy License References README.md libgsqlite A SQLite extension which loads a Google Sheet as a virtual table. Tested Platform * SQLite 3.39.2 * Rust 1.62.1-aarch64-apple-darwin * macOS 12.5 (Monterey) on Apple M1 MAX Getting Started Setup Google Cloud Create a Project 1. Log in to the Google Cloud console. 2. Go to the Manage resources page. 3. 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. 4. Click Create Project. 5. In the New Project window that appears, enter a project name, say libgsqlite, and select a billing account as applicable. 6. Enter the parent organization or folder resource in the Location box. 7. When you're finished entering new project details, click Create. Enable Google Sheets API for the Project 1. Go to the API Library page. 2. From the projects list, select the project you just created. 3. In the API Library, select Google Sheets API. 4. On the API page, click Enable. Setup Google OAuth Consent Screen 1. Go to the OAuth consent screen page. 2. Select Internal as User Type, then click Create 3. Add required information like an app name (libgsqlite) and support email address. 4. Click Save and Continue. 5. Click Add or Remove Scopes. 6. On the dialog that appears, select the scope .../auth/ spreadsheets.readonly (See all your Google Sheets spreadsheets) and click Update. 7. Click Save and Continue. 8. Click Back to Dashboard. Create a Credential 1. Go to the Credentials page. 2. Click Create Credentials - OAuth Client ID. 3. Select Desktop app as Application Type. 4. Type libgsqlite as Name. 5. Click Download JSON to save your Client ID and Client Secret locally. Create a Sample Spreadsheet 1. 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 2. Copy the URL of the spreadsheet. Query the Spreadsheet with SQLite 1. 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 2. Launch SQLite: $ sqlite3 3. 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 with brew install sqlite3, and use it. 4. Create a virtual table for your spreadsheet by providing ID (url of the spreadsheet), SHEET (sheet name), and RANGE 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 ); 5. 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%'; Contributing Please read CONTRIBUTING for more detail. Acknowledgements 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. Limitations * 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 and DELETE statements won't be implemented. Welcome PRs. Security 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. Privacy The extension never send your data to any server. License This extension is released under the MIT License. See LICENSE for details. References * Quickstart: Manage your Google Cloud resources * Getting started | Cloud APIs * Setting up OAuth 2.0 About A SQLite extension which loads a Google Sheet as a virtual table. Topics rust sqlite-extension Resources Readme License MIT license Stars 266 stars Watchers 1 watching Forks 40 forks Releases 1 Release 2022-08-09T07:14:16 v0.1.0 Latest Aug 9, 2022 Languages * Rust 92.5% * C 7.5% Footer (c) 2023 GitHub, Inc. Footer navigation * Terms * Privacy * Security * Status * Docs * Contact GitHub * Pricing * API * Training * Blog * About You can't perform that action at this time. You signed in with another tab or window. Reload to refresh your session. You signed out in another tab or window. Reload to refresh your session.