Search on a literal comma (string) in Google apps script

Question

I'm using a script to retrieve items from Google Calendar and put it into Google Sheets. In my agenda, I've formatted the calendar items that I want to retrieve in a comma separated fashion as follows: Client, Project, Activities.

Now, I want to retrieve ONLY the agenda items that contain a comma in the title (or, better yet, ultimately two comma's), but I cannot find a way to search on comma's as a string. I use the "search" function, and replacing the comma with a word, perfectly filters only the items with that word. But searching on just the comma (or any other special character) doesn't work. I tried dozens of combinations, including REGEX expressions, but nothing works. Part of the problematic code:

I'm using a script to retrieve items from Google Calendar and put it into Google Sheets. In my agenda, I've formatted the calendar items that I want to retrieve in a comma separated fashion as follows: Client, Project, Activities.

Now, I want to retrieve ONLY the agenda items that contain a comma in the title (or, better yet, ultimately two comma's, separated bij any character or characters), but I cannot find a way to search on comma's as a string. I use the "search" function, and replacing the comma with a word, perfectly filters only the items with that word. But searching on just the comma (or any other special character) doesn't work. I tried dozens of combinations, including REGEX expressions, but nothing works. My code so far:

function getCalendarEvents() {
  var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Brondata"); 
  var cal = CalendarApp.getCalendarById("mail@domain."); 
  var eind = new Date;
  eind.setDate(eind.getDate()+1);
  var events = cal.getEvents(new Date("1/1/2022"), eind, {search: '(/,{2}/)'});
  var lr = ss.getLastRow();
  ss.getRange(3, 1, lr,14).clearContent();
  var result = []
  events.forEach(e => {
    result.push([e.getStartTime(),
                e.getEndTime(),
                e.getTitle(),
                e.getDescription(),
                e.getDateCreated(),
                e.getLastUpdated(),
                e.isRecurringEvent(),
                e.getCreators()])
  })
  ss.getRange(3,1,result.length,result[0].length).setValues(result)
}

What code do I need to use after the "search" command, to find only the items that contain a comma?

Some things I've tried:

'\,' (filters out nothing)
'==\,' (filters out nothing)
'"\,"' (filters out a bit more, but I can't figure out what exactly)
'"==\,"' (same result as previous)

Updated the script with the input of @TheWizEd, and it now runs fine perfectly! The complete, working script is as follows:

function getCalendarEvents() {
  let ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Brondata"); 
  let cal = CalendarApp.getDefaultCalendar();
  let eind = new Date();
  eind.setDate(eind.getDate()+1);
  let events = cal.getEvents(new Date("1,1,2022"), eind);
  let lr = ss.getLastRow();
  ss.getRange(3, 1, lr,14).clearContent();
  let result = []
  let match = events.filter( e =>  { if( e.getTitle().match(".*\,.*\,") !== null ) return e; } );
  if( match ) {
    match.forEach(e => {
      let title = e.getTitle();
      let codes = [];
        for( let i=0; i<title.length; i++ ) {
          codes.push( title.charCodeAt(i) );
        }
      result.push([e.getStartTime(),
                  e.getEndTime(),
                  e.getTitle(),
                  e.getDescription(),
                  e.getDateCreated(),
                  e.getLastUpdated(),
                  e.isRecurringEvent(),
                  e.getCreators()])
    })
  ss.getRange(3,1,result.length,result[0].length).setValues(result)
  }
}

How do you distinguish between the comma in the calendar items that I want to retrieve in a comma separated fashion and the comma in agenda items that contain a comma in the title? — TheWizEd
– TheWizEd, Commented Jun 9, 2022 at 13:44
I don't think I understand you question... I've elaborated my question: I want to be able to filter out only the Google Calendar items that contain a comma... — Marc van Maastricht
– Marc van Maastricht, Commented Jun 9, 2022 at 14:00
But you say they are comma delimitied and you want the agenda that contains commas. That doesn't make sense. — TheWizEd
– TheWizEd, Commented Jun 9, 2022 at 14:37
I’m not using the comma’s as a delimiter, in the code: I just want to only import calendar items that contain two comma’s: no need to split or join or something like that. The search command works fine if I use it to filter out items with specific string that exist of normal characters. For example: if I search for ‘coffee bar’, only my appointments in a coffee bar are shown. Works flawlessly. But if I use a punctuation character (a dot or a comma) than it ignores it and shows every appointment; also the ones without any comma. — Marc van Maastricht
– Marc van Maastricht, Commented Jun 9, 2022 at 15:45

TheWizEd · Accepted Answer · 2022-06-22 10:26:19Z

1

Descrition

I have created 3 events in my calander that have "Test, event *, ***" in the title with and without spaces. This example script will find all event with that in there title.

Code.gs

function getCalanderEvent() { 
  try {
    let cal = CalendarApp.getDefaultCalendar();
    let start = new Date("1,1,2022");
    let end = new Date();
    let events = cal.getEvents(start,end);
    let match = events.filter( event =>  { if( event.getTitle().match(/.*\,.*\,/) !== null ) return event; } );
    if( match ) {
      match.forEach( event => {
          let title = event.getTitle();
          console.log(title);
        }
      );
    }
  }
  catch(err) {
    console.log(err)
  }
}

Execution log

3:20:56 AM  Notice  Execution started
3:20:56 AM  Info    Test, event one, test
3:20:56 AM  Info    Test, event 3, test
3:20:56 AM  Info    Test,event,two
3:20:56 AM  Notice  Execution completed

Reference

RegExr regex tester

edited Jun 22, 2022 at 10:26

answered Jun 10, 2022 at 13:12

TheWizEd

8,6542 gold badges14 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

19 Comments

Marc van Maastricht Over a year ago

Thank you so much for your time and efforts @TheWizEd! That seems like a nice piece of code! I've tried to implement it, but am apparently too new and inexperienced to be able to fully understand and make use of it. I've updated my question with the complete code I've got so far.

Marc van Maastricht Over a year ago

I've also added 'your' search string in the code, but then only 10 items (out of ±360 items) are found.

Marc van Maastricht Over a year ago

Furthermore: the search string you use will definitely find events that are written like the test events you've tried it on: Test,,event1. But would it also find events like: Test,Projectname,event1?

TheWizEd Over a year ago

I've updated my script to suit your situation.

Marc van Maastricht Over a year ago

Thanks again!! I've updated my script with your input (see my original question: I've added the new code), but get the following error: TypeError: Cannot read property 'length' of undefined getCalendarEvents2 @ Urenoverzicht.gs:45. When I change "match.forEach" in "events.forEach" the script works, but the filter is obviously not applied.

|

Collectives™ on Stack Overflow

Search on a literal comma (string) in Google apps script

1 Answer 1

19 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

19 Comments

Your Answer

Sign up or log in

Post as a guest

Related