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;
}
}