How to avoid service calls when writing to Google Document table cells

Question

I'm designing a Transcript for my school, using google apps script to take info from a gsheet and writing the treated info to three tables in a Document.

I'm set on how to do my data manipulations in script to avoid service calls, but I'm stuck on how to avoid calling the Document service each time I write to the 40 something cells in three Document tables. Currently, for 200+ students, it's taking 33 seconds to write a sample text to 40 cells, but that's without the cost of formatting calls, saving the Document and calling the Drive service to convert each doc to a PDF.

I should mention that GAS does not currently allow setting tabs, which is why I'm filling in each cell of templated tables rather than building those completely outside the service.

Here are the pertinent lines of code (filtered is the list of students and some filler code, to test any loss in performance related to the amount of info treated, which seems minimal):

// ***** SET TEXT IN THE DOCUMENT
var tables = DocumentApp.openById("ID").getBody().getTables();

  var j=0;
  while (j<filtered.length){

  var cell1 = tables[0].getCell(0,0).setText(filtered[j]);
  var cell2 = tables[0].getCell(0,1).setText(filtered[j]);
  var cell3 = tables[0].getCell(0,2).setText(filtered[j]);

...

  var cell40 = tables[0].getCell(13,0).setText(filtered[j]);

  j++
  }

To state this another way: is there a way to bulk or batch write to multiple cells, or run something like setText to more than one Document table cell on a single service call?

Rubén Rubén · Accepted Answer · 2021-04-05T14:34:08

Instead of writing one cell at a time write all of them at once. This can be done by using appendTable or insertTable from Class Body.

Example from https://developers.google.com/apps-script/reference/document/table

var body = DocumentApp.getActiveDocument().getBody();

// Create a two-dimensional array containing the cell contents.
var cells = [
  ['Row 1, Cell 1', 'Row 1, Cell 2'],
  ['Row 2, Cell 1', 'Row 2, Cell 2']
];

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

How to avoid service calls when writing to Google Document table cells

2 Answers