jeudi 21 septembre 2023

Equipment Log Google Sheets - fixing while/for loop in Google Apps Script for check in/check out based on checkbox

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