从Spreadsheet自定义列中检索值并将其放入模板Document [英] Retrieve values from Spreadsheet custom columns and put to template Document

问题描述

此脚本在我的电子表格中检索所有行都相同的"A"和"B"列的所有值，并检索"C"，"D"和"E"列的所有值所有行的变量.
该脚本通过将占位符替换为Spreadsheet中的值来创建一个Google文档.
占位符用％括起来.

如何在脚本中设置更改后的列，以及如何设置自定义列?

This script, in my Spreadsheet, retrieve all values of columns of "A" and "B" that are the same for all rows and retrieve all values of columns of "C","D" and "E" that are variables for all the rows.
The script creates one Google Document by replacing the placeholder by the values from Spreadsheet.
The placeholder is enclosed by %.

How to set, in the script, the columns if they change and so to set custom columns?

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());
}

推荐答案

您要执行以下操作:

• 将某些行复制到文档模板.
• 仅复制某些列(您将手动指定它们的索引).
• 某些列是固定数据"，将替换文档顶部的某些占位符.
• 某些列是变量数据"，并且将使用此类数据创建表.
• 某些变量数据"是需要格式化的日期.
• 数据被复制到文档模板的副本中，并复制到指定的文件夹中.

根据要选择要复制到模板的行的方式，可以采用两种方法:

Depending on how you want to select the rows to copy to the template, there are two methods you can follow:

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

This method will create the document faster, but you have to manually select all rows you want to copy to the template, one by one (excluding the headers row).

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:复制已过滤的行:

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

Method #2: Copying filtered rows:

In this method, the execution will take more time, specially if the sheet has many rows, but there is no need no manually select the rows, the filter handles this.

该脚本几乎类似于方法1中的脚本，但是您必须替换此行:

The script is almost like the one from method #1, but you would have to replace this line:

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 ).
• 您应该在代码中手动指定 templateGoogleDocumentID 和 destinationFolderID (检查内联注释).
• 工作表页眉应与文档中的占位符值匹配，以使其正常工作，包括大小写(例如，在您共享的副本中，占位符的编写方式类似于 Codice FIscale ，而不是 Codice Fiscale ).
• 不是根据任何占位符而是根据其在工作表中的相对位置来复制表列.标头也被复制.
• 我认为最好用正确的尺寸创建一个新表并删除旧的表，而不是检查表的尺寸是否与数据的尺寸匹配.

Notes:

• You should manually define the indexes of (1) fixed columns (fixedColumns), (2) variable columns (variableColumns) and (3) variable columns with date to be formatted (formatDateColumns).
• You should manually specify the templateGoogleDocumentID and the destinationFolderID in your code (check inline comments).
• The sheet headers should match the placeholder values in the document for this to work, including case (in the copy you shared, for example, the placeholder was written like Codice FIscale, instead of Codice Fiscale).
• The table columns are not getting copied according to any placeholder, but according to their relative position in the sheet. Headers are being copied too.
• Instead of checking whether the table dimensions match the data dimensions, I think it's better to create a new table with the right dimensions and remove the old one.

这篇关于从Spreadsheet自定义列中检索值并将其放入模板Document的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！