使用Google Apps脚本从Google表格数据表复制到Google文档表 [英] Using Google Apps Script to Copy from Google Sheets Data Table to Google Documents Table

问题描述

是否可以在Google文档中编写Google Apps脚本，以从Google表格中检索非空白行的范围并将这些行显示为表格?

我正在寻找一个脚本，用于使用Google Apps脚本(我经验有限)将非空白行的数据从Google表格范围的单元格复制到Google文档中的表中.

I'm looking for a script to copy non-blank rows of data from a Google Sheets range of cells to a table in Google Documents using Google Apps Script (which I've limited experience with).

要复制的数据似乎太大，无法直接链接到Google文档，因此从电子表格到文档的复制粘贴操作不会提示选择链接数据.

The data to be copied seem too large for linking directly to Google Documents so the Copy-Paste action from spreadsheet to document does not prompt a choice for linking the data.

行数也是动态的，因此固定范围不能解决问题.在电子表格中，我使用了SORTN函数并将其设置为显示领带，因此范围的非空白行的大小会发生变化.

Also the number of rows is dynamic so a fixed range wouldn't resolve the problem. In the spreadsheet, I've used the SORTN function and set it to display ties so the size of the non-blank rows of the range changes.

我从以下代码概述开始:

I've started with the following code outline:

function myFunction() {

  // Get Google Sheet data
  var app = SpreadsheetApp;
  var ss = app.openById('SHEET_ID');
  var activeSheet = app.getActiveSpreadsheet();
  var range = activeSheet.getRange("B4:D");

  // Position to paste data in Google Docs
  var doc = DocumentApp.getActiveDocument();
  var body = DocumentApp.getActiveDocument().getBody();

  // Build a table from the array.
  body.appendTable(cells);

}

这是在SE上找到的最接近的问题，但未回答以下查询:复印表数据到文档表.

This is closest question found on SE but doesn't answer this query: Copying Sheet Data to Doc table.

推荐答案

• 您要将表格中的数据范围从Google Spreadsheet复制到Google Document.
  • 您要将表格添加到Google文档中.
  • 在这种情况下，您不想将表格底部的空白行包含在值中.
  • 根据您的情况，您无需将原始电子表格链接到文档"表.
  • You want to copy the data range from Google Spreadsheet to Google Document as the table.
    • You want to append the table to the Google Document.
    • In this case, you don't want to include the empty rows of the bottom of sheet to the values.
    • In your situation, you are not required to link the original Spreadsheet to the table of Document.

    我可以像上面那样理解.如果我的理解是正确的，那么这个答案呢?请认为这只是几个可能的答案之一.

    I could understand like above. If my understanding is correct, how about this answer? Please think of this as just one of several possible answers.

    此示例脚本的流程如下.

    The flow of this sample script is as follows.

    1. 从Google Spreadsheet检索数据范围.
       • 在这种情况下，我使用了 getDataRegion(dimension) .
    1. Retrieve the data range from Google Spreadsheet.
       • In this case, I used getDataRegion(dimension).

    示例脚本:

    从问题脚本中，该脚本假定为Google文档的容器绑定脚本.因此，为了测试该脚本，请将以下脚本放入您共享的Google文档的容器绑定脚本中.

    Sample script:

    From your script in your question, it supposes that the script is the container-bound script of Google Document. So in order to test the script, please put the following script to the container-bound script of Google Document you shared.

    function myFunction() {
      // Get Google Sheet data
      var ss = SpreadsheetApp.openById("###");  // Please set the Spreadsheet ID.
      var sheet = ss.getSheetByName("Sheet1");
      var range = sheet.getRange(4, 2, 1, 5).getDataRegion(SpreadsheetApp.Dimension.ROWS);
      var values = range.getValues();
      var backgroundColors = range.getBackgrounds();
      var styles = range.getTextStyles();
    
      // Position to paste data in Google Docs
      var body = DocumentApp.getActiveDocument().getBody();
      var table = body.appendTable(values);
      table.setBorderWidth(0);
      for (var i = 0; i < table.getNumRows(); i++) {
        for (var j = 0; j < table.getRow(i).getNumCells(); j++) {
          var obj = {};
          obj[DocumentApp.Attribute.BACKGROUND_COLOR] = backgroundColors[i][j];
          obj[DocumentApp.Attribute.FONT_SIZE] = styles[i][j].getFontSize();
          if (styles[i][j].isBold()) {
            obj[DocumentApp.Attribute.BOLD] = true;
          }
          table.getRow(i).getCell(j).setAttributes(obj);
        }
      }
    }
    

    注意:

    • 在此示例脚本中，从共享的电子表格中检索单元格"B4:F"的值.因此，如果要更改此范围，请修改上面的脚本.
    • getDataRegion(dimension)
    • getValues()
    • getBackgrounds()
    • getTextStyles()
    • appendTable()
    • setAttributes(属性)
    • getDataRegion(dimension)
    • getValues()
    • getBackgrounds()
    • getTextStyles()
    • appendTable()
    • setAttributes(attributes)

    可以反映列宽.但是，当列宽设置为Google Document时，即使将单位从Spreadsheet转换为Document，结果也似乎与直接复制并粘贴表格的结果不同.

    It is possible to reflect the column width. But when the column width is set to Google Document, it seems that the result is different from that of the direct copy&paste the table, even when the unit is converted from Spreadsheet to Document.

    在共享的电子表格中，列"B"至"F"的宽度分别为21、100、100、100和100像素.但是发现，当将表从Spreadsheet手动复制到Document时，每个列的宽度都会从原始大小更改.这样，不幸的是，当脚本复制表的列宽时，无法复制手动复制的结果.

    In your shared Spreadsheet, the widths of column "B" to "F" are 21, 100, 100, 100 and 100 pixels, respectively. But it was found that when the table is manually copied from Spreadsheet to Document, each column width is changed from the original size. By this, unfortunately, when the column width of table is copied by the script, the result by manual copy cannot be replicated.

    在以下示例脚本中，Google Spreadsheet的列宽被复制到Google Document的表中.

    At the following sample script, the column width of Google Spreadsheet is copied to the table of Google Document.

    function myFunction() {
      // Get Google Sheet data
      var ss = SpreadsheetApp.openById("###");  // Please set the Spreadsheet ID.
      var sheet = ss.getSheetByName("Sheet1");
      var range = sheet.getRange(4, 2, 1, 5).getDataRegion(SpreadsheetApp.Dimension.ROWS);
      var values = range.getValues();
      var backgroundColors = range.getBackgrounds();
      var styles = range.getTextStyles();
    
      var colWidth = [];  // Added
      for (var col = 2; col <= 6; col++) {  // Added
        colWidth.push(sheet.getColumnWidth(col) * 3 / 4);
      }
    
      // Position to paste data in Google Docs
      var body = DocumentApp.getActiveDocument().getBody();
      var table = body.appendTable(values);
      table.setBorderWidth(0);
    
      colWidth.forEach(function(e, i) {table.setColumnWidth(i, e)});  // Added
    
      for (var i = 0; i < table.getNumRows(); i++) {
        for (var j = 0; j < table.getRow(i).getNumCells(); j++) {
          var obj = {};
          obj[DocumentApp.Attribute.BACKGROUND_COLOR] = backgroundColors[i][j];
          obj[DocumentApp.Attribute.FONT_SIZE] = styles[i][j].getFontSize();
          if (styles[i][j].isBold()) {
            obj[DocumentApp.Attribute.BOLD] = true;
          }
          table.getRow(i).getCell(j).setAttributes(obj);
        }
      }
    }
    

    • 运行脚本时，可以看到创建的表与手动复制的表不同.因此，关于列的宽度，在当前阶段，请提供用于手动设置colWidth的值.或者，请通过修改电子表格侧的列宽来调整文档侧的列宽.或者，请使用上面的脚本.这是由于我的技能差.我对此深表歉意.
    • When you run the script, you can see the created table is different from the manually copied table. So about the width of column, in the current stage, please give the values for manually setting colWidth. Or please adjust the column width of Document side by modifying the column width of Spreadsheet side. Or please use above script. This is due to my poor skill. I deeply apologize for this.

    这篇关于使用Google Apps脚本从Google表格数据表复制到Google文档表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！