请求优化Google Apps Script脚本：增强可视化与HTML适配

1. 修正图表数据适配问题 #

图表无法正确展示数据通常是因为数据源范围固定未适配筛选结果，或未处理隐藏行数据。以下是针对性修复方案：

动态适配数据源 #

避免使用固定单元格范围，改用动态获取可见数据：

function createBarChart(sheet) {
  // 获取工作表全部数据，过滤掉筛选隐藏的行
  const allData = sheet.getDataRange().getValues();
  const visibleData = allData.filter((row, index) => !sheet.isRowHiddenByFilter(index + 1));

  if (visibleData.length < 2) { // 至少需要表头+1行数据
    SpreadsheetApp.getUi().alert('数据不足，无法生成图表');
    return;
  }

  // 将可见数据写入临时区域（避免原数据干扰）
  const tempRange = sheet.getRange(5, 1, visibleData.length, visibleData[0].length);
  tempRange.setValues(visibleData);

  // 创建适配数据的柱状图
  const chart = sheet.newChart()
    .setChartType(Charts.ChartType.BAR)
    .addRange(tempRange)
    .setPosition(5 + visibleData.length, 1, 0, 0) // 放在数据下方
    .setOption('title', 'AE数据统计')
    .setOption('hAxis', { title: '指标', textStyle: { fontSize: 10 } })
    .setOption('vAxis', { title: '数值', minValue: 0 })
    .setOption('legend', { position: 'top' })
    .setOption('chartArea', { width: '75%', height: '65%' })
    .build();
  sheet.insertChart(chart);
}

2. 替换原生UI为HTML交互界面 #

用HTML构建更友好的交互界面，替代原生弹窗输入：

第一步：创建HTML界面文件 #

在脚本编辑器中新建index.html文件，内容如下：

<!DOCTYPE html>
<html>
<head>
  <base target="_top">
  <style>
    .container { padding: 20px; max-width: 400px; margin: 0 auto; }
    .form-group { margin-bottom: 16px; }
    label { display: block; margin-bottom: 6px; font-weight: 500; }
    input { width: 100%; padding: 8px; box-sizing: border-box; border: 1px solid #ddd; border-radius: 4px; }
    button { width: 100%; padding: 10px; background: #2196F3; color: white; border: none; border-radius: 4px; cursor: pointer; }
    button:hover { background: #1976D2; }
    .msg { margin-top: 12px; padding: 10px; border-radius: 4px; }
    .success { background: #E8F5E9; color: #2E7D32; }
    .error { background: #FFEBEE; color: #C62828; }
  </style>
</head>
<body>
  <div class="container">
    <h3>AE数据查询</h3>
    <div class="form-group">
      <label for="name">姓名</label>
      <input type="text" id="name" placeholder="请输入姓名" required>
    </div>
    <div class="form-group">
      <label for="year">年份</label>
      <input type="number" id="year" placeholder="请输入年份" min="2000" max="2099" required>
    </div>
    <button onclick="submitQuery()">查询并生成报表</button>
    <div id="message" class="msg" style="display:none;"></div>
  </div>

  <script>
    function submitQuery() {
      const name = document.getElementById('name').value.trim();
      const year = document.getElementById('year').value;
      const msgEl = document.getElementById('message');

      if (!name || !year) {
        showMsg('请填写完整信息', 'error');
        return;
      }

      google.script.run
        .withSuccessHandler(res => {
          if (res.success) {
            showMsg(`报表已生成：<a href="${res.url}" target="_blank">点击查看</a>`, 'success');
          } else {
            showMsg(res.error, 'error');
          }
        })
        .withFailureHandler(err => showMsg(`查询失败：${err.message}`, 'error'))
        .fetchAEData(name, year);
    }

    function showMsg(text, type) {
      const msgEl = document.getElementById('message');
      msgEl.innerHTML = text;
      msgEl.className = `msg ${type}`;
      msgEl.style.display = 'block';
    }
  </script>
</body>
</html>

第二步：修改后端脚本 #

更新原showAEdata()为带参数的fetchAEData()，并添加UI触发函数：

// 后端数据处理函数
function fetchAEData(name, year) {
  try {
    const ss = SpreadsheetApp.getActiveSpreadsheet();
    const dbSheet = ss.getSheetByName('Monitoring (Database)');
    if (!dbSheet) throw new Error('未找到"Monitoring (Database)"工作表');

    // 获取表头与数据
    const [header, ...rows] = dbSheet.getDataRange().getValues();
    const nameColIndex = header.indexOf('姓名');
    const yearColIndex = header.indexOf('年份');
    if (nameColIndex === -1 || yearColIndex === -1) throw new Error('数据表头格式错误');

    // 筛选数据
    const filteredRows = rows.filter(row => row[nameColIndex] === name && row[yearColIndex].toString() === year.toString());
    if (filteredRows.length === 0) throw new Error('未找到匹配的记录');

    // 创建/更新结果工作表
    const sheetName = `${name}-${year}数据报表`;
    let resultSheet = ss.getSheetByName(sheetName);
    if (resultSheet) ss.deleteSheet(resultSheet);
    resultSheet = ss.insertSheet(sheetName);

    // 写入数据
    resultSheet.getRange(1, 1, 1, header.length).setValues([header]);
    resultSheet.getRange(2, 1, filteredRows.length, header.length).setValues(filteredRows);

    // 生成图表与优化样式
    createBarChart(resultSheet);
    optimizeVisualStyle(resultSheet);

    return {
      success: true,
      url: `${ss.getUrl()}#gid=${resultSheet.getSheetId()}`
    };
  } catch (err) {
    return { success: false, error: err.message };
  }
}

// 打开HTML界面的触发函数
function showQueryUI() {
  const html = HtmlService.createHtmlOutputFromFile('index.html')
    .setWidth(450)
    .setHeight(320);
  SpreadsheetApp.getUi().showModalDialog(html, 'AE数据查询工具');
}

// 新增菜单，方便用户调用
function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('AE工具')
    .addItem('打开数据查询界面', 'showQueryUI')
    .addToUi();
}

3. 优化可视化呈现效果 #

从工作表格式与图表样式两方面提升视觉体验：

function optimizeVisualStyle(sheet) {
  // 自动调整列宽
  sheet.autoResizeColumns(1, sheet.getLastColumn());

  // 设置表头样式
  const headerRange = sheet.getRange(1, 1, 1, sheet.getLastColumn());
  headerRange.setBackground('#F5F5F5')
    .setFontWeight('bold')
    .setHorizontalAlignment('center')
    .setBorder(true, true, true, true, true, true);

  // 优化图表样式（如果存在图表）
  const charts = sheet.getCharts();
  if (charts.length > 0) {
    const updatedChart = charts[0].modify()
      .setOption('titleTextStyle', { fontSize: 14, bold: true })
      .setOption('series', {
        0: { color: '#2196F3' },
        1: { color: '#FFC107' },
        2: { color: '#4CAF50' }
      }) // 自定义系列颜色
      .setOption('annotations', { textStyle: { fontSize: 9 } }) // 显示数据标签
      .setOption('legend', { textStyle: { fontSize: 10 } })
      .build();
    sheet.updateChart(updatedChart);
  }
}

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