DOCSEARCHbyAlgolia
Submit Docs

DuckDB GSheets Extension

repository·main·Indexed 43 days ago

https://github.com/evidence-dev/duckdb_gsheets

A DuckDB extension for interacting with Google Sheets, supporting reading via read_gsheet and writing via the COPY command. It features multiple authentication methods including OAuth, access tokens, and service accounts, and is compatible with DuckDB version 1.4.0.

Tokens
5.2K
Snippets
16
Records
27
Agent score
56%

What's inside duckdb_gsheets

  1. Data Storage and Persistence

    main

    DuckDB GSheets operates locally. Data accessed from Google Sheets is only persisted to your local machine if you explicitly perform an operation to save it, such as:

    • Using CREATE TABLE in DuckDB to save results to a local table.
    • Exporting data to a local file (e.g., CSV).

    If no such command is issued, no data is retained after the DuckDB session ends.

  2. OAuth Scopes and Authentication for DuckDB GSheets

    main

    When using OAuth authentication to access Google Sheets, the extension requests the following scope:

    • https://www.googleapis.com/auth/spreadsheets

    This scope grants the ability to read and modify your Google Sheets data. The authentication process utilizes Proof Key for Code Exchange (PKCE) to secure the flow between your local system and Google's OAuth service.

  3. Handle DuckDB C++ API changes during updates

    main

    Extensions built with this template use DuckDB's internal C++ API, which is not guaranteed to be stable. Updating the target DuckDB version may cause build failures due to API changes.

    To identify and fix breaking changes in the C++ API, consult these resources:

    • DuckDB Release Notes: Check the official Release Notes.
    • Core Extension Patches: Review the history of Core extension patches.
    • Git History: Inspect the git history of the specific C++ Header file associated with the changed API.

  6. Authenticate with Google Sheets

    main

    The extension supports several authentication methods via DuckDB's CREATE SECRET command. Choose the method that fits your workflow:

    1. Interactive (Browser-based): The default method for interactive sessions.
    2. Access Token: Use a specific Google API access token.
    3. Service Account (JSON File): Use a non-expiring JSON key file. Ideal for non-interactive data pipelines.
    4. Service Account (Direct): Pass the service account email and private key directly in the secret definition.
    -- Authenticate with Google Account in the browser (default)
CREATE SECRET (TYPE gsheet);

-- Create a secret with your Google API access token
CREATE SECRET (
    TYPE gsheet,
    PROVIDER access_token,
    TOKEN '<your_token>'
);

-- Create a non-expiring JSON secret with your Google API private key
CREATE SECRET (
    TYPE gsheet,
    PROVIDER key_file,
    FILEPATH '<path_to_JSON_file_with_private_key>'
);

-- Pass the secret directly
CREATE SECRET (
    TYPE gsheet,
    PROVIDER key_file,
    EMAIL '<service_account_email>',
    SECRET '<private_key>'
);

  7. Get a Google API Access Token

    main

    To use the access_token provider, follow these steps:

    1. Google Cloud Setup:
      • Create a project in the Google API Console.
      • Enable the Google Sheets API.
      • Create a Service Account under Credentials and assign it the Owner role.
      • Generate a JSON Key for the Service Account and download it.
    2. Generate Token via gcloud CLI:
      • Install the gcloud CLI.
      • Activate the service account: gcloud auth activate-service-account --key-file /path/to/key/file.
      • Print the token: gcloud auth print-access-token --scopes=https://www.googleapis.com/auth/spreadsheets.
    3. Permissions: Share your Google Sheet with the Service Account email address.
    4. Usage: Use the printed token in a CREATE SECRET (TYPE gsheet, PROVIDER access_token, TOKEN '<token>') command. Note that this token expires periodically.
    # Activate service account
gcloud auth activate-service-account --key-file /path/to/key/file

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

  8. Update the extension to a new DuckDB version

    main

    When a new stable version of DuckDB is released, you must update the repository submodules and GitHub workflow configurations to ensure the extension builds against the correct version.

    1. Bump Submodules

    • Set ./duckdb to the latest tagged release of DuckDB.
    • Set ./extension-ci-tools to the branch corresponding to the latest DuckDB release (e.g., if building for DuckDB v1.1.0, check out the v1.1.0 branch in extension-ci-tools).

    2. Bump Workflow Versions

    Update the duckdb_version input in the following locations within ./github/workflows:

    • The duckdb-stable-build job in MainDistributionPipeline.yml.
    • The duckdb-stable-deploy job in MainDistributionPipeline.yml.
    • The reusable workflow duckdb/extension-ci-tools/.github/workflows/_extension_distribution.yml used by the duckdb-stable-build job.

  9. Build the duckdb_gsheets extension

    main

    To build the extension and its associated binaries, use the make command with the GEN=ninja flag.

    Build Outputs:

    • ./build/release/duckdb: DuckDB shell with the extension auto-loaded.
    • ./build/release/test/unittest: SQL test runner with the extension linked in.
    • ./build/release/extension/gsheets/gsheets.duckdb_extension: The loadable extension binary.
    GEN=ninja make

  10. Install and manage dependencies via VCPKG

    main

    DuckDB extensions use VCPKG for dependency management. You must clone VCPKG, bootstrap it, and export the toolchain path to your environment before building.

    1. Clone and bootstrap VCPKG:
    git clone https://github.com/Microsoft/vcpkg.git
./vcpkg/bootstrap-vcpkg.sh
    1. Export the toolchain path:
    export VCPKG_TOOLCHAIN_PATH=`pwd`/vcpkg/scripts/buildsystems/vcpkg.cmake
    git clone https://github.com/Microsoft/vcpkg.git
./vcpkg/bootstrap-vcpkg.sh
export VCPKG_TOOLCHAIN_PATH=`pwd`/vcpkg/scripts/buildsystems/vcpkg.cmake

  11. Authenticate DuckDB GSheets with Google

    main

    When authenticating with Google, you may be redirected to an OAuth callback page. If the authorization is successful, the page will display an access_token in a code box. You must copy this token and paste it into your DuckDB GSheets application to complete the authentication process.

    Possible States:

    • Authorization Pending: The process is still in progress.
    • Authorization Successful: A token is displayed for you to copy.
    • Authorization Failed: No token was received; you should restart the authorization process.

    Note: This page is specifically used for the Google authentication flow.