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.