I am teacher (which means that I don't really know what I am doing) who is trying to make a routine documentation faster for our staff. I have created a google sheet that will embed information from the cell row onto a google doc. I would like to figure out a way that when I run the script it will run it for each row and collect all of it onto a pdf so I print data from multiple students all at once. Here is a link to my google sheets document that I am running this script.

Any ideas on how I could make this work? The script I currently have works for the first row but it will not continue to any additional rows.

 var data = sheet.getRange(4,1,sheet.getLastRow(),
 sheet.getLastColumn()).getValues();
//var data = sheet.getRange(4,1,1,sheet.getLastColumn()).getValues();


var i;
  for (i = 0; i < data.length ; i++) 
    var row = data[i++];{

  // setting up the temporary document
    var docid = DriveApp.getFileById(templateid).makeCopy().getId();
    var doc = DocumentApp.openById(docid);
    var body = doc.getBody();

 // I have removed the parts that gather data and the replace text.


    ss.toast("Creating your ALE document");
    Utilities.sleep(sleepINT);


  var file = DriveApp.getFileById(doc.getId());
  var newfolder = DriveApp.getFolderById("1oQ8evDj8dlHoDdX01DvZqjcIkSq31oen");
  var oldfolder = DriveApp.getFolderById("1IzY9PiobBC-O87AxCkU32j2n2wUU1zUE");
  newfolder.addFile(file);
  oldfolder.removeFile(file);

  ss.toast("PDF has been created!");
  Utilities.sleep(sleepINT);

  var usernamefordoctitle = sheet.getRange(4,1,1,1).getValues()
  var name = doc.getName();
  doc.setName(month + " ALE for " + studentName);
  ss.toast("Document has been named");
  Utilities.sleep(sleepINT);

  doc.saveAndClose();
  var pdffolder = DriveApp.getFolderById ("1iwBEUZpFwz_eaCVKemYozF5FA0_t0YGv");
  var pdfFILE = DriveApp.getFileById(doc.getId()).getAs('application/pdf');
  pdfFILE.setName(doc.getName() + ".pdf");
  var theFile = DriveApp.createFile(pdfFILE);
  pdffolder.addFile(theFile);
  ss.toast("Document has been saved to Google Drive");
  Utilities.sleep(sleepINT);

  var pdfEMAIL = DriveApp.getFileById(doc.getId()).getAs('application/pdf').getBytes();
  var message ="This is your" + month + "ALE documents for your AG student."
  var emailTo = emailAddress;
  var subject ="Your " + month + " ALE for " + studentName + " 😀";
  var message ="The attached pdf document is your " + month + " monthly ALE document for " + studentName + ".";

  MailApp.sendEmail(emailTo, subject, message, {attachments: pdfFILE});
  ss.toast("Email has been sent");
}  
  Utilities.sleep(sleepINT);
  ss.toast("🎉 FINISHED!!!! 🎉");
}
  • I have reduced the lines to only what I have for the loop. As stated earlier, it only runs through the script once and does not repeat for any additional rows I have in the script. – Mike Johnson Aug 30 at 16:26
  • I added the entire function that is supposed to loop except for the replacing of text when it gets embedded into the google doc. – Mike Johnson Aug 30 at 16:29
  • 1
    Then view the Stackdriver Logs after executing, and report in your question your findings. Also, note that you double-increment your iterator i: var row = data[i++] and for(var i = 0; i < data.length; i++). Should be var row = data[i]; – tehhowch Aug 30 at 16:36
  • 1
    You don't seem to have the opening curly brace for your 'for' lop here. for (i = 0; i < data.length ; i++) var row = data[i++];{ The proper syntax for multiple statements within the 'for' loop is "for (ExpressionNoIn ; Expression ; Expression) { Statement; Statement; Statement; }". However, since your script is partial, it's not clear whether you somehow affected the code by Ctrl + C – Anton Dementiev Aug 30 at 18:22
  • 1
    @Rubén I think it should probably be closed until OP can clarify what is going on – tehhowch Aug 31 at 14:49

I'm pretty basic with loops, but shouldn't they be like so:

for (i = 0; i < data.length ; i++){ <--- the curly brace here.

try that?

  • 1
    The braces are required for a multi-statement body. If you have a single statement, they are optional. The same goes for other conditional code, such as ifs. E.g. if (a === b) c = d + e; – tehhowch Aug 31 at 11:54
  • @tehhowch aaaah okay, thanks for explaining that! – RichTea Aug 31 at 12:31
  • @tehhowch It's kind of the strange that the OP wouldn't want to include multiple statements in the 'for' loop as his main issue, from what I can gather, lies in the 'for' loop executing only once. Otherwise, the sole purpose of the loop would be to update the 'row' variable. But then again, the code is somewhat messy, so maybe I'm reading into something into it. – Anton Dementiev Aug 31 at 14:49
  • 1
    @AntonDementiev the OP changed the code in their question on my request (they originally had a long wall, then chopped a bunch out, then added some back in). In the original code, there was an opening brace - hence my comments that I think OP's issue is unrelated to the code currently shown here. Based on the question title / description, I think we all agree that OP wants several statements in the for-loop body. I've voted to close until OP can clarify their question with an MCVE, and logging/debug information that confirm that it only executes once. – tehhowch Aug 31 at 14:57

NOTE: The following referred code on this answer was introduced by the OP on revision 3.

The statement block after var row = data[i++]; is not part of the for-loop, the for- loop only include one statement.

for (i = 0; i < data.length ; i++) 
    var row = data[i++];

While JavaScript is very flexible it could be helpful, specially for those who aren't aware of any particular JavaScript styles guideline, to use the same style as it's used on samples included on https://developers.google.com/apps-script

  1. Start statement blocks on the same line

    Example 1:

    function myFuncion(){
    
    }
    

    Example 2:

    for(var i = 0;i < data.length; i++){
    
    }
    
  2. Use line terminators ;

  3. etc.

  • If you look at the original code in the question, this wasn't an issue there. – tehhowch Aug 31 at 14:20
  • @tehhowch I could understand that if my post was criticizing another post, but this answer is pointed to answer the current question revision. The referred code on my answer was introduced by the OP on revision 3. – Rubén Aug 31 at 14:26

Your Answer

 

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.