Can't Determine Cell Limits from A1 String Only Having Columns or Rows

[StevenMMortimer]

as.cell_limits() will correctly parse an A1 formatted string that has the column letter and row number; however, it fails on instances that only specify the columns or rows such as Africa!A:B or Africa!2:3.

> my_range <- "Africa!A:B"
> as.cell_limits(my_range)
Error: Can't guess format of this cell reference:
A:B
In addition: Warning message:
Cell reference follows neither the A1 nor R1C1 format. Example:
A
NAs generated. 
> 

Given that Africa!A:B or Africa!2:3 are common shorthands it would be good to support their parsing. I was able to get things working by updating the following regex statements in a way that relaxes the assumption that there is at least 1 letter and 1 number in the string. That seemed reasonable, but maybe I'm missing something.

assign('A1_rx', 
       "^\\$?[A-Za-z]{0,3}\\$?[0-9]{0,5}$", 
       envir=cellranger:::.cr)

assign('A1_ncg_rx', 
       paste0("(?P<col_abs>\\$?)(?P<col_ref>[A-Za-z]{0,3})",
              "(?P<row_abs>\\$?)(?P<row_ref>[0-9]{0,})"),  
       envir=cellranger:::.cr)

> as.cell_limits("Africa!A:B")
<cell_limits (-, 1) x (-, 2) in 'Africa'>
> as.cell_limits("Africa!2:3")
<cell_limits (2, -) x (3, -) in 'Africa'>

[jennybc]

Given that Africa!A:B or Africa!2:3 are common shorthands

I had no idea! How does Excel know if this means rows or columns? Is this documented somewhere as an officially supported form of cell reference (whatever that means 🙄)?

cellranger does support such ranges already inside cell_rows() and cell_cols(), but maybe you noticed that.

[StevenMMortimer]

It's supported by Excel and Google Sheets and defaults to this notation as soon as you select the entire row or column as part of a formula. Here is some Sheets documentation on valid A1 notations: https://developers.google.com/sheets/api/guides/concepts#a1_notation

[jennybc]

I need this myself ASAP, so am working on it.

[jennybc]

@StevenMMortimer In case you still have a special interest in this, do you know or are you willing to research whether Excel supports all of these:

https://developers.google.com/sheets/api/guides/concepts#a1_notation

• Sheet1!A:A refers to all the cells in the first column of Sheet1.
• Sheet1!1:2 refers to the all the cells in the first two rows of Sheet1.
• Sheet1!A5:A refers to all the cells of the first column of Sheet 1, from row 5 onward.

I think the first two are "no brainer" yes's. But it's that last one that seems unfamiliar to me. Plus it brings up several variations on the same theme that ... might work?

No pressure, but I'm just putting out a feeler in case you are interested in this.

[nacnudus]

Sorry to butt in but I also have a special interest in this! Excel interprets the second A in Sheet1!A5:A as a name. (So does tidyxl::xlex).

If you use the formula =SUM(Sheet1!A5:A) in Google Sheets and export to Excel, then it alters the formula to =SUM(Sheet1!A5:A1000) (or however many rows are in the spreadsheet).

[StevenMMortimer]

Below is the list of supported A1 Notations from the Google Sheets documentation. I've crossed out the ones that don't work in Excel.

• Sheet1!A1:B2 refers to the first two cells in the top two rows of Sheet1.
• Sheet1!A:A refers to all the cells in the first column of Sheet1.
• Sheet1!1:2 refers to the all the cells in the first two rows of Sheet1.
• Sheet1!A5:A refers to all the cells of the first column of Sheet 1, from row 5 onward.
• A1:B2 refers to the first two cells in the top two rows of the first visible sheet.
  • I'm not sure why it says "first visible sheet" in the Sheets documentation. In Excel and Google Sheets if you don't specify the sheet, then it operates on the sheet that the formula exists on.
• Sheet1 refers to all the cells in Sheet1.

With the fourth bullet, Excel commits you to providing both the row and column for each endpoint of the range or neither endpoints of the range. For example, A:A or A1:A10 is acceptable, but A5:A is not acceptable.

With the sixth bullet, I assume Excel is trying to protect you from inadvertently operating over some non-numeric or unexpected values by not letting you specify the entire sheet. Or it's some protection against the computing power used when running against an entire sheet. You know how Excel is always trying to anticipate what you want to do before you do it ;)

[jennybc]

A1:B2 refers to the first two cells in the top two rows of the first visible sheet.

• I'm not sure why it says "first visible sheet" in the Sheets documentation. In Excel and Google Sheets if you don't specify the sheet, then it operates on the sheet that the formula exists on.

This is coming from the Sheets API docs (vs browser UI), so I think it's saying that ... if you ask for cells A1:B2 without specifying the sheet, it defaults to the first visible sheet.

With the sixth bullet, I assume Excel is ...

I think this is another case that's pretty unique to an API request (vs. normal UI). If you send that as the range for reading a Google Sheet, you get all cells on that sheet.

[jennybc]

So it's really stuff of this form: Sheet1!A5:A that creates tension between parsing cell ranges for Google Sheets vs Excel.