javascript - 根据其他工作表的值/验证检查 ID 复选框

Question

这是包含应用程序脚本的示例表： https ://docs.google.com/spreadsheets/d/1AOzYMVqOqww4bUfSigDWz6gL6tmESfQxuKw4auNS4kU/edit?usp=sharing

我有一个主列表，用于跟踪是否发送了交易信函。应该在交易日期之后发送 3 封信 - 初始交易日期后的第 1、第 3 和第 6 个月，因此每个 ID 有 3 个复选框。如果发送了一封信，现在我会根据信函月份手动选中一个复选框。我创建了第二张表，它将显示每个未检查的 ID 的所有最早日期，并且该日期早于单元格 B2 上的日期。我的目标是当我运行脚本时，它将检查客户 ID 和日期匹配的所有相应复选框。

这是我当前的应用程序脚本代码：

function myFunction() {
  var sh1 = Sheets.Spreadsheets.Values.get('1AOzYMVqOqww4bUfSigDWz6gL6tmESfQxuKw4auNS4kU','Payment Due by Date!A4:D');
  var sh2 = Sheets.Spreadsheets.Values.get('1AOzYMVqOqww4bUfSigDWz6gL6tmESfQxuKw4auNS4kU','Master List!A2:K');

  for(var i = 0; i < sh1.values.length; i++){
    var sh1Id = sh1.values[i][0];     //sheet1 ID
    var sh1DD = sh1.values[i][2];     //sheet1 date
    
    for(var j = 0; j < sh2.values.length; j++){
      var sh2Id = sh2.values[j][0];    //sheet2 ID
      var sh2m1d = sh2.values[j][3];   //sheet2 month1 date
      var sh2m1c = sh2.values[j][4];   //sheet2 month1 checkbox
      var sh2m3d = sh2.values[j][5];   //sheet2 month3 date
      var sh2m3c = sh2.values[j][6];   //sheet2 month3 checkbox
      var sh2m6d = sh2.values[j][7];   //sheet2 month6 date
      var sh2m6c = sh2.values[j][8];   //sheet2 month6 checkbox
      
      //Logger.log(sh2m1c);
      
      //compare the ID & month and check the corresponding checkbox
      if(sh1Id == sh2Id && sh1DD  == sh2m1d)
        sh2m1c = "TRUE";
      else if(sh1Id == sh2Id && sh1DD  == sh2m3d)
        sh2m3c = "TRUE";
      else if(sh1Id == sh2Id && sh1DD  == sh2m6d)
        sh2m6c = "TRUE";
  }
  }
}

当我检查记录器时，我认为循环是获取值（如果我错了，请纠正我）。我不知道我的循环是否运行错误，或者我检查复选框的方式是否错误。

编辑：我的工作表中的一个具体例子是我想要做什么：

如果在付款到期日此行存在

客户ID	顾客姓名	付款截止日期	到期付款
1042	汤姆，C	2020 年 6 月 5 日	第六个月到期

当脚本运行时，我希望选中主列表中 6th Mo Due 下的 ID 1042 复选框。

客户ID	顾客姓名	交易日期	第一个月到期	第一个莫派？	第三个月到期	三号莫派？	第六个月到期	六号莫派？	上次未检查日期（计算）	最后到期日期（计算）
1042	汤姆，C	2019 年 12 月 5 日	2020 年 1 月 5 日	真的	2020 年 3 月 5 日	真的	2020 年 6 月 5 日	错误的	2020 年 6 月 5 日	第六个月到期

那么在第六次莫派下？列，将选中现在为 FALSE 或未选中的复选框

标签： javascriptgoogle-apps-scriptgoogle-sheets

Answer 1

试试这个：

  function myFunction() {
  var sheet1 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Master List');
  var sheet2 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Payment Due By Date');
  var spreadsheetId = SpreadsheetApp.getActiveSpreadsheet().getId();
  //Use getDisplayValues so the complier won't automatically convert the date based on timezone.
  var sheet1_val = sheet1.getRange(2, 1, 12, 11).getDisplayValues();
  //get Ids and merge into 1 dimensional array
  var sheet1_ids = [].concat(...sheet1.getRange(2, 1, 12, 1).getDisplayValues());
  var sheet2_val = sheet2.getRange(4,1,sheet2.getLastRow()-3, 4).getDisplayValues();
  for(var i = 0; i < sheet2_val.length; i++){
    var customer_id = sheet2_val[i][0];
    var pay_due_date = sheet2_val[i][2];
    //check if customer id exists in the id list (sheet1_ids)
    if (sheet1_ids.indexOf(customer_id) !== -1) {
      //get the position/index of the id
      var index = sheet1_ids.indexOf(customer_id);
      //I used 3 and 8 since we only need the column D-H
      for(var j = 3; j < 8; j++){
        //Iterate and check each column if match to the pay_due_date
        if(pay_due_date == sheet1_val[index][j]){
          //assign new value
          sheet1_val[index][j+1] = "TRUE";
        }
      }
    }
  }
  
  var lastRow = sheet1.getLastRow();
  //merge subarrays with same index
  var sheet1Transpose = transpose(sheet1_val);
  var request = {
    'valueInputOption': 'USER_ENTERED',
    'data': [
      {
        'range': 'Master List!E2:E'+lastRow,
        'majorDimension': 'COLUMNS',
        'values': [sheet1Transpose[4]]
      },
      {
        'range': 'Master List!G2:G'+lastRow,
        'majorDimension': 'COLUMNS',
        'values': [sheet1Transpose[6]]
      },
      {
        'range': 'Master List!I2:I'+lastRow,
        'majorDimension': 'COLUMNS',
        'values': [sheet1Transpose[8]]
      }
    ]
  };

  var response = Sheets.Spreadsheets.Values.batchUpdate(request, spreadsheetId);
}

function transpose(matrix) {
  return matrix[0].map((col, i) => matrix.map(row => row[i]));
}

注意： 确保在服务中添加 Google 表格。

例子：

付款截止日期：

前：

后：

参考：

• 获取显示值
• 写入多个范围