Can a loop be added to a Google Sheets script that searches a Google calendar?-CodePudding

My code searches my calendar for a search term specified by the user and returns relevant results. I want to be able to search for multiple terms. E.g., "Arnold Schwartzeneger" AND "Bruce Willis" AND "Sylvester Stallone." Can a loop be added to search for each term listed in a range one at a time?

function export_gcal_to_gsheet(){
  
var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var sh0 = sheet.getSheets()[0];
  var sh1 = sheet.getSheets()[1];
  
var mycal = sh0.getRange("B1").getValue();
var cal = CalendarApp.getCalendarById(mycal);
  
  var search1 = sh0.getRange("B2").getValue();
  var events = cal.getEvents(new Date("January 12, 1990 00:00:00 EST"), new          Date("May 18, 2050 23:59:59 EST"), { search: (search1) });
  
var range = sh1.getRange(1,1,1,14);
for (var i=0;i<events.length;i  ) {
var row=i 1;
var details=[[mycal,events[i].getTitle(), events[i].getDescription(), events[i].getStartTime(), events[i].getLocation()]];
var range=sh1.getRange(row,1,1,5);
range.setValues(details);
}}

CodePudding user response：

You can use an array.includes() method. So this should fit you needs. You can ofcourse get the values from a sheet. To get a 1d array make sure you add .flat() after the .getValues() --> .getValues().flat() This is case sensitive.

function export_gcal_to_gsheet() {
  const searchValues = ["Arnold Schwartzeneger", "Bruce Willis", "Sylvester Stallone"];

  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var sh0 = sheet.getSheets()[0];
  var sh1 = sheet.getSheets()[1];

  var mycal = sh0.getRange("B1").getValue();
  var cal = CalendarApp.getCalendarById(mycal);

  var search1 = sh0.getRange("B2").getValue();
  var events = cal.getEvents(new Date("January 12, 1990 00:00:00 EST"), new Date("May 18, 2050 23:59:59 EST"), { search: (search1) });

  var range = sh1.getRange(1, 1, 1, 14);
  for (var i = 0; i < events.length; i  ) {
    if (searchValues.includes(events[i].getTitle())) {
      var details = [[mycal, events[i].getTitle(), events[i].getDescription(), events[i].getStartTime(), events[i].getLocation()]];
      var range = sh1.getRange(sh1.getLastRow() 1, 1, 1, 5);
      range.setValues(details);
    }
  }
}

CodePudding user response：

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.