在Google表格中使用Google脚本循环遍历区域内的单元格

Question

我想编写一个脚本，使我可以仅在选定范围内突出显示（背景颜色）某些单元格，例如仅在第2列和第2行中的单元格，即使我选择整个表格也是如此。

示例：

For each cell in selected range {
 If selected cell[i][j] is within allowed range {
  cell background color = green
 };
};

我正在尝试完成的代码：

function BackgroundColor() {
  var spreadsheet = SpreadsheetApp.getActive();
  var sheet = spreadsheet.getActiveSheet();
  var range = sheet.getActiveRange();
  var cells = range.getValues();
  var numRows = range.getNumRows();
  var numCols = range.getNumColumns();
  
  for (var i = 0; i < numRows; i++) {
    for (var j = 0; j < numCols; j++) {
      if (range.getValues()[i][j] == '') {
        range.setBackground('#00B050').setFontColor('#ffffff')
      }
    }
  }
  range.setValues(cells);
};

我能够在VBA中实现它：

Sub cmdGreen()

    Dim Cel As Range
    Dim GreenArrayCount As Integer
    Dim InteriorColor As Long, FontColor As Long
    Dim GreenArray() As Variant
    Dim BodyRange As String

    InteriorColor = VBA.RGB(0, 176, 80) 'interior in green
    FontColor = VBA.RGB(255, 255, 255) 'font in white
    GreenArray = Array("COLUMN 2", "ROW 2")
    BodyRange = ActiveSheet.ListObjects(1).DataBodyRange.Address

    For Each Cel In Application.Selection.Cells
        If Not Intersect(Cel, Range(BodyRange)) Is Nothing Then
            For GreenArrayCount = LBound(GreenArray) To UBound(GreenArray)
                If (ActiveSheet.ListObjects(1).HeaderRowRange(Cel.Column).Value _
                    = GreenArray(GreenArrayCount) Or _
                    ActiveSheet.ListObjects(1).DataBodyRange(Cel.Row - 1, 1).Value _
                    = GreenArray(GreenArrayCount)) Then
                    Cel.Interior.Color = InteriorColor
                    Cel.Font.Color = FontColor
                End If
            Next GreenArrayCount
        End If
    Next Cel

End Sub

有人能帮助我吗？

这个问题与IT技术无关。

- Bernardo Giarola

---

请详细说明“允许范围”。我们应该如何知道哪些单元格需要标记为绿色？ - Jeremy Kahan

在我的示例中，如附图所示（https://i.stack.imgur.com/wCJhe.png），允许的范围是第2列和第2行。只有在此范围内选择的单元格才能被着色。 - Bernardo Giarola

“允许范围”始终是R2和C2吗？如果是这样，那么就不需要所有的代码了。只需将范围设置为R2和C2，然后设置背景即可。 - Ron Kloberdanz

在这个例子中，是的。但我想阻止人们设置超出此范围的单元格背景。这是一个可共享的工作表。 - Bernardo Giarola

我已经添加了一个等效的VBA代码，可以在Excel中实现相同的功能。这就是我想要的，但是我希望在Google Sheets中实现。 - Bernardo Giarola

编写Excel宏并要求将其翻译为Google Apps脚本超出了SO的范围。你的当前代码有什么问题？你没有提到任何问题或错误。你也没有提到你如何尝试修改它，却未能获得所需的结果。 - tehhowch

Answer 1

修改后的代码：

function BackgroundColor() {

  var okColumn = [3, 6, 7, 8, 9];
  var spreadsheet = SpreadsheetApp.getActive();
  var sheet = spreadsheet.getActiveSheet();
  var selection = sheet.getSelection();
  var range = sheet.getActiveRange();
  var numRows = range.getNumRows();
  var numCols = range.getNumColumns();
  var startRow = range.getRow();
  var startCol = range.getColumn();

  for (var i = 3; i < numRows; i++) {
    for (var j = 0; j < numCols; j++) {
      for (var k = 0; k <= 4; k++) {
        if ((startRow + i == okRow) || (startCol + j == okColumn[k])) {
          range.getCell(i+1,j+1).setBackground('#00B050').setFontColor('#ffffff')
        }
      }
    }
  }
};

- Bernardo Giarola

Answer 2

无疑有改善性能的方法，但这应该可以解决问题。

function BackgroundColor() {
  var okRow = 2;
  var okColumn = 2;
  var spreadsheet = SpreadsheetApp.getActive();
  var sheet = spreadsheet.getActiveSheet();
  var selection = sheet.getSelection();

  //Logger.log('Active Range: ' + selection.getActiveRange().getA1Notation());

  var range = selection.getActiveRange();
  var numRows = range.getNumRows();
  var numCols = range.getNumColumns();
  var startRow = range.getRow();
  var startCol = range.getColumn();
  //Logger.log('row: ' + startRow);
  //Logger.log('col: ' + startCol);
  //Logger.log('num row: ' + numRows);
  //Logger.log('num col: ' + numCols);

  for (var i = 0; i < numRows; i++) {
    for (var j = 0; j < numCols; j++) {
      if ((startRow+i == okRow) || (startCol+j == okColumn)){
        //Logger.log('found: ' + i+' '+j);
        range.getCell(i+1,j+1).setBackground('#00B050').setFontColor('#ffffff')
      }
    }
  }
};

注意范围是从0开始的，但getCell是从1开始的。 同样要注意，这里的ok行和列是基于实际的行和列，而不是它们的标签。在你的例子中，ok行将是4，ok列将是3。

- Jeremy Kahan

---

非常感谢，Jeremy！它起作用了，我能够修改它以适应我的需求。下面是我修改后的代码。 - Bernardo Giarola