For example, I have data like this
And I named it
I used Spreadsheet.getRangeByName and got every cell in column A including blank rows
How do I get only range that has data (like in Sheet.getDataRange)
Output should be [[data], [data], [data], [data]]
Or [[data,,,...and so on], [data,,,...], [data,,,...], [data,,,...]] is acceptable
I believe your goal is as follows.
test!A:A as the name of Data.In this case, how about the following sample script?
function myFunction() {
const nameOfNamedRange = "Data"; // Please set the name of named range.
const ss = SpreadsheetApp.getActiveSpreadsheet();
const values = ss.getRangeByName(nameOfNamedRange).getValues();
const row = values.length - [...values].reverse().findIndex(r => r.findIndex(c => c.toString() != "") > -1);
const colLen = values[0].length;
const col = Math.min(...values.map(r => colLen - r.findIndex(c => [...c].reverse().toString() != "")));
const res = values.splice(0, row).map(r => r.splice(0, col));
console.log(res); // You can see the result values in the log.
}
does get entire range like this cause performace issue?, although I'm not sure whether I could correctly understand performace issue you are thinking, for example, in my sample script, the last row and last column of the data range are retrieved from the named range. And, using these last row and column, the result values are retrieved. I think that in this flow, the process cost might be not large. If I misunderstood your additional question, I apoplogize.Sorry for my bad endlish, I am not native. I heard that getting cell data from sheet is slow, so getting thousands of cell values should be very slow. Am I right?, in my sample script, the values are retrieve by one request with getValues(). By this, the process cost is low. I think that in your reply, it might be the situation using getValues() and getValue() in a loop. In my sample script, please don't worry about it. And, when you tested my sample script, what result did you obtain?function nr() {
const ss = SpreadsheetApp.getActive();
const nrs = ss.getNamedRanges();
Logger.log(nrs.map(r => [r.getName(),r.getRange().getA1Notation()]))
}
Execution log
12:02:07 PM Notice Execution started
12:02:06 PM Info [[Sheet1, D4:G9], [one, C6:C8], [NamedRange1, C9:H11]]
12:02:08 PM Notice Execution completed
