Using Google Apps Script to Copy from Google Sheets Data Table to Google Documents Table

Question

Is there a way to write a Google Apps Script in Google Docs to retrieve from Google Sheets a range limited to non-blank rows and display those rows as a table?

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).

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.

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

}

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

Answer 1

• 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.
• You want to achieve this using Google Apps Script.

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.

Flow:

The flow of this sample script is as follows.

1. Retrieve the data range from Google Spreadsheet.
   • In this case, I used getDataRegion(dimension).
2. Retrieve values, backgrounds and text styles using the range.
3. Append new table to Google Document with the values.
4. Set the format of cells.

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

Note:

• In this sample script, the values of cells "B4:F" are retrieved from your shared Spreadsheet. So if you want to change this range, please modify above script.

References:

• getDataRegion(dimension)
• getValues()
• getBackgrounds()
• getTextStyles()
• appendTable()
• setAttributes(attributes)

Added:

Issue for reflecting column width from Spreadsheet to 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.

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.

Sample script:

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

• 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.