• Introduction
  • Why?
• Installation
• Authentication and Authorization
  • What about OAuth authentication?
• CLI Usage
  • Sheet commands
  • Drive commands
  • Ranges
  • Finding document and parent IDs
  • Environment Variables
• Use as Golang Package
• Hack

gsheet is a cli tool for sending and receiving csv data to and from Google Sheets.

With gsheet you can:

• Pipe csv data from stdin to a Google Sheet range
  cat data.csv | gsheet csv --id 1o88FhvAXg8Q_ZMFudQLuZ1ShsigbAgJ --range 'Sheet1'

• Pipe csv data from a Google Sheet range to stdout
  gsheet csv --id 1o88FhvAXg8Q_ZMFudQLuZ1ShsigbAgJ --range "'Sheet!'A1:D20" > data.csv

• Clear a Google Sheet range
  gsheet clear --id 1o88FhvAXg8Q_ZMFudQLuZ1ShsigbAgJ --range Sheet2

• Create and delete sheets of a Spreadsheet document
  gsheet newSheet --id 1o88FhvAXg8Q_ZMFudQLuZ1ShsigbAgJ --name NewSheet

• Sort a sheet by a column
  gsheet sort --id 1o88FhvAXg8Q_ZMFudQLuZ1ShsigbAgJ --name Sheet1 -c 2

Google Drive operations (with special handling for .csv):

• Upload .csv files to Google Drive as Sheet documents
  gsheet upload --parent PARENT_ID ./path/to/data.csv

• Download Google Sheet documents from Google Drive as .csv files
  gsheet download 2o88FhvAXg8Q_ZMFudQLuZ1ShsigbAgJ > data.csv

• Create a new empty folder
  gsheet createFolder --parent PARENT_ID 'New Folder Name'

You can also upload/delete and get info about arbitrary files on Google Drive, but gsheet isn’t trying to be a general purpose gdrive interface. If you need a full Google Drive command-line client, check out odeke-em/drive, or something that provides a filesystem interface like rclone.

For more on how to use gsheet see CLI Usage below.

gsheet supports authenticating with Google using service accounts, which is a simple way to allow scripts to access Google Sheets (see Authentication and Authorization below).

Why?

gsheet is not yet packaged for any package manager yet (let me know if you can help with that!), but you can download binaries for major platforms from the Releases page.

Otherwise, if you have Go installed you can:

However, building from source will pull in the build dependencies (Google’s API SDKs) which are big (200MB+) so can be slow to download.

The hardest part about getting started with gsheet is creating the API credentials so that the program can access Google Drive, but it’s not so bad and you only have to do it once. In order for gsheet to read and update Sheets documents, it must 1) be provided credentials to authenticate with Google and 2) be granted access to whichever Google Drive folders/documents it should be able to read/write:

1. Create a service account and download the credentials .json file to the computer you will use gsheet on. Set the GOOGLE_APPLICATION_CREDENTIALS envvar to the absolute path to that file before running gsheet.

   • From the Google Cloud API Dashboard create a new project and enable both the Google Drive API and the Google Sheets API on it. See Google’s Getting Started Guide for creating a project and enabling APIs.

   • From your project’s dashboard Click on "IAM & Admin" > "Service Accounts". Then click "Create Service Account". For the service account’s role I recommend "Basic>Editor".

   • Once the service account is created, click on it to manage its details. From the "Keys" tab click "Add Key" to create credentials in a .json file for the service account that gsheet will use to authenticate as the user.

     • The .json file containing the credentials should download automatically. KEEP THIS FILE SECRET (do not check it into source control). Anyone with the credentials can edit any files you grant the service account access to.

     • Google’s documentation is available as Creating and Managing Service Accounts and Creating and Managing Service Account Keys

   • Set the GOOGLE_APPLICATION_CREDENTIALS environment variable to the path to the .json file. This is how gsheet finds the credentials when it runs. NOTE: GOOGLE_APPLICATION_CREDENTIALS must containt the absolute path to the .json file. (See Google’s Authentication Overview.)

2. In Google Drive grant share files and folders with the service account (the email address generated when you created the service account above) as if it were any other user. Note that if you only want to store files in the service account’s own drive (not viewable from the Google Drive web interface), then you don’t actually have to grant it access to any of your folders or documents.

What about OAuth authentication?

To get an overview of all the commands provided by gsheet run:

You can also run gsheet help CMD to get help for each command. Below are some further usage hints.

Remember that for any of the commands to work you must have the GOOGLE_APPLICATION_CREDENTIALS environment variable set to a path containing the service account credentials in a .json file.

Sheet commands

# Replace an entire sheet of a Spreadsheet doc with the contents of data.csv
gsheet --id SHEETS_DOC_ID clear --range Sheet1
cat data.csv | gsheet --id SHEETS_DOC_ID --range Sheet1

# Read a specific range of a sheet to output.csv
# (You can always single quote sheet names and include the exclamation point in
# the single quotes so that the shell doesn't try to interpret it.)
gsheet --id SHEETS_DOC_ID --range 'Sheet1!'A2:C5 > output.csv

# Sort sheet by B coloumn in ascending order
sort --id SHEET_NAME -name Sheet1 --column=1 --asc

# After running this you should see a new sheet called "SHEET_NAME" in the
# spreadsheet with id "SHEETS_DOC_ID"
gsheet newSheet --id SHEETS_DOC_ID --name SHEET_NAME

# After running this it should be gone again
gsheet deleteSheet --id SHEETS_DOC_ID --name SHEET_NAME

Drive commands

# Upload data.csv as a Sheets document in the service account's root directory
gsheet upload --parent root data.csv

# Download an image from drive
# Note that download takes a single positional argument: the id of the google
# drive file to download, and it sends its output to stdout.
gsheet download DRIVE_DOC_ID > image.png

# List all files and their ids that are in the service account's root folder
gsheet list --parent root

# Create a foler in service account's root (specify --parent to use a different
# folder)
$ gsheet createFolder FOLDER_NAME
Created directory named FOLDER_NAME with id 1ApMOHtZtTVM_UU7HyUCvMIIa3R5fDf6N

Ranges

Finding document and parent IDs

Environment Variables

In addition to the cli tool, gsheet can be used as a Golang package to simplify access to Google Sheets and Google Drive from Go.

All of the Sheets related functions are in the gsheets pacakge (gsheets/sheets.go), and all of the Drive related functions are in the gdrive pacakge (gdrive/files.go).

Online godoc documentation for the packages can be found here:

• https://pkg.go.dev/github.com/cristoper/[email protected]/gdrive

• https://pkg.go.dev/github.com/cristoper/[email protected]/gsheets

For a quick-and-dirty example of how to use the packages look at the integration_test.go file included in each package.

To run tests:

To build:

To build binaries for various platforms in build/:

To release:

tbd