Google Apps Script函数无法识别纯数字发票号输入求助

问题根源 #

1. 纯数字输入不兼容：用户输入的发票号是字符串类型（如 "1"），但从数据库工作表读取的发票号是数字类型（如 1），Array.includes() 使用严格相等判断（===），导致类型不匹配无法匹配成功。
2. 429请求过多：批量生成PDF时请求频率过高，触发Google服务的限流机制，现有退避策略仅处理重试，未从根源降低请求频率。

解决方案及代码修改 #

1. 修复纯数字输入匹配问题 #

选择以下两种方式之一统一类型即可：

方式一：将用户输入转为数字类型

修改 promptUserToSelectInvoices 函数中处理输入的部分，把字符串转成数字：

function promptUserToSelectInvoices(data) {
  var selectedInvoices = [];
  var ui = SpreadsheetApp.getUi();
  var response = ui.prompt('Select Invoices to Print', 'Enter the invoice numbers separated by commas:', ui.ButtonSet.OK_CANCEL);
  
  if (response.getSelectedButton() === ui.Button.OK) {
    var input = response.getResponseText().trim();
    selectedInvoices = input.split(',').map(function(value) {
      return Number(value.trim()); // 把输入的字符串转为数字
    });

    PrintSelectedToPDF(selectedInvoices);
  }
}

方式二：将数据库读取的发票号转为字符串类型

修改 PrintSelectedToPDF 函数中读取数据库值的部分，转成字符串：

function PrintSelectedToPDF(invoicesToPrint) {
  var spreadsheet = SpreadsheetApp.getActive();
  var databaseSheet = spreadsheet.getSheetByName('Database');
  var invoiceSheet = spreadsheet.getSheetByName('Invoice');
  
  var dataRange = databaseSheet.getRange(2, 1, databaseSheet.getLastRow() -1, 1);
  var data = dataRange.getValues();
  
  for (var i = 0; i < data.length; i++) {
    var valueToPrint = String(data[i][0]); // 把数据库的数字转为字符串
    
    // 现在字符串类型的输入和数据库值可以正常匹配
    if (invoicesToPrint.length === 0 || invoicesToPrint.includes(valueToPrint)) {
      // 后续代码不变...
    }
  }
}

2. 优化429请求过多问题 #

在每次成功生成PDF后增加固定等待时间，降低请求频率，减少触发限流的概率：
在 PrintSelectedToPDF 函数的 Logger.log 之后添加一行：

Logger.log('PDF file URL: ' + fileUrl);
Utilities.sleep(1000); // 每次生成后等待1秒，降低连续请求频率

同时可以调整初始退避时间，比如从500ms改为1000ms，进一步降低重试时的请求频率：

var backoffTime = 1000; // 初始等待时间从500ms改为1000ms

修改后完整代码 #

function promptUserToSelectInvoices(data) {
  // Prompts the user to enter invoice numbers separated by commas
  var selectedInvoices = [];
  var ui = SpreadsheetApp.getUi();
  var response = ui.prompt('Select Invoices to Print', 'Enter the invoice numbers separated by commas:', ui.ButtonSet.OK_CANCEL);
  
  if (response.getSelectedButton() === ui.Button.OK) {
    var input = response.getResponseText().trim();
    selectedInvoices = input.split(',').map(function(value) {
      return Number(value.trim()); // 转换输入为数字类型
    });

    // Calls the PrintSelectedToPDF() function with the selectedInvoices array
    PrintSelectedToPDF(selectedInvoices);
  }
}

function PrintSelectedToPDF(invoicesToPrint) {
  var spreadsheet = SpreadsheetApp.getActive();
  var databaseSheet = spreadsheet.getSheetByName('Database');
  var invoiceSheet = spreadsheet.getSheetByName('Invoice');
  
  var dataRange = databaseSheet.getRange(2, 1, databaseSheet.getLastRow() -1, 1);
  var data = dataRange.getValues();
  
  for (var i = 0; i < data.length; i++) {
    var valueToPrint = data[i][0];
    
    // Check if invoicesToPrint is empty or includes the current invoice number
    if (invoicesToPrint.length === 0 || invoicesToPrint.includes(valueToPrint)) {
      invoiceSheet.getRange('F12').setValue(valueToPrint);
      
      var url = 'https://docs.google.com/spreadsheets/d/' + spreadsheet.getId() + '/export?';
      var params = {
        "exportFormat": "pdf",
        "format": "pdf",
        "size": "letter",
        "portrait": "true",
        "fitw": "true",
        "sheetnames": "false",
        "printtitle": "false",
        "top_margin": 0,
        "bottom_margin": 0,
        "left_margin": 0,
        "right_margin": 0,
        "gid": invoiceSheet.getSheetId()
      };
      
      var range = invoiceSheet.getDataRange();
      var rangeParam = '&range=' + range.getA1Notation();
      url += getUrlParams(params) + rangeParam;
      
      var backoffTime = 1000; // 调整初始退避时间为1秒
      var maxBackoffTime = 5000;
      var attempts = 0;
      var response;
      
      while (true) {
        try {
          Utilities.sleep(backoffTime);
          response = UrlFetchApp.fetch(url, {
            "headers": {
              "Authorization": 'Bearer ' + ScriptApp.getOAuthToken()
            },
            "muteHttpExceptions": true
          });
          
          break; // Break out of the while loop if the request is successful
        } catch (e) {
          if (e.message.indexOf('Exception: Request failed for') === -1 || e.message.indexOf('returned code 429') === -1) {
            throw e; // Throw an exception if it's not a 429 error
          }
          
          attempts++;
          if (attempts >= 5) {
            throw e; // If the maximum number of attempts is reached, throw an exception
          }
          
          backoffTime *= 2; // Exponential backoff
          backoffTime = Math.min(backoffTime, maxBackoffTime); // Limit backoff time to maximum value
        }
      }
      
      var fileName = 'Invoice_' + valueToPrint + '.pdf';
      var downloadsFolder = DriveApp.getRootFolder().getFoldersByName('Reportes_2B').next();
      var file = downloadsFolder.createFile(response.getBlob().setName(fileName));
      var fileUrl = file.getUrl();
      Logger.log('PDF file URL: ' + fileUrl);
      Utilities.sleep(1000); // 每次生成后等待1秒，降低请求频率
    }
  }
}

内容的提问来源于stack exchange，提问作者Luis Fernando Álvarez Hernández