javascript - Google Sheet 脚本 - 如果找到单元格值则返回标题值

Question

预先感谢您的帮助。

我有一个 Google 工作表，其中第一行包含标题值。我有一个脚本正在查看工作表的其余部分(逐行)，如果单元格是某种颜色，则脚本会保留计数。最后，如果计数大于我在工作表中设置的变量，脚本将触发一封电子邮件。

我正在尝试做的事情是，如果脚本找到具有设置颜色的单元格，还捕获列标题值？我确定我需要创建一个包含标题值的数组，然后比较位置，我只是不确定如何有效地执行此操作。

function sendEmails() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var dataSheet = ss.getSheets()[0];
  var lastRow = dataSheet.getLastRow();
  var lastColumn = dataSheet.getLastColumn();

  //Project Range Information
  var projectRange = dataSheet.getRange(6,3,lastRow-5,lastColumn);
  var projectRangeValues = projectRange.getValues()[0];
  var cellColors = projectRange.getBackgrounds();

  //Student Information Range 
  var studentRange = dataSheet.getRange(6,1,lastRow-5,lastColumn);
  var studentRangeValues = studentRange.getValues();

  //Pull email template information
  var emailSubject = ss.getRange("Variables!B1").getValue(); 
  var emailText = ss.getRange("Variables!B2").getValue();
  var triggerValue = ss.getRange("Variables!B4").getValue();
  var ccValue = ss.getRange("Variables!B5").getValue();

  //Where to Start and What to Check
  var colorY = ss.getRange("Variables!B6").getValue(); 
  var count = 0;
  var startRow = 6;

  //Loop through sheet and pull data
  for(var i = 0; i < cellColors.length; i++) {  
    //Pull some information from the rows to use in email
    var studentName = studentRangeValues[i][0];
    var studentBlogUrl = studentRangeValues[i][1];
    var studentEmail = studentRangeValues[i][2];
    var studentData = [studentName,studentBlogUrl];

    //Loop through cell colors and count them
    for(var j = 0; j < cellColors[0].length ; j++) { 
      if(cellColors[i][j] == colorY) { 

           /*This is where I feel I need to add the array comparisons to get the header values */

           count = count + 1;
      };//end if statement
    };//end for each cell in a row

    //If the count is greater than trigger, send emails
    if (count >= triggerValue) {
        //A call to another function that merges the information              
        var emailBody = fillInTemplateFromObject(emailText, studentData);
        MailApp.sendEmail({
          to: studentEmail,
          cc: ccValue,
          subject: emailSubject,
          htmlBody: emailBody,
        });
    } else {};
    //reset count to 0 before next row
    count = 0;
  };//end for each row
};

编辑:我已根据响应更新了上述代码部分:

//Header Information
var headers = dataSheet.getRange(4,4,1,lastColumn);
var headerValues = headers.getValues();
var missingAssignments = new Array();

在 for 循环中我添加了:

//Loop through cell colors and count them
for(var j = 0; j < cellColors[0].length ; j++) { 
  if(cellColors[i][j] == colorY) {
       //This pushes the correct information into the array that matches up with the columns with a color.
       missingAssignments.push(headervalues[i][j]);
       count = count + 1;
  };//end if statement
};//end for each cell in a row

我遇到的问题是我收到错误 - TypeError: 无法从未定义中读取属性“2”。这是由于脚本移动到下一行时 for 循环中的推送造成的。我不确定为什么会收到此错误。从我读过的其他内容来看，该数组被设置为未定义。我尝试将数组设置为空并将其长度设置为 0，但这没有帮助。我认为我不理解数组运行时的范围。

编辑:弄清楚了，“i”不应该迭代。它应该是:

missingAssignments.push(headervalues[0][j]);

第一个 for 循环结束时，我清除了下一行的数组。

missingAssignments.length = 0;

Answer 1

您应该获取整个工作表的值。然后使用shift方法只获取标题。如果没有有关您的工作表的更多信息，我很难完全理解您的意图。如果我可以提供更多信息，请告诉我。

function sendEmails() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var dataSheet = ss.getSheets()[0];
  var lastRow = dataSheet.getLastRow();
  var lastColumn = dataSheet.getLastColumn();
  //below gets the whole sheet and shifts off the first row as headers
  var fullSheet = dataSheet.getDataRange().getValues();
  var headers = fullSheet.shift();

  //then in your loops you can check against the index of the headers array