Google Apps Script - Appending data from spreadsheet to table in document template

Question

I am trying to write a script that will take a list of expenses from a google sheet and append them to a already existing table in a google docs template. At the moment I have this (the reason it says names is because I am testing with dummy data of names and ages):

function myFunction() {

  var ss = SpreadsheetApp.getActive();
  var sheet = ss.getActiveSheet()

 var numRows = sheet.getLastRow()
 var numColumns = sheet.getLastColumn()

  var data = sheet.getRange(1, 1, numRows, numColumns).getValues()


var doc = DocumentApp.openById('13l6O8nmEZgJiZnzumWihsRVOZiq_8vUj6PtBtb9My_0')
var body = doc.getBody()
var tables = body.getTables()
var table = tables[1] 

  for (var i = 1; i < data.length; i++){
    var row = data[i]
    var name = row[0]
    var age = row[1]
    var state = row[2]
    var done = 'Done'
 //Check to see if line has already been processed or not
   if (!state){
     sheet.getRange(i + 1, 3).setValue(done)
     //Slices out the blank state value
     var slice = row.slice(0, numColumns-1)     
     table.appendTableRow(slice)
     }
   }
}

This just adds a new table to the document but I can't find way to add rows to an existing table of the data I can add indvidual cells one per row but that isn't useful and can't seem to/don't understand how the appendtoRow instruction works. Any thoughts on how best to do this?

Serge insas Serge insas · Accepted Answer · 2014-09-05T06:02:23

To add a row to an existing table you need to use appendTableRow() and then add cells to this newly added row.

Change the last part of your script like below (make sure that your table is indeed the second table in the document since you used var table = tables[1])

...
if (!state){
  sheet.getRange(i + 1, 3).setValue(done);
  //Slices out the blank state value
  var slice = row.slice(0, numColumns-1); 
  Logger.log(slice);
  var tableRow = table.appendTableRow();
  for(var n in slice){
    tableRow.appendTableCell(slice[n]);
  }
}
...

Google Apps Script - Appending data from spreadsheet to table in document template

1 Answers