Home > Software design >  Trying to fix error on Google Sheets to Docs data merge
Trying to fix error on Google Sheets to Docs data merge

Time:02-11

How can I fix this error?

The code was working well and then I added some more code for more functionality but now I can't get past this error.

Error: Too many changes applied before saving document. Please save changes in smaller batches using Document.saveAndClose(), then reopen the document with Document.openById().

Here is my code

function myFunction() {
  var docTemplateID = "1g87Ak-ms8Ctv1B4zAquGaBn55shBKFZi5VUoP0Fgfvg";
  var docFinalID = "19K9N2uuBV312FuW0dLQv0BD8wBZYvtJmJHsjbSlqgCc";
  var wsID = "12sS_0rZn5_OzOigB75gSkSR1i7NkW5bMqY99onIi3qY";

  var docTemplate = DocumentApp.openById(docTemplateID);
  var docFinal = DocumentApp.openById(docFinalID);
  var ws = SpreadsheetApp.openById(wsID).getSheetByName("SCRAPED22PROBATE7");

  var data = ws.getRange(2,1,3,4,5,6,7,ws.getLastRow()-1,7).getValues();

  var templateParagraphs = docTemplate.getBody().getParagraphs();

  docFinal.getBody().clear();

  data.forEach(function(r){
    createMailMerge(r[1],r[0],r[2],r[3],r[4],r[5],r[6],templateParagraphs,docFinal);
  });


}


function createMailMerge(first,decdname,last,address,city,state,zip,templateParagraphs,docFinal){
  templateParagraphs.forEach(function(p){
    var elType = p.getType();

    if(elType == "PARAGRAPH"){
    docFinal.getBody().appendParagraph(
      p
      .copy()
      .replaceText("{PRFirstName}",first)
      .replaceText("{DecdNamePulled}",decdname)
      .replaceText("{PRFirstMail}",first)
      .replaceText("{PRLastMail}",last)
      .replaceText("{PRAddressMail}",address)
      .replaceText("{PRCityMail}",city)
      .replaceText("{PRStateMail}",state)
      .replaceText("{PRZipMail}",zip)
    );
     docFinal.saveAndClose()
  } else if(elType == "LIST_ITEM"){
    docFinal.getBody().appendListItem(
      p
      .copy()
      .replaceText("{PRFirstName}",first)
      .replaceText("{DecdNamePulled}",decdname)
      .replaceText("{PRFirstMail}",first)
      .replaceText("{PRLastMail}",last)
      .replaceText("{PRAddressMail}",address)
      .replaceText("{PRCityMail}",city)
      .replaceText("{PRStateMail}",state)
      .replaceText("{PRZipMail}",zip)
    ).setGlyphType(DocumentApp.GlyphType.BULLET);
  }

  });

  docFinal.getBody().appendPageBreak();
}

CodePudding user response:

This is wrong: var data = ws.getRange(2,1,3,4,5,6,7,ws.getLastRow()-1,7).getValues(); way too many parameters row,column,number of rows, number of columns

sheet.getRange()

CodePudding user response:

Given the error I'd try to open and close the final doc inside the function createMailMerge():

function createMailMerge(first,decdname,last,address,city,state,zip,templateParagraphs,docFinal) {

  var docFinal = DocumentApp.openById('19K9N2uuBV312FuW0dLQv0BD8wBZYvtJmJHsjbSlqgCc'); 
  
  // . . . 
  // the rest code of the function
  // . . .
  
  docFinal.getBody().appendPageBreak();
  
  docFinal.saveAndClose();
}
  • Related