0

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

  1. referral-doctor-data-new the data source to merge (monthly-q) worksheet is defined to copy fields to the google documents
  2. accounting-monthly-statement-template Use as template <<variable>>
  3. 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

Google Sheet with rows of data Merged Google Doc

Element need to be detached

Rubén
  • 34,714
  • 9
  • 70
  • 166
Random I.T
  • 121
  • 1
  • 10
  • 1
    Please create an example sheets and docs file and add it to your question. It is hard to understand without it. – ZektorH Feb 06 '20 at 09:03
  • Updated, please check thanks :) I used a template to replace <> to replace a row of record. but I have many record, and I cannot append the Google Document. – Random I.T Feb 06 '20 at 09:12
  • Could you create a copy of the files and use sample data so we can use that as a starting point for solving your issue? – ZektorH Feb 06 '20 at 09:14
  • 1
    I want to copy the current google sheet to the google document. How to append the table written in the Document? – Random I.T Feb 06 '20 at 09:42
  • Please provide templates (documents, not screenshots) of the spreadsheet and docs that reproduce the issue. Replace all personal data with dummy values, but provide the correct doument structure - otherwise it is hard to reproduce your problem.. – ziganotschka Feb 06 '20 at 12:15
  • The Data structure is linked quite complicated, please look at the screenshot first, the sample data are preparing. I want to print more than one line in the merged document. How to append the table in a google doc ? – Random I.T Feb 06 '20 at 13:53
  • The error `Element must be detached` comes from the fact that before copying the element you need to know which type of element it is. Modify `sourceDoc.getChild(j).copy(); ... body.appendTable(element);` to `sourceDoc.getChild(j); ... body.appendTable(element.asTable().copy());`. Same for paragraph. However your code seems to have also other issue. For example you cannot append a row and then append to this row several cells in the same request. I suggest you to simplify your code and then add functionalities one by one. – ziganotschka Feb 07 '20 at 12:34
  • i changed my code to getting data join as a string it works but i have 8500 plus records and it took too long. unable to finish the script execution before it ends any suggestions ? – Random I.T Feb 07 '20 at 16:50
  • 1
    Have a look [here](https://developers.google.com/apps-script/guides/support/best-practices). If you don't find a way to optimize your code suffciently, you might have to split your request into two. – ziganotschka Feb 10 '20 at 14:50

0 Answers0