Explanation / Issue:
I think your code is pretty much optimized.
Well, some things could be further optimized, for example:
![ "B22","B23",..."B45", "B46"].some(f => f == range.getA1Notation()))
could be replaced with a simple comparison:
!(row >= 22 && row <=46 && col == 2)
or instead of an array you can define a string and use the includes or you can use includes for the array as well, but the string version would be:
!"B22,B23,..B46".includes(range.getA1Notation())
However, even if you make all these optimizations, the runtime would probably go from 30+ seconds to 25 or so. My point is, in the short term future you will face the same issues again assuming you want to add more sheets or more columns in the sheets etc.
My approach here would be to take advantage of the full 6 or 30 minutes quota that Google gives you instead of the limited 30 seconds quota given for onEdit triggers.
The logic is the following:
Rename onEdit(e) to myInstallableEdit(e) or any name you want except for onEdit. This will allow you to create an installable trigger for myInstallableEdit(e).
The installable onEdit trigger will allow you to create time-driven triggers. The idea is to execute the part of the code that takes a lot of time with a time-driven trigger which has a quota of 6 or 30 minutes (depending on your account).
As soon as there is a successful edit in the sheet (based on the conditions you have specified already), create a time trigger for the function you want to run that is going to be executed only one time within the next minute. Sure, you have to wait less than 60 seconds for your function to start running and that is a limitation, but at least your function will have 6 minutes to run and this will solve your issues.
Workaround Solution:
Manually execute only and once the createOnEditTrigger function. This will create an installable onEdit trigger for myInstallableEdit. Therefore, myInstallableEdit is allowed to create time driven triggers for functionToRun which is the function which needs the most time to finish:
function createOnEditTrigger() {
const ss = SpreadsheetApp.getActive();
ScriptApp.newTrigger('myInstallableEdit')
.forSpreadsheet(ss).onEdit().create();
}
function myInstallableEdit(e) {
const ss = e.source;
const range = e.range;
const activeSheet = range.getSheet();
const row = range.getRow();
const col = range.getColumn();
if (activeSheet.getSheetName() != "General" || !(row >= 22 && row <=46 && col == 2)) return;
ss.toast('Script start');
// 2. Retrieve the values of the 1st row of sheet "Hide sheet", and the columns are hidden or shown by the values.
// run functionToRun with a time-trigger
deleteTriggers();
ScriptApp.newTrigger("functionToRun")
.timeBased()
.after(1)
.create();
}
function functionToRun(){
const ss = SpreadsheetApp.getActive();
const only = ['Sheet1', 'Sheet2', 'Sheet5',
'Sheet6', 'Sheet8', 'Sheet9',
'Sheet10', 'Sheet11','Sheet12'].
map(name=>ss.getSheetByName(name));
only.forEach(sheet => {
sheet
.getRange(1, 1, 1, sheet.getLastColumn())
.getValues()[0]
.forEach((e, i) => sheet[e === true ? "showColumns" : "hideColumns"](i + 1));
});
}
function deleteTriggers() {
const triggers = ScriptApp.getProjectTriggers();
for (let i = 0; i < triggers.length; i++) {
if ( triggers[i].getHandlerFunction() == "functionToRun") {
ScriptApp.deleteTrigger(triggers[i]);
}
}
}
I also made some optimizations in your current solution, but if these optimization don't work for you, then use your current code, and just adapt the logic of the time-driven trigger creation upon edits.
