求助：如何在Google Sheets侧边栏实现动态更新的柱形图

问题描述 #

我需要在Google Sheets侧边栏中展示基于表格数据的柱状图，无需刷新侧边栏即可动态更新。目前已实现侧边栏调用和Google Charts加载图表，但遇到两个问题：

1. 如何从表格拉取数据填充图表所需的DataTable？scriptlets是否可行，有没有更优方案？
2. 无需手动点击按钮，如何让图表自动更新以反映表格数据变化？考虑过定时函数，但不知道如何更新图表数据。

当前代码：

<!DOCTYPE html>
<html>
<head>
<base target="_top">
  <script src="https://www.gstatic.com/charts/loader.js"></script>
</head>

<body>
  <div id="genderChart" style="margin: 5px 5px 5px 5px"></div>

<script>
  google.charts.load('current', {'packages':['corechart']});
  google.charts.setOnLoadCallback(drawChart);

  var genderMale = Math.random() * 100;
  var genderFemale = Math.random() * 100;
  var genderNB = Math.random() * 100;

  function drawChart() {
    var data = new google.visualization.DataTable();
    data.addColumn('string', 'Gender');
    data.addColumn('number', 'Students');
    data.addColumn({role: 'style'});

    data.addRows([
      ['Male', genderMale, '#9fc5e8'],
      ['Female', genderFemale, '#d5a6bd'],
      ['Non-binary', genderNB, '#b7b7b7']
    ]);

    var options = {
      legend: {position: "none"},
      animation: {
        duration: 1000,
        easing: 'in'
      }
    };

    var chart = new google.visualization.ColumnChart(document.getElementById('genderChart'));
    chart.draw(data, options);

    updateData();

    function updateData() {
      var genderMale = Math.random() * 100;
      var genderFemale = Math.random() * 100;
      var genderNB = Math.random() * 100;
      chart.draw(data, options);
      setTimeout(updateData, 100);
    }
  }

</script>
</body>
</html>

解决方案 #

问题1：从表格拉取数据到DataTable

scriptlets仅在页面加载时执行一次，无法支持动态更新，因此最优方案是使用google.script.run调用服务器端Apps Script函数，实时获取表格数据。

核心逻辑：

• 编写服务器端函数读取表格数据并统计（比如各性别的学生数量）
• 客户端通过google.script.run.withSuccessHandler()获取数据，再填充到DataTable中

问题2：自动更新图表数据

使用setInterval定时触发数据获取和图表更新流程，每次拿到新数据后，直接修改现有DataTable的行数据，再调用chart.draw()结合Google Charts的动画配置实现平滑过渡，无需刷新侧边栏。

完整实现代码 #

1. 侧边栏HTML文件（sidebar.html）

<!DOCTYPE html>
<html>
<head>
<base target="_top">
  <script src="https://www.gstatic.com/charts/loader.js"></script>
</head>

<body>
  <div id="genderChart" style="width: 300px; height: 250px; margin: 5px;"></div>

<script>
  google.charts.load('current', {'packages':['corechart']});
  google.charts.setOnLoadCallback(initChart);

  let chart;
  let dataTable;
  const updateInterval = 3000; // 每3秒更新一次，可根据需求调整

  function initChart() {
    // 初始化DataTable结构
    dataTable = new google.visualization.DataTable();
    dataTable.addColumn('string', 'Gender');
    dataTable.addColumn('number', 'Students');
    dataTable.addColumn({role: 'style'});

    // 创建图表实例并启动更新
    chart = new google.visualization.ColumnChart(document.getElementById('genderChart'));
    fetchAndUpdateChart();
    setInterval(fetchAndUpdateChart, updateInterval);
  }

  function fetchAndUpdateChart() {
    // 调用服务器端函数获取实时数据
    google.script.run
      .withSuccessHandler(updateChartData)
      .getGenderStudentCounts();
  }

  function updateChartData(genderData) {
    // 清空旧数据，添加新数据
    dataTable.removeRows(0, dataTable.getNumberOfRows());
    dataTable.addRows(genderData);

    // 重新绘制图表（带动画效果）
    chart.draw(dataTable, {
      legend: {position: "none"},
      animation: {
        duration: 1000,
        easing: 'in'
      },
      hAxis: {title: '性别'},
      vAxis: {title: '学生数量', minValue: 0}
    });
  }
</script>
</body>
</html>

2. Apps Script代码文件（Code.gs）

// 打开侧边栏的入口函数
function showGenderChartSidebar() {
  const htmlOutput = HtmlService.createHtmlOutputFromFile('sidebar.html')
    .setTitle('性别统计图表');
  SpreadsheetApp.getUi().showSidebar(htmlOutput);
}

// 从表格获取性别统计数据的服务器端函数
function getGenderStudentCounts() {
  // 替换为你的表格名称和数据列位置
  const targetSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1');
  const allData = targetSheet.getDataRange().getValues();
  
  // 初始化统计计数器
  const genderCounts = {
    'Male': 0,
    'Female': 0,
    'Non-binary': 0
  };

  // 遍历数据统计（跳过第一行表头）
  for (let i = 1; i < allData.length; i++) {
    const gender = allData[i][0]; // 假设A列是性别数据
    if (genderCounts.hasOwnProperty(gender)) {
      genderCounts[gender]++;
    }
  }

  // 转换为Google Charts所需的格式
  return [
    ['Male', genderCounts['Male'], '#9fc5e8'],
    ['Female', genderCounts['Female'], '#d5a6bd'],
    ['Non-binary', genderCounts['Non-binary'], '#b7b7b7']
  ];
}

使用说明 #

1. 在Google Sheets中打开脚本编辑器，新建sidebar.html和Code.gs两个文件，分别粘贴上述代码
2. 修改getGenderStudentCounts函数中的表格名称、数据列位置，匹配你的实际数据结构
3. 运行showGenderChartSidebar函数，即可打开侧边栏查看动态更新的柱状图

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