How to solve error when adding big number of tables

Question

I have this Google Script where I am creating a document using a template table that lives in another document.

The new document will have a number of small tables (like cards) in it. The code bellow works fine for 100, 200 tables and it finishes in less than 10 seconds. But it fails for more than 500 tables. There is no error message in the Executions window.

I have tried the saveAndClose() function (commented out) but the error continues and it just takes longer to run.

I ran out of ideas how to fix that. Any help or ideas will be appreciated.


function insertSpecification_withSection(){
  startTime = new Date()
  console.log("Starting Function... "); 
  
  // Retuns a Table Template Copied from another Document
  reqTableItem = RequirementTemplate_Copy();
  
  
  // Creates X number of separated tables from the template
  for (var i = 0; i < 500; i++){
    table = DocumentApp.getActiveDocument().getBody().appendTable(reqTableItem.copy());

    //    if((i % 100) === 0) {
    //      DocumentApp.getActiveDocument().saveAndClose();
    //    }
    //    
    
  }
  endTime = new Date();
  timeDiff = endTime - startTime;
  console.log("Ending Function..."+ timeDiff + " ms"); 
  
}

function RequirementTemplate_Copy() {

  //---------------------------------------------------------------------------------------------------------------------------------------------------
  var ReqTableID = PropertiesService.getDocumentProperties().getProperty('ReqTableID');
  try{
    var templatedoc = DocumentApp.openById(ReqTableID);
  } catch (error) {
    DocumentApp.getUi().alert("Could not find the document. Confirm it was not deleted and that anyone have read access with the link.");
    //Logger.log("Document not accessible", ReqTableID)
  } 
  var reqTableItem = templatedoc.getChild(1).copy();
  //---------------------------------------------------------------------------------------------------------------------------------------------------
  return reqTableItem
}

try for (var i = 0; i < 500; i++){ DocumentApp.getActiveDocument().getBody().appendTable(reqTableItem); } minimal reproducible example should include RequirementTemplate_Copy() — Cooper
– Cooper, Commented Nov 13, 2020 at 22:01
If I make for (var i = 0; i < 500; i++){ DocumentApp.getActiveDocument().getBody().appendTable(reqTableItem.copy()); } I still get the error — Caue Napier
– Caue Napier, Commented Nov 13, 2020 at 22:08
Body class has a getChild() method. I your problem is in RequirementTemplate_Copy() — Cooper
– Cooper, Commented Nov 13, 2020 at 22:49

ale13 · Accepted Answer · 2020-11-16 08:33:26Z

Just like mentioned in the other answer, it is best to save the document outside the loop and call it when needed. Same goes for the body of the document.

let currentDoc = DocumentApp.getActiveDocument();
let bodyDoc = currentDoc.getBody();
for (var i = 0; i < 500; i++){
    table = bodyDoc.appendTable(reqTableItem.copy());
    //    if((i % 100) === 0) {
    //      currentDoc.saveAndClose();
    //    }
    //    
  }

However, since you are mentioning that you are receiving the Exception: Service Documents failed while accessing document with id error, you might want to take a look at this issue on Issue Tracker since what you are experiencing might be a bug with Apps Script.

If this is the situation, I recommend you to star the issue and eventually add a comment saying that you are affected by it.

Reference

Apps Script Best Practices.

Wicket · Accepted Answer · 2020-11-14 01:41:04Z

0

Instead of using a long method chaining to append the tables inside the for loop, declare a variable for the document body before the for and use it inside the for. In other words,

replace

 // Creates X number of separated tables from the template
  for (var i = 0; i < 500; i++){
    table = DocumentApp.getActiveDocument().getBody().appendTable(reqTableItem.copy());

by

 var body = DocumentApp.getActiveDocument().getBody();
 // Creates X number of separated tables from the template
  for (var i = 0; i < 500; i++){
    table = body.appendTable(reqTableItem.copy());

The above code was tested with the following template

The script finished without errors. The resulting document has 50 pages.

edited Nov 14, 2020 at 1:41

answered Nov 13, 2020 at 22:52

Wicket

39.4k9 gold badges81 silver badges201 bronze badges

4 Comments

Caue Napier Over a year ago

Thanks Rubén, that's what I was doing in the principle. But it seems that it is a memory limit problem. Because exact same code fail if I make 1000 tables or if the tables has a couple more rows or data inside. Does it fail with you to?

Caue Napier Over a year ago

At some point during the insertion I get the error "Exception: Service Documents failed while accessing document with id" while running it directly on script (before was an addon)

Wicket Over a year ago

@CaueNapier Follow-up questions should be posted as new questions. If I you do it, please include a sample table and be clear about the number of tables to insert.

Caue Napier Over a year ago

Thanks for explaining @Rubén, I have created a new question witth the details. stackoverflow.com/questions/64835452/…

Collectives™ on Stack Overflow

How to solve error when adding big number of tables

2 Answers 2

Reference

Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Reference

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related