javascript - 多次显示窗口警报 - Google Apps 脚本

Question

当函数在我编写了一些自定义 Google Apps 脚本的电子表格中运行时，我遇到了多次显示警告的问题。我想我已经查明了问题所在，但我不确定如何解决它......我认为问题是我在 for 循环内有警报，其次数取决于数据结构的长度，但我我不确定如何构建它以仅显示一次警报而不将警报从“else if”循环中取出。

为了解释代码，它循环遍历电子表格并根据 opportunityID 变量查找值，并根据找到的行更改该行的值。我需要输入所有字段才能运行更新脚本。

非常感谢任何帮助!

如果您有任何需要澄清的问题，请告诉我。

这是我的代码示例:

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

Answer 1

您有 if 语句检查 for 循环内的空白单元格，因此它被执行了 data.length 次。由于这些检查的结果都没有改变，您会收到重复的警报。

相反，它应该围绕 for 循环，而不是在其中。

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