Protection against repeated entries not working as intended

Viewed 18

I am writing a script that allows a user to build a spread sheet database of devices and their specs.

I want to be able to prevent a user from entering the same named specification more than once. For instance if Device A has a spec for "input voltage" that gets added to the database. Then if the user tried to add Device B to the database, I don't want that device's "input voltage" to appear on yet another row in the database. I want the value entered for Device B "input voltage" to just be put on the same row as the previous entry for Device A, but under the column for Device B.

Full code below. The area of the script in question starts under the comment

//check if newSpecName matches an existing spec name. indexOf returns -1 if the desired search is not present in the specNameArray
var ssC = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("CreateSheet");
function CreateFromTemplate() {
  var ui = SpreadsheetApp.getUi();
  //add a new device to the register

  //find the next empty column for entry
  var emptyColumn = ssC.getLastColumn() + 1;

  //get model number info
  var newMPN = ui.prompt("Please enter new device according to the following structure: Manufacturer MPN");
  ssC.getRange(1,emptyColumn).setValue(newMPN.getResponseText()).setFontWeight("Bold");

  //get specs to list for new device

  //find the next empty row for entry
  var emptyRow = ssC.getLastRow() + 1;

  //begin gathering new specs
  var numSpecs = ui.prompt("Enter the number of specifictions you would like to consider with the new device:");
  numSpecs = numSpecs.getResponseText();
    //protection against non-numerical, negative number responses, and decimal responses
  if (isNaN(numSpecs) || numSpecs < 0 || (numSpecs.includes("."))) {
     numpSpecs = ui.prompt("Enter the number of specifictions you would like to consider with the new device: Please enter a positive integer!");
    }

  //create an array of existing spec names
  var specNameArray = ssC.getRange(2,1,emptyRow,1).getValues();

  //begin adding specifications for the new entry based on the number of new specs desired
  for (var i = 1; i <= numSpecs; i++)
  {
    //gather new spec names
    var newSpecName = ui.prompt("Enter the name of the next specification:");
    newSpecName = newSpecName.getResponseText();

    //check if newSpecName matches an existing spec name. indexOf returns -1 if the desired search is not present in the specNameArray
      //update value of emptyRow and specNameArray to account for previous new spec entries
    emptyRow = ssC.getLastRow() + 1;
    specNameArray = ssC.getRange(2,1,emptyRow,1).getValues();

    if (specNameArray.indexOf(newSpecName) == -1)
    {
      ssC.getRange(emptyRow,1).setValue(newSpecName).setFontWeight("Bold");
    }

    //gather new spec values
    var newSpecValue = ui.prompt("Enter the value of " + newSpecName + ":");
    newSpecValue = newSpecValue.getResponseText();
    ssC.getRange(emptyRow,emptyColumn).setValue(newSpecValue);

    emptyRow = ssC.getLastRow() + 1;
  }
}

Pictured here is the script output to the spreadsheet

0 Answers
Related