For 循环中的一部分的颜色行，用于分配类别以形成提交

Question

我有一个在表单提交触发器上设置的 for 循环。每次提交某些内容时，它都会检查是否已对行进行分类。如果没有，它将添加一个类别。我想将颜色分配作为分类的附加步骤。如果分类为A，则为蓝色，如果分类为B，则为黄色。

我已经根据其他StackOverflow帖子尝试了下面的代码。没有运气。

function eventCategorization() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var activeSheet = sheet.setActiveSheet(sheet.getSheetByName('Form Responses 1')); //Update Sheet Name here
  Logger.log("sheet: " + activeSheet.getSheetName());
  var startRow = 3;  // First row of data to process
  var numRows = activeSheet.getLastRow() - 1;   // Number of rows to process
  var totalColumns = activeSheet.getMaxColumns();
  // Fetch the range of all cells with responses
  var dataRange = activeSheet.getRange(startRow, 1, numRows, totalColumns)
  // Fetch values for each row in the Range.
  var data = dataRange.getValues();
  for (var i = 0; i < data.length; ++i) {
    var row = data[i];  
    var broadEventType = row[11];
    Logger.log("broadEventType: "+broadEventType);
    var groupedEvent = row[1];
    Logger.log("groupedEvent: "+groupedEvent)
    if (groupedEvent ==""){
      if (broadEventType =="Gala"){
        activeSheet.getRange(i+3,2).setFormulaR1C1('="Expensive"');
        row.getRow().setBackgroundColor("#99CC99");
        activeSheet.getRange(i+3,2).getRow().setBackgroundColor("#99CC99");
      }
    }
    else if(broadEventType =="Picnic"|| 
            broadEventType=="Beach Trip"||
            broadEventType=="Drinks"){
      activeSheet.getRange(i+3,2).setFormulaR1C1('="Cheap"');
      activeSheet.getRange(i+3,2).getRow().setBackgroundColor("#4286f4");
    }
  }  
} 

错误信息：

类型错误： 在对象中找不到函数 getRow ，，Y，Y，Jill DesRosier，424584515,,,,,,Internal

Answer 1

function eventCategorization() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var activeSheet = sheet.setActiveSheet(sheet.getSheetByName('Form Responses 1')); //Update Sheet Name here
  Logger.log("sheet: " + activeSheet.getSheetName());
  var startRow = 3;  // First row of data to process
  var numRows = activeSheet.getLastRow() - 1;   // Number of rows to process
  var totalColumns = activeSheet.getMaxColumns();
  // Fetch the range of all cells with responses
  var dataRange = activeSheet.getRange(startRow, 1, numRows, totalColumns)
  // Fetch values for each row in the Range.
  var data = dataRange.getValues();
  for (var i = 0; i < data.length; ++i) {
    var row = data[i];  
    var broadEventType = row[11];
    Logger.log("broadEventType: "+broadEventType);
    var groupedEvent = row[1];
    Logger.log("groupedEvent: "+groupedEvent)
    if (groupedEvent ==""){
      if (broadEventType =="Gala"){
        activeSheet.getRange(i+3,2).setFormulaR1C1('="Expensive"');
        var activeRow = activeSheet.getRange(i+3,1,1,totalColumns).setBackgroundColor("#CC6666");
      }
    }
    else if(broadEventType =="Picnic"|| 
            broadEventType=="Beach Trip"||
            broadEventType=="Drinks"){
      activeSheet.getRange(i+3,2).setFormulaR1C1('="Cheap"');
var activeRow = activeSheet.getRange(i+3,1,1,totalColumns).setBackgroundColor("#4286f4");
    }
  }  
} 

Answer 2

我发现使用条件格式为行着色比尝试显式设置颜色要容易得多。

您可以根据行中一个或多个单元格的内容设置整行的颜色。

基于两个单元格的谷歌表格条件格式