jeudi 18 mars 2021

How can I use Google Sheets Apps Script to clear cells if I uncheck a tick box?

Not a programmer so I'm begging for a little help. I have a very simple spreadsheet with certain columns that have tick boxes and adjacent empty columns. My code at this point allows that if the box is checked, the current date and time will be populated in the next cell in the adjacent column. (The code works on all the sheets in the workbook.) I need to know what to add or tweak so that if the box is UNchecked, the neighboring cell will switch back to empty. Thanks in advance!

function onEdit() {
var s = SpreadsheetApp.getActiveSheet();
var r = s.getActiveCell();
if( r.getColumn() == 2 ) { 
var nextCell = r.offset(0, 1);
if( nextCell.getValue() === '' ) 
nextCell.setValue(new Date()).setNumberFormat("MM-dd-yyyy hh:mm");
}

if( r.getColumn() == 4 ) { 
var nextCell = r.offset(0, 1);
if( nextCell.getValue() === '' ) 
nextCell.setValue(new Date()).setNumberFormat("MM-dd-yyyy hh:mm");
}

if( r.getColumn() == 8 ) { 
var nextCell = r.offset(0, 1);
if( nextCell.getValue() === '' ) 
nextCell.setValue(new Date()).setNumberFormat("MM-dd-yyyy hh:mm");
}

if( r.getColumn() == 10 ) { 
var nextCell = r.offset(0, 1);
if( nextCell.getValue() === '' ) 
nextCell.setValue(new Date()).setNumberFormat("MM-dd-yyyy hh:mm");
}

if( r.getColumn() == 14 ) { 
var nextCell = r.offset(0, 1);
if( nextCell.getValue() === '' ) 
nextCell.setValue(new Date()).setNumberFormat("MM-dd-yyyy hh:mm");
}

if( r.getColumn() == 16 ) { 
var nextCell = r.offset(0, 1);
if( nextCell.getValue() === '' ) 
nextCell.setValue(new Date()).setNumberFormat("MM-dd-yyyy hh:mm");
}
}



Aucun commentaire:

Enregistrer un commentaire