Google Sheets Function Runs Partially a Second Time

Question

I've been creating inventory spreadsheets to help my dad's employees with year end inventory. A user filling out inventory will enter data into a few cells in a sheet titled "Form", and will then select "SUBMIT" from a dropdown box located on the form. The script then uses the form to make the following changes to a sheet titled "MSPL":

Inserts a new row in a specific location, based on data entered in the form
Copies data from the form into the cells of the new row

This works 95% of the time. However, sometimes the script seems to run parts of itself a second time. I have watched the spreadsheet changes being made in real time and it seems to: add a new row, copy data into the first 3 cells, create a 2nd row above the 1st one created, copy the entire data into this row. With there being only 1 "insertRowAfter" line in the code, I can't figure out why the code would be inserting two.

function msplFormSubmit() {

  var form = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("MSPL Form");
  var mspl = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("MSPL");
  var formWIP = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("MSPL WIP Form");
  var msplWIP = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("MSPL WIP");
  var Pricing = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Pricing")

  if (form.getRange("E2").getValue() == "SUBMIT") {

    var sectionName = form.getRange("C5").getValue() + '"' + " " + form.getRange("C3").getValue() + " - SUBTOTAL";
    var rowSearch = mspl.getRange("B:B").getValues();
    var rowID = 0
    var unitCost = Pricing.getRange("B3:D").getValues();

    form.getRange("E2").clearContent();
    form.getRange("C10").clearContent();
    form.getRange("B10").setValue("Processing ...")

    for (i = 0; i < mspl.getLastRow(); i ++) {
      if (rowSearch[i] != sectionName) {
        rowID += 1
      } else {
        break;
      }
    }

    mspl.insertRowAfter(rowID)

    rowID += 1 //rowID now points to the newly added row, in order to populate it

    if (mspl.getRange(rowID - 1, 2, 1, 1).getBackground() === "#ffffff") {
      mspl.getRange(rowID, 2, 1, 10).setBackground("#dcdcdc")
    }
    if (mspl.getRange(rowID - 1, 2, 1, 1).getBackground() === "#dcdcdc") {
      mspl.getRange(rowID, 2, 1, 10).setBackground("#ffffff")
    }

    mspl.getRange(rowID, 2, 1, 1).setValue(form.getRange("C2").getValue()); //Tag #
    mspl.getRange(rowID, 3, 1, 1).setValue(form.getRange("C3").getValue()); //Description
    mspl.getRange(rowID, 4, 1, 1).setValue(form.getRange("C4").getValue()); //Quantity
    mspl.getRange(rowID, 5, 1, 1).setValue(form.getRange("C5").getValue()); //Thickness
    mspl.getRange(rowID, 5, 1, 1).setNote(mspl.getRange(rowID-1, 5, 1, 1).getNote()); //Thickness Note
    mspl.getRange(rowID, 6, 1, 1).setValue(form.getRange("C6").getValue()); //Width
    mspl.getRange(rowID, 7, 1, 1).setValue(form.getRange("C7").getValue()); //Length
    mspl.getRange(rowID, 8, 1, 2).setFormulasR1C1(mspl.getRange(rowID-1, 8, 1, 2).getFormulasR1C1()); //Area and Weight Formulas
    mspl.getRange(rowID, 11, 1, 1).setFormulasR1C1(mspl.getRange(rowID-1, 11, 1, 1).getFormulasR1C1()); //Cost Formula

    for (i = 0; i < unitCost.length; i ++) {
      if (unitCost[i][0] == form.getRange("C3").getValue() && unitCost[i][1] == form.getRange("C5").getValue()) {
        mspl.getRange(rowID, 10, 1, 1).setValue(unitCost[i][2]);
        break;
      }
    }

    mspl.getRange(rowID, 10 ,1, 1).setNumberFormat("$0.0000")

    form.getRange("C2:C7").clearContent();
    form.getRange("B10").clearContent();
    form.getRange("C10").setValue("READY");
    }
}

What do you mean by select "SUBMIT" from a dropdown box located on the form? Is this data validation? How do msplFormSubmit() is called? — Rubén
There is a data validation box on the form sheet, which contains only "SUBMIT" as an option. the msplFormSubmit() is linked to an onEdit trigger, so every time the spreadsheet is edited the script runs (and checks if cell E2 on form says submit). Not very elegant. — Nosliw
I don't see any logging or error handling code in your code. This is basically a debugging question at this point. You need to isolate where the problem is coming from by debugging your code. Read the troubleshooting guide. Link to Apps Script troubleshooting guide Information about try/catch Link to Try/Catch info — Alan Wells

Rubén Rubén · Accepted Answer · 2017-11-29T00:41:27

Instead of using data validation together with an edit installable trigger, use a custom menu or a clickable image. For details see Custom menus in Google Sheets.

Another alternative could be to change the value of the cell with the data validation immediately after (form.getRange("E2").getValue() == "SUBMIT") is evaluated / before any other change be made by the script.

Google Sheets Function Runs Partially a Second Time

2 Answers