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

CodePudding user response：

I believe your goal is as follows.

• You have a named range of test!A:A as the name of Data.
• You want to retrieve the values from the named range and want to retrieve the data range from the named range.
• You want to achieve this using Google Apps Script.

In this case, how about the following sample script?

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.
}

• When this script is run, the values of the data range are retrieved from a named range.

References:

• map()
• splice()

CodePudding user response：

Get named ranges

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

Spreadsheet.getNamedRanges