Google Sheets Log row to another sheet (same book) when checkbox checked

0
votes

Let's start with all this will be in the same book. I have Sheet1, and Sheet2.

Sheet1 contains several rows. The rows have checkboxes in column D, E, F.

I'm trying to write a script that when checkbox F is checked, it will copy the entire row to Sheet2 as a running log. I've worked with appendRow in some scripts I'm "toying with", but keep running into trouble.

I've also seen simple formulas such as: (This will not work with what I need to do) [Copy over the row in another sheet when checkbox checked in Google Sheets

That DO NOT log. This would be great if the Rows in Sheet1 would never be deleted. However the rows may be deleted from time to time. So I really need it to copy/log to Sheet2 when F on Sheet1 is checked.

I have Google searched for days now and have not found any Google Sheets logs triggered by a specific cell (or cells within a column).

P.S. I was "stupid hacking" with some code trying to get an onEdit function to check F. I'm very interested in learning :)

1
javascriptgoogle-sheetsgoogle-sheets-formula

1 Answers

1
votes

onEdit is the way to go

I recommend you to get familiar with Google Apps Script that allows you to detect onEdit either a checkbox has been checked (and if yes - which one). An if statement would help you to copy data in case the checked box is in the column of interest.

You adapt the following sample script to your needs after investing some time studying the Apps Script documentation:

function onEdit(){
  var ss=SpreadsheetApp.getActive();
  var sheet1=ss.getSheetByName("Sheet1");
  var sheet2=ss.getSheetByName("Sheet2");
  var cell=SpreadsheetApp.getActiveRange();
  if(SpreadsheetApp.getActiveSheet().getName()=="Sheet1" && cell.getColumn()==6 && cell.getValue() == true){
    Logger.log('bla');
    var row=cell.getRow();
    var range=sheet1.getRange(row,1,1,sheet1.getLastColumn());
    range.copyTo(sheet2.getRange((sheet2.getLastRow()+1),1));
  }
}