Skip to contents

Export a survey instrument to Google Sheets collection format

Source: R/google_sheets.R
export_google_sheet.Rd

Generates a Google Apps Script file that, when run in a Google Sheet, creates a response collection endpoint for a survey instrument. The builder can store the deployed Apps Script URL in survey metadata, and the same sheet can be read back with read_sheet_responses().

Usage

export_google_sheet(instrument, sheet_url, output_dir = ".")

Arguments

instrument

An sframe object.

sheet_url

Character. The URL of an existing Google Sheet. The generated script records it as a comment and a constant, so the file identifies the sheet it was written for. The collector writes through the active spreadsheet it is bound to, so the sheet needs no change to its sharing settings.

output_dir

Character. Directory to write the Apps Script file. Defaults to the current working directory.

Value

The path to the generated .gs Apps Script file, invisibly.

Changing the instrument mid-collection

Regenerating and redeploying the collector after an instrument change is safe, and it is what a pilot study normally does after a face-validity pass. The collector maps every response to the sheet's own header by name, so a column that already exists never moves and rows collected before the change stay valid. An item added to the instrument gets a new column at the right-hand end of the sheet, and rows collected before it existed are left blank in that column.

Before surveyframe 0.4.1 this was not true. The header was written once, at sheet creation, and rows were built positionally, so an item added mid-collection shifted every value from the insertion point onward into the wrong column, silently. If you collected responses through a redeployed collector generated by 0.4.0 or earlier, check the sheet's header against the instrument before analysing it.

Who can reach the collected responses

Keep the sheet private. The collector is an Apps Script bound to the sheet and writes through SpreadsheetApp.getActiveSpreadsheet(), so it runs with the access of whoever deployed it. No sharing change is needed for collection to work.

Step 5 of the generated setup sets the web app's "Who has access" to "Anyone". That setting belongs to the web app endpoint, which accepts submissions. It grants no access to the sheet itself.

Sharing the sheet so that any link holder can edit would expose every collected response, personal data included, to anyone holding the URL, and would let them alter or delete it. Before surveyframe 0.4.2 this help advised exactly that. If you followed it, review the sharing settings on any sheet you collected into.

read_sheet_responses() reads through googlesheets4, which authenticates as you, so a private sheet is readable with no sharing change.

See also

read_sheet_responses(), read_responses(), write_sframe()

Examples

instr <- read_sframe(
  system.file("extdata", "tourism_services_demo.sframe",
              package = "surveyframe")
)
script <- export_google_sheet(
  instr,
  sheet_url = "https://docs.google.com/spreadsheets/d/demo",
  output_dir = tempdir()
)
#> Apps Script written to: /tmp/Rtmp44tQ8U/surveyframe_collector.gs
#> Follow the setup instructions inside the file to deploy it.
file.exists(script)
#> [1] TRUE