google-apps-scriptgoogle-drive-apigoogle-docs

How to wait for previous lines of code to finish in Google Script? (flush() & sleep() Can't Be Used)

I have been working on a mail merge template that is fed by google sheet data. I can get the mail merge template to work, but I run into issues while converting the new document into a PDF so it can be attached to an email. For some reason the conversion from .doc to .pdf is happening before the new document's variables are updated. The effect is that a .pdf version is created early, keeping all the placeholders, instead of the updated variables. I know the placeholders are successfully being replaced in the .doc after the .pdf is create, as I can see this happening live. How do I make the script wait for the .doc to update the placeholders before creating the .pdf? Here is the script:

  const receiptTemplate = DriveApp.getFileById('1-wOmJz3Vbvwk1EeLgFCAMEa-bo5Wgwbu3L5m8pFvRUw');
  const destinationFolder = DriveApp.getFolderById('1CpmKci_kV_Dv4m4hNUxbIZIVNXrjwcCH');

  //Generate Receipt Based on Template
  const friendlyDate = new Date(transactData[7]).toLocaleDateString();
  const copy = receiptTemplate.makeCopy(`${transactData[0]} \- ${transactData[1]} \- ${transactData[4]} \- ${friendlyDate}`, destinationFolder)
  const doc = DocumentApp.openById(copy.getId());
  const body = doc.getBody();
  body.replaceText('{{Reference No}}', transactData[0]);
  body.replaceText('{{Customer Name}}', transactData[1]);
  body.replaceText('{{Policy Type}}', transactData[2]);
  body.replaceText('{{Insurance Company}}', transactData[3]);
  body.replaceText('{{Policy Number}}', transactData[4]);
  body.replaceText('{{Payment Amount}}', transactData[5]);
  body.replaceText('{{Payment Method}}', transactData[6]);
  body.replaceText('{{Date}}', friendlyDate);
  body.replaceText('{{Agent}}', transactData[8]);

  //Wait to Make Sure The Docs Body Is Updated Fully Before Creating PDF
  Utilities.sleep(120000);
  SpreadsheetApp.flush();

  //Convert the New Doc Receipt to PDF
  let pdfDoc = doc.getAs('application/pdf');
  pdfDoc.setName(doc.getName() + ".pdf");
  let file = DriveApp.getFolderById('1CpmKci_kV_Dv4m4hNUxbIZIVNXrjwcCH').createFile(pdfDoc);
  let fileId = file.getId();

I have tried both .flush() and .sleep(). Flush() only seems to work on script lines that are running on a Google Sheet directly and not any other Google App (like Docs or Drive). Sleep() seems to pause the execution of all the body.replaceText lines, which just delays the same result as a whole, instead of fixing it.

Solution

  • In your script, it is required to use the saveAndClose method of Class Document instead of the flush method of Class SpreadsheetApp. Because Google Document is updated. So, please modify as follows.

    From

    Utilities.sleep(120000);
SpreadsheetApp.flush();

    To:

    // Utilities.sleep(120000); // I think that this is not required to be used.
doc.saveAndClose(); // doc is from const doc = DocumentApp.openById(copy.getId())

    References: