自定义Y轴：实现数据自适应+10%缓冲的脚本问题排查

我需要给Google Sheets里的多个图表设置Y轴自适应数据最大值，并且额外保留10%的缓冲空间，但自带工具没有这个选项。用ChatGPT生成了脚本却没生效，脚本如下：

function setDynamicYAxis() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var chart = sheet.getCharts()[0]; // Assumes the chart you want to edit is the first one
  var dataRange = sheet.getDataRange();
  var values = dataRange.getValues();
   
  // Find the maximum value in the data range
  var maxValue = 0;
  for (var i = 0; i < values.length; i++) {
    for (var j = 0; j < values[i].length; j++) {
      if (values[i][j] > maxValue) {
        maxValue = values[i][j];
      }
    }
  }

  // Set the new max value with a buffer (e.g., 10% higher)
  var buffer = maxValue * 0.1;
  var newMax = maxValue + buffer;

  // Modify the chart's y-axis maximum value
  var updatedChart = chart.modify()
    .setOption('vAxis.maxValue', newMax)
    .build();

  sheet.updateChart(updatedChart);
}

原脚本失效的原因 #

1. 仅处理工作表中的第一个图表，无法覆盖多个图表的需求
2. 直接取整个工作表的所有数据，包含表头等非数值内容，导致最大值计算错误
3. 没有针对图表自身绑定的数据源计算最大值，逻辑不符合实际需求
4. 未处理数据为空或全为0的边界情况

修正后的脚本 #

function setDynamicYAxisForAllCharts() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const charts = sheet.getCharts(); // 获取当前工作表所有图表

  charts.forEach(chart => {
    // 获取图表绑定的数据源范围
    const dataRange = chart.getRange();
    const values = dataRange.getValues();
    
    let maxValue = 0;
    // 遍历数据源中的数值，跳过第一行表头（可根据实际表头行数调整起始值）
    for (let i = 1; i < values.length; i++) {
      for (let j = 0; j < values[i].length; j++) {
        const cellValue = values[i][j];
        // 只处理数值类型的单元格
        if (typeof cellValue === 'number' && cellValue > maxValue) {
          maxValue = cellValue;
        }
      }
    }

    // 处理最大值为0的情况，避免缓冲后仍为0
    if (maxValue === 0) {
      maxValue = 10; // 可根据需求调整默认基准值
    }

    // 计算带10%缓冲的新最大值（如需调整比例，修改1.1即可，如15%为1.15）
    const newMax = maxValue * 1.1;

    // 更新图表Y轴最大值
    const updatedChart = chart.modify()
      .setOption('vAxis.maxValue', newMax)
      .build();
    
    sheet.updateChart(updatedChart);
  });
}

使用步骤（无编程基础也能操作） #

1. 打开目标Google Sheets文档
2. 点击顶部菜单「扩展程序」→「Apps脚本」
3. 删除原有代码，粘贴上面的修正脚本
4. 点击工具栏的「运行」按钮，首次运行会要求授权，按照提示完成权限验证即可
5. 运行完成后返回工作表，所有图表的Y轴会自动设置为对应数据源最大值+10%缓冲

内容的提问来源于stack exchange，提问作者Joop de Groot