I'm currently facing an issue with an alert appearing multiple times whenever a function is executed within a Google Apps Script written for a spreadsheet. I believe I've identified the root cause of the problem, but I'm uncertain about the correct solution... The issue seems to stem from having the alert placed inside a for loop, resulting in multiple alerts based on the data structure's length. I'm struggling to figure out how to structure it so that the alert displays only once without removing it from the "else if" loop.
Just to provide some context on the code, it's essentially going through a spreadsheet, searching for values based on the opportunityID variable, and updating the row's values accordingly. The update script requires all fields to be filled in to successfully run.
Any assistance on this matter would be greatly appreciated!
Feel free to ask if you need any further details.
Here's a snippet of my code:
function updateOpportunity() {
// Get active spreadsheets and sheets
var updateSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Search & Create New Records');
var OppsAndContracts = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Opportunities & Contracts');
var opportunityUpdateCopy = updateSheet.getRange('A8:P8').getValues();
Logger.log(opportunityUpdateCopy);
var ui = SpreadsheetApp.getUi();
// Set variables to check for empty values
var OppID = updateSheet.getRange("H14");
var OpportunityName = updateSheet.getRange("H15");
var AssociatedAccountID = updateSheet.getRange("H16");
var AssociatedAccountName = updateSheet.getRange("H17");
var OpportunityOwner = updateSheet.getRange("H18");
var LeadSource = updateSheet.getRange("H19");
var Type = updateSheet.getRange("H20");
var CloseDate = updateSheet.getRange("H21");
var Amount = updateSheet.getRange("H22");
var ProposalOwner = updateSheet.getRange("H23");
var Stage = updateSheet.getRange("H24");
var AeroServicesProducts = updateSheet.getRange("H25");
var MechServicesProducts = updateSheet.getRange("H26");
var ProjectStatus = updateSheet.getRange("H27");
var ProposalNumber = updateSheet.getRange("H28");
var ContractNumber = updateSheet.getRange("H29");
// Search for Opportunities using OpportunityID
var last = OppsAndContracts.getLastRow();
var data = OppsAndContracts.getRange(1, 1, last, 16).getValues();
var opportunityID = updateSheet.getRange("A8").getValue();
Logger.log(opportunityID);
for (nn = 0; nn < data.length; ++nn) {
if (OppID.isBlank() || OpportunityName.isBlank() || AssociatedAccountID.isBlank() || AssociatedAccountName.isBlank()
|| OpportunityOwner.isBlank() || LeadSource.isBlank() || Type.isBlank() || CloseDate.isBlank() || Amount.isBlank() || ProposalOwner.isBlank()
|| Stage.isBlank() || AeroServicesProducts.isBlank() || MechServicesProducts.isBlank() || ProjectStatus.isBlank() || ProposalNumber.isBlank()
|| ContractNumber.isBlank()) {
ui.alert("You must fill in all fields to update an Opportunity");
}
else if (data[nn][0] == opportunityID) {
OppsAndContracts.getRange(nn + 1, 1, 1, 16).setValues(opportunityUpdateCopy);
}
}
}