如何用脚本在Google Sheets中为指定两行生成折线图并修复异常

我有一个需23天完成数据填充的表格，需要为第3行（Comp Temp）和第6行（Air Meas）生成折线图。当前遇到的问题是：若为第4行填入数据，第3行的折线图会出现错乱；若为第7行填入数据，第6行的折线图也会错乱。我已在Google Sheets的扩展功能中尝试了以下脚本（注释已翻译为中文）：

function createCompTempLineChart(sheet) {
  // 定义Comp Temp（第3行）的范围，从B列（第2列）开始
  var startColumn = 2; // B列
  var lastColumn = sheet.getLastColumn(); // 找到有数据的最后一列

  // 清空其他行的空值或零值，防止图表将其纳入计算
  for (var col = startColumn; col <= lastColumn; col++) {
    sheet.getRange(4, col).setValue(''); // 清空第4行
    sheet.getRange(5, col).setValue(''); // 清空第5行
    // 若需要，可将更多行设为''或0，确保无其他数据影响图表
  }

  // 明确限定范围仅为第3行（忽略其他行）
  var compTempRange = sheet.getRange(3, startColumn, 1, lastColumn - startColumn + 1); // 仅第3行

  // 创建或更新Comp Temp图表
  var chart = sheet.newChart()
    .setChartType(Charts.ChartType.LINE) // 设置图表类型为折线图
    .addRange(compTempRange) // 仅使用第3行数据
    .setTransposeRowsAndColumns(true) // 将第3行视为数据系列
    .setPosition(30, 5, 0, 0) // 调整图表位置
    .setOption('title', 'Comp Temp (第3行)') // Comp Temp图表标题
    .build();

  sheet.insertChart(chart); // 插入Comp Temp图表
}

function createAirMeasLineChart(sheet) {
  // 定义Air Meas（第6行）的范围，从B列（第2列）开始
  var startColumn = 2; // B列
  var lastColumn = sheet.getLastColumn(); // 找到有数据的最后一列

  // 清空其他行的空值或零值，防止图表将其纳入计算
  for (var col = startColumn; col <= lastColumn; col++) {
    sheet.getRange(3, col).setValue(''); // 清空第3行，避免影响此图表
    sheet.getRange(4, col).setValue(''); // 清空第4行
    sheet.getRange(5, col).setValue(''); // 清空第5行
    // 若需要，可将更多行设为''或0
  }

  // 明确限定范围仅为第6行（忽略其他行）
  var airMeasRange = sheet.getRange(6, startColumn, 1, lastColumn - startColumn + 1); // 仅第6行

  // 创建或更新Air Meas图表
  var chart = sheet.newChart()
    .setChartType(Charts.ChartType.LINE) // 设置图表类型为折线图
    .addRange(airMeasRange) // 仅使用第6行数据
    .setTransposeRowsAndColumns(true) // 将第6行视为数据系列
    .setPosition(50, 5, 0, 0) // 调整图表位置
    .setOption('title', 'Air Meas (第6行)') // Air Meas图表标题
    .build();

  sheet.insertChart(chart); // 插入Air Meas图表
}

问题分析 #

原脚本存在两个核心问题：

1. 强制清空其他行数据：生成图表时会清空第3-5行数据，与保留这些行数据的需求冲突，且未处理第7行对Air Meas图表的干扰。
2. 重复插入图表：每次执行函数都会新建图表，导致表格堆积大量重复图表，且未更新原有图表的数据源。

修正后的脚本 #

以下脚本无需清空其他行数据，通过严格锁定数据源范围、更新已有图表解决干扰问题：

function updateOrCreateCompTempChart() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var startCol = 2; // B列（日期/天数列）
  var lastCol = sheet.getLastColumn();
  // 严格锁定第3行的数据源范围
  var targetRange = sheet.getRange(3, startCol, 1, lastCol - startCol + 1);

  // 查找已存在的Comp Temp图表
  var charts = sheet.getCharts();
  var targetChart = null;
  for (var i = 0; i < charts.length; i++) {
    var chart = charts[i];
    if (chart.getOptions().get('title') === 'Comp Temp (第3行)') {
      targetChart = chart;
      break;
    }
  }

  if (targetChart) {
    // 更新已有图表的数据源
    targetChart = targetChart.modify()
      .clearRanges()
      .addRange(targetRange)
      .setTransposeRowsAndColumns(true)
      .build();
    sheet.updateChart(targetChart);
  } else {
    // 新建图表
    var chart = sheet.newChart()
      .setChartType(Charts.ChartType.LINE)
      .addRange(targetRange)
      .setTransposeRowsAndColumns(true)
      .setPosition(30, 5, 0, 0)
      .setOption('title', 'Comp Temp (第3行)')
      .setOption('legend', {position: 'none'}) // 可选：隐藏图例，单系列无需显示
      .build();
    sheet.insertChart(chart);
  }
}

function updateOrCreateAirMeasChart() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var startCol = 2; // B列（日期/天数列）
  var lastCol = sheet.getLastColumn();
  // 严格锁定第6行的数据源范围
  var targetRange = sheet.getRange(6, startCol, 1, lastCol - startCol + 1);

  // 查找已存在的Air Meas图表
  var charts = sheet.getCharts();
  var targetChart = null;
  for (var i = 0; i < charts.length; i++) {
    var chart = charts[i];
    if (chart.getOptions().get('title') === 'Air Meas (第6行)') {
      targetChart = chart;
      break;
    }
  }

  if (targetChart) {
    // 更新已有图表的数据源
    targetChart = targetChart.modify()
      .clearRanges()
      .addRange(targetRange)
      .setTransposeRowsAndColumns(true)
      .build();
    sheet.updateChart(targetChart);
  } else {
    // 新建图表
    var chart = sheet.newChart()
      .setChartType(Charts.ChartType.LINE)
      .addRange(targetRange)
      .setTransposeRowsAndColumns(true)
      .setPosition(50, 5, 0, 0)
      .setOption('title', 'Air Meas (第6行)')
      .setOption('legend', {position: 'none'}) // 可选：隐藏图例
      .build();
    sheet.insertChart(chart);
  }
}

使用说明 #

1. 将上述脚本替换原脚本，保存后授权运行。
2. 可通过扩展功能 > 宏执行对应的更新函数，或设置触发器（如每次编辑表格时自动更新图表），确保图表数据始终准确。

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