This is a very short article that serves mostly as internal documentation of how googlesheets4 prepares the range when requesting cell data from the Sheets API. The main purpose is to publish the table below that shows how all possible combinations of row and cell limits are translated into an A1-style range.
There are several ways to convey which cells you want to read in googlesheets4:
range is the primary and most powerful argument for this and takes precedence over all others. range can be:
cell_limits object made with helpers from the cellranger packagesheet is an optional argument specifying sheet name or number. The Sheets API defaults to “first visible sheet” when sheet is unspecified. Note that a sheet name can also be conveyed via range, alone or along with a cell range.skip and n_max are optional arguments that can place lower/upper bounds on where we start reading or stop parsing.If a specific range is targeted, the Sheets API requires that this be specified as an A1-style range. Here is how all possible cell limits are converted to a valid range in the GET request for cell data. Depending on how the user expressed this range, googlesheets4 may do additional post-processing when parsing the cell data into the returned data frame.
| start_col | start_row | end_col | end_row | naive_range | range |
|---|---|---|---|---|---|
| 2 | 2 | 4 | 4 | B2:D4 | B2:D4 |
| 2 | 2 | 4 | NA | B2:D? | B2:D |
| 2 | 2 | NA | 4 | B2:?4 | B2:4 |
| 2 | 2 | NA | NA | B2:?? | B2:ZZZ |
| 2 | NA | 4 | 4 | B?:D4 | B1:D4 |
| 2 | NA | 4 | NA | B?:D? | B:D |
| 2 | NA | NA | 4 | B?:?4 | B1:4 |
| 2 | NA | NA | NA | B?:?? | B:ZZZ |
| NA | 2 | 4 | 4 | ?2:D4 | A2:D4 |
| NA | 2 | 4 | NA | ?2:D? | A2:D |
| NA | 2 | NA | 4 | ?2:?4 | 2:4 |
| NA | 2 | NA | NA | ?2:?? | 2:5000000 |
| NA | NA | 4 | 4 | ??:D4 | A1:D4 |
| NA | NA | 4 | NA | ??:D? | A:D |
| NA | NA | NA | 4 | ??:?4 | 1:4 |
| NA | NA | NA | NA | ??:?? | <NULL> |