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.
I believe your goal is as follows.
1, you want to copy the value from column "B" to column "BW".setCalculate.In this case, how about the following modification?
var range = srcSheet.getRange("BY2:BY");
var values = range.getValues();
if( values == "1"){
}
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);
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] : ""]);.From the following replying,
however I need it to change only the cell BW2, otherwise it is perfect
How about the following modification?
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]);