First of all, I'm not an experienced programmer and I'm very new to Google Apps Script.
I'm running a Google Apps Script and I'm stuck. What the script does: it copies a part of a sheet to a temp sheet, makes that into a PDF and sends it by mail.
I want to do this for (right now) 40 mail addresses. If I run the script it gives me a 429 (too many requests) error, after 5 to 8 addresses. This is the heavy part, I found out: var response = UrlFetchApp.fetch(url, params).getBlob(); If I comment it out, it works great, even copy-pasting the temp sheet and sending the emails.
To prevent this I added a sleep timer. I had to go up to 12 seconds and didn't get the error. Great. But now the script takes more than 6 min (the maximum time), so it takes too long and doesn't finish (gets about halfway).
After reading some I think the script (correct me if I'm wrong) is pretty optimal and I need to "chain function calls". But I have no idea how to go about that. I assigned this script to a button in the sheet. But I can't see how I can run 1 function and have that trigger other functions, without it considering that the same function (and thus stopping after 6 min). How do I go about this?
Here's the full code. Sorry for the Dutch text (they are just some confirmation windows and such):
function exportNamedRangesAsPDF() {
var y = 1
var sec = 40
var ui = SpreadsheetApp.getUi();
var result = ui.alert('Weet je zeker dat je alle maandstaten wil versturen via mail?',
ui.ButtonSet.YES_NO);
var html = HtmlService.createHtmlOutputFromFile('Page')
.setWidth(400)
.setHeight(200);
if (result == ui.Button.YES) {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName('Maandstaten print');
var namenSheet = ss.getSheetByName('Alle_namen');
var namenSheetLastRow = namenSheet.getLastRow();
var namenSheetAantalX = namenSheet.getRange("Q1").getValue()
var startKol = 4
var namenVerzonden = [];
var mailOntbreekt = [];
//Logger.log(namenSheetLastRow)
var newSheet = ss.getSheetByName('print');
if (!newSheet) {
newSheet = ss.insertSheet('print');
}
newSheet.showSheet();
var gid = newSheet.getSheetId();
var ssID = "138zfRxR_SQ6oRJouQsMwKQZdyZYbqarUuMCfZTc8fGs";
var url = "https://docs.google.com/spreadsheets/d/"+ssID+"/export"+
"?format=pdf&"+
"size=7&"+
"fzr=false&"+
"portrait=true&"+
"fitw=true&"+
"gridlines=false&"+
"printtitle=false&"+
"sheetnames=false&"+
"pagenum=UNDEFINED&"+
"gid=1186495600&"+
"top_margin=0.75&"+
"bottom_margin=0.75&"+
"left_margin=0.2&"+
"right_margin=0.2&"+
"attachment=true";
var params = {method:"GET",headers:{"authorization":"Bearer "+ ScriptApp.getOAuthToken()}};
var i;
for (i = 1; i < namenSheetLastRow; i++) { //////// START FOR LOOP
if( namenSheet.getRange(i+1,16, 1, 1).getValue() == "x" ) { /////// START IF 1
var startRij = namenSheet.getRange(i+1,15, 1, 1).getValue()
var voornaam = sheet.getRange(startRij+10, startKol-1, 1, 1).getValues();
var zoeknaam = sheet.getRange(startRij+4, startKol-1, 1, 1).getValues();
ui.showModalDialog(html, "Bezig met versturen... " + y + " van " + namenSheetAantalX);
/*if (y % 5 == 0) { // Wait (sleep) every 5th time the script runs, to prevent 429 error (too many reqests)
ui.showModalDialog(html, "Wachten... "+ sec + " sec");
Utilities.sleep(sec*1000); // https://stackoverflow.com/questions/47648338/creating-multiple-google-sheets-pdfs-throws-429-error
Logger.log("Sleep: "+ sec + " sec")
}
y = y+1*/
if( namenSheet.getRange(i+1,16, 1, 1).getValue() == "x" && namenSheet.getRange(i+1,14, 1, 1).getValue() != "" ) { ////////// START IF 2
var volleNaam = sheet.getRange(startRij+2, startKol-2, 1, 1).getValues();
var maand = sheet.getRange(1, 2, 1, 1).getValues();
var mailAdres = sheet.getRange(startRij+9, startKol-1, 1, 1).getValue();
Logger.log(mailAdres + " " + volleNaam);
namenVerzonden.push(" " + zoeknaam);
sheet.getRange(startRij, startKol, 39, 16).copyTo(newSheet.getRange(1, 1, 39, 16), {contentsOnly: true}); //copy the right part of the sheet to the new sheet, content only
sheet.getRange(startRij, startKol, 39, 16).copyTo(newSheet.getRange(1, 1, 39, 16), {formatOnly: true});//copy the right part of the sheet to the new sheet, formatting only
var response = UrlFetchApp.fetch(url, params).getBlob(); // This is the super heavy part, running it too often causes a 429 (too many requests) error
//DriveApp.createFile(response); //save to drive
var message = { //send as email
to: mailAdres,
subject: "Maandstaat "+ maand,
body: "Beste "+ voornaam + ",\n\nIn de bijlage vind je de maandstaat van maand " + maand + ".\n\nMet vriendelijke groet,\nCJ Hendriks Group",
name: "CJ Hendriks",
attachments: [{
fileName: "Maandstaat - " + maand + " - " + volleNaam + ".pdf",
content: response.getBytes(),
mimeType: "application/pdf"
}]
}
//MailApp.sendEmail(message); // This is the actual mail action
} ////////// END IF 2
} /////// END IF 1
else if( namenSheet.getRange(i+1,16, 1, 1).getValue() == "x" && namenSheet.getRange(i+1,14, 1, 1).getValue() == "" ) {
mailOntbreekt.push(" " + zoeknaam);
}
} //////// END FOR LOOP
ui.showModalDialog(html, "Maandstaten verzonden naar: " + namenVerzonden);
Logger.log('Maandstaten verzonden naar: \n'+namenVerzonden);
if( mailOntbreekt.length != 0) {
ui.alert('Mail adres ontbreekt bij: \n'+mailOntbreekt);
}
newSheet.hideSheet(); // hide the "print" sheet
}
else {
ui.alert('Maandstaten NIET verzonden.');
}
Logger.log("Succesvol voltooid")
}
Thank you!
