gpt4 book ai didi

loops - 使用 Google Script 循环浏览 Google Sheets 中某个范围内的单元格

转载 作者:行者123 更新时间:2023-12-03 23:54:08 25 4
gpt4 key购买 nike

我想编写一个脚本,允许我仅突出显示(背景颜色)选定范围内的某些单元格,例如,仅在 COLUMN 2 和 ROW 2 中的单元格,即使我选择了整个表格。

前任:
How the script is supposed to work

例如:

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

任何人都可以帮助我吗?

最佳答案

修改后的代码:

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

关于loops - 使用 Google Script 循环浏览 Google Sheets 中某个范围内的单元格,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/52085571/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com