I’ve made a semi automated calender []https://docs.google.com/spreadsheets/d/1hkgDEGrxfZqbCCnslHTbMlVaa96bG1mSgBx0MUWYCSM/edit?gid=787834846#gid=787834846, it’s supposed to work like this, when the pc starts , a batch file automatically opens the browser with the calender, the script then runs on open, the calender has 52 sheets which is a lot i know, each sheet representing a calender week, the script is supposed to check all 52 sheets (only the 5 cells contianing a date) compare that with the date on week1, row100 which always shows todays date, and if there is a match activates that sheet containing the date e.g
it’s 1.4.24 which was week 14 so the sheet named “week 14” will be selected on opening the sheet.
first the script became slower and slower, it started from about 15 seconds now up to about 45 seconds (the more data there is in the calender throughout the year), which is annoying but given the data not surprising, is there a better way to get to the same result?
second and way bigger issue is that now i get over 50% of failed excecutions due to timeout, this only happens when the script runs automatically, if i start it manually it works fine.
function onOpen() {
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var sheetNames = ["KW01", "KW02", "KW03", "KW04", "KW05", "KW06", "KW07", "KW08", "KW09", "KW10", "KW11", "KW12", "KW13", "KW14", "KW15", "KW16", "KW17", "KW18", "KW19", "KW20", "KW21", "KW22", "KW23", "KW24", "KW25", "KW26", "KW27", "KW28", "KW29", "KW30", "KW31", "KW32", "KW33", "KW34", "KW35", "KW36", "KW37", "KW38", "KW39", "KW40", "KW41", "KW42", "KW43", "KW44", "KW45", "KW46", "KW47", "KW48", "KW49", "KW50", "KW51", "KW52"];
var dateCells = ["B3", "E3", "H3", "K3", "N3",];
var comparisonSheet = spreadsheet.getSheetByName("KW01");
var comparisonDate = comparisonSheet.getRange("A100").getValue();
for (var i = 0; i < sheetNames.length; i++) {
var sheet = spreadsheet.getSheetByName(sheetNames[i]);
for (var j = 0; j < dateCells.length; j++) {
var cellDate = sheet.getRange(dateCells[j]).getValue();
if (cellDate.getTime() === comparisonDate.getTime()) {
spreadsheet.setActiveSheet(sheet);
return;
}
}
}
}
before i found this method i’ve searched a lot trying to find a similar script or template i can use, there are if searched for day, month or year but not for calendar week, since i had no success i’ve tried chat gpt after a while i got the script being used, knowing chat gpt there is a good chance the script isn’t even a good one but the only one that worked, even if slow it was good enough for what was needed until it stopped working.