Helper package to support R scripts or packages that interact with spreadsheets. Original development was motivated by the wish to have a common interface for specifying cell ranges in readxl and googlesheets.

Actual usage in googlesheets:

gs_read(..., range = "D12:F15")
gs_read(..., range = "R1C12:R6C15")
gs_read(..., range = cell_limits(c(1, 1), c(6, 15)))
gs_read(..., range = cell_limits(c(2, 1), c(NA, NA)))
gs_read(..., range = cell_rows(1:100))
gs_read(..., range = cell_cols(3:8))
gs_read(..., range = cell_cols("B:MZ"))
gs_read(..., range = anchored("B4", dim = c(2, 10)))
gs_read(..., range = anchored("A1", dim = c(5, 6), col_names = TRUE))
## internal usage in functions that put data into a googlesheet
anchored(input = head(iris))
anchored(input = head(iris), col_names = FALSE)
anchored(input = head(LETTERS))
anchored(input = head(LETTERS), byrow = TRUE)

Range specification

The main goal is to translate Excel-like ranges, such as A3:D7 or R3C1:R7C4, into something more programmatically useful. cellranger provides an S3 class, cell_limits, as the standard way to store a cell range. Construct cell_limits explicitly by specifying the upper left and lower right cells: cell_limits(ul = c(ROW_MIN, COL_MIN), lr = c(ROW_MAX, COL_MAX)). Think of it like R3C1:R7C4 notation, but with the R and C removed.

library("cellranger")

(foo <- cell_limits(c(1, 1), c(3, 5)))
#> <cell_limits (1, 1) x (3, 5)>

The print method reports the cell rectangle as (UPPER LEFT CELL) x (LOWER RIGHT CELL) where cell locations are specifed as (ROW, COL).

The dim method reports dimensions of the targetted cell rectangle. as.range() converts a cell_limits object back into an Excel range.

dim(foo)
#> [1] 3 5

as.range(foo)
#> [1] "A1:E3"

as.range(foo, RC = TRUE)
#> [1] "R1C1:R3C5"

Use NA to leave a limit unspecified.

cell_limits(c(3, 2), c(7, NA))
#> <cell_limits (3, 2) x (7, -)>

If the maximum row or column is specified but the associated minimum is not, then it is set to 1.

cell_limits(c(NA, NA), c(3, 5))
#> <cell_limits (1, 1) x (3, 5)>

Get a cell_limits object from an Excel range

as.cell_limits("C7")
#> <cell_limits (7, 3) x (7, 3)>

as.cell_limits("A1:D8")
#> <cell_limits (1, 1) x (8, 4)>

as.cell_limits("R2C3:R6C9")
#> <cell_limits (2, 3) x (6, 9)>

Recall the anticipated usage: read_excel(..., range = "D12:F15"). The intent is that as.cell_limits() will be called on user input for the range = argument.

Helpers for row- or column-only specification

cell_rows(1:100)
#> <cell_limits (1, -) x (100, -)>

cell_cols(3:8)
#> <cell_limits (-, 3) x (-, 8)>

cell_cols("B:MZ")
#> <cell_limits (-, 2) x (-, 364)>

cell_cols(c(NA, "AR"))
#> <cell_limits (-, 1) x (-, 44)>

Specify the rectangle via an anchor cell

The rectangle can be described in terms of the upper left anchor cell and the dimensions of the rectangle, either directly or via an object (anticipates writing that object into the spreadsheet).

## direct specification of dimensions
anchored(anchor = "R4C2", dim = c(8, 2))
#> <cell_limits (4, 2) x (11, 3)>

as.range(anchored(anchor = "R4C2", dim = c(8, 2)), RC = TRUE)
#> [1] "R4C2:R11C3"

dim(anchored(anchor = "R4C2", dim = c(8, 2)))
#> [1] 8 2

## indirect specification of dimensions, via the dimensions of an object
input <- head(iris)
anchored(input = input)
#> <cell_limits (1, 1) x (7, 5)>

as.range(anchored(input = input))
#> [1] "A1:E7"

dim(anchored(input = input))
#> [1] 7 5

The anchored() function has additional arguments col_names = and byrow = for more control with 2-dimensional and 1-dimensional objects, respectively.

Other helpers

We've exposed utility functions which could be useful to anyone manipulating Excel-like references.

## convert character column IDs to numbers ... and vice versa
letter_to_num(c('AA', 'ZZ', 'ABD', 'ZZZ', ''))
#> [1]    27   702   732 18278    NA

num_to_letter(c(27, 702, 732, 18278, 0, -5))
#> [1] "AA"  "ZZ"  "ABD" "ZZZ" NA    NA

## convert between A1 and R1C1 cell references
A1_to_RC(c("A1", "AZ10"))
#> [1] "R1C1"   "R10C52"

RC_to_A1(c("R1C1", "R10C52"))
#> [1] "A1"   "AZ10"