使用Google Script遍历Google表格中某个范围内的单元格 [英] Looping through cells in a range in Google Sheets with Google Script

问题描述

我想编写一个脚本，使我只突出显示(选定背景范围内的)选定范围内的某些单元格，例如，即使选择了整个表格，也仅突出显示COLUMN 2和ROW 2中的单元格.

I want to write a script that allows me to highlight (background color) only some cells within a selected range, e.g., only cells in COLUMN 2 and ROW 2, even if I select the entire table.

例如:

例如:

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中做到这一点

I was able to make it in 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

请问有人可以帮我吗?

推荐答案

修改后的代码:

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')
        }
      }
    }
  }
};

这篇关于使用Google Script遍历Google表格中某个范围内的单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！