Error in Cellranger: rows[1] <= rows[2] is not TRUE`

[devu123]

I've been able to reproduce an error in cellranger, when reading a worksheet:

reading from a test sheet :
https://docs.google.com/spreadsheets/d/1WbIrT7NtMUvqQ4FR6IX5ZdkNDLBISF9IHSbzNqlBNPU/edit#gid=1898788512

here is my code:

require(googlesheets4)

ss = as_sheets_id("https://docs.google.com/spreadsheets/d/1WbIrT7NtMUvqQ4FR6IX5ZdkNDLBISF9IHSbzNqlBNPU/edit#gid=1898788512")
currentSheet <- data.frame(read_sheet(ss,
                                       sheet = 'test', .name_repair = 'minimal'), stringsAsFactors = F)

i get the error:Error in cellranger::cell_limits(ul = c(start_row, start_column), lr = c(end_row, : rows[1] <= rows[2] is not TRUE

when I remove the row with the filters on top of the sheet, I no longer get this error. curious to understand why.

my sessionInfo:

Platform: x86_64-w64-mingw32/x64 (64-bit)
Running under: Windows 10 x64 (build 17763)

Matrix products: default

locale:
[1] LC_COLLATE=English_United States.1252  LC_CTYPE=English_United States.1252   
[3] LC_MONETARY=English_United States.1252 LC_NUMERIC=C                          
[5] LC_TIME=English_United States.1252    

attached base packages:
[1] stats     graphics  grDevices utils     datasets  methods   base     

other attached packages:
[1] googlesheets4_0.2.0

loaded via a namespace (and not attached):
 [1] Rcpp_1.0.2        compiler_3.6.0    cellranger_1.1.0  pillar_1.4.1      prettyunits_1.0.2
 [6] remotes_2.1.0     tools_3.6.0       testthat_2.3.2    digest_0.6.20     pkgbuild_1.0.3   
[11] pkgload_1.0.2     jsonlite_1.7.0    googledrive_1.0.1 memoise_1.1.0     tibble_2.1.3     
[16] lifecycle_0.1.0   gargle_0.5.0.9000 pkgconfig_2.0.2   rlang_0.4.6       cli_1.1.0        
[21] rstudioapi_0.10   curl_4.3          httr_1.4.1        withr_2.2.0       dplyr_0.8.1      
[26] askpass_1.1       desc_1.2.0        fs_1.4.2          vctrs_0.3.1       devtools_2.0.2   
[31] rprojroot_1.3-2   tidyselect_0.2.5  glue_1.4.1        R6_2.4.1          processx_3.3.1   
[36] sessioninfo_1.1.1 callr_3.2.0       purrr_0.3.4       magrittr_1.5      backports_1.1.4  
[41] ps_1.3.0          usethis_1.5.0     assertthat_0.2.1  openssl_1.4.2     crayon_1.3.4   ```

[devu123]

#57 is the previous issue that referred to this.

[galabananana]

I'm facing the exactly same issue. But there is no filter in the sheet I tried to read, and when I copied the whole sheet to a new one the problem solved itself. Would highly appreciate it if anyone can take a look!

[danielvartan]

I think I found the error (or one of the triggers).

While reading the sheet, googlesheets4::read_sheet() asesss every sheet and named range of the spreadsheet. If you have a broken named range (#REF), the function can't assess it cell limits and trows an error.

I test this with one of my sheets. After I removed the broken named range googlesheets4::read_sheet() worked as normal.

Here is the error Traceback:

googlesheets4::read_sheet()
↓ (line 9: df <- ...)
googlesheets4:::get_cells()
↓ (line 13: x <- ...)
googlesheets4:::gs4_get()
↓ (line 4: new_google...)
googlesheets4:::new_googlesheets4_spreadsheet()
↓ (line 29: named_ranges$cell_...)
googlesheets4:::make_cell_range()
↓ (line 4: cl <- cellranger...)
cellranger::cell_limits()

[danielvartan]

That appears to be the problem with your spreadsheet @devu123 . If you go to Data > Named Ranges you will see that you have a broken named range called "forecast_recipes".

[devu123]

Thank you! Looks like even if no formulas depend on the named range, it can trigger a break. appreciate this. I have left the broken named range in the example sheet for posterity's sake.

[jennybc]

Here's the current situation with OP's sheet (thanks for providing that!):

library(googlesheets4)

gs4_deauth()

ssurl <- "https://docs.google.com/spreadsheets/d/1WbIrT7NtMUvqQ4FR6IX5ZdkNDLBISF9IHSbzNqlBNPU/edit#gid=1898788512"

gs4_get(ssurl)
#>  Spreadsheet name: cellranger_error
#>                ID: 1WbIrT7NtMUvqQ4FR6IX5ZdkNDLBISF9IHSbzNqlBNPU
#>            Locale: en_US
#>         Time zone: America/New_York
#>       # of sheets: 1
#> # of named ranges: 1
#> 
#> (Sheet name): (Nominal extent in rows x columns)
#>         test: 9385 x 11
#> 
#>           (Named range): (A1 range)
#> 'test'!forecast_recipes: NA

range_read(ssurl, range = "'test'!forecase_recipes")
#> ✔ Reading from "cellranger_error".
#> Error in `as_range_spec()` at googlesheets4/R/get_cells.R:26:2:
#> ! `range` doesn't appear to be a range in A1 notation, a named range, or
#>   a sheet name:
#> ✖ ''test'!forecase_recipes'

#> Backtrace:
#>     ▆
#>  1. └─googlesheets4::range_read(ssurl, range = "'test'!forecase_recipes")
#>  2.   └─googlesheets4:::get_cells(...) at googlesheets4/R/range_read.R:106:2
#>  3.     ├─googlesheets4:::as_range_spec(...) at googlesheets4/R/get_cells.R:26:2
#>  4.     └─googlesheets4:::as_range_spec.character(range, sheet = sheet, skip = skip, sheets_df = x$sheets, nr_df = x$named_ranges) at googlesheets4/R/range_spec.R:21:2
#>  5.       └─googlesheets4:::gs4_abort(...) at googlesheets4/R/range_spec.R:93:4
#>  6.         └─cli::cli_abort(...) at googlesheets4/R/utils-ui.R:137:2
#>  7.           └─rlang::abort(message, ..., call = call, use_cli_format = TRUE)

^{Created on 2022-08-12 by the reprex package (v2.0.1.9000)}

googlesheets4 no longer falls over upon even trying to access the Sheet. Operations should work as expected as long as they don't actually make use of the dysfunctional named range.

Obviously if you try to read from the named range, it's an error. If this comes up again, we can consider throwing a more informative error and/or alerting the user more proactively. But I'm not bothering for now. It's not really clear to me how or how often this sort of weird name range comes about.

In my own attempts to create a Sheet with this problem, I could not. I could create a named range, then delete those cells, thereby creating a dysfunctional named range that looks like the one in OP's Sheet (reference is #REF!). But then the API does not return any info on that broken named range. I don't even know how to create a Sheet as weird as the one OP shared. I guess it's a case of "All well-formed named ranges are alike; each malformed named range is dysfunctional in its own way."