Insert texts in Doc

[JMR...]

Hi,

I am trying to create a document letter with information but I am lost when trying to append a section with texts (separated by a comma).

** Grab the account email, temporary password and things, append the Doc letter.

** Go to next row, check if the account email is the same as previous. If yes, only grab the "things" values and insert it the document If no, execute the 1st step.

Here's the code I am using:

function generateUserWelcomeLetter() {

var date = new Date();

var letterDateString = Utilities.formatDate(date, "PST", "MM/dd/yyyy");

/* Create new folder for PDF and DOC */

var folder = DriveApp.getFolderById('XXXX');

var docsFolder = folder.createFolder("DOC- " + letterDateString);

var pdfsFolder = folder.createFolder("PDF- " + letterDateString);

var sheet_ = SpreadsheetApp.openById('1GSBklZZrNm8dy69c8QX1HdGpiFztHC3Cqq-Equ9DA3s');

var sheet_AR = sheet_.getSheetByName('Sheet_3');

var sheet_Range = sheet_AR.getRange(2, 1, sheet_AR.getLastRow() - 1, sheet_AR.getLastColumn());

var sheet_Values = sheet_Range.getValues();

for (var i = 0; i < sheet_Values.length; i++) {

var fullName = sheet_Values[i][3];

var aEmail = sheet_Values[i][4];

var tempPwd = sheet_Values[i][5];

var things = sheet_Values[i][6];

var docTemplateID = "1TUpuQh_lJVu2e38HguLa0W-dQz4fXIELjRz5aqvp1j4";

/* Make a copy of the template file */

var newDoc = DriveApp.getFileById(docTemplateID).makeCopy(docsFolder);

var newDocId = newDoc.getId();

DriveApp.getFileById(newDocId).setName('Letter: ' + aEmail);

/* Get the document body as variable */

var body = DocumentApp.openById(newDocId).getBody();

/* Insert the data into the body */

body.replaceText('##Date##', letterDateString)

body.replaceText('##FullName##', fullName)

body.replaceText('##AccountEmail##', aEmail)

body.replaceText('##Password##', tempPwd)

/* Insert Things*/

body.replaceText('##Things##', things)

var newDoc2 = DocumentApp.openById(newDocId);

newDoc2.saveAndClose();

// Generate docblock for PDF

var docBlob = DocumentApp.openById(newDocId).getAs('application/pdf').setName(newDoc2.getName() + ".pdf");

// Create PDF

var pdfFile = pdfsFolder.createFile(docBlob);

}

}

TIA for your time and help!

[JMR...]

Here is the desired result.

[Martin Molloy]

This works - I split it into 2 functions

function generateUserWelcomeLetter() {
  var date = new Date();
  var letterDateString = Utilities.formatDate(date, "PST", "MM/dd/yyyy");

  /* Create new folder for PDF and DOC */

  var folder = DriveApp.getFolderById('1tsTz28xp1OWvyXXNpP8Cdj-Zq7vs0P1B');  //replace with your folder ID

  var docsFolder = folder.createFolder("DOC- " + letterDateString);
  var pdfsFolder = folder.createFolder("PDF- " + letterDateString);

//  var sheet_ = SpreadsheetApp.openById('1GSBklZZrNm8dy69c8QX1HdGpiFztHC3Cqq-Equ9DA3s');
  var sheet = SpreadsheetApp.getActiveSpreadsheet()  
  var sheet_AR = sheet.getSheetByName('Sheet1');  //replace with your sheet name
  var sheet_Range = sheet_AR.getRange(2, 1, sheet_AR.getLastRow() - 1, 8);  //Changed to 8 to write back 'Done'
  var sheet_Values = sheet_Range.getValues();

  var newCust = true;  //to stop the doc creation running for very first row
  var oldEmail =''
  var fullName=''
  var tempPwd =''
  var things = ''
 
  //start loop

  for (var i = 0; i < sheet_Values.length; i++) {

        var aEmail = sheet_Values[i][4];

        var doneYN = sheet_Values[i][7];
       
        // if aEmail is not equal to the previous email then create the doc and start a new one
  if(aEmail && aEmail != oldEmail && newCust != true && doneYN !='Done'){
    doMerge(oldEmail, fullName, tempPwd, things, docsFolder, pdfsFolder, letterDateString )  //Calls the doMerge function below
    things = ''  //reset things to ''
    }
    if(aEmail && doneYN != 'Done'){
    newCust = false;
    oldEmail = aEmail

    fullName = sheet_Values[i][3];

    tempPwd = sheet_Values[i][5];

    if(things !=''){
    things = things+", "+sheet_Values[i][6];
    }else{

    things = sheet_Values[i][6];
    }

    sheet_Values[i][7]='Done'
  }
  } //End Loop
 
    //  Create doc for last record
    if(aEmail && doneYN != 'Done'){
      doMerge(oldEmail, fullName, tempPwd, things, docsFolder, pdfsFolder, letterDateString ) //Calls the doMerge function below
      }
      // paste back info to show done
     sheet_Range.setValues(sheet_Values)
}

function doMerge(aEmail, fullName, tempPwd, things, docsFolder, pdfsFolder, letterDateString){
    var docTemplateID = "1xHdEASJpYcNvMLP6QJQB9zEwIkuQEhCNHhgijnccz8c";  //Replace with your template docID

    /* Make a copy of the template file */
    var newDoc = DriveApp.getFileById(docTemplateID).makeCopy(docsFolder);
    var newDocId = newDoc.getId();
    DriveApp.getFileById(newDocId).setName('Letter: ' + aEmail);

    /* Get the document body as variable */
    var body = DocumentApp.openById(newDocId).getBody();

    /* Insert the data into the body */
    body.replaceText('##Date##', letterDateString)
    body.replaceText('##FullName##', fullName)
    body.replaceText('##AccountEmail##', aEmail)
    body.replaceText('##Password##', tempPwd)

    /* Insert Things*/
    body.replaceText('##Things##', things)

    var newDoc2 = DocumentApp.openById(newDocId);
    newDoc2.saveAndClose();

    // Generate docblock for PDF
    var docBlob = DocumentApp.openById(newDocId).getAs('application/pdf').setName(newDoc2.getName() + ".pdf");
    // Create PDF
    var pdfFile = pdfsFolder.createFile(docBlob);
}

Hope that helps

Martin

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/4dcc53d5-2b19-416f-91d9-4c378dcf406dn%40googlegroups.com.

[JMR...]

Hi Martin,

Thank you for your  input. I have a question on these, see in blue:

  // if aEmail is not equal to the previous email then create the doc and start a new one

if(aEmail && aEmail != oldEmail && newCust != true && doneYN !='Done'){
    doMerge(oldEmail, fullName, tempPwd, things, docsFolder, pdfsFolder, letterDateString )  //Calls the doMerge function below
    things = ''  //reset things to ''
    }
    if(aEmail && doneYN != 'Done'){
    newCust = false;
    oldEmail = aEmail

    fullName = sheet_Values[i][3];

    tempPwd = sheet_Values[i][5];

    if(things !=''){
    things = things+", "+sheet_Values[i][6]; ---- > Can be this dynamic? Meaning, how would this change if I have more than 2+ things?
    }else{

    things = sheet_Values[i][6];
    }

    sheet_Values[i][7]='Done'
  }
  } //End Loop
  

Thanks!

[Martin Molloy]

I think it would be a struggle to be completely dynamic but you could make it work with a variable number of "things" columns if you specify upfront the maximum number of columns which could contain things. (You would also need to decide which column to put the Done response in. I put it in Column 7.

So if you had 20 columns with "things" in (30, 40, 50, 100 columns would work in the same way) you would need to do a loop over those columns

var thingsTemp = sheet_Values[i][6]

for (var k = 7; k<26; k++) {

var thingsNew =  sheet_Values[i][k]

if(thingsNew){

thingsTemp = thingstemp+", "+thingsNew

}

}//end of k loop - the maximum value of K would depend on how many columns you want to check in

hope that helps

Martin

 

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/795f932a-807e-4ee7-a823-fab6a883aa0cn%40googlegroups.com.

[JMR...]

Hi Martin,

Sorry for the confusion. I won't have more than 1 column for "Things".

I make a screenshot of the sample sheet and expected result. I hope this makes more sense.

https://docs.google.com/document/d/1TUpuQh_lJVu2e38HguLa0W-dQz4fXIELjRz5aqvp1j4/edit

[Martin Molloy]

Sorry I misunderstood.

That's what the original script I posted does.

I shared a folder with you which contains a template doc and a spreadsheet. The spreadsheet has a bound script. I made you an editor of everything in the folder.

Try it out for yourself.

Martin

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/e3ff6800-6475-4118-bbec-efe61b181488n%40googlegroups.com.

[Jonathan Vargas]

You can actually simply set the data range to read the header as the identifier for your mail merge tags. That way you can go ahead and add as many columns with different data as you want and just use the header as the tag. I have a video and a sample code I'm attaching here that seems to do around what you are looking for. 

Video Tutorial explaining code: https://www.youtube.com/watch?v=DR2lDE1Up4g&t=222s

GitHub repository with code: https://github.com/sayuga/GAS-Tools/blob/master/TutorialsScripts/Mail%20Merge.gs 

// onOpen opens a pull down menu on the spreadsheet you are adding the data to. 

 function onOpen() {

   var ss = SpreadsheetApp.getActiveSpreadsheet();      

   var menuEntries = [];

   menuEntries.push({name: "Mail Merge", functionName: "SayugaSolFunction"});

   menuEntries.push({name: "Show Data", functionName: "SayugaSolFunction1"});   

   ss.addMenu("SayugaSol Menu", menuEntries);      

 }

// get data to be manipulated

function SayugaSolFunction1(){

  var ss = SpreadsheetApp.getActiveSpreadsheet();

  var sheet = ss.getActiveSheet();

  

  var lastRow = sheet.getLastRow();

  var lastCol = sheet.getLastColumn();

  

  var dataRange = sheet.getRange(1, 1, lastRow, lastCol).getValues();

  

  Logger.log(dataRange);   

  

  var ui = SpreadsheetApp.getUi();

  ui.alert(dataRange);

  

  return dataRange;  

}

//build data template and merge

function SayugaSolFunction(){

  var dataRange = SayugaSolFunction1();

  var docID = "<GOOGLEID>";   

  var doc = DocumentApp.openById(docID);

  var destinationID = "<GOOGLEID>";

  var destinationFolder = DriveApp.getFolderById(destinationID);      

    

  for (var i = 1; i<dataRange.length; i++){

    

    var lName = "letter" +i;

    var newLetter = DriveApp.getFileById(docID).makeCopy(lName, destinationFolder); //Copy template to folder      

    var newDocID = newLetter.getId();

    

    var newDoc = DocumentApp.openById(newDocID);

    var body = newDoc.getBody();

    var currentData = dataRange[i];

    

    for (var x = 0; x< currentData.length;x++){

      var tag = "{"+dataRange[0][x]+"}";

      

      var entry = dateTimeCorrector(x, currentData[x]);

      

      while (body.findText(tag) != null){        

      //  body.replaceText(tag, currentData[x]);

        body.replaceText(tag, entry);

      }      

    }

    newDoc.saveAndClose();    

  }  

}

function dateTimeCorrector(x, entry){

  if (x == 1){

       return Utilities.formatDate(entry, "GMT", "MM-dd-yyyy");

  }

  

  else if (x == 2){

    

    return Utilities.formatDate(entry, "EST","hh:mm a");

  }

  else{

    return entry;

  }

}

[JMR...]

Thank you! I will check this out.

[Lawrence Sheldon]

why are you emailing me?  if you are from github I'll block you.  Skata...@gmail.com ot username lockedwimp might give you a reason why shits about to hit githubs head.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/f7e17ffa-191c-42cd-a491-f4a2120fcb77n%40googlegroups.com.

[JMR...]

@Lawrence Sheldon...I am sorry but I don't know why you responded in that manner. I am simply saying thank you to the person that responded to my question. I wish you a good day!

[Lawrence Sheldon]

Sorry some employees at github have been hacking me and actually using my private unposted pics and videos that were uploaded in my google drive and one drive for 2 years now.

 After I wrote that I checked and you were not one of them to my knowledge so I apologize But per my attorney there's gonna be a big shake up there.

 Sorry again my friend

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/853c129d-74a5-4e7e-985a-3d9aa040193dn%40googlegroups.com.