write_gsheet() writes tsg tables to a Google Sheet. It
takes the same input and styling as write_xlsx(): titles,
merged headers, footnotes and facades all carry over.
write_gsheet() writes the tables with
write_xlsx() to a temporary .xlsx file and
uploads that file to Google Drive, which converts it to a Google Sheet.
Anything write_xlsx() can produce,
write_gsheet() can too, and the styling is whatever
Google’s conversion keeps. In practice that covers merged cells,
borders, fonts, fills and number formats.
write_gsheet() talks to Google Drive through the
googledrive package, which is not installed with tsg:
Sign in once per session:
A browser window opens for you to choose a Google account. On the consent screen, tick the box that lets the app “See, edit, create and delete all of your Google Drive files”. Google leaves it unticked by default, and without it every write fails (see Troubleshooting).
To skip the browser in later sessions, let googledrive reuse the cached sign-in for your account:
Give a name, and write_gsheet() creates a new Google
Sheet in your Drive:
sheet <- person_record |>
generate_frequency(sex) |>
add_table_title("Population by sex") |>
add_source_note("Source: person_record") |>
write_gsheet("Population by sex")It returns the new file, invisibly, as a dribble
(googledrive’s file object). Keep it to open, share or update the sheet
later:
To create the sheet inside a folder, give a path. The folder must already exist:
googledrive::drive_mkdir("reports")
person_record |>
generate_frequency(sex) |>
write_gsheet("reports/Population by sex")Each call with a name creates a new file, even if a sheet with that name already exists. Google Drive allows several files with the same name.
Pass an existing sheet instead of a name, and
write_gsheet() replaces its contents. It stays the same
file, so its link, sharing settings and location in Drive don’t change.
This suits a report others have bookmarked and that you refresh with new
data.
The existing sheet can be:
dribble an earlier write_gsheet() call
returned,googledrive::as_id(). The ID is
the long string in the sheet’s URL,
https://docs.google.com/spreadsheets/d/<file id>/edit:person_record |>
generate_frequency(marital_status) |>
write_gsheet(googledrive::as_id("1AbC...xyz"))A plain string is always treated as a name for a new
sheet. Wrap an ID in googledrive::as_id() to update.
Pass a facade as you would to write_xlsx(), e.g. a
built-in one by name. See vignette("facade") for the full
list:
person_record |>
generate_crosstab(marital_status, sex) |>
write_gsheet("Marital status by sex", facade = "striped")Settings added to a table with add_facade() apply too,
as do all other write_xlsx() arguments, such as
title, subtitle, source_note,
footnotes and include_table_list:
A list of tables becomes one spreadsheet with one tab per table, named after the list items:
person_record |>
generate_frequency(sex, marital_status, employed) |>
write_gsheet("Population profile")The same goes for tables generated per group with
group_as_list = TRUE:
person_record |>
dplyr::group_by(sex) |>
generate_summary(age, group_as_list = TRUE) |>
write_gsheet("Age by sex")Add include_table_list = TRUE for a first tab listing
the tables. Its links, and the “Back” link on each tab, open the right
tab in Google Sheets: write_gsheet() rewrites them after
the upload, because Google Sheets does not follow Excel’s links between
sheets.
person_record |>
generate_frequency(sex, marital_status, employed) |>
write_gsheet("Population profile", include_table_list = TRUE)separate_files = TRUE, which writes each table to its
own xlsx file, is not supported. Call write_gsheet() once
per table instead.
generate_output()generate_output() writes a Google Sheet with
format = "gsheet". Its path is passed to
write_gsheet() unchanged, so it can be a name or an
existing sheet:
A script run by a scheduler (cron, CI) has no browser to sign in with. Use a Google Cloud service account and its JSON key instead:
Files a service account creates belong to the service account, not to you. To write into your own Drive, share a folder with the service account’s email address and create sheets in that folder, or share an existing sheet with it and update the sheet in place.
“(403) Forbidden: Request had insufficient authentication scopes”: you signed in without ticking the Drive box on Google’s consent screen. Clear the token and sign in again, asking for Drive access explicitly, and tick the box:
googledrive::drive_deauth()
googledrive::drive_auth(
scopes = "https://www.googleapis.com/auth/drive",
cache = FALSE
)“(401) Unauthorized: Request had invalid authentication credentials”: no valid token is in use, either because you haven’t signed in this session or because the cached token has expired or been revoked. Check, then sign in again:
googledrive::drive_has_token() # FALSE: run googledrive::drive_auth()
googledrive::drive_user() # the signed-in account
gargle::gargle_oauth_sitrep() # tokens cached on diskTo test the sign-in apart from tsg, list one file. If this fails, the
problem is the sign-in, not write_gsheet():
A styling detail is missing in the sheet: the xlsx
file is converted by Google, and a few Excel features have no Google
Sheets equivalent. Check the same output with write_xlsx();
if it looks right there, the difference comes from Google’s conversion,
not from tsg.