如何通用化Google Apps脚本，通过URL参数读取Sheets数据展示为DataTable

我们可以通过读取URL查询参数、模板变量传值、动态生成配置的方式完成通用化改造，改造后你可以通过如下格式的URL指定配置：
https://script.google.com/macros/s/你的部署ID/exec?spreadsheetId=表格ID&range=数据范围&headers=列1,列2,列3,列4

function doGet(e) {
  // 读取URL参数存入模板变量
  const template = HtmlService.createTemplateFromFile('index');
  template.queryParams = {
    spreadsheetId: e.parameter.spreadsheetId || '',
    range: e.parameter.range || '',
    headers: e.parameter.headers || ''
  };
  return template
    .evaluate()
    .setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL);
}
 
// 改造为接收动态参数
function getData(spreadSheetId, dataRange){
  if(!spreadSheetId || !dataRange) throw new Error('缺少表格ID或数据范围参数');
  const range = Sheets.Spreadsheets.Values.get(spreadSheetId, dataRange);
  return range.values;
}
 
function include(filename) {
  return HtmlService.createHtmlOutputFromFile(filename)
         .getContent();
}

新增全局参数暴露逻辑，把模板拿到的URL参数传递给前端JS：

<script src="https://code.jquery.com/jquery-3.6.0.min.js" integrity="sha256-/xUj+3OJU5yExlq6GSYGSHk7tPXikynS7ogEvDej/m4=" crossorigin="anonymous"></script>
<script type="text/javascript" src="https://cdn.datatables.net/1.11.3/js/jquery.dataTables.min.js"></script>
<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.11.3/css/jquery.dataTables.min.css">
<link rel="stylesheet" type="text/css" href="https://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/5.0.1/css/bootstrap.min.css"/>

<!-- 把URL参数暴露给全局JS -->
<script>
  window.queryParams = <?!= JSON.stringify(queryParams) ?>;
</script>

<?!= include('javascript.html'); ?> <!-- include javascript file -->

<table id="data-table" class="table table-striped table-sm table-hover table-bordered">
  <!-- Table data is added by the showData() function -->
</table>

动态生成列配置，调用后端接口时传入动态参数：

<script>
  $(document).ready(function(){
    // 参数合法性校验
    if(!window.queryParams.spreadsheetId || !window.queryParams.range || !window.queryParams.headers) {
      alert('缺少必要配置，请检查URL参数是否包含spreadsheetId、range、headers');
      return;
    }
    // 拆分表头参数，动态生成列配置
    const headerArr = window.queryParams.headers.split(',');
    const columns = headerArr.map(title => ({title}));

    // 调用后端接口传入动态参数
    google.script.run
      .withSuccessHandler(dataArray => {
        $('#data-table').DataTable({
          data: dataArray,
          order: [[0,"asc"]],
          autoWidth: true,
          columns: columns
        });
      })
      .withFailureHandler(err => alert('加载数据失败：' + err.message))
      .getData(window.queryParams.spreadsheetId, window.queryParams.range);
  });
</script>

• 部署时需要将访问权限设置为「任何人，甚至匿名用户」，否则无权限访问服务
• 请确保你的Apps Script服务账号对目标Google Sheet有至少只读权限
• headers参数的列数需要和你指定range的列数一致，否则会出现列错位问题

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