How to load Named Ranges into array?-CodePudding

I have defined a couple of named ranges in my sheet following the naming schema fieldName1, fieldName2, fieldName3 etc. They are located in different places in my sheet.

I am trying to load those fieldName(s) into an array now, but I encounter an issue, as getRange("fieldName1").getValue() is expecting a "String" of fieldName rather than a getRange(fieldName[c]).getValue() code.

Here is what I have so far:

  const noFields = wsS.getRange("noFields").getValue()
  var fieldName = []
    for (var c = 1; c <= noFields;   c) {
    fieldName[c] = wsS.getRange(fieldName[c]).getValue()
  }

noFields is a variable of how many fieldNameX named ranges I have to load, as I didn't think of a better way to load them still.

Please help, thank you.

CodePudding user response：

So you want all the named ranges that start with "fieldName", and want to get all their values as an array.

You can get all the named ranges from the spreadsheet using .getActive(). However, they will not be sorted as expected.

If you simply want an array with all the values from named ranges, do:

var ss = SpreadsheetApp.getActive()
var values = ss.getNamedRanges()
    .map(namedRange => namedRange.getRange().getValue())

If you only want the values from those ranges following the naming schema, do:

const PREFIX = "fieldName" // case sensitive
var values = ss.getNamedRanges()
    .filter(namedRange => namedRange.getName().startsWith(PREFIX))
    .map(namedRange => namedRange.getRange().getValue())

You might also want to sort the named ranges by the number after "fieldName", just in case they were entered in different order, right? But that complicates things a little since the names are strings.

One way of sorting the array is by constructing an associative array with the range names:

const PREFIX = "fieldName" // case sensitive
var ss = SpreadsheetApp.getActive()
var namedRanges = ss.getNamedRanges()
    .filter(namedRange => namedRange.getName().startsWith(PREFIX))
var associativeArray = []
for (var namedRange of namedRanges) {
  associativeArray[namedRange.getName().substring(PREFIX.length)] =
      namedRange.getRange().getValue()
}
var values = associativeArray.flat()

Or with a custom comparator:

const PREFIX = "fieldName" // case sensitive
var ss = SpreadsheetApp.getActive()
var values = ss.getNamedRanges()
    .filter(namedRange => namedRange.getName().startsWith(PREFIX))
    .map(namedRange => [
        Number(namedRange.getName().substring(PREFIX.length)),
        namedRange.getRange().getValue() ])
    .sort((itemA,itemB) => itemA[0] - itemB[0])
    .map(item => item[1])

In any case, you will end up with an array of values that you can use.

CodePudding user response：

Get all named ranges in to an array

SpreadsheetApp.getActive().getNamedRanges();

Class namedRanges

function namedRangesInAnArray() {
  const ss = SpreadsheetApp.getActive();
  const names = ['r1','r2'.....];//array of names to get
  return SpreadsheetApp.getActive().getNamedRanges().filter(r => ~names.indexOf(r.getName()));
}