Google script running too slow

Question

I am currently writing a google script, which unfortunately runs too slow. (More than the 6 min limit). The script opens a document, substitutes two strings (which are being set in a google sheet), saves it out as a PDF. Nothing fancy.

I have roughly 200 of these documents to run through this script, but within the 6 minute limit, it only manages to go through six. Is google script just this slow, or have I accidentally made the most inefficient google script ever?

function createAllPDF() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var range = SpreadsheetApp.getActiveSheet().getActiveRange()
  var numRows = SpreadsheetApp.getActiveSpreadsheet().getLastRow() - 1;
  for(var i = 9; i <= numRows; i++) {
    var thisRange = sheet.getRange("A" + n + ":C" + n);
    var fond = thisRange.getCell(1, 1).getValue();
    var adresse = thisRange.getCell(1, 3).getValue();
    thisRange.setBackground('#cfe2f3');
    genDoc(fond, adresse);
  }
}
//// CREATE PDF ////////////////// FUNCTION FOR GENERATING THE PDF  /////////////////////////////////
function genDoc(fond, adresse) {
  // Finds the template and duplicate it into a new file.
  var template = ("xxxxxxxxxxxx");
  var docId = DriveApp.getFileById(template).makeCopy().getId();
  // Opens the newly created Document for editing
  var doc = DocumentApp.openById(docId);
  var body = doc.getActiveSection();
  // Renames the newly generated document
  var newName = doc.setName(fond);
  // Replaces each with the parsed variables.
  body.replaceText("%FOND%", fond);
  body.replaceText("%ADRESSE%", adresse);
  doc.saveAndClose();
  //Adds the PDF ID to the invoice_input sheet
  var conv = DriveApp.getFileById(docId);
  var pdf = conv.getAs("application/pdf");
  var fileId = DriveApp.createFile(pdf).getId();
  // Gets the PDF file by ID
  var thisPDF = DriveApp.getFileById(fileId);
  // The ID of the folder I'd like to put the PDF into.
  var folderId = "xxxxxxxxxxx";
  // Gets the folder by ID
  var targetFolder = DriveApp.getFolderById(folderId);
  // Adds the PDF to the Folder
  targetFolder.addFile(thisPDF);
  // Removes the PDF from the root.
  var root = DriveApp.getRootFolder().removeFile(thisPDF);
  // Deletes the duplicated document
  DriveApp.getFileById(docId).setTrashed(true)
  return fileId;
}

Any pointers on how to optimize will greatly be appreciated. I am very new to google script and programming in general, so no big words, hehe. Apologize if I am using this board Incorrectly. Please let me know and I will correct.

Answer 1

I immediately notice that you have an undefined variable n. It is in the line:

var thisRange = sheet.getRange("A" + n + ":C" + n);

This should have made the code entirely unuseable. With these variables changed to i, I was able to successfully run the code for the full 6 minutes. During that time, it was able to loop about 41.5 times. It created files for rows 9-53 but stopped before being able to add the last file to the correct folder.

Looking at the execution transcript, your longest operations are the several calls to create, move and delete files.

You also define range and then never use this variable, making it unnecessary.

I rewrote the code in the structure and method I have used for myself many times. I was able to fully process rows 9-59. This method was able to extend it by 6 rows. I would suggest adding in a timeout trigger to stop the function every 5min55sec and then restart it again 5 seconds later. Guides on javascript time can be found here. I use a different match method as well; using RegEx. There are extensive guides on RegEx and how to minimize that processing time. I am nowhere near proficient in this area.

function PDFCreator() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Test Sheet");
  var lastRow = sheet.getLastRow();
  var checkRange = sheet.getRange(9, 1, (lastRow - 8), 3);
  var check = checkRange.getBackgrounds();
  var lightBlue = "#cfe2f3";
  var template = DriveApp.getFileById("1RslWGntAwfLTSytOv_IoOv2_iBhSmsK0ZtEVWaq3ezM");
  var folder = DriveApp.getFolderById("0BwZ6LWJudkOHaTFiQjd5cFA5OG8");
  for (i = 0; i < check.length; i++) {
    if (check[i] == lightBlue) {
      continue;
    } else {
      var dataRow = sheet.getRange((i + 9), 1, 1, 3);
      var fond = sheet.getRange((i + 9), 1, 1, 1).getValue();
      var adresse = sheet.getRange((i + 9), 3, 1, 1).getValue();
      var docName = fond + ".pdf";
      var docCopy = template.makeCopy(docName, folder);
      var docId = docCopy.getId();
      var docToEdit = DocumentApp.openById(docId);
      var docBody = docToEdit.getBody();
      docBody.replaceText(/\%{1}[F][O][N][D]\%{1}/g, fond);
      docBody.replaceText(/\%{1}[A][D][R][E][S][S][E]\%{1}/g, adresse);
      docToEdit.saveAndClose();
      var fileToPDF = DriveApp.getFileById(docId);
      var pdfBlob = fileToPDF.getAs(MimeType.PDF);
      var pdfRoot = DriveApp.createFile(pdfBlob).setName(docName);
      var pdf = pdfRoot.makeCopy(folder);
      pdfRoot.setTrashed(true);
      fileToPDF.setTrashed(true);
      dataRow.setBackground(lightBlue);
    }
  }
}

You'll notice that I nested the for() and if() inside the main function. That way, you are not passing information back and forth between functions over and over. In general, the more that you can define outside the loops, the less calls you will have to make. There are more variables that I could have set outside the for loop to further extend it's run.

Basically, it's a long process and it's not going to be able to run it 200 times in 6min. You can extend this to maybe 55/60 total rows with perfect efficiency but at that point you'll just need to have it run again.

Answer 2

(Note: haven't tested this, but should get you started)

Some ideas:

1. Move the "getRange" command out of the for loop.
2. Read the template, targetFolder and rootFolder outside of the for loop and pass them as arguments to genDoc.
3. Re-use "sheet" and "conv", where possible.
4. Write all bgColors at once, instead of individually.
5. Do not return anything.

So something like this:

---

function createAllPDF() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var numRows = sheet.getLastRow() - 1;
  var range = sheet.getActiveRange();
  var values = range.getValues();
  var templateId = ("xxxxxxxxxxxx");
  var template = DriveApp.getFileById(templateId);
  // The ID of the folder I'd like to put the PDF into.
  var folderId = "xxxxxxxxxxx";
  // Gets the folder by ID
  var targetFolder = DriveApp.getFolderById(folderId);
  var rootFolder = DriveApp.getRootFolder();
  for(var i = 9; i <= numRows; i++) {
    var fond = values[i][1]; // TODO: check syntax.
    var adresse = values[i][3];
    genDoc(fond, adresse, template, targetFolder, rootFolder);
  }
  range.setBackground('#cfe2f3'); // TODO: only first 3 columns
}
//// CREATE PDF ////////////////// FUNCTION FOR GENERATING THE PDF  /////////////////////////////////
function genDoc(fond, adresse, template, targetFolder, rootFolder) {
  // Finds the template and duplicate it into a new file.
  var conv = template.makeCopy();
  var docId = conv.getId();
  // Opens the newly created Document for editing
  var doc = DocumentApp.openById(docId);
  var body = doc.getActiveSection();
  // Renames the newly generated document
  var newName = doc.setName(fond);
  // Replaces each with the parsed variables.
  body.replaceText("%FOND%", fond);
  body.replaceText("%ADRESSE%", adresse);
  doc.saveAndClose();
  //Adds the PDF ID to the invoice_input sheet
  var pdf = conv.getAs("application/pdf");
  var fileId = DriveApp.createFile(pdf).getId();
  // Gets the PDF file by ID
  var thisPDF = DriveApp.getFileById(fileId);
  // Adds the PDF to the Folder
  targetFolder.addFile(thisPDF);
  // Removes the PDF from the root.
  rootFolder.removeFile(thisPDF);
  // Deletes the duplicated document
  conv.setTrashed(true);
}

Answer 3

If your code has been improved, look at using triggers but remember the code needs to delete the trigger once finished as there is a limit to the number you can create.

The initial code should set a list in a sheet of documents that need to be created and then set a trigger to run the create document.

The create document code could then check to see if there are any more documents to be created from the list and if so create the document and set the next trigger deleting the old trigger.

That way one document will run successfully and if there are more to be done the next will be triggered shortly afterwards.

Remember as well as the 6 min limit there is also the total time limit.