Error 'This cell has not been loaded yet' - with Named Range

[pravynandas]

await sheet.loadCells('A1:A200');
let a1 = sheet.getCell(0, 0);

works !! but..

await sheet.loadCells('MyRange');
let a1 = sheet.getCell(0, 0);   <--- errored

does not work. (MyRange is a valid named range)

[manasouza]

Does anyone else faced this issue?

Here I'm trying the following:

await billsSheet.loadCells({ startRowIndex: rowNum, startColumnIndex: 0})
 ...
 billsSheet.getCell(rowNum, i)

And this is working for the production spreadsheet but for other spreadsheet I use for testing it isn't. Here's the stacktrace:

Error: This cell has not been loaded yet
    at GoogleSpreadsheetWorksheet.getCell (<project_home_folder>\node_modules\google-spreadsheet\lib\GoogleSpreadsheetWorksheet.js:175:13)

[jaismith]

@manasouza you are likely trying to load a cell that is not actually included in the named range you loaded.

This does surface a bit of a usability issue with named ranges in this package, from what I can tell it is not possible to determine what cells you have actually loaded when you call loadCells with a named range, without looking into the _cells property of the sheet itself. It would be great to add the loaded range to the cellStats property.

I'm currently determining my loaded range like this:

const firstCell = sheet._cells.find(r => r!!).find(c => c!!);
const lastCell = sheet._cells.reverse().find(r => r!!).reverse().find(c => c!!);

const [firstRow, firstColumn] = [firstCell.rowIndex, firstCell.columnIndex];
const [lastRow, lastColumn] = [lastCell.rowIndex, lastCell.columnIndex];

@theoephraim is this still being maintained?

[manasouza]

Thanks for your inputs @jaismith

Since I was dealing with a test spreadsheet, it was easy to create another new one and skip this issue

[mark05e]

This does surface a bit of a usability issue with named ranges in this package, from what I can tell it is not possible to determine what cells you have actually loaded when you call loadCells with a named range, without looking into the _cells property of the sheet itself. It would be great to add the loaded range to the cellStats property.

I'm currently determining my loaded range like this:

const firstCell = sheet._cells.find(r => r!!).find(c => c!!);
const lastCell = sheet._cells.reverse().find(r => r!!).reverse().find(c => c!!);

const [firstRow, firstColumn] = [firstCell.rowIndex, firstCell.columnIndex];
const [lastRow, lastColumn] = [lastCell.rowIndex, lastCell.columnIndex];

Non typescript version (replace !! with != null )

const firstCell = sheet._cells.find(r => r != null).find(c => c != null);
const lastCell = sheet._cells.reverse().find(r => r != null).reverse().find(c => c!= null);

const [firstRow, firstColumn] = [firstCell.rowIndex, firstCell.columnIndex];
const [lastRow, lastColumn] = [lastCell.rowIndex, lastCell.columnIndex];

// quote: reverse is destructive -- it changes the original array
// from: https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Array/reverse
// flip the sheet back before accessing data
sheet._cells.reverse()