Write value of several cells not in a row to array-CodePudding

I know how to create a range to get values from cells and add to an array. I am having issues adding values from several cells that are not in sequence (1,1,10,1).

I am trying to get add the values like this

  var Array = [
    sheet.getRange("AD3").getValue(),
    sheet.getRange("AG3").getValue(),
    sheet.getRange("AJ3").getValue(),
    sheet.getRange("AM3").getValue(),
    sheet.getRange("AD23").getValue(),
    sheet.getRange("AG23").getValue(),
    sheet.getRange("AJ23").getValue(),
    sheet.getRange("AM23").getValue(),
  ]

Its not working, each return "undefined". They are dates in those cells. Here's how I tested:

for (var i=0; i < Array.length; i  ) {
     if (Array[i][0] != ""){ 
        Logger.log(Array[i][0])
    }
}

Any help would be apricated.

CodePudding user response：

From your repling in the comment, I understood your issue is from the following script.

var Array = [
  sheet.getRange("AD3").getValue(),
  sheet.getRange("AG3").getValue(),
  sheet.getRange("AJ3").getValue(),
  sheet.getRange("AM3").getValue(),
  sheet.getRange("AD23").getValue(),
  sheet.getRange("AG23").getValue(),
  sheet.getRange("AJ23").getValue(),
  sheet.getRange("AM23").getValue(),
];
for (var i=0; i < Array.length; i  ) {
  if (Array[i][0] != ""){
    Logger.log(Array[i][0])
  }
}

In your value of Array is 1 dimensional array. So, when you use this script, the top letter of each element is retrieved. In this situation, when each value is the date object, null is returned. I thought that this might be the reason of your issue.

When you want to see each value from Array using your additional script, how about the following modification?

Modified script:

In this modification, your for loop is modified.

for (var i = 0; i < Array.length; i  ) {
  if (Array[i].toString() != "") {
    Logger.log(Array[i]);
  }
}

CodePudding user response：

Using RangeList Class:

function myfunk() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName('Sheet0');
  const rgl = sh.getRangeList(["AD3","AG3","AJ3","AM3","AD23","AG23","AJ23","AM23"]);
  let vals = []
  rgl.getRanges().forEach(r => {
    vals.push({loc: r.getA1Notation(),value: r.getValue()})
  });
  Logger.log(JSON.stringify(vals))
}

RangeList

Execution log
11:16:15 AM Notice  Execution started
11:16:17 AM Info    [{"loc":"AD3","value":""},{"loc":"AG3","value":""},{"loc":"AJ3","value":""},{"loc":"AM3","value":""},{"loc":"AD23","value":""},{"loc":"AG23","value":""},{"loc":"AJ23","value":""},{"loc":"AM23","value":""}]
11:16:17 AM Notice  Execution completed

Obviously my values will not agree with yours since I don't have your data