​
Load the extension

Connect to the database and run the following command to load the extension:

CREATE EXTENSION IF NOT EXISTS gsheets;

​
Authenticate

To interact with Google Sheets, you need to authenticate your PostgreSQL environment with Google. Run the following command to authenticate:

SELECT gsheets_auth();

This command opens a URL in your browser. After getting the token, use the following command to set the token:

SET gsheets.access_token='your_access_token';

​
Read data

Following is the function signature to read data from Google Sheets:

read_sheet(spreadsheet_id/url text,
             sheet_name DEFAULT 'Sheet1',
             header boolean DEFAULT true);

This function returns tuples.

Here’s an example of reading data from a Google Sheet:

SELECT * FROM
read_sheet('<spreadsheet_id/url>')
as (name text, age int);

SELECT * FROM
read_sheet('<spreadsheet_id/url>',
             sheet_name=>'Sheet2',
             header=>false);
as (name text, age int);

​
Write data

Following is the function signature to write data to Google Sheets:

write_sheet(data anyelement, -- see examples below
              options jsonb DEFAULT '{}');

Available options are:

{
  "spreadsheet_id": "string",   -- Optional. If not provided, a new spreadsheet is created
  "sheet_name": "string",       -- Optional. Default is 'Sheet1'
  "header": "array"             -- Optional. Default is []
}

Here’s an example of writing data to a Google Sheet:

SELECT write_sheet((name, age)) FROM person;

SELECT write_sheet(t.*) FROM person t;

SELECT write_sheet(name) FROM person;

SELECT write_sheet((name, age),
                     '{"spreadsheet_id": "<spreadsheet_id>",
                       "sheet_name": "Sheet2",
                       "header": ["name", "age"]}'::jsonb)
FROM person;