使用if()从另一个google工作表中一次读取和检查多个值

Question

我正在尝试设置一个脚本，该脚本从另一个工作表中获取值，并将它们(使用if())与活动工作表中通过ui.prompts输入的值进行比较。

用户需要在ui.prompt中输入需要与另一个工作表中保存的值匹配的值，如果它们匹配，则运行另一个函数。

我已经写了下面的代码，不幸的是它不能工作-我相信我写if()和for()函数的方式可能有一些问题，但我不能指出它-有人能帮助我吗？

代码如下：

function firstOpen() {
  var sheet = SpreadsheetApp.openById("1HsE2Smq6iSkNxvonEOTOmz54MfzWJduWoNA5P6xaKxk").getSheetByName('Users');
  var range = sheet.getDataRange();
  var values = range.getDisplayValues();
  var lastRow = range.getLastRow();
  var sh = SpreadsheetApp.getActive().getSheetByName("BALANCE SHEET");
  var getAccNo = ui.prompt('Login', 'Please enter your account number:', ui.ButtonSet.OK);
  var getSgEm = ui.prompt('Login', 'And your subscriber email address:', ui.ButtonSet.OK);
  var sgCode = ui.prompt('Login', 'Finally, your subscription code:', ui.ButtonSet.OK);
  sh.getRange('AI30').setValue(getAccNo);
  sh.getRange('AI31').setValue(getSgEm);
  sh.getRange('AI32').setValue(sgCode);
  var accNo = sh.getRange('AI30').getValue(); // user entered acc no
  var sgEmail = sh.getRange('AI31').getValue(); // user entered subscriber email
  var subCode = sh.getRange('AI32').getValue(); // user entered subscriber code (as on email)

  for (var i = 1; i < lastRow; i++)
  {
    if (subCode == values[i][3] && accNo == values[i][4] && sgEmail == values[i][5])
    {
      ui.alert('Success', 'You have entered details correctly', ui.ButtonSet.OK);
      sortOutSheets();
    }
    else
    {
      ui.alert('Failed', 'The details were incorrect.', ui.ButtonSet.OK);
    }
  }
}

Answer 1

ui.prompt('Login', 'Please enter your account number:', ui.ButtonSet.OK);的返回值不仅仅是一个值。它是一个名为Class PromptResponse的对象

方法

getResponseText()

getSelectedButton()

您的代码中未定义ui

function firstOpen() {
  var sheet = SpreadsheetApp.openById("1HsE2Smq6iSkNxvonEOTOmz54MfzWJduWoNA5P6xaKxk").getSheetByName('Users');
  var range = sheet.getDataRange();
  var values = range.getDisplayValues();
  var lastRow = range.getLastRow();
  var sh = SpreadsheetApp.getActive().getSheetByName("BALANCE SHEET");
  var ui=SpreadsheetApp.getUi();//this defines ui
  var getAccNo = ui.prompt('Login', 'Please enter your account number:', ui.ButtonSet.OK).getResponseText();
  var getSgEm = ui.prompt('Login', 'And your subscriber email address:', ui.ButtonSet.OK).getResponseText()
  var sgCode = ui.prompt('Login', 'Finally, your subscription code:', ui.ButtonSet.OK).getResponseText();
  sh.getRange('AI30').setValue(getAccNo);
  sh.getRange('AI31').setValue(getSgEm);
  sh.getRange('AI32').setValue(sgCode);
  var accNo = sh.getRange('AI30').getValue(); // user entered acc no
  var sgEmail = sh.getRange('AI31').getValue(); // user entered subscriber email
  var subCode = sh.getRange('AI32').getValue(); // user entered subscriber code (as on email)
  for (var i = 1; i < lastRow; i++) {
    if (subCode == values[i][3] && accNo == values[i][4] && sgEmail == values[i][5]) {
      ui.alert('Success', 'You have entered details correctly', ui.ButtonSet.OK);
      sortOutSheets();
    } else {
      ui.alert('Failed', 'The details were incorrect.', ui.ButtonSet.OK);
    }
  }
}