I am having Fleet maintenance google sheet in that i need to copy and Paste the data to another sheet when criteria match.

When Service Status in Data sheet Equal to "Service Pre Alert" or "Service Alert" or "Expired" any of these that row should copy and paste to output sheet with selected column and date stamp. it is possible with app script.

Note : Data sheet, Service status column will update auto when ever HMR reading is updated, so it will do repeated process.

Spreadsheet Link: https://docs.google.com/spreadsheets/d/1UkPGyBFTXUAtlpGmdEDLYo0p1G-tAazjjCFMsu2FqgM/edit?usp=sharing

Thanks in Advance

Johnn

CodePudding user response：

Try this

function alert2() {
  // with integration of the extra column
  var sh1 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Data");
  var sh2 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Output2");
  var output = []
  if (sh1.getLastRow() == 1) { return }
  if (sh2.getLastRow() > 2) { 
    output = sh2.getRange(2, 1, sh2.getLastRow() - 1, sh2.getLastColumn()).getValues().filter(e => (e[12] == "Completed"))
    sh2.getRange('A2:L' (sh2.getLastRow())).clearContent()
  }
  var data = sh1.getRange(2, 1, sh1.getLastRow() - 1, sh1.getLastColumn()).getValues().filter(e => (e[14] == "Expired" || e[14] == "Service Alert" || e[14] == "Service Pre Alert"))
  try {
    data.forEach(function(r){
      var alertData = []
      r.forEach((c, col) => {
        if (col<=9 || col==14) alertData.push(c);
      })
      alertData.push(new Date())
      var workSatus = r[14] == 'Expired' ? 'Pending' : 'Service due'
      alertData.push(workSatus)
      output.push(alertData)
    });
  } catch (e) { }
  sh2.getRange(2,1,output.length,output[0].length).setValues(output)
}