email - Script is clearing data too early, does anyone know why?

Question

So I'm working on a project in Google Sheets, using scripting, that will eventually do the following;

Firstly, based on a name in a Cell , find the last 9 entries for that person in form responses.

It then arranges that data in a way that I need and writes it to a sheet, within my spreadsheet

The last part of the script (not my own work, but something i found here) Script I found online I've tried to adapt for my needs, not quite there yet. Creates a PDF, saves it in google drive then emails it.

This part requires a bit more work, as I want to specify what the PDF is called using the name and date. Also I'd like to specify where it's saved in google. Lastly the script only produces one PDF. Would like to eventually duplicate the script so I can either create 1 PDF or create them in batches. Will possibly post about these later, if I get stuck.

So anyways that is the overview.

Currently the script works and can query the data I want, write it to a sheet, save it to drive as PDF and email it to a single hard-coded email address. Awesomeness.

But I then tried to add a function called clearRanges which would clear out the template sheet before writing data. I used name ranges to define the 3 sections to clear. But since introducing it, and i've tried it in various parts of my script. I'm getting blank PDF's in my drive and by email.

It's like it's not waiting for the PDF to be created or email to be sent before clearing data. I've tried to put it at the start of my script too, but same thing. Got no idea why.

I was playing around with lock and waitlock as a possible solution, but it didn't seem to help.

If anyone can help out, I'd appreciate it.

  function getAgentName() {
      var ss = SpreadsheetApp.getActiveSpreadsheet();
      Browser.msgBox("Please go to the sheet called PDF Creator, in cell A2, choose the agent you wish to create a PDF for");
      var sheet = ss.getSheetByName("PDF Creator");
      var range = sheet.getRange("A2")
      var value = range.getValue();

      if (value == 0) {
          Browser.msgBox("You need to go to the sheet named PDF Creator and put an agent name in cell A2");
      } else {
          getAgentData(value);
      }
  }

  function getAgentData(value) {
      var ss = SpreadsheetApp.getActiveSpreadsheet();
      var sourceSheet = ss.getSheetByName("Form responses 1")
      var sourceRange = sourceSheet.getDataRange();
      var sourceValues = sourceRange.getValues();
      var agentData = [];
      var commentsData = [];

      for (i = 0; i < sourceValues.length; i++) {
          // Defines the data layout for PDF.
          var agentName = sourceValues[i][2];
          var dateTime = sourceValues[i][3];
          var callType = sourceValues[i][7];
          var opening = sourceValues[i][8];
          var rootCause = sourceValues[i][9];
          var rootFix = sourceValues[i][10];
          var process = sourceValues[i][11];
          var consumer = sourceValues[i][12];
          var control = sourceValues[i][13];
          var wrapup = sourceValues[i][14];
          var dpa = sourceValues[i][15];
          var score = sourceValues[i][22];
          var comments = sourceValues[i][16];
          var agentRow = [dateTime, callType, opening, rootCause, rootFix, process, consumer, control, wrapup, dpa, score];
          var commentsRow = [dateTime, comments];

          if (agentName == value && agentData.length < 9) {
              agentData.push(agentRow)
              commentsData.push(commentsRow)
          }
      }

      agentData.sort(function (a, b) {
          return b[0] - a[0]
      });

      commentsData.sort(function (a, b) {
          return b[0] - a[0]
      });

      var destSheet = ss.getSheetByName("AgentPDF");
      destSheet.getRange("A1").setValue(value + "'s Quality Score card");
      var range = destSheet.getRange(6, 1, agentData.length, agentData[0].length);
      range.setValues(agentData);
      var commentRange = destSheet.getRange(18, 1, commentsData.length, commentsData[0].length);
      commentRange.setValues(commentsData);
      emailSpreadsheetAsPDF();
  }

  /* Send Spreadsheet in an email as PDF, automatically */
  function emailSpreadsheetAsPDF() {
      // Send the PDF of the spreadsheet to this email address
      var email = "[email protected]";
      // Subject of email message
      // The date time string can be formatted in your timezone using Utilities.formatDate method
      var subject = "PDF Reports - " + (new Date()).toString();
      // Get the currently active spreadsheet URL (link)
      // Or use SpreadsheetApp.openByUrl("<<SPREADSHEET URL>>");
      var ss = SpreadsheetApp.getActiveSpreadsheet();
      // Email Body can  be HTML too with your logo image - see ctrlq.org/html-mail
      var body = "PDF generated using code at ctrlq.org from sheet " + ss.getName();
      var url = ss.getUrl();
      url = url.replace(/edit$/, '');
      /* Specify PDF export parameters
  // From: https://code.google.com/p/google-apps-script-issues/issues/detail?id=3579
    exportFormat = pdf / csv / xls / xlsx
    gridlines = true / false
    printtitle = true (1) / false (0)
    size = legal / letter/ A4
    fzr (repeat frozen rows) = true / false
    portrait = true (1) / false (0)
    fitw (fit to page width) = true (1) / false (0)
    add gid if to export a particular sheet - 0, 1, 2,..
  */
      var url_ext = 'export?exportFormat=pdf&format=pdf' // export as pdf
      + '&size=a4' // paper size
      + '&portrait=1' // orientation, false for landscape
      + '&fitw=true&source=labnol' // fit to width, false for actual size
      + '&sheetnames=false&printtitle=false' // hide optional headers and footers
      + '&pagenumbers=false&gridlines=false' // hide page numbers and gridlines
      + '&fzr=false' // do not repeat row headers (frozen rows) on each page
      + '&gid=928916939'; // the sheet's Id
      var token = ScriptApp.getOAuthToken();
      //  var sheets = ss.getSheets(); 
      //make an empty array to hold your fetched blobs  
      var blobs = [];
      //  for (var i=0; i<sheets.length; i++) {
      // Convert individual worksheets to PDF
      //    var response = UrlFetchApp.fetch(url + url_ext + sheets[i].getSheetId(), {
      var response = UrlFetchApp.fetch(url + url_ext, {
          headers: {
              'Authorization': 'Bearer ' + token
          }
      });

      //convert the response to a blob and store in our array
      blobs[0] = response.getBlob().setName("Tester " + '.pdf');
      //  }
      //create new blob that is a zip file containing our blob array
      //  var zipBlob = Utilities.zip(blobs).setName(ss.getName() + '.zip'); 
      var test = DriveApp.createFile(blobs[0]);
      //optional: save the file to the root folder of Google Drive
      DriveApp.createFile(test);
      // Define the scope
      Logger.log("Storage Space used: " + DriveApp.getStorageUsed());

      // If allowed to send emails, send the email with the PDF attachment
      if (MailApp.getRemainingDailyQuota() > 0)
          var lock = LockService.getScriptLock();
      GmailApp.sendEmail(email, subject, body, {
          attachments: [test]
      });

      lock.waitLock(20000);
      lock.releaseLock();
      clearRanges();
  }

  function clearRanges() {
      var ss = SpreadsheetApp.getActiveSpreadsheet();
      ss.getRangeByName('Header').clearContent();
      ss.getRangeByName('Scores').clearContent();
      ss.getRangeByName('Comments').clearContent();
  }

Answer 1

Can you try adding SpreadsheetApp.flush(); around line 60 before calling emailSpreadsheetAsPDF();

SpreadsheetApp.flush()

commentRange.setValues(commentsData);
SpreadsheetApp.flush();
emailSpreadsheetAsPDF();

I've faced a similar problem before and this worked.