Copying Sheet Data to Doc table

Question

I have a sheet with information that needs to be filled and once a submit process is started it should copy the cell data to an existing table header in a document. There is only one table in the document with the header info.

I can get this to work just fine with appendTable, but I would like to append rows to the existing table in the document. Data from spreadsheet looks like [[1234, Jordan, Connecticut], [6899123, Job Site, Connecticut], [, , ]]

//  Sheet Data
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheets()[0]
var range = sheet.getRange(2,1,sheet.getLastRow(),sheet.getLastColumn());
var rows = sheet.getLastRow()
var values = range.getValues();


var document = DocumentApp.openById(tmpl);
var body = document.getBody();
   body.replaceText('#{jobNum}', job);
var table = body.getTables()[0]

document.saveAndClose()

score 0 · Answer 1 · answered Aug 04 '16 at 16:35

You already have the values in the variable values and you also selected the first table in the document in the variable table, now you just need to to loop through your Array and append rows using appendTableRow() and appendTableCell()

Your loop should look like this:

  for (var row = 0; row < values.length; row++) {
    var newRow = table.appendTableRow(); // Append new Row
    for (var col = 0; col < values[row].length; col++) {
      newRow.appendTableCell(values[row][col]); // Append a Cell with the value
    }
  }

This will append a row at the end of the table you already have in the document.

Copying Sheet Data to Doc table

1 Answers1

Linked