How to set "status" is 'UP' when the result of column "CountID" change from 0 to >= 1 by google script

• I have 2 sheets, main sheet have table below, raw data sheet have raw data auto refresh every hour.
• Column "CountID" from main sheet count id from raw data sheet using arrayfomula and CountA function. =arrayformula(COUNTIF('raw data'!A2:A11,A2:A))
• In my official spreadsheet, the column index may change therefore, I must use the column header ("Status", "Count") to refer to column position.
• "Status" clolumn from main sheet using data validation dropdown list with up to 6 value like: (Cancel, Pending, UP, DOWN, Return ...)
• Testing sheet: Testing sheet

The code below working and I just upgrade for column header reference to ensure script working when coloumn index change. Thanks Jacques-Guzel Heron.

function updateStatus() {
  var sheet = SpreadsheetApp.getActiveSheet()
  var headers = sheet.getRange(1,1,1,sheet.getLastColumn()).getValues()[0];
  var status_col = headers.indexOf('Status');
  var countid_col = headers.indexOf('CountID');

  var dataRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("main")
    .getDataRange();
  var statusRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("main")
    .getRange(1,status_col 1,sheet.getLastRow(),1);
var data = dataRange.getValues();
  var newData = []
  for (var i = 0; i < data.length; i  ) {
    if (data[i][countid_col] > 0) {
      data[i][status_col] = "UP";
    }
    newData.push(new Array(data[i][status_col]));
    Logger.log(countid_col);
  }
  statusRange.setValues(newData);
}

CodePudding user response：

You can develop a fast Apps Script project easily to update the Status column of your Sheet by using installable triggers. I will show you how in the example below. I chose to run the trigger every half an hour with the everyMinutes method, but you can adapt it to your needs. First you would have to run the createTrigger function once to create the trigger. Then the function updateStatus will run automatically every half hour. Please check the SpreadsheetApp class to learn more about the applied methods.

function createTrigger() {
  ScriptApp.newTrigger('updateStatus').timeBased().everyMinutes(30).create();
}

function updateStatus() {
  var dataRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("main")
    .getDataRange();
  var statusRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("main")
    .getRange("B:B");
  var data = dataRange.getValues();
  var newData = []
  for (var i = 0; i < data.length; i  ) {
    if (data[i][2] > 0) {
      data[i][1] = "UP";
    }
    newData.push(new Array(data[i][1]));
  }
  statusRange.setValues(newData);
}

Please keep in mind that the code above only checks if the CountID column equals to 1 or greater to set up the UP status. Therefore, if the CountID gets lowered to 0 the status will still be UP. Please drop a comment if you need further advice with this approach.