Using GoogleSheet Appscript to pull an ID from one column to a cell when another column has the number 1 in it

user_16574562 0
Viewed 53

Column B is filled with a list of IDs, say B2 is 'w131' and B3 is 'z1122', etc.

I need Cell BW2 to be filled with one of these IDs if I put a '1' in column BY,

but it has to be the corresponding ID so putting a '1' in BY2 fills BW2 with 'w131' and putting a '1' in BY3 fills BW2 with 'z1122' etc.

I have the start of some Apps Script code here:

function setCalculate(){
 
  var ss = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/1Z-iZLMiCumHmyloF62335QYOWTn14canbEb4r_7eamM/edit#gid=0");
  var srcSheet = ss.getSheetByName("AutoQuoteDataBase");
  var range = srcSheet.getRange("BY2:BY");  
  var values = range.getValues();  
   
  if( values == "1"){
    
      
  }
}

obviously it isn't finished, or maybe this could be done with a formula.

google-apps-script google-sheets javascript
1 Answers

I believe your goal is as follows.

  • When the column "BY" is 1, you want to copy the value from column "B" to column "BW".
  • You want to achieve this by modifying your function of setCalculate.

In this case, how about the following modification?

Modified script:

From:

var range = srcSheet.getRange("BY2:BY");  
var values = range.getValues();  
 
if( values == "1"){
  
    
}

To:

var lastRow = srcSheet.getLastRow();
var values = srcSheet.getRange("A2:BY" + lastRow).getValues();
var res = values.map(r => [r[76] == 1 ? r[1] : r[74]]);
srcSheet.getRange("BW2:BW" + lastRow).setValues(res);
  • When this script is run, your goal is achieved.
  • If you want to set the empty when the column "BY" is not 1, please modify var res = values.map(r => [r[76] == 1 ? r[1] : r[74]]); to var res = values.map(r => [r[76] == 1 ? r[1] : ""]);.

Reference:

Added:

From the following replying,

however I need it to change only the cell BW2, otherwise it is perfect

How about the following modification?

Modified script:

var lastRow = srcSheet.getLastRow();
var values = srcSheet.getRange("A2:BY" + lastRow).getValues();
var res = values.flatMap(r => r[76] == 1 ? [r[1]] : []);
if (res.length == 0) return;
srcSheet.getRange("BW2").setValue(res[0]);
user_71086530
Related