I'd like to optimise my script for greater efficiency and faster execution time, as the processing is hitting the 6 minute limit.

[StreetSavvy Publishing]

I'm a complete noob at coding but, with some help, I have written a Google Apps Script.

I get teachers to submit student reports via a Google Form. On the form, they select their name (out of 15 faculty members), the student's name (out of 60 students), whether the student is in Middle School or High School, which subject they are reporting on (out of 50 subjects), the grade (type, percent and letter) and their remarks.

As our students take, on average, 6 or 7 subjects, we end up with around 400 form responses. We then need to generate student reports containing the school's logo, the Principal's signature, and all the data for each of the 60 individual students.

To generate the reports in PDF format, I have made a crude attempt at writing a script. I wait until all faculty have submitted all of their form responses and I then take the Form Responses 1 sheet and copy it into another sheet named mainSheet. I also have a detailsSheet which contains values of items which need to be inserted into the reports (school logo image, principal signature image, semester, year and the Folder ID where I want the generated report PDF files to be stored).

The script works and does generate all the correctly formatted and named PDF reports for the students, but it takes around 6 minutes, and I can't help thinking that it's execution time is down to my crude attempt at coding and my code's inefficiency.

Here's the code, and, if required, I can share a sample MainSheet and detailsSheet for testing purposes.

Thank you for whatever insight you can provide on how I could get my code to be more efficient and use less of Google's resources to achieve what I need to do.

function onOpen() {

var spreadsheet = SpreadsheetApp.getActive();

var menuItems = [

{ name: 'Generate Reports...', functionName: 'createDocuments' }

];

spreadsheet.addMenu('Reports', menuItems);

}

function createDocuments() {

// Enable the Advanced Drive Service

function enableAdvancedDriveService() {

return DriveApp.getStorageUsed();

}

// Access the active Google Sheet

var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();

// Access the main sheet containing student data

var mainSheet = spreadsheet.getSheetByName("MainSheet");

// Access the sheet containing additional details

var detailsSheet = spreadsheet.getSheetByName("DetailsSheet");

// Get the data range for the main sheet

var dataRange = mainSheet.getDataRange();

// Get all data from the main sheet

var data = dataRange.getValues();

// Create an object to store data for each student

var studentData = {};

// Loop through each row of data (skipping header row)

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

var row = data[i];

// Extract data from the row

var studentName = row[1];

var program = row[2];

var teacherName = row[0];

var subject = row[3];

var typeGrade = row[4]; // Assuming Grade Type is in column 8 (change as needed)

var percentageGrade = row[5];

var letterGrade = row[6];

var remarks = row[7];

// Check if student data exists in the object

if (!studentData[studentName]) {

// Initialize student data object if it doesn't exist

studentData[studentName] = {

"program": program,

"subjects": []

};

}

// Add subject data to the student's subjects array

studentData[studentName]["subjects"].push({

"teacherName": teacherName,

"subject": subject,

"typeGrade": typeGrade,

"percentageGrade": percentageGrade,

"letterGrade": letterGrade,

"remarks": remarks

});

}

// Loop through each student in the student data object

for (var studentName in studentData) {

if (studentData.hasOwnProperty(studentName)) {

var student = studentData[studentName];

// Create filename for the report

var programInitials = (student["program"] === "High School") ? "HS" : "MS";

var semester = detailsSheet.getRange("B1").getValue();

var semesterShort = semester.slice(0, 2); // Extract first two letters of semester

var year = detailsSheet.getRange("B2").getValue();

var yearShort = year.toString().slice(-2); // Extract last two digits of year

var typeReport = detailsSheet.getRange("B5").getValue(); // Assuming Report Type is in cell B6 (change as needed)

var filename = studentName + " " + programInitials + " " + semesterShort + yearShort + " " + typeReport + " Report.pdf";

// Create a new Google Doc

var doc = DocumentApp.create(filename);

// Access the body of the document

var body = doc.getBody();

// Access the header of the document

var header = doc.addHeader();

// Insert School Logo image

var logoImageFileId = detailsSheet.getRange("B3").getValue();

var logoFile = DriveApp.getFileById(logoImageFileId);

var logoImg = header.appendImage(logoFile.getBlob());

var styles = {};

styles[DocumentApp.Attribute.HORIZONTAL_ALIGNMENT] = DocumentApp.HorizontalAlignment.CENTER;

logoImg.getParent().setAttributes(styles);

// Insert student details

var reportHeader = studentName.toUpperCase() + "\n" +

student["program"].toUpperCase() + " - " + typeReport.toUpperCase() + " REPORT\n" +

semester.toUpperCase() + ", " + year;

body.appendParagraph(reportHeader).setHeading(DocumentApp.ParagraphHeading.HEADING2).setAlignment(DocumentApp.HorizontalAlignment.CENTER).setBold(true);

// Loop through each subject for the student

for (var j = 0; j < student["subjects"].length; j++) {

var subjectData = student["subjects"][j];

// Insert subject and teacher details on the same line

body.appendParagraph(""); // Add an empty paragraph

body.appendParagraph(subjectData["subject"] + " - " + subjectData["teacherName"]).setFontSize(12).setBold(true);

body.appendParagraph(subjectData["typeGrade"] + ": " + subjectData["percentageGrade"] + "% (" + subjectData["letterGrade"] + ")").setFontSize(12).setBold(false);

body.appendParagraph("Remarks: " + subjectData["remarks"]);

body.appendParagraph(""); // Add an empty paragraph

}

// Insert Principal's signature and stamp image

var signatureImageFileId = detailsSheet.getRange("B4").getValue();

var signatureFile = DriveApp.getFileById(signatureImageFileId);

body.appendImage(signatureFile.getBlob());

// Insert disclaimer below Principal's signature and stamp image

var disclaimer = "Please note that this document has no official validity and is solely for informative purposes.";

body.appendParagraph(""); // Add an empty paragraph

body.appendParagraph(disclaimer).setFontSize(8).setAlignment(DocumentApp.HorizontalAlignment.LEFT);

// Save the document

doc.saveAndClose();

// Convert the document to PDF

var pdf = DriveApp.getFileById(doc.getId()).getAs('application/pdf');

// Get the folder ID where you want to store the generated reports

var folderId = detailsSheet.getRange("B6").getValue();

// Access the specified folder

var folder = DriveApp.getFolderById(folderId);

// Create the PDF file in the specified folder

var pdfFile = folder.createFile(pdf);

// Delete the temporary Google Doc

DriveApp.getFileById(doc.getId()).setTrashed(true);

}

}

}

[George Ghanem]

In general, you need to avoid calling the spreadsheet methods in a loop. Especially if its a potential large set of data you are Looping through.

Try grabbing the whole sheet data in one shot as you did at the top of your function and loop through the data rather than calling getValue() over and over inside the loop.

That should shorten your script time execution.

--
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/d27d1145-e25d-4f4e-a4f8-1330f3c8b2b0n%40googlegroups.com.

[StreetSavvy Publishing]

Thank you George, I'm a bit of a coding noob, but I'll see what I can do. I assume you mean to pull the few values from the detailsSheet (cells B1:B7) at the beginning, so they can then be used when looping through the students to create the filename , insert the images and select the folder ID to store the reports?
If so, pointers on what I'd need to remove from the loop and sample code of how to achieve it would be most appreciated, but I'll start by giving it a go myself - thanks!

[Edward Friedman (Eddie)]

You're right about removing those calls accessing cells B1:B7 from the body of the loop. You can safely move those and the values derived from them above the loop. That will shave a little time off of your execution.

I think  you'll save even more time by separating out the conversion of the docs into PDFs into its own function. This effectively doubles your execution time limit.

The code below does both of the above, but it is far from perfect. For example, you will need to add a folder id for the folder that will temporarily store the Google Docs version of the reports. If you can add it to cell B7 on the Details Sheet, you're fine, but if you add it to another cell, you'll need to change that in 2 places in the code (both the 'createPdfReports' and 'createDocReports' functions). This is generally poor practice, but it is late and I wanted to share something helpful. Also, I didn't have any data to test this on, so it is possible there are typos or issues with it. If you try it out and run into any problems, it would be helpful if you could make a copy of your spreadsheet, delete all but a few students, change their names (and any other personally identifiable data) to something generic like 'Student Name1', and share it so I could debug any issues.

function onOpen() {

  SpreadsheetApp.getUi().createMenu('Reports')

    .addItem('Generate Report Docs...', 'createDocReports')

    .addItem('Generate Report PDFs...', 'createPdfReports')

    .addSeparator()

    .addItem('Generate Report PDFs in 1 Step', 'allInOne')

    .addToUi();

}

//called from the custom menu created in onOpen to create the doc reports

function createDocReports() {

  //get the active Google Sheet

  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();

 

  // Access the sheet containing additional details

  const detailsSheet = spreadsheet.getSheetByName("DetailsSheet");

 

  //Define constants

  const semester = detailsSheet.getRange("B1").getDisplayValue();

  const semesterShort = semester.slice(0, 2); // Extract first two letters of semester

 

  const year = detailsSheet.getRange("B2").getDisplayValue();

  const yearShort = year.slice(-2); // Extract last two digits of year

 

  const logoFile = DriveApp.getFileById(detailsSheet.getRange("B3").getValue());

  const signatureFile = DriveApp.getFileById(detailsSheet.getRange("B4").getValue());

   

  const typeReport = detailsSheet.getRange("B5").getDisplayValue(); // Assuming Report Type is in cell B6 (change as needed)

 

  //The suffix to add to every doc header

  const headerSuffix = typeReport.toUpperCase() + " REPORT\n" + semester.toUpperCase() + ", " + year

 

  //The suffix to add to every pdf filename

  const filenameSuffix = " " + semesterShort + yearShort + " " + typeReport + " Report.pdf";

 

  //Access the Google drive Folder where the PDF files will be created

  const pdfFolder = DriveApp.getFolderById(detailsSheet.getRange("B6").getDisplayValue()); //Assuming the folder id is in cell B6 (change as needed)

 

  //Access the Google drive Folder where the docs will be created

  const docFolder = DriveApp.getFolderById(detailsSheet.getRange("B7").getDisplayValue()); //Assuming the folder id is in cell B7 (change as needed)

 

  // Access the main sheet containing student data

  const mainSheet = spreadsheet.getSheetByName("MainSheet");

 

  const studentData = getStudentData(mainSheet, filenameSuffix, headerSuffix);

  createDocs(studentData, docFolder, logoFile, signatureFile);

}

//called from the custom menu created in onOpen to convert doc reports into PDF reports

function createPdfReports() {

  //get the active Google Sheet

  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();

   

  // Access the sheet containing additional details

  const detailsSheet = spreadsheet.getSheetByName("DetailsSheet");

 

  //Access the Google drive Folder where the PDF files will be created

  const pdfFolder = DriveApp.getFolderById(detailsSheet.getRange("B6").getDisplayValue()); //Assuming the folder id is in cell B6 (change as needed)

 

  //Access the Google drive Folder where the docs will be created

  const docFolder = DriveApp.getFolderById(detailsSheet.getRange("B7").getDisplayValue()); //Assuming the folder id is in cell B7 (change as needed)

   

  convertDocsToPdf(docFolder, pdfFolder);

}

//helper function to get the initials for the program

function getProgramInitials(programName) {

  return (programName === "High School") ? "HS" : "MS";

}

//reads the student data from the spreadsheet and putsit into an object

function getStudentData(sh, filenameSuffix, headerSuffix) {

  // Get all data from the main sheet

  const data = sh.getDataRange().getValues();

 

  //remove the header row

  data.shift();

 

  // Create an object to store data for each student

  const studentData = {};

 

  // Loop through each row of data (skipping header row)

  for (let row of data) {    

    // Extract data from the row

    let studentName = row[1];

    let program = row[2];

    let teacherName = row[0];

    let subject = row[3];

    let typeGrade = row[4]; // Assuming Grade Type is in column 8 (change as needed)

    let percentageGrade = row[5];

    let letterGrade = row[6];

    let remarks = row[7];

   

    // Check if student data exists in the object

    if (!studentData[studentName]) {

      // Initialize student data object if it doesn't exist

      studentData[studentName] = {

        "program": program,

        "subjects": [],

        "filename": studentName + " " + getProgramInitials(program) + filenameSuffix,

        "header": studentName.toUpperCase() + "\n" + program.toUpperCase() + " - " + headerSuffix

      }

    }

   

    // Add subject data to the student's subjects array

    studentData[studentName]["subjects"].push({

      "teacherName": teacherName,

      "subject": subject,

      "typeGrade": typeGrade,

      "percentageGrade": percentageGrade,

      "letterGrade": letterGrade,

      "remarks": remarks

    });

  }

 

  return studentData;

}

//creates the Google Doc files

function createDocs(studentData, docFolder, logoFile, signatureFile) {

  const disclaimer = "Please note that this document has no official validity and is solely for informative purposes.";

     

  // Loop through each student in the student data object

  for (let student in studentData) {

    // Create a new Google Doc

    let doc = DocumentApp.create(student["filename"]);

   

    //Put it in the docFolder for easy access if we need to run file creation and pdf conversion separately in the future

    DriveApp.getFileById(doc.getId()).moveTo(docFolder);

       

    // Access the body of the document

    let body = doc.getBody();

   

    // Access the header of the document

    let header = doc.addHeader();

    // Insert School Logo image

    let logoImg = header.appendImage(logoFile.getBlob());

    let styles = {};

    styles[DocumentApp.Attribute.HORIZONTAL_ALIGNMENT] = DocumentApp.HorizontalAlignment.CENTER;

    logoImg.getParent().setAttributes(styles);  

     

    // Insert student details

    let reportHeader = student["header"];

    body.appendParagraph(reportHeader)

      .setHeading(DocumentApp.ParagraphHeading.HEADING2)

      .setAlignment(DocumentApp.HorizontalAlignment.CENTER)

      .setBold(true);

     

    // Loop through each subject for the student

    for (let subj of student["subjects"]) {  

      // Insert subject and teacher details on the same line

      body.appendParagraph(""); // Add an empty paragraph

      body.appendParagraph(subj["subject"] + " - " + subj["teacherName"])

        .setFontSize(12)

        .setBold(true);

      body.appendParagraph(subj["typeGrade"] + ": " + subj["percentageGrade"] + "% (" + subj["letterGrade"] + ")")

        .setFontSize(12)

        .setBold(false);

      body.appendParagraph("Remarks: " + subj["remarks"]);

      body.appendParagraph(""); // Add an empty paragraph

    }

     

    // Insert Principal's signature and stamp image

    body.appendImage(signatureFile.getBlob());

     

    // Insert disclaimer below Principal's signature and stamp image

    body.appendParagraph(""); // Add an empty paragraph

    body.appendParagraph(disclaimer)

      .setFontSize(8)

      .setAlignment(DocumentApp.HorizontalAlignment.LEFT);

     

    // Save the document

    doc.saveAndClose();

  }

}

//converts all of the Google Doc files in the docFolder into PDFs, puts the PDFs in the pdfFolder, and trashes the Google Doc file

function convertDocsToPdf(docFolder, pdfFolder) {

  const docFiles = docFolder.getFilesByType(MimeType.GOOGLE_DOCS);

  for (let docFile of docFiles) {

    // Convert the document to PDF

    let pdf = docFile.getAs('application/pdf');

     

    // Create the PDF file in the specified folder

    let pdfFile = pdfFolder.createFile(pdf);

     

    // Delete the temporary Google Doc

    docFile.setTrashed(true);

  }

}

//does everything in one click of a button

function allInOne() {

  createDocReports();

  createPdfReports();

}

[StreetSavvy Publishing]

Eddie, that is really helpful, thank you so much! However, I do run into an error:

Error

Exception: Invalid argument: name

createDocs

@ Code.gs:532

createDocReports

@ Code.gs:452

In createDocs, the line at 532 is 

let doc = DocumentApp.create(student["filename"]);

and, in createDocReports, the line at 452 is:

createDocs(studentData, docFolder, logoFile, signatureFile);

I have created a Sample Sheet for your use. It contains a Main Sheet with over 300 rows of student data (generated as a sample dataset with fictitious names, etc), and a Details Sheet where I have completed the Semester, Year and Report Type, but have left blank the file IDs for the two images (logo and Principal's sig) and the folder IDs for the locations where I would like to store the generated reports.

The disclaimer in my original code can now be removed, as I have included that line in the Principal's signature image, so it doesn't get separated from that image in the generated reports.

If you are able to test using the Sample Sheet, that would be wonderful - feel free to remove or change as many rows as you need in that sheet, and/or create new sheets with differently sized datasets - the Sample Sheet is available here:

https://docs.google.com/spreadsheets/d/1yxgDLMw4pWarWNHc1LSix03k5DJo1emNoqIORpUdcZU/edit?usp=sharing

Again, thank you so much for helping with this, I was at a loss as to how to get it going and remain within the execution timeout limits.

Kind regards,

[Edward Friedman (Eddie)]

Thanks for creating that Sample Sheet! It was perfect.

I spent a bit of time playing around with this. I was surprised to find that creating the initial documents is the part of this process that takes the most time. Converting the script to use a template file to create your reports successfully shaved off some processing time. However, if you ever have more students (let's say 100) you would still come up against the script processing time limit. 

So I borrowed from Phil Bainbridge's work here to allow the scripts to gracefully exit when processing time is close to the max run time, and keep track of which reports have been generated. This required the addition of a column I in the data sheet.

I did something similar with the conversion to PDF process. It compares the files that already exist in the Google Doc folder and the PDF folder and only processes the files that don't yet exist in the PDF folder.

I also created several helper functions to make the main functions a little more readable. You can check it all out here:

• Google Sheet Sample v2: https://docs.google.com/spreadsheets/d/1vArRddQkL9tHLHpuAkcKEq2hWs7Bp37yT8D4f73qle4/edit?usp=sharing
• Template Google Doc: https://docs.google.com/document/d/1UQ9cQP3d9-nnoaZYz83p5_etUbzp-5r23ePf48q1yVA/edit?usp=sharing

I tried to comment as much as humanly possible within the code. If you have questions, please let me know. I'm happy to help.

-Eddie

[Phil Bainbridge]

Glad my post has been helpful Eddie. It's my typical approach as I'm usually automating at scale and no matter how efficient it is you'll inevitably hit the runtime limit at some point.

Kind regards

Phil

[StreetSavvy Publishing]

This is just brilliant!

Thank you so much for your help. 

I made a copy so feel free to delete the shared sample whenever you want.

Will have a play around with it now, but, honestly, you have no idea how much this will change a tired old teacher's life!

I owe you many beers!

Thank you, again, for your time and your generosity.

Very best wishes!

[Edward Friedman (Eddie)]

Feel free to reach out again in the future. I'm a former elementary special education teacher myself and I love to help folks who are still out there teaching.

-Eddie Friedman