Question 250091 in Javascript Question posted in
A very good W3school tutorial can be found here.

Javascript – 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])
  }
}
Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    I found an answer.

    I created another variable and assigned it to checkbox. i used that in my conditional to check the status then changed the value from false to true in the loop. this resolved my issue.


  2. 0

    Try starting with somethings like this for your log:

    function onEdit(e) {
  const sh = e.range.getSheet();
  if (sh.getName() == 'check-out' && e.range.columnStart == 5) {
    let [equip, person, checkout, condition, checkbox] = sh.getRange(e.range.rowStart,1,1,5).getValues();
    let logSheet = e.source.getSheetByName("equip-log");
    logSheet.appendRow([new Date(),equip, person, checkout, condition, checkbox])
  }
}
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search