如何在Google Apps Script的SpreadSheet中更改动态数据集散点图点颜色？

我在调整散点图点的颜色时遇到问题，需求是将B28:B60数据系列设为红色（代表出发状态），C28:C60数据系列设为绿色（代表到达状态），现在已经解决该问题，以下是完整代码：

function doGet(n) {
  Logger.log("--- doGet ---");
  var name = "",
    status = "";
  try {
    if (n == null) { n = {}; n.parameters = { name: "test", status: "-1" }; }
    name = n.parameters.name;
    status = n.parameters.status;
    save_data(name, status);
    return ContentService.createTextOutput("Wrote:\n  name: " + name + "\n  status: " + status);
  } catch (error) {
    Logger.log(error);
    return ContentService.createTextOutput("oops...." + error.message
      + "\n" + new Date()
      + "\nname: " + name +
      + "\nstatus: " + status);
  }
}

function addNewSheetForToday() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var date = new Date();
  var formattedDate = Utilities.formatDate(date, spreadsheet.getSpreadsheetTimeZone(), "MM-dd-yyyy");
  var existingSheet = spreadsheet.getSheetByName(formattedDate);
  if (existingSheet) {
    Logger.log("Sheet for today already exists.");
    return;
  }
  var newSheet = spreadsheet.getSheetByName("Sheet1").copyTo(spreadsheet).setName(formattedDate);
  newSheet.getCharts().forEach(c => newSheet.removeChart(c));
  createScatterPlot(newSheet);
  var numRows = 60;
  var numColumns = 15;
  var colors = ['#BBBBBB', '#CCCCCC'];
  for (var c = 28; c <= numRows; c++) {
    var colorIndex = (c - 2) % colors.length;
    var row = newSheet.getRange(c, 1, 1, numColumns);
    row.setBackground(colors[colorIndex]);
  }
  var headerRow = newSheet.getRange(27, 2, 1, numColumns);
  headerRow.setFontStyle("italic");
  var headerColumn = newSheet.getRange(28, 1, numRows, 1);
  headerColumn.setFontWeight("bold");
}

function createScatterPlot(newSheet) {
  Logger.log("--- createScatterPlot ---");
  var title = "Today's Timestamps";
  var titleFontSize = 18;
  var titleRow = 1;
  var titleColumn = 6;
  var titleCell = newSheet.getRange(titleRow, titleColumn);
  titleCell.setValue(title)
    .setFontWeight("bold")
    .setFontSize(titleFontSize)
    .setHorizontalAlignment("right");

  var range1 = newSheet.getRange('A28:B60');
  var range2 = newSheet.getRange('C28:O60');
  var chartBuilder = newSheet.newChart()
    .asScatterChart()
    .setXAxisTitle('Student')
    .setYAxisTitle('Time')
    .addRange(range1)
    .addRange(range2)
    .setPosition(2, 1, 0, 0)
    .setOption('width', 960)
    .setOption('height', 480)
    .setOption('useFirstColumnAsDomain', true)
    .setColors(["red", ...Array(range2.getNumColumns()).fill("green")])
    .build();
  newSheet.insertChart(chartBuilder);
}

function onEdit(e) {
  const { range } = e;
  const sheet = range.getSheet();
  const charts = sheet.getCharts();
  if (range.rowStart < 28 || range.rowEnd > 60 || range.columnStart == 1 || range.columnStart > 15 || charts.length == 0) return;
  const range2 = sheet.getRange('C28:O60');
  const chart = charts[0].modify().asScatterChart().setColors(["red", ...Array(range2.getNumColumns()).fill("green")]).build();
  sheet.updateChart(chart);
}

function save_data(name, status) {
  Logger.log("--- save_data ---");
  try {
    addNewSheetForToday();
    var today = new Date();
    var time = Utilities.formatDate(today, "GMT-4", "hh:mm");
    var ss = SpreadsheetApp.openByUrl("putyourURLhere");
    var dataLoggerSheet = ss.getSheetByName(Utilities.formatDate(today, ss.getSpreadsheetTimeZone(), "MM-dd-yyyy"));
    var studentnames = ["", "Alep", "Bet", "Gamal", "Dalat", "Heh", "Waw", "Zen", "Khet", "Tet", "Yod", "Kap", "Lamad", "Meem", "Noon", "Simkat", "Eh", "Peh", "Sahdeh", "Qope", "Resh", "Sheen", "Tao", "Martin", "Allyson", "Ashur", "Matilda", "Odin", "Ophelia", "Brownie", "Oreo", "SubCard1", "SubCard2", "SubCard3"];
    for (var i = 0; i < studentnames.length; i++) {
      dataLoggerSheet.getRange("A" + (i + 27)).setValue(studentnames[i]);
    }
    if (name == "Alep") {
      if (dataLoggerSheet.getRange("C28").getValue() == "" || dataLoggerSheet.getRange("C28").getValue() == null) {
        dataLoggerSheet.getRange("B27").setValue("1st Departure");
        dataLoggerSheet.getRange("C27").setValue("1st Return");
        if (status == "Departed") {
          dataLoggerSheet.getRange("B28").setValue(time);
        } else if (status == "Returned") {
          dataLoggerSheet.getRange("C28").setValue(time);
        }
      }
    }
  } catch (error) {
    Logger.log(JSON.stringify(error));
  }
  Logger.log("--- save_data end---");
}

关键配置说明 #

• 在createScatterPlot和onEdit函数中，通过setColors(["red", ...Array(range2.getNumColumns()).fill("green")])精准设置颜色：第一个数据系列（B28:B60）设为红色，后续所有数据系列（C28:O60）统一设为绿色。
• 开启useFirstColumnAsDomain: true，将A列作为散点图的X轴定义域，确保各列数据与学生条目正确关联。

内容的提问来源于stack exchange，提问作者ScythaScytha