function setCalculate(){
 
var ss = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/1M/edit#gid=0");
  var srcSheet = ss.getSheetByName("AutoQuoteDataBase");
  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]);
}

This code works perfectly for its original purpose which was to copy IDs from Column B, say B2 is 'w131' and B3 is 'z1122', etc.

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

and 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.

What I need is for the same row to be moved from column BZ into CB2 when the code is triggered.

CodePudding user response：

I believe your goal is as follows.

• You want to copy the cell "B" to "BW" when the cell "BY" is edited to 1.
• You want to run the script by a trigger.

In this case, how about the following modification?

Modified script:

Please copy and paste the following script to the script editor and save the script. When you use this script, please put 1 to the column "BY" of "AutoQuoteDataBase" sheet. By this, the script is run.

function onEdit(e) {
  var range = e.range;
  var sheet = range.getSheet();
  if (sheet.getSheetName() != "AutoQuoteDataBase" || range.columnStart != 77 || range.getValue() != 1) return;
  sheet.getRange("B"   range.rowStart).copyTo(sheet.getRange("BW"   range.rowStart), {contentsOnly: true});

Note:

• This script is run by a simple trigger of onEdit. So when you directly run this script, an error like TypeError: Cannot read property 'range' of undefined occurs. Please be careful about this.

• For example, when you want to copy the column "B" to the column "BW" by checking the column "BY" without using onEdit, you can also use the following script. In this case, you can directly run this function with the script editor.

    function myFunction() {
      var ss = SpreadsheetApp.getActiveSpreadsheet();
      var srcSheet = ss.getSheetByName("AutoQuoteDataBase");
      var lastRow = srcSheet.getLastRow();
      var values = srcSheet.getRange("A2:BY"   lastRow).getValues();
      var res = values.map(r => [r[76] == 1 ? r[1] : null]);
      if (res.length == 0) return;
      srcSheet.getRange("BW2:BW"   (res.length   1)).setValues(res);
    }
  

Reference:

• Simple Triggers