Google Apps Script无法替换Google Docs表格内值的问题求助

我编写了一段Google Apps Script，希望将Google Sheet中的数据替换到Google Docs模板中。脚本能够识别变量并基于表格信息生成唯一文件名，但无法修改模板内表格中的占位值，仅能成功修改文件名。

原代码 #

function createDocument() {
  var headers = Sheets.Spreadsheets.Values.get('1yugsklotVyaj2ecZT0ZJip7XyGmEq1zxaK_6tcA1pa4', 'B1:AD1');
  var tactics = Sheets.Spreadsheets.Values.get('1yugsklotVyaj2ecZT0ZJip7XyGmEq1zxaK_6tcA1pa4', 'A2:AD2');
  var templateId = '1xcIm53z5FAX4pJLCoJVdDYDgVhsRkXO3uqZgsNDfO_w';

  for(var i = 0; i < tactics.values.length; i++){

    var Brand = tactics.values[i][1];
    var Model = tactics.values[i][2];
    var Size = tactics.values[i][3];
    var Groupset = tactics.values[i][5];
    var Price = tactics.values[i][10];
    var Frame = tactics.values[i][11];
    var Fork = tactics.values[i][12];
    var Gears = tactics.values[i][13];
    var Cassette = tactics.values[i][14];
    var BrakeType = tactics.values[i][15];
    var Handlebar = tactics.values[i][16];
    var Seatpost = tactics.values[i][17];
    var Saddle = tactics.values[i][18];
    var WheelMaterial = tactics.values[i][19];
    var RimType = tactics.values[i][20];

   //复制模板文件
    var documentId = DriveApp.getFileById(templateId).makeCopy().getId();

    //重命名复制后的文件
    DriveApp.getFileById(documentId).setName( Brand + Model + ' Spec Card');

    //获取文档正文
    var body = DocumentApp.openById(documentId).getBody();

    body.replaceText('##[Brand]##',Brand)
    body.replaceText('##[Model]##', Model)
    body.replaceText('##[Size]##', Size) 
    body.replaceText('##[Groupset]##', Groupset)
    body.replaceText('##[Price]##', Price)
    body.replaceText('##[Frame]##', Frame)
    body.replaceText('##[Fork]##', Fork)
    body.replaceText('##[Gears]##', Gears)
    body.replaceText('##[Cassette]##', Cassette)
    body.replaceText('##[Brake Type]##', BrakeType)
    body.replaceText('##[Handlebar]##', Handlebar)
    body.replaceText('##[Seatpost]##', Seatpost)
    body.replaceText('##[Saddle]##', Saddle)
    body.replaceText('##[Wheel Material]##', WheelMaterial)
    body.replaceText('##[RimType]##', RimType)
  }
 
}

Google Docs的Body.replaceText()方法不会自动处理表格单元格内的文本，必须单独遍历所有表格、行和单元格来执行替换操作。修改后的代码如下：

function createDocument() {
  var headers = Sheets.Spreadsheets.Values.get('1yugsklotVyaj2ecZT0ZJip7XyGmEq1zxaK_6tcA1pa4', 'B1:AD1');
  var tactics = Sheets.Spreadsheets.Values.get('1yugsklotVyaj2ecZT0ZJip7XyGmEq1zxaK_6tcA1pa4', 'A2:AD2');
  var templateId = '1xcIm53z5FAX4pJLCoJVdDYDgVhsRkXO3uqZgsNDfO_w';

  for(var i = 0; i < tactics.values.length; i++){

    var Brand = tactics.values[i][1];
    var Model = tactics.values[i][2];
    var Size = tactics.values[i][3];
    var Groupset = tactics.values[i][5];
    var Price = tactics.values[i][10];
    var Frame = tactics.values[i][11];
    var Fork = tactics.values[i][12];
    var Gears = tactics.values[i][13];
    var Cassette = tactics.values[i][14];
    var BrakeType = tactics.values[i][15];
    var Handlebar = tactics.values[i][16];
    var Seatpost = tactics.values[i][17];
    var Saddle = tactics.values[i][18];
    var WheelMaterial = tactics.values[i][19];
    var RimType = tactics.values[i][20];

    //复制模板文件
    var documentId = DriveApp.getFileById(templateId).makeCopy().getId();
    //重命名文件
    DriveApp.getFileById(documentId).setName(Brand + Model + ' Spec Card');
    //打开文档
    var doc = DocumentApp.openById(documentId);
    var body = doc.getBody();

    //替换正文非表格区域的占位符
    body.replaceText('##[Brand]##', Brand);
    body.replaceText('##[Model]##', Model);
    body.replaceText('##[Size]##', Size);
    body.replaceText('##[Groupset]##', Groupset);
    body.replaceText('##[Price]##', Price);
    body.replaceText('##[Frame]##', Frame);
    body.replaceText('##[Fork]##', Fork);
    body.replaceText('##[Gears]##', Gears);
    body.replaceText('##[Cassette]##', Cassette);
    body.replaceText('##[Brake Type]##', BrakeType);
    body.replaceText('##[Handlebar]##', Handlebar);
    body.replaceText('##[Seatpost]##', Seatpost);
    body.replaceText('##[Saddle]##', Saddle);
    body.replaceText('##[Wheel Material]##', WheelMaterial);
    body.replaceText('##[RimType]##', RimType);

    //遍历所有表格，替换单元格内的占位符
    var tables = body.getTables();
    for (var t = 0; t < tables.length; t++) {
      var table = tables[t];
      var numRows = table.getNumRows();
      for (var r = 0; r < numRows; r++) {
        var row = table.getRow(r);
        var numCells = row.getNumCells();
        for (var c = 0; c < numCells; c++) {
          var cell = row.getCell(c);
          //替换当前单元格内的所有占位符
          cell.replaceText('##[Brand]##', Brand);
          cell.replaceText('##[Model]##', Model);
          cell.replaceText('##[Size]##', Size);
          cell.replaceText('##[Groupset]##', Groupset);
          cell.replaceText('##[Price]##', Price);
          cell.replaceText('##[Frame]##', Frame);
          cell.replaceText('##[Fork]##', Fork);
          cell.replaceText('##[Gears]##', Gears);
          cell.replaceText('##[Cassette]##', Cassette);
          cell.replaceText('##[Brake Type]##', BrakeType);
          cell.replaceText('##[Handlebar]##', Handlebar);
          cell.replaceText('##[Seatpost]##', Seatpost);
          cell.replaceText('##[Saddle]##', Saddle);
          cell.replaceText('##[Wheel Material]##', WheelMaterial);
          cell.replaceText('##[RimType]##', RimType);
        }
      }
    }

    //保存并关闭文档
    doc.saveAndClose();
  }
}

关键修改说明 #

1. 新增表格遍历逻辑：通过body.getTables()获取文档内所有表格，逐层遍历行和单元格，调用cell.replaceText()处理表格内的占位符
2. 添加doc.saveAndClose()确保所有修改被正确保存

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