Window Alert showing multiple times- Google Apps Script

Question

I'm having an issue with an alert showing multiple times when a function is run inside a spreadsheet where I've written some custom Google Apps Script. I think I've pinpointed the problem, but I'm unsure how to fix it... I think the problem is I have the alert inside the for loop with the number of times depending on the length of the data structure, but I'm unsure of how to structure it to show the alert only once without taking the alert out of the "else if" loop.

To explain the code, it's looping through a spreadsheet and finding values based off the opportunityID variable and changing the values of that row based on the row that's found. I'm requiring all the fields to be entered to run the update script.

Any help is much appreciated!

Please let me know if you have any clarifying questions.

Here is my code example:


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 whether or not they are empty
  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();// create an array of data from columns A through Q
  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);}
  }
}

Forcing users that have edit access to do something is very difficult because if they wanted to, they could just go into the script editor and change your code thus bypassing all of the restrictions. The best way to accomplish this in my opinion is through a webapp and then not giving any one except yourself edit access to your spreadsheet. They can view it directly but they only way to edit it is via the webapp. — Cooper
This is a good idea, Cooper, and I've given it some thought. However, it's outside the current scope of this project for a client of mine. I appreciate the suggestion though! Love webApps. — Nathan Kittrell

Barmar Barmar · Accepted Answer · 2019-12-16T22:06:01

You have the if statement that checks for blank cells inside the for loop, so it's being executed data.length times. Since none of the results of those checks change, you'll get repeated alerts.

Instead, it should be around the for loop, not inside it.

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 {
  for (nn = 0; nn < data.length; ++nn) {
    if (data[nn][0] == opportunityID) {
      OppsAndContracts.getRange(nn + 1, 1, 1, 16).setValues(opportunityUpdateCopy);
    }
  }
}

Window Alert showing multiple times- Google Apps Script

2 Answers