Copy row contents to another sheet and clear data except formulas-CodePudding

I am needing to copy all data in a row (including formulas and formatting) to another tab (when a checkbox is ticked), and then I want to clear the contents of that row on the source sheet, but keep all formulas in place. I'm working on Google Sheets App Script.

I have been playing around with using range.getFormulas() and range.setFormulas() but I haven't been able to make it work.

This is the current script I have which clears all the content. So I'm wanting to build on this one, hopefully.

And here's an editable link to a copy of the sheet: https://docs.google.com/spreadsheets/d/1vUjLrkwuhat28lInKDvCwJgY5BH6U85JDl_U61eeC2A/edit?usp=sharing

function onEdit(event) {
// assumes source data in sheet named Day to Day
// target sheet of move to named Total TO/YO
// getColumn with check-boxes is currently set to colu 36 or AJ
var ss = SpreadsheetApp.getActiveSpreadsheet();
var s = event.source.getActiveSheet();
var r = event.source.getActiveRange();

if(s.getName() == "Day to Day" && r.getColumn() == 36 && r.getValue() == true) {
var row = r.getRow();
var numColumns = s.getLastColumn();
var targetSheet = ss.getSheetByName("Total TO/YO");
var target = targetSheet.getRange(targetSheet.getLastRow()   1, 1);
s.getRange(row, 1, 1, numColumns).copyTo(target);
s.getRange(row, 1, 1, numColumns).clearContent();
}
}

Thanks in advance for any help sent my way!

CodePudding user response：

This cannot be run without event object provided by trigger

function onEdit(e) {
  //e.source.toast('Entry')
  var sh = e.range.getSheet();
  if (sh.getName() == "Day to Day" && e.range.columnStart == 36 && e.value == "TRUE") {
    var numColumns = sh.getLastColumn();
    var tsh = e.source.getSheetByName("Total TO/YO");
    var trg = tsh.getRange(tsh.getLastRow()   1, 1);
    sh.getRange(e.range.rowStart, 1, 1, numColumns).copyTo(trg);
    sh.getRange(e.range.rowStart, 1, 1, numColumns).getFormulas().flat().forEach((f, i) => {
      if (!f) {
        sh.getRange(e.range.rowStart, i   1).setValue('')
      }
    })
  }
}

If you get error TypeError: Cannot read property 'range' of undefined it's probably because you ran it from the script editor without supplying the event object.