golib/io/transforms/gsheet2csv

gsheet2csv

A simple wrapper around encoding/csv to read Google Sheet CSVs from URL, or a given Reader.

This does surprisingly little - you should probably just handle the boilerplate yourself. However, these are the problems it solves for us:

• works with Google Sheet URLs, regardless of URL format
  • Edit URL: https://docs.google.com/spreadsheets/d/XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX/edit?gid=0000000000#gid=0000000000
  • Share URL (Sheet 1): https://docs.google.com/spreadsheets/d/XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX/edit?usp=sharing
  • CSV Export URL: https://docs.google.com/spreadsheets/d/XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX/export?format=csv&usp=sharing&gid=0000000000
  • anything with a path like /spreadsheets/d/{docid}/ and (optionally) a hash or query param like gid={gid}
• can ignore quoted comments (if all other fields in the row are empty)
• can preserve comments
• swaps \r (Windows) for \n (Unix) and ensures trailing newline (a la encoding/csv)

Note: The Google Sheet must be shared to Anyone with the link.

Usage

Same as encoding/csv (embedded), but with two extra options:

package main

import (
	"fmt"
	"os"

	"github.com/therootcompany/golib/io/transform/gsheet2csv"
)

func main() {
	switch len(os.Args) {
	case 2:
		break
	case 1:
		fmt.Fprintf(os.Stderr, "Usage: %s <url>\n", os.Args[0])
		os.Exit(1)
	}
	urlOrPath := os.Args[1]

	gsr := gsheet2csv.NewReaderFrom(urlOrPath)
	records, err := gsr.ReadAll()
	if err != nil {
		fmt.Fprintf(os.Stderr, "Error reading from %s: %v\n", gsr.URL, err)
		os.Exit(1)
	}

	csvw := gsheet2csv.NewWriter(os.Stdout)
	csvw.Comment = gsr.Comment
	if err := csvw.WriteAll(records); err != nil {
		fmt.Fprintf(os.Stderr, "Error writing csv %v\n", err)
		os.Exit(1)
	}
}

CLI

There are two convenience utilities:

• gsheet2csv
• gsheet2tsv

They're only slightly different from a direct export of a Google CSV in that they reformat comments and newlines.

Flags & Options

--raw           download without processing
--print-ids     print ids to stdout without download
--print-url     print url to stdout without downloading
-o <filepath>   write records to file
-d              field delimiter
--comment '#'   treat lines starting with # as comments
--crlf          use CRLF (\r\n) as record separator

Installation

go get github.com/therootcompany/golib/io/transform/gsheet2csv

ASCII Delimiters

,   comma
\t  tab (or a normal tab)
    space (just a normal space)
:   colon
;   semicolon
|   pipe
^_  unit separator
^^  record separator
^]  group separator
^\  file separator
\f  form feed (also ^L)
\v  vertical tab (also ^K)