Everyone was so helpful on the last time I was stumped I thought I would come back. I learned a lot from that last time and I have been successful on other projects since.
I am working on a script that will automatically generate an invoice in a Google Sheet, and then PDF it. Everything is working as intended, with the exception if I remove line 23 of the below code the file name will say 'Invoice 2' but the invoice will read Invoice 1. I only had that line there to try and trace the error, but it works as intended with it there; my question is why does it break if I remove line 23?
Thank you in advance!
function autoInvoice() {
// Seting up to handle the spreadsheet
var sheetID = 'ID'; //Edit this to your sheet ID.
let sheet = SpreadsheetApp.openById(sheetID);
let invoice = sheet.getSheetByName('Invoice');
let dataValues = sheet.getSheetByName('Data').getDataRange().getValues(); // Gets all values of the sheet into an array
let dataRowNum = sheet.getSheetByName('Data').getLastRow(); // Gets last row of the Autoforward Sheet
let invoiceCell = invoice.getRange('C3');
let clientCodeCell = invoice.getRange('C4');
let invoiceDate = invoice.getRange('D5');
// A(0) - Active Client - Yes / No
// B(1) - Client Code
// C(2) - Client Name
// D(3) - Owner Name
// E(4) - Owner E-Mail
// F(5) - Date of Invoice
for (let a = 1; a < dataRowNum; a++) {
let clientName = dataValues[a][2];
if (dataValues[a][0] == 'Yes') {
clientCodeCell.setValue(dataValues[a][1]); // Changes client code on invoice, sheet code does the rest
let invoiceNum = parseInt(invoiceCell.getValue()) + 1; // Adds one to the invoice number
invoiceCell.setValue(invoiceNum); // Sets the invoice number on the sheet
Logger.log(invoiceNum + " " + invoiceCell.getValue()); // removing this causes the invoice in the file name to be mismatched to the invoice number
// Date Conversion
var date = new Date(dataValues[a][5]); //convert js date into gs date
dataValues[a][5] = Utilities.formatDate(date, "PDT", "yyyy-MM-dd"); //format date using gs method
invoiceDate.setValue(dataValues[a][5]); // Sets date on Invoice
Utilities.sleep(1000);
// Makes PDF
const folderName = `Test Folder`;
DriveApp.getFoldersByName(folderName)
.next()
.createFile(SpreadsheetApp.getActiveSpreadsheet()
.getBlob()
.getAs(`application/pdf`)
.setName(dataValues[a][5] + ' - ' + clientName + ' - Invoice #' + invoiceNum)); // Sets file name
Logger.log(clientName + ' Active, making Invoice');
Logger.log('Invoice #' + invoiceNum + ' created for ' + clientName);
} else {
Logger.log(clientName + " no longer active, skipping to next client")
}
}
}Utilities.sleep(1000); to wait for the specified requests to finish before you proceed with further code execution.sleep() is apparently not enough. The Logger.log gives you additional waiting time for the right values to be fetched before continuing with code execution.Spreadsheet operations are sometimes bundled together to improve performance, such as when doing multiple calls to Range.getValue(). However, sometimes you may want to make sure that all pending changes are made right away, for instance to show users data as a script is executing.
Spreadsheet.flush() will make sure that the name will not assigned to the pdf before the correct value for the given for loop iteration is retrieved.Spreadsheet.flush() is mostly preferable to Utilities.sleep() given that it forces the operaiton to become synchroneous and automatically adjusts the necessary amount of "sleeping time".