Script won't wait for sheets page to refresh before saving as pdf

Question

I have a script that saves a report as a pdf in a specific drive folder. The report can be printed in three different ways, depending on an option selected in cell B2 of the spreadsheet. I would like it to save one pdf for each option.

Most of the script works very well. The only problem is that when the script changes the value of cell B2, the sheet doesn't refresh with the new option before it saves as the pdf. So, I get the three differently named pdf's, but they have the same content. The logger and three different pdf's tell me that the for loop is cycling through all three options. But the spreadsheet is not updating to the new option before saving to pdf.

In desperation, I tried putting in a wait command (I know, crappy coding) after the setValue command, to no avail.

function sendWeeklyToFaculty(){
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var folder = DriveApp.getFolderById("0B-m6KcESC3NCSG02WVhtdUVBLTQ");
var reportSheet = spreadsheet.getSheetByName("Faculty Report");

//These are the different report options
var optionArray = ["Option","Student","Percentage"];

//This cycles through each report option
for each (var info in optionArray) {
Logger.log(info);
reportSheet.getRange("B2").setValue(info);

var reportOption = info;
var pdfName ="GA Summary - By "+ reportOption;

//This bit removes any files with the same name
var files = folder.getFilesByName(pdfName + ".pdf");
while (files.hasNext()) {
  files.next().setTrashed(true);
  }

//This bit creates the new file
var sheetId = spreadsheet.getSheetByName("Faculty Report").getSheetId();
var url_base = spreadsheet.getUrl().replace(/edit$/,'');
       var url_ext = 'export?exportFormat=pdf&format=pdf'   //export as pdf

          // Print either the entire Spreadsheet or the specified sheet if optSheetId is provided
          + (sheetId ? ('&gid=' + sheetId) : ('&id=' + spreadsheetId)) 
          // following parameters are optional...
          + '&size=letter'      // paper size
          + '&portrait=true'    // orientation, false for landscape
          + '&fitw=true'        // fit to width, false for actual size
          + '&sheetnames=false&printtitle=false&pagenumbers=false'  //hide optional headers and footers
          + '&gridlines=false'  // hide gridlines
          + '&fzr=false';       // do not repeat row headers (frozen rows) on each page
      var options = {
        headers: {
          'Authorization': 'Bearer ' +  ScriptApp.getOAuthToken(),
        }
      }

  var response = UrlFetchApp.fetch(url_base + url_ext, options);
  var blob = response.getBlob().setName(pdfName + '.pdf');
  folder.createFile(blob);

  //need a bit to send a reminder e-mail to the faculty, once I get the reports to create themselves correctly.
  }
}

Any thoughts on how to make the script wait while the sheet changes, or to force the sheet to refresh?

Perhaps I am misunderstanding something, in which case please correct my mistake. The role of cell "B2" seems ambiguous since it appears that the code creates all three PDF options regardless of the initial (or subsequent) value of cell "B2". In which case, might you be over complicating things by trying to refresh "B2". Instead, just loop through optionArray creating and saving the three PDFs. — Tedinoz
Possible duplicate of Script is clearing data too early, does anyone know why? — tehhowch
@Tedinoz - The role of Cell B2 is not apparent in the script. Changing the value of B2 causes the sheet to reorganize the report, built on queries, which reference cell B2. — DHolcomb
Please share a copy of your spreadsheet with example of the desired output. This should demonstrate the role of cell B2. — Tedinoz

Mario R. Mario R. · Accepted Answer · 2019-07-09T03:52:49

Try to use SpreadsheetApp.flush(); after any changed made to the spreadsheet then save as pdf. If that does not works, use SpreadsheetApp.flush(); and to make the script to wait you can use Utilities.sleep(); after the flush and try again, that worked for me.

Script won't wait for sheets page to refresh before saving as pdf

1 Answers