Extracting calendar entries based on various criteria

[Cenk Eldem]

Hi, 

I am a newb to google apps script and thought that my need isn't unique and there should be a solution script available already but I don't even know how to look for it. 

I need to extract from my google calendar (main view) all entries between FROM and TO that (in my case) meet the criteria "Calendar Entry title is starting with the word "xyz"".  All these entries should be extracted and listed in a spreadsheet incl. all relevant calendar fields.   

In a second step I'd like to transfer these calendar entries from my main calendar (the one with my name) to calendar "ABC". 

Your help is highly appreciated. 

[Edward Ulle]

Here is a sample script of how to get event between dates and titled.

function test_calendar() {

  try {

    let calendar = CalendarApp.getDefaultCalendar();

    let events = calendar.getEvents(new Date(2022,4,1),new Date(2022,5,1));

    events = events.filter( event => event.getTitle().includes("Test") );

    events.forEach( event => console.log(event.getTitle()) );

  }

  catch(err) {

    console.log(err)

  }

}

11:09:59 AM Notice Execution started 

11:09:59 AM Info Test, event one, test 

11:09:59 AM Info Test, event 3, test 

11:09:59 AM Info Test,event,two 

11:10:00 AM Notice Execution completed

[Edward Ulle]

I finally figured out the query.  The script can be further simplified as:

function test_calendar() {

  try {

    let calendar = CalendarApp.getDefaultCalendar();

    let events = calendar.getEvents(new Date(2022,4,1),new Date(2022,5,1),{search: "Test"});

[Cenk Eldem]

Thx for the quick response. When trying to execute the script I saw that I need to grant access to the app, which I will try to solve tomorrow. 

[Edward Ulle]

This happens when ever a new Google Service is added to a script. Since you are the owner simply enable in “Unsafe” mode.

[Cenk Eldem]

Worked :-). How do I get access to the other fields in each calendar entry and how to I write them into single columns in a spreadsheet?

[Edward Ulle]

Look at Calendar Events

[Edward Ulle]

Here is an updated script to get different info for each email.  You can expand on it as you see fit.  The results array can then be put into a spreadsheet using getRange(i,j,k,l).setValues(results).

// Explanation of how the search section works (as it is NOT quite like most things Google) as part of the getEvents function:

//    {search: 'word1'}              Search for events with word1

//    {search: '-word1'}             Search for events without word1

//    {search: '-word1 word2'}        Search for events with word2 ONLY

//    {search: 'word1 -word2'}       Search for events without word2

//    {search: 'word1+word2'}        Search for events with word1 AND word2

//    {search: 'word1+-word2'}       Search for events with word1 AND without word2

//    {search: '-word1+-word2'}      Search for events without word1 AND without word2 (I find this to work logically like an 'OR' statement)

function test_calendar() {

  try {

    let calendar = CalendarApp.getDefaultCalendar();

    let events = calendar.getEvents(new Date(2022,4,1),new Date(2022,5,1),{search: "Test"});

    events.forEach( event => console.log(event.getTitle()) );

    let results = events.map( event => {

        return [event.getTitle(), event.getStartTime().toDateString(), event.getStartTime().toLocaleTimeString()];

      }

    );

    console.log(results);

  }

  catch(err) {

    console.log(err)

  }

}

[Cenk Eldem]

Works almost perfect! 

The returned output is too large to be logged and therefore truncated. 

How can I write each event directly into a spreadsheet line or a file instead of the console log?

[Edward Ulle]

I'll give you an example and leave it up to you to research and work it out.

Range.setValues()