I have multi sheet have Same format (header).

• Cost_Tmp: Calculate cost from raw data by arrayformula function

• Cost: If Cost_tmp change and > Cost then I want to copy value from Cost_Tmp to Cost.

• I have multi sheet so I would use array to speed-up script process

• Column index can change -> I Would like to use column header to refer to column.

• I wrote this pice of code to do that but It doesn't work (copy function work but creteria of if function doesn't work). I don't know how to compare 2 object value of array. The problem is if function below

• if (data[i][Cost_Tmp_col] > data[i][Cost_col].toString())

• Here is exaple sheet: Google sheet testing sheet

function updateCost() {
  var mysheetname = ["RETAIL_ORDER","SEA FOOD_ORDER","HAMPER_ORDER","GARDEN_ORDER"];

  for(var j = 0;j < mysheetname.length;j  )

  {
    var sheet = SpreadsheetApp.getActive().getSheetByName(mysheetname[j]);
    var headers = sheet.getRange(1,1,1,sheet.getLastColumn()).getValues()[0];
    var Cost_col = headers.indexOf('Cost');
    var Cost_Tmp_col = headers.indexOf('Cost_Tmp');

    var dataRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(mysheetname[j])
      .getDataRange();
    var CostRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(mysheetname[j])
      .getRange(1,Cost_col 1,sheet.getLastRow(),1);
    var data = dataRange.getValues();
    var newData = []
    for (var i = 0; i < data.length; i  ) {
      var cost_tmp_val = data[i][Cost_Tmp_col];
      if (data[i][Cost_Tmp_col] > data[i][Cost_col].toString()) {
        data[i][Cost_col] = data[i][Cost_Tmp_col].toString();
      }
      newData.push(new Array(data[i][Cost_col]));
      
    }
    CostRange.setValues(newData);
  }
}

CodePudding user response：

Issues:

• You want to compare two numbers, but (at least) one of the items in the comparison is a string data[i][Cost_col].toString(). Do not use toString() if you want to compare numbers.
• You are only pushing values to newData if the condition is met, whereas you want all Cost column to be populated (not just the rows that have to get updated).
• The constructor new Array(number) will create an empty array with the number's length. That's not what you want to accomplish.

Solution:

• Do not use toString() if you want to compare numbers.
• Retrieve the values corresponding to CostRange and modify those values instead of creating a new array via newData.

Code sample:

function updateCost() {
  var mysheetname = ["RETAIL_ORDER","SEA FOOD_ORDER","HAMPER_ORDER","GARDEN_ORDER"];
  for(var j = 0;j < mysheetname.length;j  )  {
    var sheetname = mysheetname[j];
    var sheet = SpreadsheetApp.getActive().getSheetByName(sheetname);
    var headers = sheet.getRange(1,1,1,sheet.getLastColumn()).getValues()[0];
    var Cost_col = headers.indexOf('Cost')   1;
    var Cost_Tmp_col = headers.indexOf('Cost_Tmp')   1;
    var dataRange = sheet.getDataRange();
    var CostRange = sheet.getRange(1,Cost_col,sheet.getLastRow(),1);
    var costValues = CostRange.getValues();
    var data = dataRange.getValues();
    for (var i = 1; i < data.length; i  ) {
      var rowData = data[i];
      var costTemp = rowData[Cost_Tmp_col - 1];
      if (costTemp > rowData[Cost_col - 1]) {
        costValues[i] = [costTemp];
      }
    }
    CostRange.setValues(costValues);
  }
}

Note:

• I modified your script a bit in order to avoid repetitive tasks (e.g. accessing SpreadsheetApp.getActiveSpreadsheet().getSheetByName(mysheetname[j]) or data[i] multiple times).
• I'm assuming that triggering this when the spreadsheet is edited is not the issue, but only making the script do the appropriate actions. If you need help with that, take a look at onEdit.