*! googlesheets v0.1.2 2026-07-31
help googlesheets

Title

googlesheets -- Read, write, edit, and chart Google Sheets directly from Stata

Syntax

googlesheets subcommand [...] [, options]

where subcommand is one of:

import read a range into the current dataset (like import excel)

export write the current dataset to a sheet (like export excel)

put place a single value, formula, or matrix at a cell (like putexcel)

format apply colour / font / number-format to a cell range

addchart insert a chart object (column / bar / line / area / pie / donut)

list list the sheet (tab) names in a spreadsheet

ping confirm OAuth + API access against a target spreadsheet

create create a new spreadsheet in the user's Drive (returns its id / URL)

addsheet add a new tab

deletesheet delete a tab by title

renamesheet rename a tab

Subcommand syntax

googlesheets import using "<id-or-url>" ,
sheet(name)
[range(A1:Z)
firstrow
clear
stringall
since(column=value)
tail(N)
...]

googlesheets export using "<id-or-url>" ,
sheet(name)
[range(A1)
replace | append
firstrow(variables)
...]

googlesheets put using "<id-or-url>" ,
sheet(name) cell(A1)
(one of value(#) string(text) formula(=...) matrix(M)) [names]

googlesheets format using "<id-or-url>" ,
sheet(name) range(A1:E1)
[bgcolor("#1B2D55")
fgcolor("#FFFFFF")
bold italic
font(Montserrat)
fontsize(#)
numfmt("0.0%")
halign(left|center|right)
wrap]

googlesheets addchart using "<id-or-url>" ,
sheet(source) type(column|bar|stacked_column|stacked_bar|line|area|scatter|pie|donut)
domain(A2:A6) series(B2:B6|C2:C6|...)
[names("label1|label2|...")
colors("#1B2D55|#D44500|...")
title("...")
xlabel("...") ylabel("...")
legendpos(top|right|bottom|left|none|labeled) (default bottom)
tx2036style
piehole(0.5)
targetsheet(name) anchor(H1)
width(#) height(#)]

googlesheets list, spreadsheet("<id-or-url>")

googlesheets ping, spreadsheet("<id-or-url>")

googlesheets addsheet, spreadsheet("...") title("New Tab") [rows(#) cols(#) index(#)]

googlesheets deletesheet, spreadsheet("...") title("Old Tab")

googlesheets renamesheet, spreadsheet("...") from("Old") to("New")

Spreadsheets are addressed by either the bare ~44-character ID or a full URL.
The wrapper extracts the ID either way.

Every subcommand accepts these common options:

keyfile(path) OAuth Desktop client JSON (overrides $GS_CLIENT)

tokenfile(path) OAuth token cache file (overrides $GS_TOKEN)

verbose echo the underlying Python invocation + Sheets API result

One-time setup

This package talks to Google's Sheets API via a small Python helper that
ships with the package. Auth uses an OAuth 2.0 Desktop client.

Step 1. Create a new project in https://console.cloud.google.com
(any name). Under APIs & Services > Library, enable the Google Sheets API
and Google Drive API.

Step 2. APIs & Services > OAuth consent screen: choose External.
Fill in the required fields, add yourself as a test user, and grant the scopes
.../auth/spreadsheets and .../auth/drive.

Step 3. APIs & Services > Credentials > Create credentials > OAuth client ID.
Application type: Desktop app. Download the JSON file.

Step 4. Save the JSON locally and tell Stata where to find it by
setting $GS_CLIENT once. The recommended place is your profile.do,
which Stata reads on every launch. On macOS the profile is usually at
~/Documents/Stata/ado/personal/profile.do; on Windows it lives next to
the Stata executable. Add one of these blocks to it:

Simple form -- absolute path

global GS_CLIENT "~/.config/stata-googlesheets/client.json"

Cross-OS form -- auto-detects the Google Drive mount point

if inlist("`c(os)'", "MacOSX", "Unix") {

local _gd_base "/Users/`c(username)'/Library/CloudStorage"

local _gd_folders : dir "`_gd_base'" dirs "GoogleDrive-*"

local _gd_folder : word 1 of `_gd_folders'

local _gd_folder = subinstr(`"`_gd_folder'"', `"""', "", .)

local _my_drive `"`_gd_base'/`_gd_folder'/My Drive"'

}

else if "`c(os)'" == "Windows" local _my_drive "G:/My Drive"

global GS_CLIENT `"`_my_drive'/path/to/client.json"'

You can also point at a default test Sheet so the shipped
test_googlesheets.do example just runs:

global GS_TEST_SHEET "https://docs.google.com/spreadsheets/d/<your-id>/edit"

Step 5. Run any googlesheets command. The first call opens
a browser for Google sign-in (one time per machine); click Allow, and a
refresh token is cached next to the client JSON (token.json, mode 0600).
All future calls are silent.

On the very first invocation the package also creates a private Python
virtualenv at ~/.config/stata-googlesheets/venv and pip-installs the
Google client libraries into it. The user's system Python is left untouched.

Verify the setup

display "$GS_CLIENT" // should print the JSON path

display fileexists("$GS_CLIENT") // should print 1

googlesheets ping, spreadsheet("$GS_TEST_SHEET")

Styling a chart or a range

Pass explicit hex codes to colors() on addchart, and to
bgcolor() / fgcolor() on format, to style a chart or a cell
range yourself. The tx2036style option is a shortcut that applies one
fixed house style (a Montserrat title in navy plus a matching series palette)
if you publish under it. A manual call looks like this:

googlesheets format using "..." , sheet("Auto") range("A1:L1") ///

bgcolor("#1B2D55") fgcolor("#FFFFFF") bold font("Montserrat") fontsize(12)

Examples

The examples below use Stata's built-in auto.dta. They work
end-to-end against any Google Sheet you own -- replace $SS with your
own spreadsheet URL or ID. Each example builds on the previous one.

First, set the global pointing to your auth client JSON and the global
pointing to a Sheet you can edit:

global GS_CLIENT "~/.config/stata-googlesheets/client.json"

global SS "https://docs.google.com/spreadsheets/d/.../edit"

1. Verify auth works, list the existing tabs

googlesheets ping, spreadsheet("$SS")

googlesheets list, spreadsheet("$SS")

The first time you run this on a new machine your browser pops a Google
sign-in screen. Click Allow once and the token is cached forever.

2. Push the auto dataset to a fresh tab

sysuse auto, clear

capture googlesheets deletesheet, spreadsheet("$SS") title("Auto")

googlesheets addsheet, spreadsheet("$SS") title("Auto")

googlesheets export using "$SS", sheet("Auto") firstrow(variables)

firstrow(variables) writes the Stata variable names as row 1 so
the resulting Sheet has a real header. Without it the data starts at row 1
and the Sheet has no header.

3. Brand-style the header row

googlesheets format using "$SS", sheet("Auto") range("A1:L1") ///

bgcolor("#1B2D55") fgcolor("#FFFFFF") bold font("Montserrat") fontsize(12)

Applies the navy / white / Montserrat / 12pt / bold treatment. Useful
above any range, not just headers (e.g. highlighting a totals row).

4. Format a numeric column as a percentage / currency

googlesheets format using "$SS", sheet("Auto") range("C2:C75") numfmt("0.0")

googlesheets format using "$SS", sheet("Auto") range("B2:B75") numfmt("\$#,##0")

numfmt() accepts any Sheets number-format pattern: 0.0%,
#,##0.00, yyyy-mm-dd, [h]:mm:ss, etc. The pattern is applied
to every cell in the range.

5. Place individual cell values (titles, notes, totals)

googlesheets put using "$SS", sheet("Auto") cell(O1) string("Auto data summary")

googlesheets put using "$SS", sheet("Auto") cell(O2) string("Run date: `=c(current_date)'")

summarize price

googlesheets put using "$SS", sheet("Auto") cell(O3) value(`=r(mean)')

googlesheets put using "$SS", sheet("Auto") cell(O4) formula("=AVERAGE(B2:B75)")

value(#) writes a number. string("text") writes text
verbatim. formula("=...") writes a Sheets formula -- the cell will
display the computed result.

6. Drop a Stata matrix at a target cell

correlate price mpg weight length

matrix C = r(C)

googlesheets put using "$SS", sheet("Auto") cell(O6) matrix(C) names

matrix(M) writes a rowsof(M) x colsof(M) block starting
at the given cell. Missing values become blank cells. Add names to write the matrix's row and column names as well: the column names go in a header row and the row names in the column to their left. Numbers are written
as numbers (USER_ENTERED parsing applies, so Sheets stores them as numeric).

7. Insert a chart from the data on the Sheet

googlesheets addchart using "$SS", sheet("Auto") type(column) ///

domain(A2:A75) series(B2:B75) ///

names("Price") ///

title("Price by make") ///

xlabel("Make") ylabel("Price (USD)") ///

legendpos(NONE) ///

targetsheet("Auto") anchor(O15) width(640) height(360)

Multi-series example -- price by gear ratio and weight:

googlesheets addchart using "$SS", sheet("Auto") type(scatter) ///

domain(K2:K75) ///

series(B2:B75) ///

names("Price") ///

title("Price vs gear ratio") ///

xlabel("Gear ratio") ylabel("Price") ///

legendpos(NONE) ///

targetsheet("Auto") anchor(O35) width(560) height(360)

Diverging-style stacked bar across categories (Sheets has no native
diverging chart; this stacks normally with a red-to-blue palette):

googlesheets addchart using "$SS", sheet("Auto") type(stacked_bar) ///

domain(A2:A11) series(B2:B11) ///

names("Price") ///

colors("#10487F") title("First 10 makes") ///

legendpos(NONE) ///

targetsheet("Auto") anchor(O60) width(560) height(400)

Donut variant -- pie with 50% inner hole:

googlesheets addchart using "$SS", sheet("Auto") type(donut) ///

domain(A2:A11) series(F2:F11) piehole(0.5) ///

title("Trunk space (first 10 rows)") ///

legendpos(BOTTOM) ///

targetsheet("Auto") anchor(O90) width(420) height(340)

8. Read the Sheet back into Stata for round-trip QA

googlesheets import using "$SS", sheet("Auto") range("A1:L75") firstrow clear

list make price mpg in 1/5, abbrev(15) noobs

firstrow maps row 1 to Stata variable names. Without it every
cell is treated as data and Stata auto-names columns v1 v2 ....

9. Append more rows on a subsequent run

sysuse auto, clear

keep in 1/5 // stand-in for new rows

googlesheets export using "$SS", sheet("Auto") append

append adds the current dataset below the existing rows. The
firstrow(variables) option is intentionally NOT passed here -- you'd
end up with a duplicate header in the middle of the data.

Working with Google Forms response sheets

When a Form writes to a Sheet, every response is a new row appended to
Form Responses 1. Two helpers make incremental ingestion painless:

Pull only new responses since a date

googlesheets import using "$SS", sheet("Form Responses 1") firstrow clear ///

since(Timestamp=2026-01-01)

since(column=value) keeps rows where the named column is greater
than or equal to the value. Works on timestamp columns (>= compares
string ISO dates) and integer/string codes.

Pull only the last N responses

googlesheets import using "$SS", sheet("Form Responses 1") firstrow clear tail(50)

Pull only respondents matching a column condition (post-import)

googlesheets import using "$SS", sheet("Form Responses 1") firstrow clear

keep if email == "foo@bar.org"

For column-equality filters, importing the whole sheet and applying
Stata's if clause is simplest. For very large response sheets,
combine since() + tail() so that no more rows than you asked
for enter Stata (the helper filters the reply before loading it).

Tips, gotchas, and headless / scheduled runs

-- Tabs must exist before you write to them. googlesheets export and
put both fail if the target sheet doesn't exist. Create
it first with googlesheets addsheet, title("...") (the helpfile examples
above always do this).

-- Tab names are case-sensitive. Auto and auto are two
different tabs.

-- Ranges follow A1 notation. A1 is a single cell; A1:E10
is a rectangle; 'My Sheet'!A1:E10 is an A1 with explicit sheet name
(equivalent to passing the sheet via sheet()).

-- Headless / scheduled runs. OAuth needs a browser the first
time per machine, so cron jobs / GitHub Actions / unattended Stata batches
can't use the default flow. Swap in a Service Account JSON instead:
create one in the same GCP project, share each target Sheet with the
service-account email, and point $GS_CLIENT at the SA JSON. The
wrapper auto-detects.

-- Token expiry. Refresh tokens last ~6 months under Google's
External-Testing OAuth consent screen. When that expires the next call pops
the browser again; click Allow and you're back.

Author and license

Authored by Eric A. Booth, Sr Researcher, Texas2036.org (eric.a.booth@gmail.com), 2026. MIT-licensed. Built atop
the Google Sheets API; this package is not affiliated with Google.