I'm working on a project where I'm trying to have values on a specific row to automatically input into different cells when a checkbox of that row is checked.

I have tried many different methods along with asking many questions online but still is not able to achieve my goal.

Please look at the picture below.

Image of Project Page

So what this does is the following:

When a name is typed into cell C7, all info under that specific name will appear in B20:F. When the info is filtered, checkboxes will appear in A20:A only to a row where a data exists in B20:F.

The code for checkboxes to appear has been given by someone awesome in stackoverflow and it looks like the following: (thank you!!)

function onEdit(e) {
    const sheet = SpreadsheetApp.getActiveSheet();
    const sName = sheet.getName();
    if (sName === "sheet1") {
        const len = sheet.getRange('B20:B').getValues().filter(row => row[0] != '').length;

        // Condition-2: edit range === C7;
        if (e.range.getColumn() === 3 && e.range.getRow() === 7) {
            const range = sheet.getRange(20,1,len,1);
            sheet.getRange('A20:A').removeCheckboxes();
            range.insertCheckboxes().uncheck();
        }

        // Condition-3: column of edit range === A, row of edit range > 19, value === true;
        if (e.range.getRow() > 19 && e.range.getColumn() === 1 && e.range.getValues()[0][0] === true) {
            sheet.getRange('A20:A').uncheck();
            e.range.check();
        }
    }
}

So with this done, now I'm trying to achieve two things.

Number 1: Delete checkboxes in A20:A when the name typed in C7 is deleted.

Number 2: When a checkbox in A20:A is checked, have the data in that row to auto input into C9, C11 and C13

I am only familiar with python and it seems like tying to code this language like python gives me unsuccessful outcome everytime. Anyone willing to help me out?

If actually trying the spreadsheet out helps, try accessing https://docs.google.com/spreadsheets/d/14ZzzPUc4TRNVFB0fjfPfIJWWwvLGIuagT2u8Gwjmt9E/edit#gid=0

I desperately need a code master's help.

CodePudding user response：

Your onEdit function is a bit of a mess. You don't execute and define multiple functions within a function at the same time. In your case you can define the steps inside of onEdit function direct (like I did), or you move those two functions outside, define them and trigger within onEdit. Anyway, I think this should solve your problem:

function onEdit(e) {
  const sheet = SpreadsheetApp.getActiveSheet();
  const sName = sheet.getName();
  if (sName === "Teacher's Input Form") {
    const len = sheet.getRange('B20:B').getValues().filter(row => row[0] != '').length;

    // Condition-2: edit range === C7;
    if (e.value != null) {
      if (e.range.getColumn() === 3 && e.range.getRow() === 7) {
        const range = sheet.getRange(20, 1, len, 1);
        sheet.getRange('A20:A').removeCheckboxes();
        range.insertCheckboxes().uncheck();
      }
      // Condition-3: column of edit range === A, row of edit range > 19, value === true;
      if (e.range.getRow() > 19 && e.range.getColumn() === 1 && e.range.getValues()[0][0] === true) {
        sheet.getRange('A20:A').uncheck();
        e.range.check();
      }
    } else {
      sheet.getRange('A20:A').removeCheckboxes();
    }
  }

  const sh = e.range.getSheet();
  if (sh.getName() == "Teacher's Input form" && e.range.columnStart == 1 && e.range.rowStart > 19 && e.value == "TRUE") {
    ["C9", "C11", "C13"].forEach((r, i) => sh.getRange(r).setValue(e.range.offset(0, i   1).getValue()));
  }
}