Change data validation column if values in another column equals to X or Y-CodePudding

In sheet 'Template', I have a data validation column B and I would like to change the value in this column, if value in column AB equals X or Y. Otherwise, I want the value in column B to be the same.

I found the following script which doesn't make much sense to me

function Test1() {

   var ss = SpreadsheetApp.getActiveSpreadsheet();
   var s = ss.getSheetByName("Template");
   var numRows = SpreadsheetApp.getActiveSheet().getLastRow();
   var range;

  /* Loop through Column AB and find cells equal to order payment
  and set Column B value based on it */

   for (var i = 1; i <= numRows; i  ) {
      range = s.getRange('AB'   i );
      if (range.getValue() == "X") {
        range.offset(0, 6).setValue("X"); 
      }
     else {
       range.offset(0,6).setValue("NO");
     }
   }
}

CodePudding user response：

please check if this code works for you:

function Test1() {

 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var s = ss.getSheetByName("Template");
 // 27 = number of columns
 var sheetContent = s.getRange(1,2,s.getLastRow(),27).getValues(); 

 for (var i = 0; i < sheetContent.length; i  ) {
  if(sheetContent[i][26] == "X" || sheetContent[i][26] == "Y"){
   //You can replace the updated value by anything you want it to be.
   sheetContent[i][0] = "Updated Value";
  }
 }
 s.getRange(1,2,sheetContent.length,sheetContent[0].length).setValues(sheetContent);
}

CodePudding user response：

Try this:

function Test1() {
   const ss = SpreadsheetApp.getActive();
   const sh= ss.getSheetByName("Template");
   const vs = sh.getRange(1,2,sh.getLastRow(),sh.getLastColumn()).getValues()
   let a = vs.map((r,i) => {
     if(r[27] == "X" || r[27] == "Y") {
       return ["Yes"];
     } else {
       return [r[1]];
     }
   });
   sh.getRange(1,2,a.length, a[0].length).setValues(a);
}