On Form Submission compare values in Inventory and if it matches, tick a box

Question

I am trying to create a script that compares the Serial number of the submitted form to my inventory sheet, and if it matches tick a box. It currently works when I edit it in the sheet but not when I submit the form, any help would be appreciated :)

[Form Script]:

function onFormSubmit(e) {
      var formResponsesSheetName = "Form Responses";
      var targetSheetName = "Inventory"; 
    
      const formResponsesSheet = 
      SpreadsheetApp.openByUrl('URL').getSheetByName('Form responses')
      const targetSheet = 
      SpreadsheetApp.openByUrl('URL').getSheetByName('Inventory');  
      
      if (formResponsesSheet && targetSheet) {
       var valueToCompare = [6];
       
       let targetColumn = targetSheet.getRange("C:C").getValues();
       let checkboxColumn = targetSheet.getRange("D:D");
       let checkboxValues = checkboxColumn.getValues();
    
       for (var i = 0; i < targetColumn.length; i++) {
         if (targetColumn[i][0] === valueToCompare) {
           checkboxValues[i][0] = true;
         } else {
            checkboxValues[i][0] = false;
         }
    }
}

Form Trigger

[Sheets Script]:

function onEdit(e) {
  var sheet = e.source.getSheetByName("Form responses");
  var range = e.range;
  
  if (sheet.getName() === "Form responses" && range.getColumn() === 7) {
    var valueToCompare = range.getValue();
    var targetSheet = e.source.getSheetByName("Inventory");
    var targetColumn = targetSheet.getRange("C:C").getValues();
    var checkboxColumn = targetSheet.getRange("D:D");
    var checkboxValues = checkboxColumn.getValues();
    
    for (var i = 0; i < targetColumn.length; i++) {
      if (targetColumn[i][0] === valueToCompare) {
        checkboxValues[i][0] = true;
      } else {
        checkboxValues[i][0] = false;
      }
    }
    
    checkboxColumn.setValues(checkboxValues);
  }
}

Sheet Triggers

And if you require any more information, please let me know All the best

Answer 1

function onMyFormSubmit(x) {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("Sheet0");
  const ns = sh.getRange(2, 1, sh.getLastRow() - 1).getValues().flat();
  const idx = ns.indexOf(Number(x.values[1]));
  if(~idx) {
    sh.getRange(idx + 2,2).setValue("TRUE");
  }
}

Sheet0: After submitting a 1 and a 5 in first question which was just a short answer question.

• Event Object