CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Johnn
Johnn

Reputation: 13

Copy the row data and Paste to another sheet when criteria match in google sheet with App Script

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

Upvotes: 0

Views: 326

Answers (1)

Mike Steelson
Mike Steelson

Reputation: 15318

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)
}

Upvotes: 0

Related Questions