I am trying to create a script on google sheets where when I click a checkbox, another sheet is updated with a new row with a log of that click. My issue right now:
I'm trying to get it so that when I uncheck the checkbox, it logs when it was unchecked. My issue is, I can't find a workaround from a while loop or a conditional loop where after it's set to false it does the action once and does not continually do so. Here is my code:
function hours12(today=(new Date())) {
let hours = (today.getHours() + 24) % 12 || 12;
return hours;
}
function TIMESTAMP() {
let today = new Date();
let mins = ('0'+ today.getMinutes()).slice(-2);
let seconds = ('0'+ today.getSeconds()).slice(-2);
let hours = hours12(today)
let date = (today.getMonth()+1)+'-'+today.getDate()+'-'+ (today.getYear()-100);
let time = hours + ":" + mins + ":" + seconds;
let dateTime = date+' '+time;
return dateTime;
}
function onEdit() {
let ss = SpreadsheetApp.getActive();
let sheet = ss.getSheetByName('check-out');
let logSheet = ss.getSheetByName("equip-log");
let selectedRow = sheet.getActiveRange().getRow();
let checkbox = sheet.getRange(selectedRow, 5).getValue();
let person = sheet.getRange(selectedRow, 2).getValue();
let equip = sheet.getRange(selectedRow, 1).getValue();
let condition = sheet.getRange(selectedRow, 4).getValue();
let checkout = sheet.getRange(selectedRow, 3).getValue();
while (checkbox == true) {
if (person == '' || equip == '' || condition == '' || checkout == '') {
Logger.log('Incomplete row information while true');
break;
} else {
addValues(checkbox, logSheet, equip, person, condition, checkout);
break;
}
}
if (checkbox == false) {
let logRange = logSheet.getDataRange().getValues();
for (i=0; i<logRange.length;i++) {
let rangeValue = logRange[i];
console.log(selectedRow)
if (!rangeValue.includes(equip, person, condition)) {
Logger.log('Incomplete row information while false');
} else {
addValues(checkbox, logSheet, equip, person, condition, checkout);
sheet.getRange(selectedRow, 2).clearContent();
sheet.getRange(selectedRow, 4).clearContent();
}
}
}
}
function addValues(checkbox, logSheet, equip, person, condition, checkout) {
const current = TIMESTAMP()
if (checkbox == true) {
logSheet.appendRow([equip, person, checkout, '', condition])
} else {
logSheet.appendRow([equip, person, '', current, condition])
}
}
Aucun commentaire:
Enregistrer un commentaire