使用Google Apps脚本确认值在电子表格列中 [英] Confirm that a value is in a spreadsheet column using Google Apps Script

问题描述

此功能根据一些条件发送电子邮件提醒.我需要检查的一件事是，访问ID(在电子邮件日志"工作表的第11列中)存在于单独的工作表中("DATA"，存储在enrollmentData变量中).如何搜索此数组并返回ID以匹配下面的IF语句?

This function sends email reminders based on a few conditions. One of the things I need to check for is that the Visit ID (which is in column 11 in the "email log" sheet) exists in a separate sheet ("DATA", stored in the enrollmentData variable). How do I search this array and return the ID to match in the IF statement below?

function sendReminders() {
      var ss = SpreadsheetApp.getActiveSpreadsheet ();
      var sheet = ss.getSheetByName("Email Log");
    var rows = sheet.getLastRow()-1;
      var startRow = 2;  // First row of data to process
      var numRows = rows;   // Number of rows to process
      var now = new Date();

  // Fetch the range of cells
  var dataRange = sheet.getRange(startRow, 1, numRows, 22)

  // Fetch values for each row in the Range.
  var data = dataRange.getValues();

  var today = Utilities.formatDate(new Date(), "GMT-5", "m/d/yyyy")
  var reminderSent = "Reminder Sent";

  //get email body and subject
  var bodyTem = ss.getSheetByName("Email Templates").getRange('b8').getValues();
  var subject = ss.getSheetByName("Email Templates").getRange('d13').getValues();

  //get enrollments data to search for visit ID
  var enrollmentData = ss.getSheetByName("DATA").getRange('H:H').getValues();

  for (var i = 0; i < data.length; i++) {
    var row = data[i];




   //set conditions
    var sendReminder = row[18];
    var reminderDate = row[19];
    var reminderStatus = row[20];
    var visitID = row[11]
    //need condition to look for visit ID to not include already cancelled. Search enrollmentData for visitID and return as foundID for conditional below


    if (sendReminder == "Yes" && reminderStatus != reminderSent && reminderDate >= today && visitID == foundID) {


      //assemble email
      var studentEmail = row[13];
      var firstName = row[12];
      var instructor = row[0];
      var body1 = bodyTem.replace(/*name*/gi,firstName); 
      var body2 = body1.replace(/*instructorFull*/gi,instructor);



      MailApp.sendEmail(studentEmail, subject, body2);

      //need to write in that the reminder email was sent.
      sheet.getRange(startRow + i, 20).setValue(reminderSent);
      sheet.getRange(startRow + i, 21).setValue(now);




    };
  };
};

推荐答案

您要搜索数组

var enrollmentData = ss.getSheetByName("DATA").getRange('H:H').getValues();

方法getValues始终返回双精度数组:在这种情况下，其格式为[[1], [2], [3],..]，因为每一行都有一个元素.我通常将其压扁:

The method getValues always returns a double array: in this case, it's of the form [[1], [2], [3],..] since each row has one element. I usually flatten this:

var enrollmentDataFlat = enrollmentData.map(function(row) {return row[0];});

现在enrollmentDataFlat与[1, 2, 3, ..]类似，因此indexOf将照常工作:

Now enrollmentDataFlat is like [1, 2, 3, ..] so indexOf will work as usual:

if (enrollmentDataFlat.indexOf(visitID) != -1) {
    // it's there 
}
else {
    // it's not there
}

这篇关于使用Google Apps脚本确认值在电子表格列中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！