如何使用应用程序脚本获取某个范围内所有单元格的背景色并将背景设置为相同大小的其他范围？
昆西·弗里温
我正在尝试从一定范围的单元格中复制背景色并将其粘贴到其他范围。在我的情况下，我希望的颜色是第3到53行以及从E到BC的每其他列之间的范围。在A1格式中，它将是（['E3：E53'，'G3：G53'，'I3：I53'，...'BA3：BA53'，'BC3：BC53']）。我想获得该范围的背景色，然后将其粘贴到我的目标范围内，介于第3到53行以及从D到BB的每隔一列之间。A1表示法的范围是（['D3：D53'，'F3：F53'，'H3：H53'...'AZ3：AZ53'，'BB3：BB53']）。
换句话说，我希望目标范围内的每个单元格都与其右边的相邻单元格具有相同的颜色。
这就是我目前所拥有的。
这是我使用app脚本获得的理想结果。
我知道我可以手动更改颜色，但是工作表中的值会经常更改，并且要复制的单元格颜色是基于条件格式规则的，这意味着我必须定期手动更改所有单元格颜色。这就是为什么我要使用apps脚本的原因，所以我所要做的就是运行一个函数，它将为我创造我想要的效果。
我对编码世界很陌生，但这是我尝试过的。
setColor(){
var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Coach");
var targetArea = ss.getRangeList(['D3:D53','F3:F53','H3:H53','J3:J53','L3:L53','N3:N53','P3:P53','R3:R53','T3:T53','V3:V53','X3:X53','Z3:Z53',
'AB3:AB53','AD3:AD53','AF3:AF53','AH3:AH53','AJ3:AJ53','AL3:AL53','AN3:AN53','AP3:AP53','AR3:AR53','AT3:AT53','AV3:AV53','AX3:AX53','AZ3:AZ53',
'BB3:BB53']);
var desiredColor = ss.getRangeList(['E3:E53','G3:G53','I3:I53','K3:K53','M3:M53','O3:O53','Q3:Q53','S3:S53','U3:U53','W3:W53','Y3:Y53',
'AA3:AA53','AC3:AC53','AE3:AE53','AG3:AG53','AI3:AI53','AK3:AK53','AM3:AM53','AO3:AO53','AQ3:AQ53','AS3:AS53','AU3:AU53','AW3:AW53','AY3:AY53',
'BA3:BA53','BC3:BC53']);
var background = desiredColor.getBackgrounds;
targetArea.setBackgrounds(background)
}
我运行了代码，但它告诉我“ targetArea.setBackgrounds不是函数”。如果我从.setBackground中删除了“ s”，它根本不会做任何事情。没有错误或任何东西。
Any help would be greatly appreciated! Thank you!
Diego
The setBackground() method on the RangeList object allows you to only provide one color. Instead, you want to be calling setBackgrounds() (or setBackgroundObjects()) on the individual ranges. You can get those by calling getRanges() and then iterating through them to apply whatever changes you want.
function setColor() {
var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Coach");
var targetRangeList = ss.getRangeList(['D3:D53','F3:F53','H3:H53','J3:J53','L3:L53','N3:N53','P3:P53','R3:R53','T3:T53','V3:V53','X3:X53','Z3:Z53',
'AB3:AB53','AD3:AD53','AF3:AF53','AH3:AH53','AJ3:AJ53','AL3:AL53','AN3:AN53','AP3:AP53','AR3:AR53','AT3:AT53','AV3:AV53','AX3:AX53','AZ3:AZ53',
'BB3:BB53']);
var sourceRangeList = ss.getRangeList(['E3:E53','G3:G53','I3:I53','K3:K53','M3:M53','O3:O53','Q3:Q53','S3:S53','U3:U53','W3:W53','Y3:Y53',
'AA3:AA53','AC3:AC53','AE3:AE53','AG3:AG53','AI3:AI53','AK3:AK53','AM3:AM53','AO3:AO53','AQ3:AQ53','AS3:AS53','AU3:AU53','AW3:AW53','AY3:AY53',
'BA3:BA53','BC3:BC53']);
var sourceRanges = sourceRangeList.getRanges();
var targetRanges = targetRangeList.getRanges();
for (var i = 0; i < sourceRanges.length; i++) {
targetRanges[i].setBackgrounds(sourceRanges[i].getBackgrounds());
}
}
By the way, you can probably get those ranges in a more programmatic way since the target ranges seems to be odd-numbered columns while the source ranges are even-numbered and they're all of the same height.
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
编辑于
我来说两句