google-apps-script - Google表格:基于数据范围单元格背景颜色的散点图中的颜色点
问题描述
我有一个谷歌表格文件,用于存储不同区域(后、前、左、右)中不同汽车的油漆厚度值。然后将所有内容汇总在散点图中,按车龄(X 轴)显示油漆厚度(Y 轴)。
每个区域都有自己的系列,它给出的是那辆或每辆汽车,它会在汽车的年龄值上创建一条垂直的点线(序列号只是一个标签)。每个点都显示相应区域中的油漆厚度,其位置在 Y 轴上。
我正在根据每个区域报告的损坏更改单元格的背景颜色。我想在散点图中的每个对应点上都有相同的颜色,而不是一一着色。
(我不能包含任何链接/屏幕截图,因为这是机密数据)
例子 :
序列号 | 年龄 | 区域 1 | 区域 2 | 区域 3 | 区域 4 |
---|---|---|---|---|---|
42 | 15 | 178 | 210 | 246 | 143 |
序列号 42 是 15 个月大。它的左侧有 246 的油漆厚度,并且已经报告了损坏。我将包含 246 的单元格着色为红色,这会改变散点图中相应点的颜色。
该脚本必须将散点图中的每个点与其对应的单元格相关联,获取单元格的背景颜色并根据它设置点的颜色。
我无法为整个系列上色,因为每种颜色都需要特定于一个区域,在一辆车中
这个文件曾经是一个 Excel 文件,我找到了一个可以做到这一点的 VBA 宏,但是我的公司正在切换到 Gsuite,我对它还不够熟悉,无法翻译宏。
这是VBA代码:
Sub CellColorsToChart()
'Update by Extendoffice
Dim xChart As Chart
Dim I As Long, J As Long
Dim xRowsOrCols As Long, xSCount As Long
Dim xRg As Range, xCell As Range
On Error Resume Next
Set xChart = ActiveSheet.ChartObjects("Chart 1").Chart
If xChart Is Nothing Then Exit Sub
xSCount = xChart.SeriesCollection.Count
For I = 1 To xSCount
J = 1
With xChart.SeriesCollection(I)
Set xRg = ActiveSheet.Range(Split(Split(.Formula, ",")(2), "!")(1))
If xSCount > 4 Then
xRowsOrCols = xRg.Columns.Count
Else
xRowsOrCols = xRg.Rows.Count
End If
For Each xCell In xRg
.Points(J).Format.Fill.ForeColor.RGB = ThisWorkbook.Colors(xCell.Interior.ColorIndex)
.Points(J).Format.Line.ForeColor.RGB = ThisWorkbook.Colors(xCell.Interior.ColorIndex)
J = J + 1
Next
End With
Next
End Sub
解决方案
一个相当笨拙但无论如何,这是我的尝试:
function myFunction() {
var sheet = SpreadsheetApp.getActiveSheet();
var colors = sheet.getRange('C2:F2').getBackgrounds().flat();
var chart = sheet.getCharts()[0];
chart = chart.modify()
.setOption('series.0.items.0.color', colors[0])
.setOption('series.0.items.1.color', colors[1])
.setOption('series.0.items.2.color', colors[2])
.setOption('series.0.items.3.color', colors[3])
.build();
sheet.updateChart(chart);
}
更新
我仍然不确定我是否理解正确。可能是这样的吗?
function myFunction() {
var sheet = SpreadsheetApp.getActiveSheet();
var chart = sheet.getCharts()[0];
var colors = [];
colors = sheet.getRange('C2:C5').getBackgrounds().flat();
chart = recolor(chart, colors, 0);
colors = sheet.getRange('D2:D5').getBackgrounds().flat();
chart = recolor(chart, colors, 1);
colors = sheet.getRange('E2:E5').getBackgrounds().flat();
chart = recolor(chart, colors, 2);
colors = sheet.getRange('F2:F5').getBackgrounds().flat();
chart = recolor(chart, colors, 3);
sheet.updateChart(chart);
function recolor(chart, colors, series) {
return chart.modify()
.setOption('series.' + series + '.items.0.color', colors[0])
.setOption('series.' + series + '.items.1.color', colors[1])
.setOption('series.' + series + '.items.2.color', colors[2])
.setOption('series.' + series + '.items.3.color', colors[3])
.build();
}
}
更新 2
代码的更新版本。现在它可以为任意数量的汽车(行)着色:
function myFunction() {
var sheet = SpreadsheetApp.getActiveSheet();
var last = sheet.getLastRow();
var chart = sheet.getCharts()[0];
var colors = [];
// recolor zone 1
colors = sheet.getRange('C2:C' + last).getBackgrounds().flat();
chart = recolor(chart, colors, 0);
// recolor zone 2
colors = sheet.getRange('D2:D' + last).getBackgrounds().flat();
chart = recolor(chart, colors, 1);
// recolor zone 3
colors = sheet.getRange('E2:E' + last).getBackgrounds().flat();
chart = recolor(chart, colors, 2);
// recolor zone 4
colors = sheet.getRange('F2:F' + last).getBackgrounds().flat();
chart = recolor(chart, colors, 3);
// update the chart
sheet.updateChart(chart);
function recolor(chart, colors, series) {
colors.forEach((color,i) => {
chart = chart.modify()
.setOption('series.' + series + '.items.' + i + '.color', color)
.build();
});
return chart;
}
}
可能它也可以扩展为能够占用任意数量的区域。
这是可以处理任意数量区域的最终版本:
function myFunction() {
var sheet = SpreadsheetApp.getActiveSheet();
var chart = sheet.getCharts()[0];
var last_cell = chart.getRanges()[1].getA1Notation().split(':')[1]; // the bottom right cell of the chart
var data = sheet.getRange('C2:' + last_cell).getBackgrounds();
for (var i in data) chart = recolor(chart, data.map(x => x[i]), i);
sheet.updateChart(chart);
function recolor(chart, colors, series) {
colors.forEach((color,i) => {
chart = chart.modify()
.setOption('series.' + series + '.items.' + i + '.color', color)
.build();
});
return chart;
}
}
推荐阅读
- python - 是否可以根据多种条件组合对熊猫数据框进行子集化?
- php - Ubuntu 18.04 上的 Laravel 5.3,发布请求不起作用
- javascript - 数据以 JSON 对象的形式传入,如何解析?
- html - 向右浮动正在反转我的导航栏的顺序
- python - 即使在成功安装后导入模块“请求”也不起作用
- php - 阵列混乱
- java - Java:无法找到或加载主类 ubuntu
- javascript - 编写一个函数来调用其他函数而不调用用户提示
- c# - c# datatable 行计数为空表返回 1
- java - 无法从 Android 手机读取/写入文件,Android Studio 中的 AVD 除外