community/google-sheets

Commands to interact with the Google Sheets API

This package provides aux4 command wrappers around the Google Sheets API v4. It covers creating spreadsheets, reading and writing cell values, appending rows, batch operations, and managing individual sheets (tabs) within a spreadsheet. Requests are made with aux4 curl auth-request using the OAuth token that community/google-auth stores.

Installation

aux4 aux4 pkger install community/google-sheets

System Dependencies

• jq — used to percent-encode A1 ranges for the request URL. Installed automatically via brew install jq or your Linux package manager.

Authentication

Authentication is handled by community/google-auth, which is installed as a dependency. Log in once:

aux4 google auth login --services sheets

Check the current session and log out with:

aux4 google auth status
aux4 google auth logout

The token is stored at ~/.aux4.config/.oauth/google.json. Every command in this package accepts --tokenFile to point somewhere else, and honours the AUX4_GOOGLE_TOKEN_FILE environment variable.

Note: the token file holds your OAuth client id, client secret and refresh token in plain text (mode 0600). Treat it like an SSH private key.

Scopes

aux4 google auth login collects the scopes it needs from every installed Google package. This one asks for:

| Scope | Why | |-------|-----| | https://www.googleapis.com/auth/spreadsheets | read and write spreadsheets | | https://www.googleapis.com/auth/drive.file | create --folderId moves the new spreadsheet with the Drive API | | https://www.googleapis.com/auth/spreadsheets.readonly | requested instead of the two above when you use aux4 google auth login --readonly true |

Read-only logins cannot create, update, append, clear, or manage sheets — only get, values get and values batch-get work.

Quick Start

Create a new spreadsheet:

aux4 google sheets create "My Spreadsheet"

Read values from a range:

aux4 google sheets values get SPREADSHEET_ID --range 'Sheet1!A1:C10'

Append a row:

aux4 google sheets values append SPREADSHEET_ID --range 'Sheet1!A1' --values '[["Alice", 95, "Pass"]]'

Spreadsheets — create and inspect

Create a new spreadsheet with a title:

aux4 google sheets create "Q1 Budget"

This returns the full spreadsheet metadata as JSON, including the new spreadsheetId.

Create it directly inside a Drive folder:

aux4 google sheets create "Q1 Budget" --folderId FOLDER_ID

With --folderId the spreadsheet is created first and then reparented with the Drive API, so the command prints the Drive file resource rather than the spreadsheet resource.

Get metadata for an existing spreadsheet:

aux4 google sheets get SPREADSHEET_ID

Returns the spreadsheet properties, sheet names, and the numeric sheetId of each tab.

Values — read, write, update, and append

Ranges are given in A1 notation and are percent-encoded into the request URL, so quoted sheet names with spaces ('Sales Team'!A1:C10) and absolute references ($A$1:$C$10) work unchanged. Wrap ranges containing ! in single quotes to keep the shell from expanding them.

Read values

Read a single range:

aux4 google sheets values get SPREADSHEET_ID --range 'Sheet1!A1:D5'

Read multiple ranges in a single request — repeat --ranges once per range:

aux4 google sheets values batch-get SPREADSHEET_ID --ranges 'Sheet1!A1:B2' --ranges 'Sheet1!D1:E2'

A single comma-separated value works too:

aux4 google sheets values batch-get SPREADSHEET_ID --ranges 'Sheet1!A1:B2,Sheet1!D1:E2'

Write values

Update a range with new values:

aux4 google sheets values update SPREADSHEET_ID --range 'Sheet1!A1:B2' --values '[["Name", "Score"], ["Alice", 95]]'

By default, values are interpreted as if typed by a user (USER_ENTERED). To write raw unformatted values:

aux4 google sheets values update SPREADSHEET_ID --range 'Sheet1!A1' --values '[["=SUM(B1:B10)"]]' --valueInputOption RAW

Append rows

Append rows after the last row with data:

aux4 google sheets values append SPREADSHEET_ID --range 'Sheet1!A1' --values '[["Bob", 87, "Pass"]]'

The --range value tells the API where to search for existing data. New rows are appended after the last occupied row in that area.

Batch update

Update multiple ranges in a single request:

aux4 google sheets values batch-update SPREADSHEET_ID --data '[{"range": "Sheet1!A1", "values": [["Name"]]}, {"range": "Sheet1!B1", "values": [["Score"]]}]'

Clear values

Clear all values from a range (formatting is preserved):

aux4 google sheets values clear SPREADSHEET_ID --range 'Sheet1!A1:D10'

Sheets — manage tabs

Add a sheet

Add a new sheet (tab) to a spreadsheet:

aux4 google sheets sheet add SPREADSHEET_ID --title "Summary"

Delete a sheet

Delete a sheet by its numeric sheet ID (found in the spreadsheet metadata):

aux4 google sheets sheet delete SPREADSHEET_ID --sheetId 123456789

Copy a sheet

Copy a sheet to another spreadsheet:

aux4 google sheets sheet copy SOURCE_SPREADSHEET_ID --sheetId 0 --destinationSpreadsheetId TARGET_SPREADSHEET_ID

Import & export — xlsx, csv, tsv, pdf

Export a spreadsheet to a file, and create/overwrite a Sheet from a local file:

aux4 google sheets export 1AbC... --output data.xlsx          # xlsx = formulas + formatting (default)
aux4 google sheets export 1AbC... --format csv --output data.csv   # csv/tsv = values only, first sheet
aux4 google sheets export 1AbC... --format pdf --output data.pdf

aux4 google sheets import data.xlsx --title "Q3"              # NEW Sheet from a file
aux4 google sheets update 1AbC... --file data.xlsx           # overwrite an EXISTING Sheet (keeps its ID)

Fidelity matters: xlsx round-trips formulas and cell formatting; csv/tsv/pdf are flat — they hold the computed values, not formulas or formatting. import/update accept .xlsx, .csv and .tsv (format detected from the extension). update keeps the Sheet's ID, URL and sharing — so the edit loop is: export → edit locally → update the same Sheet.

Formatting — cells, ranges, rows, columns

The value commands write values; to change how cells look — bold/colors/number formats, borders, merges, column width / row height, conditional formatting, frozen rows, data validation — use batch-update, which sends a raw spreadsheets.batchUpdate requests array:

# bold + light-blue header row, then set the first three column widths
aux4 google sheets batch-update 1AbC... --requests '[
  {"repeatCell":{"range":{"sheetId":0,"startRowIndex":0,"endRowIndex":1},"cell":{"userEnteredFormat":{"textFormat":{"bold":true},"backgroundColor":{"red":0.85,"green":0.9,"blue":1.0}}},"fields":"userEnteredFormat(textFormat,backgroundColor)"}},
  {"updateDimensionProperties":{"range":{"sheetId":0,"dimension":"COLUMNS","startIndex":0,"endIndex":3},"properties":{"pixelSize":180},"fields":"pixelSize"}}
]'

Each request targets a GridRange (sheetId + row/column bounds); omit the bounds to cover whole rows or columns. See the Sheets request reference for the full list (repeatCell, updateBorders, mergeCells, addConditionalFormatRule, updateDimensionProperties, …).

Examples

Create a spreadsheet and populate it

aux4 google sheets create "Employee Directory"
aux4 google sheets values update SPREADSHEET_ID --range 'Sheet1!A1:C1' --values '[["Name", "Department", "Email"]]'
aux4 google sheets values append SPREADSHEET_ID --range 'Sheet1!A1' --values '[["Alice", "Engineering", "alice@example.com"], ["Bob", "Marketing", "bob@example.com"]]'

Read and filter data

All responses are JSON, so you can pipe to jq for filtering:

aux4 google sheets values get SPREADSHEET_ID --range 'Sheet1!A1:C100' | jq '.values[]'

Add a summary tab with formulas

aux4 google sheets sheet add SPREADSHEET_ID --title "Summary"
aux4 google sheets values update SPREADSHEET_ID --range 'Summary!A1:B2' --values '[["Total Employees", "=COUNTA(Sheet1!A2:A100)"], ["Departments", "=COUNTUNIQUE(Sheet1!B2:B100)"]]'

Environment Variables

• AUX4_GOOGLE_TOKEN_FILE — path to the OAuth token file (default: ~/.aux4.config/.oauth/google.json)

License

MIT — See LICENSE for details.