如何通过Google Apps Script获取Google Sheet数据并保留原格式？

你当前使用的Sheets.Spreadsheets.Values.get方法只能提取单元格的纯文本内容，会丢失所有富文本结构（比如有序列表的层级、HTML标签、换行等），所以才会出现列表项直接拼接在段落后的情况。以下是两种可行的解决方案：

方案一：基于原生SpreadsheetApp处理富文本转HTML #

适合Sheet内是通过富文本编辑器创建的有序列表/段落格式，手动解析文本结构生成HTML：

修改code.gs #

function getValues() {
  var spreadSheetId = "SPREADSHEETID"; // 替换为你的表格ID
  var sheet = SpreadsheetApp.openById(spreadSheetId).getSheetByName("Query");
  var range = sheet.getRange("A1:B1");
  var richTextValues = range.getRichTextValues();
  
  // 处理目标单元格（B1）的内容
  var cellText = richTextValues[0][1].getText();
  var lines = cellText.split("\n");
  var html = "";
  var inOrderedList = false;

  lines.forEach(line => {
    line = line.trim();
    if (!line) return;

    // 识别有序列表项（以数字+点开头）
    if (/^\d+\.\s?/.test(line)) {
      if (!inOrderedList) {
        html += "<ol>";
        inOrderedList = true;
      }
      var itemContent = line.replace(/^\d+\.\s?/, "");
      html += `<li>${itemContent}</li>`;
    } else {
      if (inOrderedList) {
        html += "</ol>";
        inOrderedList = false;
      }
      html += `<p>${line}</p>`;
    }
  });

  // 关闭未结束的列表
  if (inOrderedList) html += "</ol>";
  // 添加原有的分割线
  html += "<hr />";

  return [[richTextValues[0][0].getText(), html]];
}

function doGet() {
  return HtmlService
      .createTemplateFromFile('Index')
      .evaluate()
      .setSandboxMode(HtmlService.SandboxMode.IFRAME);
}

修改Index.html #

使用<?!= ?>替代<?= ?>，避免HTML标签被转义：

<body>  
  <? var temp = getValues(); ?>  
  <div class="row">
    <div class="col-10">
      <?!= temp[0][1] ?>
    </div>
  </div>
</body>

方案二：使用Sheets API高级服务获取格式信息 #

适合复杂格式场景，通过API直接获取单元格的格式元数据：

1. 先在Apps Script编辑器中启用Sheets API：点击左侧「服务」→「添加服务」→ 找到「Sheets API」并启用。

2. 修改code.gs：

function getValues() {
  var spreadSheetId = "SPREADSHEETID";
  var dataRange = "Query!A1:B1";

  // 获取带格式的单元格数据
  var sheetData = Sheets.Spreadsheets.get(spreadSheetId, {
    ranges: [dataRange],
    fields: "sheets(data(rowData(values(textRun/content,formattedValue))))"
  });

  var targetCell = sheetData.sheets[0].data[0].rowData[0].values[1];
  var textRuns = targetCell.textRun.content.split("\n");
  var html = "";
  var inOrderedList = false;

  textRuns.forEach(run => {
    run = run.trim();
    if (!run) return;

    if (/^\d+\.\s?/.test(run)) {
      if (!inOrderedList) {
        html += "<ol>";
        inOrderedList = true;
      }
      var itemContent = run.replace(/^\d+\.\s?/, "");
      html += `<li>${itemContent}</li>`;
    } else {
      if (inOrderedList) {
        html += "</ol>";
        inOrderedList = false;
      }
      html += `<p>${run}</p>`;
    }
  });

  if (inOrderedList) html += "</ol>";
  html += "<hr />";

  return [[sheetData.sheets[0].data[0].rowData[0].values[0].formattedValue, html]];
}

function doGet() {
  return HtmlService
      .createTemplateFromFile('Index')
      .evaluate()
      .setSandboxMode(HtmlService.SandboxMode.IFRAME);
}

3. Index.html修改同方案一。

特殊情况：单元格内直接输入HTML代码 #

如果你的Sheet单元格里是直接写入的完整HTML代码，只需还原转义的标签并保留换行：

function getValues() {
  var spreadSheetId = "SPREADSHEETID";
  var dataRange = "Query!A1:B1";
  var range = Sheets.Spreadsheets.Values.get(spreadSheetId, dataRange, {valueRenderOption: "FORMATTED_VALUE"});
  
  // 还原HTML标签并转换换行
  var htmlContent = range.values[0][1]
    .replace(/&lt;/g, "<")
    .replace(/&gt;/g, ">")
    .replace(/\n/g, "<br>");

  return [[range.values[0][0], htmlContent]];
}

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