Is it possible to output data from each row of a Google Sheet into a separate Google Doc

0
votes

I'm not even sure if this is possible but I have a Google Sheet that I'm planning to merge into a Google Doc using the mail merge method with a template document.

I would like to know if it's possible to produce a new Google document for each row of the sheet.

Currently the script I have in place will take all rows from the sheet, pass them through the template file and then output them to my final document. Each row of the sheet will be separated by a page break in the final document. But ideally I would like each row to be in a separate document.

My current script code is

function myFunction() {

var docTemplateId = "docTemplateId";
var docFinalId = "docFinalId";
var wsId = "wsId";

var docTemplate = DocumentApp.openById(docTemplateId);
var docFinal = DocumentApp.openById(docFinalId);
var ws = SpreadsheetApp.openById(wsId).getSheetByName("data");

var data = ws.getRange(2,1,ws.getLastRow()-1,7).getValues();
var templateParagraphs = docTemplate.getBody().getParagraphs();

docFinal.getBody().clear();

data.forEach(function(r){
createMailMerge(r[0],r[3],r[4],templateParagraphs, docFinal);

});
}
function createMailMerge(titre, description, suivantes,templateParagraphs, docFinal) {

templateParagraphs.forEach(function(p) {
var elType = p.getType();

if(elType == "PARAGRAPH"){
docFinal.getBody().appendParagraph(
p
.copy()
.replaceText("{titre}", titre)
.replaceText("{description}", description)
.replaceText("{suivantes}", suivantes)
);
}
else if(elType == "LIST_ITEM") {
 docFinal.getBody().appendListItem(
 p
 .copy()
 .replaceText("{titre}", titre)
 .replaceText("{description}", description)
 .replaceText("{suivantes}", suivantes)
 ).setGlyphType(DocumentApp.GlyphType.BULLET);
 }

});
docFinal.getBody().appendPageBreak();

};

Can anyone help me in achieving this. I know I could possibly create documents for each row and then add them as variable but maybe there is a more efficient way.

2
google-apps-scriptgoogle-sheetsgoogle-docsmailmerge

2 Answers

0
votes

An alternative using the Drive Advanced Service

Instead of making multiple requests to DocumentApp to build up the document bit by bit you could try to create the document entirely in HTML, and then using the Advanced Drive Service, create the file, with content, all in one request. This should speed up the execution considerably.

function createFile(title, html) {
  var resource = {
    title: title,
    mimeType: MimeType.GOOGLE_DOCS
    // parents: [{ id: folderId }]  You can add this to make it go in a folder.
  };
  Drive.Files.insert(resource, html);
}


function test(){
  // Build up your HTML as a string and then:
  var html = HtmlService.createHtmlOutput("<h1>Hello World</h1><p>Created from Apps Script using advanced services</p>")
  
  createFile("Test Drive Advanced Service", html)
}

Running the test function results in this document here:

enter image description here

As you can see, if you upload to Drive with HTML content but you specify it as a Google Doc, then it will convert it without issue. This can be very convenient for cases like yours.

Setting up

Be sure to first activate the Advanced Drive service here:

enter image description here

See the "+" button next to "Services", click and select Drive API.

Please note that this uses the Drive API v2 and not v3.

References

0
votes

I think this is actually already optimal in your case. I'm not sure if there is a way for actually doing bulk but since you are accessing the rows one at a time and have a template with it, this approach is already good.

data.forEach(function (r) {
  // Assuming titre is unique, use it as doc name
  // Or combine the parameters to produce a unique doc name
  createMailMerge(r[0], r[3], r[4], templateParagraphs, DocumentApp.create(r[0]));
});

As for the createMailMerge, you can remove the appendPageBreak line since we are already not appending the data in one document.