How can i specify the guest status so it will put the names in the right cell?

Question

I am very new to this kind of programming. To start the code i followed some tutorials and searched for info on this website.

I want to build a system that lets me automaticcly retrive info from my google calendar. the info i want from my calendar is:Name event, starttime, endtime, location, description, event guests and there status.

I made the following code, i am stuck. I get my guests but not there status. I want the confirmed guests emailadresses in colum 6, and want the people ho had declined in colum 7.

The problem no is that i cant find the right way/IF statements for this code. someone told me to use; getGuestStatus() inside an if statement to decide to which string / array to append the email address.

but i dont understand how to do this. Is there any one that could help me.

I right my info in a spreadsheet as you can see in my code.

function getEvents() {

  var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var cal = CalendarApp.getCalendarById("###");    "my calendar id is normaly here"
  var events = cal.getEvents(new Date("06/01/2020 12:00 AM"), new Date("06/30/2030 12:00 AM"));

  var lr = ss.getLastRow();
  ss.getRange(2,1,lr-1,6).clearContent();

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

    var title = events[i].getTitle();
    var sd = events[i].getStartTime();
    var ed = 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+", ";
    Logger.log(guest)

    }
    ss.getRange(i+2, 1).setValue(title);
    ss.getRange(i+2, 2).setValue(sd);
    ss.getRange(i+2, 2).setNumberFormat("dd/mm/yyyy /hh:mm");
    ss.getRange(i+2, 3).setValue(ed);
    ss.getRange(i+2, 3).setNumberFormat("dd/mm/yyyy /hh:mm");
    ss.getRange(i+2, 4).setValue(loc);
    ss.getRange(i+2, 5).setValue(des);
    ss.getRange(i+2, 6).setValue(guestEmails);

    }

}

Great thanks for anyone who takes the time to read this and can help me.

Alfredo

Answer 1

The guest status can be retrieved with guests[j].getGuestStatus();

You can create a combined string guestInfo that contains both the guestEmail and guestStatus.

Sample:

function getEvents() {
  var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var cal = CalendarApp.getCalendarById("primary")
  var events = cal.getEvents(new Date("04/04/2020 12:00 AM"), new Date("05/05/2020 12:00 AM"));
  var lr = ss.getLastRow();
  ss.getRange(2, 1, lr - 1, 6).clearContent();
  for (var i = 0; i < events.length; i++) {
    var title = events[i].getTitle();
    var sd = events[i].getStartTime();
    var ed = events[i].getEndTime();
    var loc = events[i].getLocation();
    var des = events[i].getDescription();
    var guests = events[i].getGuestList();
    var guestInfo = "";
    for (var j = 0; j < guests.length; j++) {
      var guest = guests[j].getEmail();
      var status = guests[j].getGuestStatus();
      Logger.log(guest);
      guestInfo += guest+": " + status + ", ";
    }
    ss.getRange(i + 2, 1).setValue(title);
    ss.getRange(i + 2, 2).setValue(sd);
    ss.getRange(i + 2, 2).setNumberFormat("dd/mm/yyyy /hh:mm");
    ss.getRange(i + 2, 3).setValue(ed);
    ss.getRange(i + 2, 3).setNumberFormat("dd/mm/yyyy /hh:mm");
    ss.getRange(i + 2, 4).setValue(loc);
    ss.getRange(i + 2, 5).setValue(des);
    ss.getRange(i + 2, 6).setValue(guestInfo);

  }
}