如何将Google Sheets数据集成到AnyChart Web App并实现公司数据切换

解决方案 #

1. 更新 code.gs 代码

替换原有的 code.gs 内容，添加读取Google Sheets数据、获取公司列表和对应员工技能数据的函数：

function doGet() {
  return HtmlService.createTemplateFromFile('test').evaluate()
    .setTitle('Skill Radar Chart');
}

// 获取所有公司名称
function getCompanies() {
  const sheet = SpreadsheetApp.openById('1TKta0IYzFr8usgWptNQI-gAoa0q9yxVY9eqqO_H2XJI').getSheetByName('Sheet1');
  const headerRow = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
  const companies = [];
  let currentCompany = '';

  for (let i = 1; i < headerRow.length; i++) {
    if (headerRow[i] !== '') {
      currentCompany = headerRow[i];
      if (!companies.includes(currentCompany)) companies.push(currentCompany);
    }
  }
  return companies;
}

// 根据公司名称获取对应技能数据
function getDataForCompany(companyName) {
  const sheet = SpreadsheetApp.openById('1TKta0IYzFr8usgWptNQI-gAoa0q9yxVY9eqqO_H2XJI').getSheetByName('Sheet1');
  const data = sheet.getDataRange().getValues();
  const headerRow = data[0];
  const employeeRow = data[1];
  const skillRows = data.slice(2);

  // 定位目标公司对应的列
  const companyColumns = [];
  let currentCompany = '';
  for (let i = 1; i < headerRow.length; i++) {
    if (headerRow[i] !== '') currentCompany = headerRow[i];
    if (currentCompany === companyName) companyColumns.push(i);
  }

  // 转换为AnyChart所需的数据格式
  const chartData = { header: ['#'], rows: [] };
  // 添加员工名称到表头
  companyColumns.forEach(colIndex => {
    if (employeeRow[colIndex] !== '') chartData.header.push(employeeRow[colIndex]);
  });
  // 添加技能及分数数据
  skillRows.forEach(row => {
    const skillRow = [row[0]];
    companyColumns.forEach(colIndex => skillRow.push(row[colIndex] || 0));
    chartData.rows.push(skillRow);
  });

  return chartData;
}

2. 更新 test.html 代码

替换原有的 test.html 内容，添加下拉菜单、动态加载数据和图表更新逻辑：

<!DOCTYPE html>
<html>
<head>
  <base target="_top">
  <script src="https://cdn.anychart.com/releases/8.11.0/js/anychart-core.min.js"></script>
  <script src="https://cdn.anychart.com/releases/8.11.0/js/anychart-radar.min.js"></script>
  <style>
    html, body {
      width: 100%;
      height: 100%;
      margin: 0;
      padding: 10px;
      box-sizing: border-box;
    }
    #company-select {
      margin-bottom: 10px;
      padding: 8px;
      font-size: 16px;
    }
    #container {
      width: 100%;
      height: calc(100% - 50px);
    }
  </style>
</head>
<body>
  <select id="company-select"></select>
  <div id="container"></div>

  <script>
    let chart; // 全局保存图表实例，避免重复创建

    // 初始化雷达图表
    function initChart(chartData) {
      chart = anychart.radar();
      chart.defaultSeriesType('Spline Area');
      chart.data(chartData);
      chart.palette(['#E5593499', '#9BC53DE6', '#64B5F6BF','#8d64f6']);
      
      // 轴与网格配置
      chart.yAxis().stroke('#545f69');
      chart.yAxis().ticks().stroke('#545f69');
      chart.xGrid().stroke({ color: "#545f69", thickness: 0.5, dash: "10 5" });
      chart.yGrid().palette(['gray 0.05', 'gray 0.025']);
      
      // 刻度范围设置
      chart.yScale().minimum(0);
      chart.yScale().maximum(5);
      chart.yScale().ticks().interval(1);
      
      // 交互与提示框配置
      chart.interactivity().hoverMode('by-x');
      chart.markerPalette(['round']);
      chart.tooltip()
        .displayMode('union')
        .useHtml(true)
        .format(function(){
          return `<span style="color:${this.series.color()}">${this.seriesName}: ${this.value}</span>`;
        });
      
      // 图例与标题
      chart.legend().align('center').position('center-bottom').enabled(true);
      chart.title(`Skill Radar - ${document.getElementById('company-select').value}`);
      
      chart.container('container');
      chart.draw();
    }

    // 更新图表数据
    function updateChart(chartData) {
      chart.data(chartData);
      chart.title(`Skill Radar - ${document.getElementById('company-select').value}`);
      chart.draw();
    }

    // 加载公司列表并填充下拉菜单
    function loadCompanies() {
      google.script.run.withSuccessHandler(companies => {
        const select = document.getElementById('company-select');
        companies.forEach(company => {
          const option = document.createElement('option');
          option.value = company;
          option.textContent = company;
          select.appendChild(option);
        });
        // 默认加载第一个公司的数据
        if (companies.length > 0) loadCompanyData(companies[0]);
      }).getCompanies();
    }

    // 加载指定公司的技能数据
    function loadCompanyData(companyName) {
      google.script.run.withSuccessHandler(chartData => {
        chart ? updateChart(chartData) : initChart(chartData);
      }).getDataForCompany(companyName);
    }

    // 页面初始化
    anychart.onDocumentReady(() => {
      // 绑定下拉菜单切换事件
      document.getElementById('company-select').addEventListener('change', e => {
        loadCompanyData(e.target.value);
      });
      // 加载公司列表
      loadCompanies();
    });
  </script>
</body>
</html>

3. 部署与权限设置

1. 在Google Apps Script编辑器中，点击部署>新部署，选择类型为Web应用
2. 执行权限选择“我自己”，访问权限根据需求选择（如“任何人，甚至匿名”）
3. 部署后获取Web应用链接，即可访问使用
4. 确保目标Google Sheets文件已设置为允许脚本访问，或设置为公开可查看（对应匿名访问场景）

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