Google Apps Script fetching the wrong PDF when creating blob from UrlFetchApp.fetch

48 Views Asked by At

I have a google apps scrip which:

  • Reads a google sheet (the template)
  • Copies the template, saves it to google drive and gives it a new name
  • I then edit this new sheet (change date and number).

The final step is to email this new sheet as PDF. I can't find the bug, but the PDF created is always the original template, instead of the newly created sheet that I reference by ID. Things I have tried:

  • Open the new spreadsheet and verify it has been edited correctly (yes, this works)

  • Call the function with a hardcoded ID. Something like: sendExportedSheetAsPDFAttachment('2l0e8OdB8[....]yxSR7234s', 'test_pdf.pdf') (hardcoding works and sends the correct PDF)

  • Introduce a delay (Utilities.sleep(80000);), to make sure the sheet is edited before creating the PDF blob (doesn't work)

Here are some of the functions involved

function getFileAsBlob(exportUrl, pdfName){
  let params = { method: "GET", headers: { "authorization": "Bearer " + ScriptApp.getOAuthToken() } };
  let blob_file = UrlFetchApp.fetch(exportUrl, params).getBlob().setName(pdfName);
  return blob_file;
}

And

function sendExportedSheetAsPDFAttachment(newSpreadsheetId, pdfName ) {
  
  // CONVERT SHEET TO PDF
  Logger.log('Creating URL for sheet with ID: ' + newSpreadsheetId)  
  let url_for_blob = "https://docs.google.com/spreadsheets/d/"+ newSpreadsheetId +"/export?format=pdf&portrait=true&size=a4&gridlines=false"
  Logger.log("URL " + url_for_blob)
  let blob = getFileAsBlob(url_for_blob, pdfName);

  // CRAFT MESSAGE
  var em_message = "Hello world,<br><br>"
  var message = {
    to: "[email protected]",
    subject: "Good bye world",
    htmlBody: em_message, 
    name: "Bad bot",
    attachments: [blob]
  }

  MailApp.sendEmail(message);
}

Which should work. Hardcoding newSpreadsheetID, works. The Logger always shows the correct new ID (and not the ID of the template, which is the one showing up in the PDF).

Before that, I edit the newly created sheet with:

function editNewSheet(newSpreadsheetId, day, currentMonthAsWord, month, year) {
  var ss = SpreadsheetApp.openById(newSpreadsheetId);
  var sheet = ss.getSheetByName('Invoice');

  // range to edit
  var cell = sheet.getRange('H4:I4');
  // date of Invoice
  cell.setValue(day + ' ' + currentMonthAsWord + ' ' + year);
  // Invoice number:
  var cell = sheet.getRange('H7:I7');
  var invoiceNr = (year-2023)*12+month-5
  cell.setValue('SPEV31-' + invoiceNR.toString() );
  // month
  var cell = sheet.getRange('C20');
  cell.setValue(currentMonthAsWord + ' ' + year);
}

These three functions are used in this order:

function createAndEmailInvoice() {

// [SOME CODE HERE]

// Get the ID of the newly created spreadsheet
  var newSpreadsheetId = copiedSpreadsheet.getId();
    // Rename the newly created spreadsheet
  DriveApp.getFileById(newSpreadsheetId).setName(newSheetName);
  
  // EDIT Spreadsheet
  Logger.log('New spreadsheet ' + newSheetName + ' created with ID: ' + newSpreadsheetId);
  editNewSheet(newSpreadsheetId, day, currentMonthAsWord, month, year)

  // Add a sleep here to wait for a few seconds. 
  Utilities.sleep(10000);
  
  // Download the sheet as PDF & send email with it.
  sendExportedSheetAsPDFAttachment(newSpreadsheetId, newSheetName );
}

Any ideas about what might be wrong are appreciated.

0

There are 0 best solutions below