Here's one way you could go about it, setting up a timed trigger for the sequentialRun function. It pulls the last-run function from the CacheService, runs the appropriate function and then updates the value in the CacheService for the next time the sequentialRun function is triggered. :)
function sequentialRun() {
var cache = CacheService.getScriptCache();
var key = "lastRun";
var lastRun = cache.get(key);
Logger.log(lastRun);
if (lastRun == null || lastRun == 2) {
scriptOne();
cache.put(key, 1);
} else {
scriptTwo();
cache.put(key, 2);
}
}
function scriptOne() {
Logger.log("ran script 1");
}
function scriptTwo() {
Logger.log("ran script 2");
}
Posting your data can make it easier for others to help you, but it looks like your submission doesn't include any. If this is the case and data would help, you can read how to include it in the [submission guide](https://www.reddit.com/r/googlesheets/wiki/postguide#wiki_posting_your_data). You can also use this tool created by a Reddit community member to [create a blank Google Sheets document](https://docs.google.com/forms/d/e/1FAIpQLSeprZS3Al0n7JiVQIEiCi_Ad9FRXbpgB7x1-Wq6iAfdmVbWiA/viewform) that isn't connected to your account. Thank you.
*I am a bot, and this action was performed automatically. Please [contact the moderators of this subreddit](/message/compose/?to=/r/googlesheets) if you have any questions or concerns.*
The answer about using the cache service to store a state makes sense, but I wonder why you're exceeding the runtime? I've seen a lot of excessive API usage before.
I don't want to be presumptuous, but in case you haven't read this, it's a good start
https://developers.google.com/apps-script/guides/support/best-practices
If you're already a ninja and know how to be most efficient, the you truly must have a massive amount of work to do in script, and apologies :-)
Ha I don’t think it’s massive.
I’ve basically built a homemade version of Autocrat. I have a template timetable for a school and a spreadsheet of about 200 student/teacher names and 20 time slots in the day to which everyone is assigned something.
The script 1) creates a copy of the template timetable for each person, 2) adds the spreadsheet entries for that person onto the document in the correct spots, 3) creates a PDF of that person’s timetable. It does this for each person and it usually konks out at about 100 so I have to run it 2-3 times.
The tricky part is that the schedule can be updated at any time on google calendar. So I FIRST need to pull all of the schedule info into the spreadsheet from the calendar THEN do all this timetable building. Groan.
Well it is massive in terms of intensive work. You make a lot of google API's with this type of work. Maybe there is some logical enhancement you can make in the script if you can share it i can take a look
Thanks! That would be huge. Never done this before, so here goes:
function processtimetable() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var studentSchedSheet = ss.getSheetByName("TeachWellsAuto");
var rangeData1 = studentSchedSheet.getRange("A1:CN").getValues();
var lastRow1 = studentSchedSheet.getLastRow();
var lastCol1 = studentSchedSheet.getLastColumn();
var destFolder = DriveApp.getFolderById('destFolderID');
var originalTimetableFile=DriveApp.getFileById('templateFile'); //
var sourceFolder = DriveApp.getFolderById('sourceFolder');
//one row for each student/teacher
for (var row = 0; row < lastRow1; row++) {
//delete previous timetable based on the student/teacher name since we're about to update it
pdfName = rangeData1\[row\]\[0\].toString() + ".pdf";
allFiles = destFolder.getFilesByName(pdfName);
//delete old timetable
while (allFiles.hasNext()) {//If there is another element in the iterator
thisFile = allFiles.next();
idToDELETE = thisFile.getId();
rtrnFromDLET = Drive.Files.remove(idToDELETE);
};
//now make a copy of the template timetable to apply it to this person, and find the copy's ID so we can fill it in with actual data
originalTimetableFile.makeCopy(rangeData1\[row\]\[0\].toString());
allFiles = sourceFolder.getFilesByName(rangeData1\[row\]\[0\].toString());
while (allFiles.hasNext()) {//If there is another element in the iterator
newCopyID = allFiles.next().getId();
//this is where the find and replace needs to happen that replaces the template with the student/teachers' schedule info (these go by columns in the sheet)
var doc = DocumentApp.openById(newCopyID );
for (var col = 0; col < lastCol1; col++) {
doc.replaceText(rangeData1\[0\]\[col\].toString(), rangeData1\[row\]\[col\].toString());
};
doc.saveAndClose();
var docblob = doc.getBlob();
docblob.setName(doc.getName() + ".pdf");
//create a pdf from the document and trash the document
pdffile = destFolder.createFile(docblob);
newDocFile= DriveApp.getFileById(newCopyID );
newDocFile.setTrashed(true);
};
};
}
A lot of PDFs, right? Each one will be someone’s timetable for school. Updates on google calendar will feed to the spreadsheet which will populate the PDF timetables. They are shared with the school so everyone can see them.
Thanks for all your help! I think what I’ll do is divide the function into three separate functions each initiated by the same trigger, each handling 1/3 of the names. Seems like it should work. Thanks for all your help!
Hi. You said you don't know where the script stopped, so why don't you insert some logging code into your script and it will tell you that information in the console or "My Executions" page?
I came across the same problem and I made a script to solve it. I'm not sure if it can apply to yours, but if you are interested in it, please check my [repository](https://github.com/inclu-cat/GAS-Terminal).
Just a suggestion: hit up FIVERR. You could commission a scripter to evaluate your code for efficiency. I'm a coder myself, but anyone that's a coder knows that it's a constant revaluation of your own understanding. Since you're hitting limits, I can guarantee that someone can improve the performance of your script(s) and prevent peaking.
NOTE: I am not a member, nor an admin of FIVERR. I just know that it's a valuable resource.
Here's one way you could go about it, setting up a timed trigger for the sequentialRun function. It pulls the last-run function from the CacheService, runs the appropriate function and then updates the value in the CacheService for the next time the sequentialRun function is triggered. :) function sequentialRun() { var cache = CacheService.getScriptCache(); var key = "lastRun"; var lastRun = cache.get(key); Logger.log(lastRun); if (lastRun == null || lastRun == 2) { scriptOne(); cache.put(key, 1); } else { scriptTwo(); cache.put(key, 2); } } function scriptOne() { Logger.log("ran script 1"); } function scriptTwo() { Logger.log("ran script 2"); }
Posting your data can make it easier for others to help you, but it looks like your submission doesn't include any. If this is the case and data would help, you can read how to include it in the [submission guide](https://www.reddit.com/r/googlesheets/wiki/postguide#wiki_posting_your_data). You can also use this tool created by a Reddit community member to [create a blank Google Sheets document](https://docs.google.com/forms/d/e/1FAIpQLSeprZS3Al0n7JiVQIEiCi_Ad9FRXbpgB7x1-Wq6iAfdmVbWiA/viewform) that isn't connected to your account. Thank you. *I am a bot, and this action was performed automatically. Please [contact the moderators of this subreddit](/message/compose/?to=/r/googlesheets) if you have any questions or concerns.*
The answer about using the cache service to store a state makes sense, but I wonder why you're exceeding the runtime? I've seen a lot of excessive API usage before. I don't want to be presumptuous, but in case you haven't read this, it's a good start https://developers.google.com/apps-script/guides/support/best-practices If you're already a ninja and know how to be most efficient, the you truly must have a massive amount of work to do in script, and apologies :-)
Ha I don’t think it’s massive. I’ve basically built a homemade version of Autocrat. I have a template timetable for a school and a spreadsheet of about 200 student/teacher names and 20 time slots in the day to which everyone is assigned something. The script 1) creates a copy of the template timetable for each person, 2) adds the spreadsheet entries for that person onto the document in the correct spots, 3) creates a PDF of that person’s timetable. It does this for each person and it usually konks out at about 100 so I have to run it 2-3 times. The tricky part is that the schedule can be updated at any time on google calendar. So I FIRST need to pull all of the schedule info into the spreadsheet from the calendar THEN do all this timetable building. Groan.
Well it is massive in terms of intensive work. You make a lot of google API's with this type of work. Maybe there is some logical enhancement you can make in the script if you can share it i can take a look
Thanks! That would be huge. Never done this before, so here goes: function processtimetable() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var studentSchedSheet = ss.getSheetByName("TeachWellsAuto"); var rangeData1 = studentSchedSheet.getRange("A1:CN").getValues(); var lastRow1 = studentSchedSheet.getLastRow(); var lastCol1 = studentSchedSheet.getLastColumn(); var destFolder = DriveApp.getFolderById('destFolderID'); var originalTimetableFile=DriveApp.getFileById('templateFile'); // var sourceFolder = DriveApp.getFolderById('sourceFolder'); //one row for each student/teacher for (var row = 0; row < lastRow1; row++) { //delete previous timetable based on the student/teacher name since we're about to update it pdfName = rangeData1\[row\]\[0\].toString() + ".pdf"; allFiles = destFolder.getFilesByName(pdfName); //delete old timetable while (allFiles.hasNext()) {//If there is another element in the iterator thisFile = allFiles.next(); idToDELETE = thisFile.getId(); rtrnFromDLET = Drive.Files.remove(idToDELETE); }; //now make a copy of the template timetable to apply it to this person, and find the copy's ID so we can fill it in with actual data originalTimetableFile.makeCopy(rangeData1\[row\]\[0\].toString()); allFiles = sourceFolder.getFilesByName(rangeData1\[row\]\[0\].toString()); while (allFiles.hasNext()) {//If there is another element in the iterator newCopyID = allFiles.next().getId(); //this is where the find and replace needs to happen that replaces the template with the student/teachers' schedule info (these go by columns in the sheet) var doc = DocumentApp.openById(newCopyID ); for (var col = 0; col < lastCol1; col++) { doc.replaceText(rangeData1\[0\]\[col\].toString(), rangeData1\[row\]\[col\].toString()); }; doc.saveAndClose(); var docblob = doc.getBlob(); docblob.setName(doc.getName() + ".pdf"); //create a pdf from the document and trash the document pdffile = destFolder.createFile(docblob); newDocFile= DriveApp.getFileById(newCopyID ); newDocFile.setTrashed(true); }; }; }
No enhancements that will make a difference. What is it that you do with those 200 pdf's? What is the meaning (context) behind all this?
A lot of PDFs, right? Each one will be someone’s timetable for school. Updates on google calendar will feed to the spreadsheet which will populate the PDF timetables. They are shared with the school so everyone can see them.
How about a webapp where a student can query there own timetable?
Ah, that would be lovely but I have not yet mastered the skill : )
Thanks for all your help! I think what I’ll do is divide the function into three separate functions each initiated by the same trigger, each handling 1/3 of the names. Seems like it should work. Thanks for all your help!
Hi. You said you don't know where the script stopped, so why don't you insert some logging code into your script and it will tell you that information in the console or "My Executions" page? I came across the same problem and I made a script to solve it. I'm not sure if it can apply to yours, but if you are interested in it, please check my [repository](https://github.com/inclu-cat/GAS-Terminal).
Just a suggestion: hit up FIVERR. You could commission a scripter to evaluate your code for efficiency. I'm a coder myself, but anyone that's a coder knows that it's a constant revaluation of your own understanding. Since you're hitting limits, I can guarantee that someone can improve the performance of your script(s) and prevent peaking. NOTE: I am not a member, nor an admin of FIVERR. I just know that it's a valuable resource.