CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
SoulCRYSIS
SoulCRYSIS

Reputation: 629

Google App Script: How do I get only data range from named range?

For example, I have data like this

enter image description here

And I named it

enter image description here

I used Spreadsheet.getRangeByName and got every cell in column A including blank rows

enter image description here

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

Upvotes: 2

Views: 1999

Answers (2)

Tanaike
Tanaike

Reputation: 201613

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:

Upvotes: 3

Cooper
Cooper

Reputation: 64100

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

Upvotes: 0

Related Questions