lundi 10 mai 2021

Copy a range to a different sheet based on checkbox value or booelan value

I need some help with my code. On my condition, I have 12 sheets based on the month calendar and I try to copy my data on a certain month to another "big data" sheet whenever the next month appears. I use a checkbox to trigger the function (copy monthly data) but the problem is the checkbox didn't work well. Every time I put my data it will copy to big data instantly and it was really confusing. I put my checkbox on cell E4 and the data that I intend to copy is on the range C:R to the last row.

  function onEdit1(event){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = event.source.getActiveSheet();
  var r = event.source.getActiveRange();
  var sheet = ss.getSheetByName("Januari");
   if(s.getName() == "Januari" && r.getColumn() == 5 && r.getRow() == 4 && r.getValue() == true); {
   var range = sheet.getRange(13, 3, sheet.getLastRow()-1, 18);
   var targetSheet = ss.getSheetByName("REKAP JADWAL BULANAN PER 5 TAHUN");
   var target = targetSheet.getRange(targetSheet.getLastRow()+1,3);
    sheet.getRange(13, 3, sheet.getLastRow()-1, 18).copyTo(target);
   sheet.getRange(13, 3, sheet.getLastRow()-1, 18).clear({contentsOnly:true});  
  }
 }

sample picture




Aucun commentaire:

Enregistrer un commentaire