This low-level function returns cell data in a tibble with integer variables row and column (referring to location with the Google Sheet), an A1-style reference loc, and a cell list-column. The flagship function read_sheet() is what most users are looking for. It is basically sheets_cells() (this function), followed by spread_sheet(), which looks after reshaping and column typing.

sheets_cells(ss, sheet = NULL, range = NULL)

Arguments

ss

Something that identifies a Google Sheet: its file ID, a URL from which we can recover the ID, or a dribble, which is how googledrive represents Drive files. Processed through as_sheets_id().

sheet

Sheet to read, as in "worksheet" or "tab". Either a string (the name of a sheet), or an integer (the position of the sheet). Ignored if the sheet is specified via range. If neither argument specifies the sheet, defaults to the first visible sheet.

range

A cell range to read from. If NULL, all non-empty cells are read. Otherwise specify range as described in Sheets A1 notation or using the helpers documented in cell-specification. Sheets uses fairly standard spreadsheet range notation, although a bit different from Excel. Examples of valid ranges: "Sheet1!A1:B2", "Sheet1!A:A", "Sheet1!1:2", "Sheet1!A5:A", "A1:B2", "Sheet1". Interpreted strictly, even if the range forces the inclusion of leading, trailing, or embedded empty rows or columns. Takes precedence over skip, n_max and sheet. Note range can be a named range, like "sales_data", without any cell reference.

Value

A tibble with one row per non-empty cell in the range. this might get dignified with a class?

Examples

sheets_cells(sheets_example("deaths"))
#> Reading from 'deaths'
#> Range "'arts'"
#> # A tibble: 82 x 4 #> row col loc cell #> <int> <dbl> <chr> <list> #> 1 1 1 A1 <S3: CELL_TEXT> #> 2 2 1 A2 <S3: CELL_TEXT> #> 3 2 6 F2 <S3: CELL_TEXT> #> 4 3 1 A3 <S3: CELL_TEXT> #> 5 3 2 B3 <S3: CELL_TEXT> #> 6 3 3 C3 <S3: CELL_TEXT> #> 7 3 5 E3 <S3: CELL_TEXT> #> 8 3 6 F3 <S3: CELL_TEXT> #> 9 4 1 A4 <S3: CELL_TEXT> #> 10 4 2 B4 <S3: CELL_TEXT> #> # ... with 72 more rows
# NOT RUN { ## use tidyr::complete() if you want one row per cell, even if empty test_sheet <- "1J5gb0u8n3D2qx3O3rY28isnI5SD89attRwhWPWlkmDM" (x <- sheets_cells(test_sheet, range = "C2:D4")) x %>% tidyr::complete(row, col, fill = list(cell = list(list()))) # }