如何用Google Script和JavaScript为谷歌表格每行生成HTML无序列表

现有基于Google表格生成数据表格的Google Script代码，需修改为为表格每行生成包含对应字段值的HTML无序列表，预期效果如下：

<ul>
<li><b>Vendor Type:</b> "Vendor Type" </li>
<li><b>Location:</b> "Location"</li>
<li><b>Pronouns:</b> "Pronouns "</li>
<li><b>LGBTQ Status:</b> "LGBTQ Status"</li>
</ul>

当前已有可正常运行的生成数据表格代码，但尝试ChatGPT生成的代码部署后页面空白，需可行的修改方案。

当前可运行的代码 #

HTML代码 #

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
    <!--INCLUDE REQUIRED EXTERNAL JAVASCRIPT AND CSS LIBRARIES-->
    <script src="https://code.jquery.com/jquery-3.5.1.js"></script>
    <script src="https://cdn.datatables.net/1.10.23/js/jquery.dataTables.min.js"></script>
    <script src="https://cdn.datatables.net/1.10.23/js/dataTables.bootstrap4.min.js"></script>
    <link rel="stylesheet" type="text/css" href="https://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/4.5.2/css/bootstrap.css">
    <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.23/css/dataTables.bootstrap4.min.css">
 
    <?!= include('JavaScript'); ?> <!--INCLUDE JavaScript.html FILE-->
  </head>
 
  <body>
    <div class="container">
      <br>
      <div class="row">
        <table id="data-table" class="table table-striped table-sm table-hover table-bordered">
          <!-- TABLE DATA IS ADDED BY THE showData() JAVASCRIPT FUNCTION ABOVE -->
        </table>
      </div>
    </div>  
  </body>
</html>

JavaScript代码 #

<script>
  /*
  *THIS FUNCTION CALLS THE getData() FUNCTION IN THE Code.gs FILE, 
  *AND PASS RETURNED DATA TO showData() FUNCTION
  */
  google.script.run.withSuccessHandler(showData).getData();
 
  //THIS FUNCTION GENERATE THE DATA TABLE FROM THE DATA ARRAY
  function showData(dataArray){
    $(document).ready(function(){
      $('#data-table').DataTable({
        data: dataArray,
        //CHANGE THE TABLE HEADINGS BELOW TO MATCH WITH YOUR SELECTED DATA RANGE
        columns: [
          {"title":"Business Name"},
          {"title":"Vendor Type"},
          {"title":"Location"},
          {"title":"Pronouns"},
          {"title":"Website URL"},
          {"title":"Facebook URL"},
          {"title":"Instagram @"},
          {"title":"TikTok URL"},
          {"title":"Pinterest URL"},
          {"title":"Short Bio"},
          {"title":"LGBTQ Status"},
          {"title":"Email Address"}
        ]
      });
    });
  }
</script> 

Code.gs代码 #

function doGet() {
  return HtmlService.createTemplateFromFile('Index').evaluate();
}
 
//GET DATA FROM GOOGLE SHEET AND RETURN AS AN ARRAY
function getData(){
  var spreadSheetId = ""; //CHANGE
  var dataRange     = "Form Responses 1!B2:M"; //CHANGE
 
  var range   = Sheets.Spreadsheets.Values.get(spreadSheetId,dataRange);
  var values  = range.values;
 
  return values;
}
 
//INCLUDE JAVASCRIPT AND CSS FILES
function include(filename) {
  return HtmlService.createHtmlOutputFromFile(filename)
      .getContent();
}

尝试过的无效代码 #

以下ChatGPT生成的代码部署后页面空白：

function generateHTML(row) {
  return `
    <li><b>Vendor Type:</b> ${row[0]}</li>
    <li><b>Location:</b> ${row[1]}</li>
    <li><b>Pronouns:</b> ${row[2]}</li>
    <li><b>LGBTQ Status:</b> ${row[3]}</li>
  `;
}

function generateHTMLList() {
  const spreadsheetId = 'your_spreadsheet_id';
  const sheetName = 'Sheet1';
  const range = `${sheetName}!A2:D`;

  const sheet = SpreadsheetApp.openById(spreadsheetId).getSheetByName(sheetName);
  const data = sheet.getRange(range).getValues();

  if (data.length > 0) {
    const htmlListItems = data.map(row => generateHTML(row)).join('');
    const htmlOutput = `<ul>${htmlListItems}</ul>`;
    Logger.log(htmlOutput);
    // You can use the HTML output as needed, e.g., to display it in a dialog or send it as an email
  } else {
    Logger.log('No data found.');
  }
}

可行解决方案 #

基于现有可运行代码修改，无需改动Code.gs，仅调整前端HTML和JavaScript逻辑即可：

1. 修改HTML代码 #

移除DataTables相关依赖（不再需要表格组件），将表格容器替换为列表容器：

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
    <!--仅保留jQuery和Bootstrap样式-->
    <script src="https://code.jquery.com/jquery-3.5.1.js"></script>
    <link rel="stylesheet" type="text/css" href="https://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/4.5.2/css/bootstrap.css">
 
    <?!= include('JavaScript'); ?>
  </head>
 
  <body>
    <div class="container">
      <br>
      <div class="row" id="vendor-lists">
        <!-- 列表将通过JavaScript生成并插入此处 -->
      </div>
    </div>  
  </body>
</html>

2. 修改JavaScript代码 #

重写showData函数，遍历返回的数据数组，为每行生成对应的<ul>列表，并插入到页面容器中：

<script>
  google.script.run.withSuccessHandler(showData).getData();
 
  function showData(dataArray){
    $(document).ready(function(){
      const container = $('#vendor-lists');
      // 遍历每行数据
      dataArray.forEach(row => {
        // 根据数据索引匹配对应字段：参考原columns顺序
        const vendorType = row[1] || '';
        const location = row[2] || '';
        const pronouns = row[3] || '';
        const lgbtqStatus = row[10] || '';
        
        // 生成单条数据的HTML列表
        const listHtml = `
          <div class="col-md-6 mb-4">
            <ul class="list-group">
              <li class="list-group-item"><b>Vendor Type:</b> "${vendorType}"</li>
              <li class="list-group-item"><b>Location:</b> "${location}"</li>
              <li class="list-group-item"><b>Pronouns:</b> "${pronouns}"</li>
              <li class="list-group-item"><b>LGBTQ Status:</b> "${lgbtqStatus}"</li>
            </ul>
          </div>
        `;
        // 将列表添加到容器中
        container.append(listHtml);
      });
    });
  }
</script>

修改说明 #

• 保留了原有的getData数据获取逻辑，确保数据来源正确
• 修正了ChatGPT代码中字段索引错误的问题（原数据中Vendor Type对应row[1]而非row[0]）
• 使用Bootstrap的list-group样式优化列表展示，同时添加了响应式布局（每行2列）
• 增加了空值处理（|| ''），避免字段为空时显示undefined

内容的提问来源于stack exchange，提问作者vphoto-so