This is not specifically an answer to your but I was motivated to write it today because of your question. It utilizes a sheet that I built a search form on to. When I build the form I get all of my calendars and put the names in a column with a checkbox next them so that I can check which calendars I want involved in the search.
When I perform the search I read this list and filter the ones that are not checked and I use that to run the following loop:
calA.forEach(c => {
evts = evts.concat(CalendarApp.getCalendarById(c).getEvents(fmdt, todt, { search: query }));
});
In code above c is a calendarId.
Here's the complete function that does the searching. I did not include the functions that save the original template or the one that creates a new form on the active page thus making it possible to have multiple search forms.
/*
Performs a calendar based update in the form
*/
function calendarSearchForm() {
const ss = SpreadsheetApp.getActive();
const sh = ss.getActiveSheet();
const cals = CalendarApp.getAllCalendars();
const locations = JSON.parse(getGlobal('locations'));
const row = locations.origins.status.row;
const col = locations.origins.status.col;
sh.getRange(row, col).setValue(`Search Initiated`);
let isChecked = {};
let calName = {};
let found = 0;
sh.getRange(locations.origins.calendar.select.row, locations.origins.calendar.select.col, cals.length, 2).getValues().forEach(r => isChecked[r[1]] = r[0]);
const calA = cals.map(c => {
let name = c.getName();
let id = c.getId();
if (isChecked[name]) {
calName[id] = name;
return id;
}
}).filter(e => e);
let calendars = (calA && calA.length) ? calA.length : 0;
let query = sh.getRange(locations.origins.query.row, locations.origins.query.col).getDisplayValue();
let fmdt = new Date(sh.getRange(locations.origins.fromdate.row, locations.origins.fromdate.col).getValue());
let todt = new Date(sh.getRange(locations.origins.todate.row, locations.origins.todate.col).getValue());
let start = `${fmdt.getMonth() + 1}/${fmdt.getDate()}/${fmdt.getFullYear()}`;
let end = `${todt.getMonth() + 1}/${todt.getDate()}/${todt.getFullYear()}`;
sh.getRange(row, col).setValue(`Searching...\nFrom: ${start}\nTo: ${end}\nQuery: ${query}\nCalendars:${calendars}`);
//Logger.log("calName: " + JSON.stringify(calName));
//Logger.log("calA: " + JSON.stringify(calA));
//Logger.log("isChecked:\n" + JSON.stringify(isChecked));
//Logger.log("fromdate: \n" + fmdt);
//Logger.log("todate:\n" + todt);
//Logger.log("query:\n" + query);
let evts = [];
let cobj = {};
calA.forEach(c => {
evts = evts.concat(CalendarApp.getCalendarById(c).getEvents(fmdt, todt, { search: query }));
});
let evA = evts.map(e => {
return [e.getId(), e.getTitle(), e.getStartTime(), calName[e.getOriginalCalendarId()]];
}).sort((a,b) => new Date(a[2]).valueOf()-new Date(b[2]).valueOf());
let idrow = locations.origins.results.id.row;
let idcol = locations.origins.results.id.col;
let rows = getColumnHeight(locations.origins.results.id.col, sh, ss) - idrow + 1;
let cols = locations.origins.results.calendar.col - idcol + 1;
if (rows) {
sh.getRange(idrow, idcol, rows, cols).clearContent();
}
if (evA && evA.length) {
sh.getRange(locations.origins.results.id.row, locations.origins.results.id.col, evA.length, evA[0].length).setValues(evA);
found = evA.length;
}
sh.getRange(row, col).setValue(`Complete...\nFrom: ${start}\nTo: ${end}\nQuery: ${query}\nCalendars:${calendars}\nFound: ${found}`);
ss.toast(`EOF\nFound: ${found}`);
}
My guess is that there are a lot of people that have done much more elegant scripts than this and to be totally honest I had never even used the search function on calendar until today. If it helps, great. If you'd rather I delete it let me know.
Search Form Template:
Search Form Built from the template on any tab:
I redacted the name of one of my calendars.
