google-apps-script - 从电子表格自定义列中检索值并放入模板文档

Question

这个脚本，在我的电子表格中，检索所有行的“A”和“B”列的所有值，并检索“C”、“D”和“E”列的所有值，这些值是所有行的变量行。
该脚本通过将占位符替换为电子表格中的值来创建一个 Google 文档。
占位符用 % 括起来。

如果它们发生变化，如何在脚本中设置列，以便设置自定义列？

function myFunction() {
  var templateGoogleDocumentID = "###";  // Please set the template Google Document ID.

  // 1. Retrieve values from Spreadsheet.
  var activeSheet = SpreadsheetApp.getActiveSheet();
  var values = activeSheet.getDataRange().getValues();

  // 2. Create an object for putting to Google Document.
  var object = {headers: {}, table: {}};
  var headerRow = values.shift();
  object.headers[headerRow[0]] = values[0][0];
  object.headers[headerRow[1]] = Utilities.formatDate(values[0][1], Session.getScriptTimeZone(), "yyyy/MM/dd");
  object.table = values.map(r => r.splice(2, 5));

  // 3. Copy a template Google Document.
  var copiedTemplateDoc = DriveApp.getFileById(templateGoogleDocumentID).makeCopy();
  var docId = copiedTemplateDoc.getId();

  // 4. Put the header values to the copied Document using the object.
  var doc = DocumentApp.openById(docId);
  var body = doc.getBody();
  Object.keys(object.headers).forEach(h => body.replaceText(`%${h.toLowerCase()}%`, object.headers[h]));

  // 5. Put the table values using the object.
  // If the table rows of Google Document are less than that of Spreadsheet, the rows are added.
  var table = body.getTables()[0];
  var r = object.table.length - table.getNumRows();
  if (r > 0) {
    for (var i = 0; i < r; i++) {
      var tr = table.appendTableRow();
      for (var j = 0; j < 3; j++) {
        tr.appendTableCell();
      }
    }
  }
  object.table.forEach((row, i) => (row.forEach((col, j) => (table.getCell(i, j).setText(col)))));
  doc.saveAndClose();

  // If you want to export the Google Document as PDF file, please use the following script.
  // var newFile = DriveApp.createFile(doc.getBlob());
}

标签： google-apps-scriptgoogle-sheets

Answer 1

您想要执行以下操作：

• 将某些行复制到文档模板。
• 仅复制一些列（您将手动指定它们的索引）。
• 有些列是“固定数据”，将替换文档顶部的一些占位符。
• 有些列是“可变数据”，将使用这些数据创建一个表。
• 一些“可变数据”是需要格式化的日期。
• 数据被复制到文档模板的副本中，并复制到指定的文件夹中。

根据您希望如何选择要复制到模板的行，您可以采用两种方法：

方法#1：复制选定的行：

此方法将更快地创建文档，但您必须手动选择要复制到模板的所有行，一一（不包括标题行）。

function exportSelectedRows() {
  var templateGoogleDocumentID = "#########";  // Please set the template Google Document ID.
  var destinationFolderID = "#########"; // Please set the destination folder ID
  var activeSheet = SpreadsheetApp.getActiveSheet();
  var headers = activeSheet.getRange(1, 1, 1, activeSheet.getLastRow()).getValues()[0]; // Get header values
  var values = activeSheet.getActiveRangeList().getRanges().map(range => range.getValues()).flat(); // Get the values of the selected rows
  values.unshift(headers);
  var fixedColumns = [1, 3, 4, 9]; // Fixed column indexes: B, D, E, J  
  var variableColumns = [10, 11, 12, 13, 21, 33]; // Variable column indexes: K,L,M,N,V,AH
  var fixedValues = removeUnwantedColumns(fixedColumns, values).slice(0, 2); // Retrieve fixed values (only header and one row is needed)
  var varValues = removeUnwantedColumns(variableColumns, values).map(row => row.map(value => { // Retrieve variable values (dates are formatted)
    if (Object.prototype.toString.call(value) === "[object Date]") {
      return Utilities.formatDate(new Date(value), Session.getScriptTimeZone(), "yyyy/MM/dd");
    } else return value;
  }));  
  // Create template copy:
  var folder = DriveApp.getFolderById(destinationFolderID); // Get folder with specified destinationFolderID
  var copiedTemplateDoc = DriveApp.getFileById(templateGoogleDocumentID).makeCopy(folder); // Copy template to destination folder
  var docId = copiedTemplateDoc.getId();
  var doc = DocumentApp.openById(docId);
  var body = doc.getBody();
  for (var i = 0; i < fixedValues[0].length; i++) {
    body.replaceText(`%${fixedValues[0][i]}%`, fixedValues[1][i]); // Replace fixed data with placeholders
  }
  body.appendTable(varValues); // Insert new table to document
  var table = body.getTables()[0];
  table.removeFromParent(); // Remove old table
}

function removeUnwantedColumns(columnsToKeep, values) {
  return values.map(row => row.filter((col, i) => columnsToKeep.includes(i)));
}

方法#2：复制过滤的行：

在这种方法中，执行会花费更多时间，特别是如果工作表有很多行，但不需要手动选择行，过滤器会处理这个问题。

该脚本几乎与方法 #1 中的脚本相似，但您必须替换这一行：

var values = activeSheet.getActiveRangeList().getRanges().map(range => range.getValues()).flat(); // Get the values of the selected rows

有了这个：

var values = activeSheet.getDataRange().getValues().filter((row, i) => !activeSheet.isRowHiddenByFilter(i + 1)); // Remove filtered data

笔记：

• 您应该手动定义 (1) 固定列 ( fixedColumns)、(2) 可变列 ( variableColumns) 和 (3) 带有要格式化的日期的可变列 ( ) 的索引formatDateColumns。
• 您应该在代码中手动指定 thetemplateGoogleDocumentID和 the destinationFolderID（检查内联注释）。
• 工作表标题应与文档中的占位符值匹配，以使其正常工作，包括大小写（例如，在您共享的副本中，占位符的写法是Codice FIscale，而不是Codice Fiscale）。
• 表格列不是根据任何占位符复制的，而是根据它们在工作表中的相对位置来复制的。标题也被复制了。
• 与其检查表维度是否与数据维度匹配，我认为最好创建一个具有正确维度的新表并删除旧表。