Google表格:如何使用Google脚本根据单元格值动态更改条形图的颜色? [英] Google sheets: How to dynamically change the color of bar chart based on cell value with google script?

问题描述

我在excel上制作了一个甘特图(请参见屏幕截图)，我试图弄清楚如何根据受让人(团队成员)动态更改条形的颜色.作为参考，这不过是一个堆积的条形图，其中该列的第一部分被制成透明的.有2个系列:开始于一天"(透明)和持续时间"(浅蓝色).

I made a gantt chart on excel (see screenshot), and I'm trying to figure out how to dynamically change the color of a bar based on assignee (team member). For reference, this is nothing but a stacked bar chart, where the first part of the column is just made to be transparent. There are 2 series: "Start on day" - transparent, and "Duration" - light blue.

正如您现在所看到的，所有条形都是相同的颜色，而我想要这样的东西:

As you can see right now, all bars are the same color, while I want something like this:

所以我找到了这段代码，我对其进行了编辑一点点，并设法使其起作用，以便当我在团队成员"列中更改名称时，它为图表提供不同的颜色(本质上，它从隐藏的列中接收CSS颜色代码).问题是，它会为所有条形着色(因为它是针对一系列的)，而不仅仅是一个.我找不到让它仅在一个酒吧工作的方法.

So I found this piece of code, I edited it a little bit, and managed to make it work so that when I change a name in the Team member column, it colors the chart differently (essentially it receives a CSS color code from a hidden column). Problem is, it colors all the bars (because it's targeting a series) and not just one. I couldn't find a way to make it work with one bar only.

建议?任何帮助深表感谢. (下面的代码)

Suggestions? Any help is much appreciated. (code below)

function modifyChart_(sheet, newCssColor) {
  // Assume there is only one chart on this sheet.
  const charts = sheet.getCharts();
  const barBuilder = charts[0].modify().asBarChart();
  const option = {};

  option[0] = {"color": "rgba(255,255,255, 0"};
  option[1] = {};
  option[2] = {"color": newCssColor};
  barBuilder.setOption("series", option);

  // Update the chart on the sheet.
  sheet.updateChart(barBuilder.build());
}

推荐答案

如何静态设置条形图的颜色

示例:

function modifyChart(sheet, newCssColor) {
  // Assume there is only one chart on this sheet.
  var sheet = SpreadsheetApp.getActive().getActiveSheet();
  const charts = sheet.getCharts();
  const barBuilder = charts[0].modify().asBarChart().setColors(["red", "green", "grey"]);
   sheet.updateChart(barBuilder.build());
}

注意:

• Apps Script在函数名称末尾不支持下划线_
• 更改条形颜色最方便的方法是 setColors

• Apps Script does not support the underscore _ at the end of a function name
• The most convenient method for changing bar colors is setColors

如何根据单元格值动态设置条形图的颜色

示例:

function modifyChart(sheet, newCssColor) {
  // Assume there is only one chart on this sheet.
  var sheet = SpreadsheetApp.getActive().getActiveSheet();
  const charts = sheet.getCharts();
  var array = [];
  //assuming the color values are in cells A10, A11 and A12
  var colorValues = sheet.getRange("A10:A12").getValues();
  for(var i = 0; i < colorValues.length; i++){
    array.push(colorValues[i][0]);
  }
  const barBuilder = charts[0].modify().asBarChart().setColors(array);
  sheet.updateChart(barBuilder.build());
}

参考:

• getValues()
• array.push()

• getValues()
• array.push()

这篇关于Google表格:如何使用Google脚本根据单元格值动态更改条形图的颜色?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！