Home > Software design >  Google Sheets Script - Checkbox
Google Sheets Script - Checkbox

Time:09-27

I found this google app script to add a checkbox based on a watch column in google sheets.

However I need to add a checkbox in col1, col2 and col4?

Could someone please amend code below to do this, many thanks in advance.

function onEdit(e) {
  const watchSheets = /^(Sheet1|Sheet2|Sheet3)$/i;
  const watchColumn = 5;
  const checkboxColumn = 1;
  if (e.range.columnStart !== watchColumn
    || !e.source.getActiveSheet().getName().match(watchSheets)) {
    return;
  }
  const checkboxCell = e.source.getActiveSheet().getRange(e.range.rowStart, checkboxColumn);
  if (e.value) {
    checkboxCell.insertCheckboxes();
  } else {
    checkboxCell.clearDataValidations();
    checkboxCell.clearContent();
  }
}

CodePudding user response:

function onEdit(e) {
  const watchSheets = /^(Sheet1|Sheet2|Sheet3)$/i;
  const watchColumns = 5;

  // changes this to array so you can add multiple columns
  const checkboxColumns = [1, 2, 4]; 

  // fixed the first condition here
  if (checkboxColumns.indexOf(e.range.columnStart) == -1
    || !e.source.getActiveSheet().getName().match(watchSheets)) {
    return;
  }
  
  // run through each watchColumns
  checkboxColumns.forEach( cellCol => {
    const checkboxCell = e.source.getActiveSheet().getRange(e.range.rowStart, cellCol);
    if (checkboxCell.getValue()) {
      checkboxCell.insertCheckboxes();
    } else {
      checkboxCell.clearDataValidations();
      checkboxCell.clearContent();
    }
  })
}

CodePudding user response:

Solution:

  • Change the checkboxColumn to an array with all the column indexes (checkboxColumns).
  • Iterate through checkboxColumns (for example, using forEach) and, for each column, retrieve the corresponding cell and insert or clear the checkboxes (as in your current code).

Code snippet:


function onEdit(e) {
  const watchSheets = /^(Sheet1|Sheet2|Sheet3)$/i;
  const watchColumn = 5;
  const checkboxColumns = [1, 2, 4];
  if (e.range.columnStart === watchColumn && e.source.getActiveSheet().getName().match(watchSheets)) {
    checkboxColumns.forEach(checkboxColumn => {
      const checkboxCell = e.source.getActiveSheet().getRange(e.range.rowStart, checkboxColumn);
      if (e.value) {
        checkboxCell.insertCheckboxes();
      } else {
        checkboxCell.clearDataValidations();
        checkboxCell.clearContent();
      }
    });
  }
}
  • Related