Google Script to return calendar guest names (only) into one cell separated by comma-CodePudding

I need help adjusting the script to return the calendar guest names only (not email) and put the names into 1 cell with each name separated by a comma.

I found a script that does this with the guests email, but I cannot figure out how to edit the script to return Names only. Here is the spreadsheet:

Daily Reminders

Any help would be appreciated.

Thank you for taking a look.

I have spent 3 days working on this. First I watched this video to learn to get the event, then had to read this previous, similar, but not really!, solution and have played with, and read this App Script Developer Site for 3 days until I figured out how to get the emails.

function getTomorrowEvents() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Tomorrow");

  var start_time = sheet.getRange("b2").getValue();
  var end_time = sheet.getRange("d2").getValue();
  var id_cal = sheet.getRange("B1:F1").getValue();

  var cal = CalendarApp.getCalendarById(id_cal);
    var events = cal.getEvents(new Date(start_time), new Date(end_time));

  for (var i = 0;i<events.length;i  ){
    
    var title =  events[i].getTitle();
    var start_time =  events[i].getStartTime();
    var end_time =  events[i].getEndTime();
    var loc = events[i].getLocation();
    var des =  events[i].getDescription();
    var guests = events[i].getGuestList();
    var guestEmails = "";
     for (var j = 0; j < guests.length; j  ) {
     var guest = guests[j].getEmail();
     guestEmails  = guest ", ";
   }
    
    sheet.getRange(i 4,2).setValue(title);
    sheet.getRange(i 4,3).setValue(start_time);
    sheet.getRange(i 4,4).setValue(end_time);
    sheet.getRange(i 4,5).setValue(loc);
    sheet.getRange(i 4,6).setValue(des);
    sheet.getRange(i 4,7).setValue(guestEmails);

  }
  Logger.log("Events have been added to the Spreadsheet");
}

CodePudding user response：

I believe your goal is as follows.

About guestEmails in your showing script, you want to retrieve the user's name instead of the email address.

Issue and workaround:

When I saw getName() of Class EventGuest, it says the guest's name, or the guest's email address if the name is not available. Ref
When I saw attendees[].displayName of Event of Calendar API, it says The attendee's name, if available.. Ref

When I tested getName() in my calendar, no value is returned. Although, unfortunately, I'm not sure about your actual calendar, if you can retrieve the user's name, you can achieve your goal by the following modification.

From
```
  var guest = guests[j].getEmail();
```
To
```
  var guest = guests[j].getName();
```

If this modification was not the direct solution to your situation, it is required to convert the email address to the user's name. In this case, how about the following modification?

Modified script:

In this case, ContactsApp is used for converting the email address to the name.

From:

var guest = guests[j].getEmail();

To:

var guest = ContactsApp.getContact(guests[j].getEmail()).getFullName();

Or, I think that you can also use People API as follows. Before you use this script, please enable People API at Advanced Google services.

From

  for (var i = 0;i<events.length;i  ){

    var title =  events[i].getTitle();
    var start_time =  events[i].getStartTime();
    var end_time =  events[i].getEndTime();
    var loc = events[i].getLocation();
    var des =  events[i].getDescription();
    var guests = events[i].getGuestList();
    var guestEmails = "";
     for (var j = 0; j < guests.length; j  ) {
     var guest = guests[j].getEmail();
     guestEmails  = guest ", ";
   }

  var res = People.People.Connections.list("people/me", { personFields: "emailAddresses,names", pageSize: 1000 });
  var obj = res.connections.reduce((o, e) => (o[e.emailAddresses[0].value] = e.names ? e.names[0].displayName : e.emailAddresses[0].value, o), {});
  for (var i = 0; i < events.length; i  ) {
    var title = events[i].getTitle();
    var start_time = events[i].getStartTime();
    var end_time = events[i].getEndTime();
    var loc = events[i].getLocation();
    var des = events[i].getDescription();
    var guests = events[i].getGuestList();
    var guestEmails = "";
    for (var j = 0; j < guests.length; j  ) {
      var guest = obj[guests[j].getEmail()] || guests[j].getEmail();
      guestEmails  = guest   ", ";
    }

Issue and workaround:

Modified script:

From:

To:

References: