CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsgoogle-docs
James Cook
James Cook

Reputation: 13

Paragraphs with inline images breaking app script merging sheet data into doc

I have a Google App Script that merges data values from rows in a sheet with a template doc. The template doc paragraphs have inline images. When I remove the images, the script works perfectly. When I add the images back, I get the following error: Service unavailable: Documents (line 34, file "Code")

Any ideas on what I need to do? FYI: Line 34 is noted with a comment below...

function buildGivingStatements() {
  var tempDocID = "1-SAQYP41wwnah6tF15nvT8fSx1rjCHN57T7lECFTCCw";
  var finDocID = "1TnR63Yq9oiUwxxJ5MTHbm-OX3pqvH6iW7bBLcFC2v1w";
  var sheetID = "19Sl61Rp37bPWc_fsCqpu59DFsDdwZF4zxdjHlplA-fI";

  var tempDoc = DocumentApp.openById(tempDocID);
  var finDoc = DocumentApp.openById(finDocID);
  var sheet = SpreadsheetApp.openById(sheetID).getSheetByName("Annual Report");

  var tDATE = Utilities.formatDate(new Date(), "America/Chicago", "EEEE, MMMM dd, yyyy");
  var tYEAR = Utilities.formatDate(new Date(), "America/Chicago", "yyyy");

  var data = sheet.getRange(6,1,1,2).getDisplayValues();
  //var data = sheet.getRange(6,1,sheet.getLastRow()-1,4).getValues();

  var tempPs = tempDoc.getBody().getParagraphs();

  finDoc.getBody().clear();

  data.forEach(function(r){
    replaceTempVars(r[0], r[1], tDATE, tYEAR, finDoc, tempPs);
  });

}



function replaceTempVars(NAME, AMOUNT, tDATE, tYEAR, finDoc, tempPs) {
  tempPs.forEach(function(p){
    var elType = p.getType();
    Logger.log(elType);

    if(elType == "PARAGRAPH") {
      finDoc.getBody().appendParagraph( //LINE 34
        p
          .copy()
          .replaceText("{{NAME}}", NAME)
          .replaceText("{{AMOUNT}}", AMOUNT)
          .replaceText("{{DATE}}", tDATE)
          .replaceText("{{YEAR}}", tYEAR)
      );
    }else if(elType == "LIST_ITEM") {
      finDoc.getBody().appendListItem(
        p
          .copy()
      ).setGlyphType(DocumentApp.GlyphType.BULLET);
    }
  });

  finDoc.getBody().appendPageBreak();
}

Upvotes: 1

Views: 663

Answers (1)

Tanaike
Tanaike

Reputation: 201428

  • You want to merge the template Document to the existing Google Document by replacing the texts.
  • In your current issue, an error of Service unavailable occurs at the script of finDoc.getBody().appendParagraph().
    • When the image at the top of Document is deleted, the script works.
  • You want to achieve this using Google Apps Script.

If my understanding is correct, how about this answer? Please think of this as just one of several possible answers.

Modification points:

  • From your shared Google Document, I could know that your template Document uses a positioned image. I think that the reason of the error of Service unavailable is due to the positioned image. When the paragraph including positioned image is copied, such error occurs.
    • In order to avoid this issue, I would like to propose to modify your script by separating the positioned image and paragraph.
    • For this, I used getNumChildren() instead of getParagraphs(). Because I thought that it is required to use insert method instead of append method. Because when append method is used, the empty paragraph is created at the top paragraph.

Modified script:

Before you run the script, please enable Google Docs API at Advanced Google services.

function buildGivingStatements() {
  var tempDocID = "1-SAQYP41wwnah6tF15nvT8fSx1rjCHN57T7lECFTCCw";
  var finDocID = "1TnR63Yq9oiUwxxJ5MTHbm-OX3pqvH6iW7bBLcFC2v1w";
  var sheetID = "19Sl61Rp37bPWc_fsCqpu59DFsDdwZF4zxdjHlplA-fI";

  var tempDoc = DocumentApp.openById(tempDocID);
  var finDoc = DocumentApp.openById(finDocID);
  var sheet = SpreadsheetApp.openById(sheetID).getSheetByName("Annual Report");
  var tDATE = Utilities.formatDate(new Date(), "America/Chicago", "EEEE, MMMM dd, yyyy");
  var tYEAR = Utilities.formatDate(new Date(), "America/Chicago", "yyyy");
  var data = sheet.getRange(6,1,1,2).getDisplayValues();

  // I modified below script.
  var finBody = finDoc.getBody();
  finBody.clear();
  var tempBody = tempDoc.getBody();
  var tempNumChildren = tempBody.getNumChildren();
  var pos = 0;
  data.forEach(function(r){
    replaceTempVars(r[0], r[1], tDATE, tYEAR, finBody, tempBody, tempNumChildren, pos);
  });

  // If you want to delete the last empty page, please use the following script. In this case, please enable Docs API at Advanced Google services.
  finDoc.saveAndClose();
  var c = Docs.Documents.get(finDocID, {fields: "body.content"}).body.content.pop();
  Docs.Documents.batchUpdate({requests: [{deleteContentRange: {range: {startIndex: c.startIndex - 1, endIndex: c.endIndex - 1}}}]}, finDocID);
}

function replaceTempVars(NAME, AMOUNT, tDATE, tYEAR, finBody, tempBody, tempNumChildren, pos) {
  for (var i = 0; i < tempNumChildren; i++) {
    var element = tempBody.getChild(i).copy();
    var type = element.getType();
    if (type == DocumentApp.ElementType.PARAGRAPH) {
      var copyPara = element.asParagraph();
      var pImgs = copyPara.getPositionedImages();
      if (pImgs.length > 0) {
        var img = pImgs[0];
        var obj = {id: img.getId(), blob: img.getBlob(), leftOffset: img.getLeftOffset(), topOffset: img.getTopOffset(), width: img.getWidth(), height: img.getHeight(), layout: img.getLayout()};
        copyPara.removePositionedImage(obj.id);
        var texts = copyPara.replaceText("{{NAME}}", NAME).replaceText("{{AMOUNT}}", AMOUNT).replaceText("{{DATE}}", tDATE).replaceText("{{YEAR}}", tYEAR);
        var paragraph = finBody.insertParagraph(pos + i, texts);
        paragraph.addPositionedImage(obj.blob).setWidth(obj.width).setHeight(obj.height).setLayout(obj.layout).setLeftOffset(obj.leftOffset).setTopOffset(obj.topOffset);
      } else {
        var texts = element.asParagraph().replaceText("{{NAME}}", NAME).replaceText("{{AMOUNT}}", AMOUNT).replaceText("{{DATE}}", tDATE).replaceText("{{YEAR}}", tYEAR);
        finBody.insertParagraph(pos + i, texts);
      }
    } else if (type == DocumentApp.ElementType.LIST_ITEM) {
      var glyphType = element.asListItem().getGlyphType();
      finBody.insertListItem(pos + i, element).setGlyphType(glyphType);
    } else if (type == DocumentApp.ElementType.INLINE_IMAGE) {
      finBody.insertImage(pos + i, element);
    }
  }
  pos += tempNumChildren;
}

Note:

  • In this modified script has been prepared by testing your template Document. If you want to use other template, an error might occur. So please be careful this.
  • About the Google Document of finDocID, please use the Document which has the same margins of Document page with the template Document. If the default Google Document is used, the result might not be what you expect. Please be careful this.

References:

Upvotes: 2

Related Questions