I am trying to change this code to say that when the checkbox is ticked provide a datestamp but when the checkbox is unticked - clear the datestamp

I have managed to get the datestamp to work on all 3 columns but do not understand where to put the Else statement required.

function onEdit(e) {
  const range = e.range;
  const currentSheet = range.getSheet();

  locations.forEach(location => {
    const SHEET_TAB_NAME = location.SHEET_TAB_NAME
    const COLUMN_TO_CHECK = location.COLUMN_TO_CHECK
    const DATE_TIME_COL_OFFSET = location.DATE_TIME_COL_OFFSET
    const IGNORE_VALS = location.IGNORE_VALS
    const CLEAR_ON_IGNORE_VALS = location.CLEAR_ON_IGNORE_VALS


    // If the sheet tab name doesn't match, stop the process.
    if (currentSheet.getName() !== SHEET_TAB_NAME) return;

    const column = range.getColumn();

    // If the column is not a match, stopp the process.
    if (column != COLUMN_TO_CHECK) return;


    const columnVals = range.getValues();
    const dateTimeStamp = new Date();

    const dateRange = range
      .offset(0, DATE_TIME_COL_OFFSET)

    const dateVals = dateRange
      .getValues()
      .map((val, idx) => {

        const ignoreVal = CLEAR_ON_IGNORE_VALS ? [""] : val;

        return IGNORE_VALS.includes(columnVals[idx][0]) ? ignoreVal : [dateTimeStamp]

      })


    dateRange.setValues(dateVals)
  });

};

I am trying to change this code to say that when the checkbox is ticked provide a datestamp but when the checkbox is unticked - clear the datestamp

I have managed to get the datestamp to work on all 3 columns but do not understand where to put the Else statement required.

function onEdit(e) {
  const range = e.range;
  const currentSheet = range.getSheet();

  locations.forEach(location => {
    const SHEET_TAB_NAME = location.SHEET_TAB_NAME
    const COLUMN_TO_CHECK = location.COLUMN_TO_CHECK
    const DATE_TIME_COL_OFFSET = location.DATE_TIME_COL_OFFSET
    const IGNORE_VALS = location.IGNORE_VALS
    const CLEAR_ON_IGNORE_VALS = location.CLEAR_ON_IGNORE_VALS


    // If the sheet tab name doesn't match, stop the process.
    if (currentSheet.getName() !== SHEET_TAB_NAME) return;

    const column = range.getColumn();

    // If the column is not a match, stopp the process.
    if (column != COLUMN_TO_CHECK) return;


    const columnVals = range.getValues();
    const dateTimeStamp = new Date();

    const dateRange = range
      .offset(0, DATE_TIME_COL_OFFSET)

    const dateVals = dateRange
      .getValues()
      .map((val, idx) => {

        const ignoreVal = CLEAR_ON_IGNORE_VALS ? [""] : val;

        return IGNORE_VALS.includes(columnVals[idx][0]) ? ignoreVal : [dateTimeStamp]

      })


    dateRange.setValues(dateVals)
  });

};

• javascript
• google-sheets
• google-apps-script

• 5 Can you provide more details of your code? locations is not declared in the code block given. Also please provide a minimal reproducible example. – Lime Husky Commented Mar 25 at 14:53

2 Answers 2

Not sure where your "locations" variable comes from. Assuming it's either removed from the presented code or bad chatgpt promt.

To answer your question, when you check value of a checkbox, if it is ticket it will return TRUE, and if it is unticked it will return FALSE. So you can add a checker to your columnVals like this:

    const columnVals = range.getValues();
if (columnVals === true) {
  //code to add datestamp
} else {
  //code to remove datestamp
}

I believe your goal is as follows.

• You want to clear the value of IGNORE_VALS.includes(columnVals[idx][0]) ? ignoreVal : [dateTimeStamp] when the checkbox is unchecked. Also, when the checkbox is checked, you want to put that value.

In this case, how about using the method isChecked() of the Class Range? When this is used in your script, please modify it as follows.

From:

const dateVals = dateRange
  .getValues()
  .map((val, idx) => {

    const ignoreVal = CLEAR_ON_IGNORE_VALS ? [""] : val;

    return IGNORE_VALS.includes(columnVals[idx][0]) ? ignoreVal : [dateTimeStamp]

  })

To:

const checked = range.isChecked(); // Added
const dateVals = dateRange
  .getValues()
  .map((val, idx) => {
    const ignoreVal = CLEAR_ON_IGNORE_VALS ? [""] : val;
    return checked ? (IGNORE_VALS.includes(columnVals[idx][0]) ? ignoreVal : [dateTimeStamp]) : [null]; // Modified
  });

When this modified script is used, the above goal can be achieved.

References:

• isChecked()
• Conditional (ternary) operator