I am reading a Google Sheet files and merge to a Google Document.
The template has a tables and the first line for replace text has a table. The next line does not have a table structure and I would like to append the table until no more records.
Description
- referral-doctor-data-new the data source to merge (monthly-q) worksheet is defined to copy fields to the google documents
- accounting-monthly-statement-template
Use as template
<<variable>>
- monthly-statement The merged document
The overview is that the template defined fields to obtain from the referral-doctor-data-new google sheet. The monthly-q worksheet is the format of the final merged document. I would like to Get all the detail transactions displayed in the Google Doc group by the Referral Doctor. All the required calculations and queries are done
Problem:
- Can merge one data but not multiple rows of data
- I could not append the second row of the table in the google document ( I need help on this) see error element must be detached.....
- There is seems unlimited Runs of the merge process ( I will figure this out)
Sample Files Merge Files for Goolge Sheet to Goolge Doc
Please advise how to detach from the element.
These Lines has error
// if second row of data
body.appendTable(element).appendTableRow()
.appendTableCell("EXAMDATE")
.appendTableCell("VISITNO")
.appendTableCell("PATNAME")
.appendTableCell("PROC NAME")
.appendTableCell("NET");
Complete Code
///end of mail merge
function copy() {
var docid="" //merge template
var docfinalid="";//merge final doc
var sheetid=""; //raw sheet
var ss=SpreadsheetApp.openById(sheetid).getSheetByName("monthly-q");
//var data=ss.getRange("B11:T").getValues(); // from raw table
var data=ss.getRange("A3:G").getValues(); // from monthly-q table
var refdoc=ss.getRange("J:J").getValues() ;//get ref doc list, unique
for (var i=0;i<refdoc.length;i++ ){
var formula="=QUERY(raw!B10:T,\"Select C,D,H,L,M,T where M='"+refdoc[i]+"'\")";
var qformula=ss.getRange("A2").setFormula(formula);
data.length=0;
data=ss.getRange("A3:G").getValues();
data.forEach(function(r){
var inv=ss.getRange("B1").getValue();//self-define invoice code
merge(inv,"31-01-2020",r[4],"TEMPADD",r[0],r[1],r[2],r[3],r[5],r[6]);
inv=inv+1;
});
}
}// end of copy function
function merge(inv,date,drname,add,examdate,visitno,patname,procname,net,total) {
var docid="" //merge template
var docfinalid="";//merge final doc
var sheetid=""; //raw sheet
var ss=SpreadsheetApp.openById(sheetid).getSheetByName("raw");
var doc=DocumentApp.openById(docid);
var sourceDoc = doc.getBody();
var targetDoc = DocumentApp.openById(docfinalid);
var totalElements = sourceDoc.getNumChildren();
targetDoc.getBody().appendPageBreak();
//get Yr from date
//get Month from date
for( var j = 0; j < totalElements; ++j ) {
var body = targetDoc.getBody();
var element = sourceDoc.getChild(j).copy();
var type = element.getType();
if( type == DocumentApp.ElementType.PARAGRAPH ){
body.appendParagraph(element).replaceText("<<inv>>", inv)
.replaceText("<<Date>>", date)
.replaceText("<<Dr Name>>", drname)
.replaceText("<<Address>>", add);
}
else if( type == DocumentApp.ElementType.TABLE){
body.appendTable(element).replaceText("<<Exam Date>>", examdate)
.replaceText("<<Visit No>>", visitno)
.replaceText("<<Patient Name>>", patname)
.replaceText("<<Procedure Name>>", procname)
.replaceText("<<Net Amount>>", net)
.replaceText("<<Total>>", total);
//second row of data
var test=element.getParent();
debugger;
// if second row of data
body.appendTable(element).appendTableRow()
.appendTableCell("EXAMDATE")
.appendTableCell("VISITNO")
.appendTableCell("PATNAME")
.appendTableCell("PROC NAME")
.appendTableCell("NET");
}else if( type == DocumentApp.ElementType.LIST_ITEM){
body.appendListItem(element);
}
// ...add other conditions (headers, footers...
inv++;
}
targetDoc.saveAndClose();
}
I have a list of data from GoogleSheet Merge to A Google Doc With reference to enter link description here I could not solve the problem