Google sheet to calendar sync to send email invites to attendees

[gitika srivastava]

Hello,

I am new to this and was hoping someone could help me with a script to cater the following scenario.

I have a Google doc- excel where I am listing the title, start date time, end date time, guest 1 email ID, guest 2 email ID, guest 3 email ID, and guest 4 email ID.

Something like this:

title	start time	end time	guest1	guest2	guest3	guest4
WCC	19-05-2021 20:30	19-05-2021 21:00	ab...@gmail.com	ab...@gmail.com	ab...@gmail.com

Based on this information, I want to run a script to fetch information from this sheet and add calendar meeting to all these email ids. This is the script that I have been able to compile until now:

function scheduleShifts() {
  /** 1) Task-1 Open the event calendar. **/
  var spreadsheet = SpreadsheetApp.getActiveSheet();
var calendarId = "My email ID";
var eventCal = CalendarApp.getCalendarById(calendarId);
/** Task-2 Select data in spreadsheet. **/
var signups = spreadsheet.getDataRange().getValues();
/** Step-3 Create the events.  **/
for (x=0;x<signups.length;x++)
var shift = signups[x];
var title = shift[0];
var startdate = shift[1];
var enddate = shift[2];
var guest1 = shift[3];
var guest2 = shift[4];
var guest3 = shift[5];
var guest4 = shift68];
eventCal.createEvent(title,new Date(startdate), new Date(enddate),
{guest1,guest2,guest3,guest4,sendInvites:true})
}

It says execution is complete but nothing appears on the calendar. I feel it is wrong but there are so many pieces that I am reading that I am stuck at it for the last couple of days.

Could someone please help me with this use case. 

Also, what is the significance of calendar ID. If I want to send out the invite to only the people whose email I have mentioned and not tag it to a calendar which has the entire team in it, would my personal email id suffice?

Thanks a lot in advance,

I hope I can find a solution here.

Regards,

Gitika

[Sandi Berg]

Calendar id is the calendar you want to add the event to. Ie)...the main calendar. It isn't the participants' but your calendar.

You will need to grab your calendar's id from settings.

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAE43t4eMhZtLkyBOhdPzOMeic30KaU6MFjeWMvTOfdyM%2BbLjrg%40mail.gmail.com.

[Sandi Berg]

Oops. I missed that you have that in there (assuming you removed your email address and wrote the generic statement for sharing.

[Bennett, Scott]

I could be wrong, but I believe that your guest list will need to be an array.  

--

[gitika srivastava]

Hey!

Thanks Sandi and Scott for responding so quickly. 

Yes, I realized that the calendar ID I was stating was where the meeting was being set up. However, the guests that I added weren't receiving the invites.

Could you please share an example of how my information should be organized and how I can call it from the script?

I am very naive in this area, as I said previously, And any inputs would be greatly appreciated.

Thank you!

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAB807N39iFNBAAVB3ugnu4PV-aAef3_7exaamRwmd4a4KmUgDg%40mail.gmail.com.

[Bennett, Scott]

eventCal.createEvent(title,new Date(startdate), new Date(enddate),
{guests:[guest1,guest2,guest3,guest4],sendInvites:true})

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAE43t4f5z5f1ZL07tWphv0PjZHhtst2j4O4rrMC0afFVSH5nGw%40mail.gmail.com.

--

Scott Bennett

[Bennett, Scott]

You may have to code in some things based on the number of guests if they will not always be the same length.  I am not sure what will happen if there is a null in the guest list.  

On Wed, May 19, 2021 at 8:15 AM gitika srivastava <gitik...@gmail.com> wrote:

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAE43t4f5z5f1ZL07tWphv0PjZHhtst2j4O4rrMC0afFVSH5nGw%40mail.gmail.com.

--

Scott Bennett

Data and Assessment Coordinator/Math Teacher

Bradley-Bourbonnais Community High School

P 815.937.3707 ext. 6010

C 708.584.0872

[gitika srivastava]

Hi Scott,

There will always be 4 email ids populated in the 4 guest columns.The length of the email ids will vary based on the individual's name.

I tried using the syntax you shared and I am getting an error message:

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAL7%3DANF_gug1%3D%3D53Lrn02yabxH7kyxYTJ%3D2Um1O8QFfK6HFeRA%40mail.gmail.com.

[Sandi Berg]

Not sure if you noticed but guest4 has a 6 instead of a [

--

[gitika srivastava]

Yup, I updated it in the code. 

Thanks!

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAB807N28Yazam-a6M3h-DMPD2c7CfnR4%3DcHE2KHoKNCTG%3DrEBg%40mail.gmail.com.

[Sandi Berg]

Here's a bit of a brute force.  eventCal.createEvent(title, startTime, endTime, {sendInvites:true}).addGuest(guest1).addGuest(guest2);

[gitika srivastava]

This avoided the error, but it still doesn't schedule meetings yet. Can you help?

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAB807N0OXoHef1dSTh1qb6YaOUL4oQPGLceRmHbVuFK25ztL9w%40mail.gmail.com.

[Bennett, Scott]

Is everything working now?

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAE43t4cxhL4na%2BwRw4KwEBDvrrm5Ke0jMmqwoet1g_z_Hmq4Mw%40mail.gmail.com.

--

Scott Bennett

[Sandi Berg]

One thing I did when creating my version to copy yours is set the format of the date cells to date with time. Did you do that?

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAL7%3DANGSdjSsDO6OP1qhEaWpfu3RSfdH0wG7GAmO9p%2B_37b%2BHQ%40mail.gmail.com.

[Sandi Berg]

Here is the sheet I made to play with it:  https://docs.google.com/spreadsheets/d/1fUk3RryYlDN3nqsFMCw56Mz68eMc1s_TSv-XHR61C3Q/edit?usp=sharing

I will note that it doesn't send the invite to the users but it works. I have deleted my email address from the script so you would need to replace that.  I also only added the first two guest emails but just repeat the .addGuest

[Bennett, Scott]

try logging your two dates to confirm they are what you are expecting.  I have done a few of these and they always give me problems.  

function scheduleShifts() {
  /** 1) Task-1 Open the event calendar. **/
  var spreadsheet = SpreadsheetApp.getActiveSheet();
var calendarId = "My email ID";
var eventCal = CalendarApp.getCalendarById(calendarId);
/** Task-2 Select data in spreadsheet. **/
var signups = spreadsheet.getDataRange().getValues();
/** Step-3 Create the events.  **/
for (x=0;x<signups.length;x++)
var shift = signups[x];
var title = shift[0];
var startdate = shift[1];

Logger.log(startdate)

var enddate = shift[2];

Logger.log(enddate)

var guest1 = shift[3];
var guest2 = shift[4];
var guest3 = shift[5];

var guest4 = shift[6];

eventCal.createEvent(title,new Date(startdate), new Date(enddate),
{guest1,guest2,guest3,guest4,sendInvites:true})
}

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAB807N1kSErJMFA_Ufu4DXDzcDRf%2BODgWU-Q%2By_JRDS-1L5ppQ%40mail.gmail.com.

[Sandi Berg]

I don't know if you saw the comment that I started to type in the ss when you were there but your date wasn't wrong. I just relooked. It was my typo. lol.

[Bennett, Scott]

When you are retrieving your data from the sheet use getDisplayValues() instead of getValues()

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAE43t4dpnNt2P_kGrAn0dBWAM0%2BPX%2BcQ11K_%2B3R2C7%2Bd9zaZUA%40mail.gmail.com.

--

Scott Bennett

[Bennett, Scott]

I think I may have figured it out. 

Make your dates in the sheet have the format of 2021-05-19 08:00 then use getDisplayValues()

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAE43t4dpnNt2P_kGrAn0dBWAM0%2BPX%2BcQ11K_%2B3R2C7%2Bd9zaZUA%40mail.gmail.com.

[Kim Nilsson]

Guys, I just can't get the list of users correct unless I type it out like this manually.

Is there no other way to create the list of guests with the correct comma-separated format surrounded by quotes?

Also, why isn't the new event id displayed in the logger row I added? It only prints the calendar id (my email address, and not the new event id).

function scheduleShifts() {

  var spreadsheet = SpreadsheetApp.getActiveSheet();

  var calendarID = "MyEmail/CalendarAddress";

  var eventCal = CalendarApp.getCalendarById(calendarID);

  var signups = spreadsheet.getRange("A2:G2").getDisplayValues();

  for (x = 0; x < signups.length; x++) {

    var shift = signups[x];

    var title = shift[0];

    var startTime = shift[1];

    var endTime = shift[2];

    var guest1 = shift[3];

    var guest2 = shift[4];

    var guest3 = shift[5];

    var guest4 = shift[6];

    // I could only get the guests to be added and invited if I formated the syntax like this

    eventCal.createEvent(title, new Date(startTime), new Date(endTime), { location: 'The Moon', sendInvites: true, guests: "'" + guest1 + "," + guest2 + "," + guest3 + "," + guest4 + "'" });

    Logger.log('Event ID: ' + eventCal.getEventId());// This doesn't display the id of the event! Why not?

  }

}

[Sandi Berg]

The code from my sheet for the emails didn't work for you? That's weird. It worked for me. 

--

You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/cef91b14-f932-418f-81eb-0e324cdab978n%40googlegroups.com.

[Sandi Berg]

Could you share a link to your file?

[cbmserv...@gmail.com]

The list of guests does not need to be an array.

 

You can just have all the emails comma separated and included in one long string as the 1 guest in the invite.

 

All of the emails in the comma separated guest list will get an invite.

--

You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/cef91b14-f932-418f-81eb-0e324cdab978n%40googlegroups.com.

[Bennett, Scott]

You are trying to get the eventID of  the calendar, not the event.

So they need to be strings, so you could just join them 

let guestArray = [guest1,guest2,guest3,guest4]

let guestString = guestArray.toString()

--

You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/cef91b14-f932-418f-81eb-0e324cdab978n%40googlegroups.com.

[Kim Nilsson]

Yes, that's the problem, because how do you insert the guest1-4 values inside the {guests: ........} to be represented as a single string?

No, bsdemon, your script doesn't send the invite. It does add the users correctly to the event, but nobody gets an invite.

[cbmserv...@gmail.com]

To get the id of the event, use:

 

eventCal.getId();

 

From: 'Bennett, Scott' via Google Apps Script Community <google-apps-sc...@googlegroups.com>
Sent: May 19, 2021 11:18 AM
To: google-apps-sc...@googlegroups.com
Subject: Re: [Apps-Script] Google sheet to calendar sync to send email invites to attendees

 

You are trying to get the eventID of  the calendar, not the event.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAL7%3DANER1t7c81H5v2LAVNUqpF_f_DBn1WPb%2BhZjXm5jOUBUCQ%40mail.gmail.com.

[Kim Nilsson]

That array-to-string did the trick!

I also figured out that I could grab the eventid in the same command.

function scheduleShifts() {

  var spreadsheet = SpreadsheetApp.getActiveSheet();

  var calendarID = "CalendarID/Email";

  var eventCal = CalendarApp.getCalendarById(calendarID);

  var signups = spreadsheet.getRange("A2:G2").getDisplayValues();

  for (x = 0; x < signups.length; x++) {

    var shift = signups[x];

    var title = shift[0];

    var startTime = shift[1];

    var endTime = shift[2];

    var guest1 = shift[3];

    var guest2 = shift[4];

    var guest3 = shift[5];

    var guest4 = shift[6];

    let guestArray = [guest1,guest2,guest3,guest4]

    let guestString = guestArray.toString()

    var eventID = eventCal.createEvent(title, new Date(startTime), new Date(endTime), { sendInvites: true, guests: guestString}).getId();

    Logger.log('Event ID: ' + eventID);// Figured out how to get the event id. :-)

  }

}

Kim

[Kim Nilsson]

Next thing to make sure is correct. Timezone in the script.

May also have to set correct language and TZ in the Sheet (I did, anyway).

Make sure appscript.json is visible in the editor and adjust the timezone to where you are. Else the calendar events will be wildly off. :-)

Worked fine after I changed mine to Europe/Stockholm.

The script works when adding more rows, as long as the range (value of G) is increased.

Should work fine to add more columns of details to be included, like location that I inserted manually a few times. Description, for example, if you wish to add some text to it.

Kim

[gitika srivastava]

So, I used the code with the array-to -string and this is how it looks now:

function scheduleShifts() {

  /** 1) Task-1 Open the event calendar. **/

  var spreadsheet = SpreadsheetApp.getActiveSheet();

var calendarId = "ab...@gmail.com";

var eventCal = CalendarApp.getCalendarById(calendarId);

/** Task-2 Select data in spreadsheet. **/

var signups = spreadsheet.getRange("A2:G2").getDisplayValues();

/** Step-3 Create the events.  **/

for (x=0;x<signups.length;x++)

var shift = signups[x];

var title = shift[0];

var startdate = shift[1];

Logger.log(startdate)

var enddate = shift[2];

Logger.log(enddate)

var guest1 = shift[3];

Logger.log(guest1)

var guest2 = shift[4];

Logger.log(guest2)

var guest3 = shift[5];

Logger.log(guest3)

var guest4 = shift[6];

Logger.log(guest4)

/**eventCal.createEvent(title,new Date(startdate), new Date(enddate),

,sendInvites:true}) **/

let guestArray = [guest1,guest2,guest3,guest4]

let guestString = guestArray.toString()

var eventID = eventCal.createEvent(title, new Date(startdate), new Date(enddate), { sendInvites: true, guests: guestString}).getId();

Logger.log('Event ID: ' + eventID);

  }

The data is as follows:

title

start time

end time

guest1

guest2

guest3

guest4

WCC

2021-05-22 09:00AM

2021-05-22 09:30AM

ab...@gmail.com

ab...@gmail.com

ab...@gmail.com

ab...@gmail.com

The timezone is:

{

  "timeZone": "America/Toronto",

  "dependencies": {

  },

  "exceptionLogging": "STACKDRIVER",

  "runtimeVersion": "V8"

}

It executed successfully, but I still don't see anything on my calendar or the guests calendar. I am not sure what isn't working for me.

--

You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CACK38a6O%3Dz-yiW84Nqkuq2U2qK%3D%3DA0epHsNPy71A59TstQq-ig%40mail.gmail.com.

[Bennett, Scott]

I used this type of code a lot over the past year.  I will tell you that if the date is off slightly it causes a huge issue.  This may sound odd but I kept having events created around 1969-1970 because the dates were off slightly.  You may want to check and see if events are being created in the timeframe.  

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAE43t4f%3DjtRWw_TcSBjKS9t1x4F%3DwbVnnHROxGtp7s8yYe2d_A%40mail.gmail.com.

[gitika srivastava]

Yeah, That is bang on. It got created for 1st JAN 1970. What time format worked for you?

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAL7%3DANEUpgQnQuoRyYTHEehhV5_2McU93m2hZwKbKr0Hq%2B4YCA%40mail.gmail.com.

[Bennett, Scott]

I would grab the date and time separately.  So I had a cell for date and a cell for time.  

var dayof = sheet.getRange('B1').getDisplayValue();// date event starts

    var starttime = sheet.getRange(activeRow,1).setNumberFormat('hh:mm:ss').getDisplayValue();// time that event starts

    var startdate = new Date(dayof+'T'+starttime);//actual value plugged into the calendar event creation

    var enddate = new Date(startdate.getTime()+(1000*60*meetinglength))//enddate and time of event

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAE43t4dyFD8AJ7xd-_f2LNZC%2BEfQtU_YEa2RBzWu0cSi4_QMRw%40mail.gmail.com.

[Bennett, Scott]

Maybe try getting rid of the AM PM in your time.  It needs to be 24 hr.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAE43t4dyFD8AJ7xd-_f2LNZC%2BEfQtU_YEa2RBzWu0cSi4_QMRw%40mail.gmail.com.

[Bennett, Scott]

I just tested it, getting rid of the AM/PM should do the trick.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAE43t4dyFD8AJ7xd-_f2LNZC%2BEfQtU_YEa2RBzWu0cSi4_QMRw%40mail.gmail.com.

[gitika srivastava]

Hi guys,

Thanks a lot for all your help. I really appreciate all your efforts. 

The code worked and is triggering the invites now. 

In case I need to add more complexity to this, will reach out to you for your guidance.

Again, thanks a lot!

Have a great day :) 

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAL7%3DANGmnsBT4MWZUXU_J5XgZiuq%3DaVL-gZ6cAa1L43LF%2BLaig%40mail.gmail.com.

[gitika srivastava]

And I am back. Too soon! haha

Just wanted to check with you on one more thing. So, I am going to use this code to schedule multiple meetings for different set of people. 

So, my sheet would look something like this:

title

start time

end time

guest1

guest2

guest3

guest4

WCC	2021-05-23 21:00	2021-05-23 21:30	email1	email2	email3	email4
WCC	2021-05-24 21:00	2021-05-24 21:30	email5	email6	email7	email8
WCC	2021-05-25 21:00	2021-05-25 21:30	email9	email10	email11	email12
WCC	2021-05-26 21:00	2021-05-26 21:30	email13	email14	email15	email16
WCC	2021-05-27 21:00	2021-05-27 21:30	email17	email18	email19	email20
WCC	2021-05-28 21:00	2021-05-28 21:30	email21	email22	email23	email24
WCC	2021-05-29 21:00	2021-05-29 21:30	email25	email26	email27	email28
WCC	2021-05-30 21:00	2021-05-30 21:30	email29	email30	email31	email32
WCC	2021-05-31 21:00	2021-05-31 21:30	email33	email34	email35	email36
WCC	2021-06-01 21:00	2021-06-01 21:30	email37	email38	email39	email40
WCC	2021-06-02 21:00	2021-06-02 21:30	email41	email42	email43	email44
WCC	2021-06-03 21:00	2021-06-03 21:30	email45	email46	email47	email48
WCC	2021-06-04 21:00	2021-06-04 21:30	email49	email50	email51	email52
WCC	2021-06-05 21:00	2021-06-05 21:30	email53	email54	email55	email56
WCC	2021-06-06 21:00	2021-06-06 21:30	email57	email58	email59	email60
WCC	2021-06-07 21:00	2021-06-07 21:30	email61	email62	email63	email64
WCC	2021-06-08 21:00	2021-06-08 21:30	email65	email66	email67	email68
WCC	2021-06-09 21:00	2021-06-09 21:30	email69	email70	email71	email72
WCC	2021-06-10 21:00	2021-06-10 21:30	email73	email74	email75	email76
WCC	2021-06-11 21:00	2021-06-11 21:30	email77	email78	email79	email80
WCC	2021-06-12 21:00	2021-06-12 21:30	email81	email82	email83	email84
WCC	2021-06-13 21:00	2021-06-13 21:30	email85	email86	email87	email88

In this scenario, is there a way to avoid duplicate triggers everytime I run the code, as I think we will be keeping the data as is and not deleting it.

So, I want to ensure that if events have been set up for lines 1 to 6 in the first run; they should somehow be flagged, so that next time when I have data populated from line 7 onwards, and I run the code, it only creates new events.

Thanks in advance!

[Bennett, Scott]

You could add a checkbox or something to the last column and have it check that it is false before creating the event and set the value to true after each event is created.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAE43t4eB8bNWMUnOUNXDO3njSo5HsmpPHpPtJoFYQeQGNeSFWw%40mail.gmail.com.

[Kim Nilsson]

Yes, a "set" value or whatever you want in the column after guest4, that is set within the loop after creating the event.

And then have an if-else clause before creating the event.

Kim

[gitika srivastava]

Hello,

I have been trying to find a method that I can use to post the calendar ID into the eventID column(added new column after guest4).

Have also declared var eventID=shift[7];

This is what I have added

function scheduleShifts() {

  /** 1) Task-1 Open the event calendar. **/

  var spreadsheet = SpreadsheetApp.getActiveSheet();

var calendarId = "gsriv...@salesforce.com";

var eventCal = CalendarApp.getCalendarById(calendarId);

/** Task-2 Select data in spreadsheet. **/

var signups = spreadsheet.getRange("A2:G2").getDisplayValues();

/** Step-3 Create the events.  **/

for (x=0;x<signups.length;x++)

var shift = signups[x];

var title = shift[0];

var startdate = shift[1];

Logger.log(startdate)

var enddate = shift[2];

Logger.log(enddate)

var guest1 = shift[3];

Logger.log(guest1)

var guest2 = shift[4];

Logger.log(guest2)

var guest3 = shift[5];

Logger.log(guest3)

var guest4 = shift[6];

Logger.log(guest4)

var eventID=shift[7];

let guestArray = [guest1,guest2,guest3,guest4]

let guestString = guestArray.toString()

var eventInstance = eventCal.createEvent(title, new Date(startdate), new Date(enddate), { sendInvites: true, guests: guestString}).getId();

Logger.log('Event ID: ' + eventInstance);

eventID= setValues(eventInstance);

  }

This creates an event but doesn't update the google sheet.

--

You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CACK38a6K9PK1KbVs-N8T9tSjBzfFF0O-NoD23Ay-GH1USi6CBg%40mail.gmail.com.

[Bennett, Scott]

You will need to set the values using

spreadsheet.getRange(x,8).setValue(eventInstance)

You may have to use  spreadsheet.getRange(x+1,8).setValue(eventInstance) or something like that to get the rows to line up correctly.

You also need to declare your iterator variable

for(let x=0;x<signups.length;x++){

//

}

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAE43t4c65bZ%2B5JP-apZXZOBewGykBK%3D5cdLLBKaJtY_VCjOVgA%40mail.gmail.com.

[gitika srivastava]

Hello,

My code was working fine until I took it to the original spreadsheet. Now it is pointing to the wrong tab. And not yielding the right output. 

Can someone suggest how to ensure to point to the right tab when calling the active spreadhseet.

As of now, I am using

var ss = SpreadsheetApp.getActiveSheet();

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAL7%3DANF_MUZU1eoyfJstBahJD7o_usa2Ke8zN6M7kDcC3QeF-g%40mail.gmail.com.

[Kim Nilsson]

Use code that grabs the sheet by name instead.

 var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Name of your sheet');

Kim

[gitika srivastava]

This worked. Thanks!

I have another question.

So, the code sends out calendar meetings to all the guests but it doesn't permit them to modify the event. I wanted to give them the option to reschedule.

Is that possible? If yes, could someone point me in the right direction?

Thanks!

--

You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CACK38a47rsC1zY14Ee3PAJeVbscWhwPgyCiMU0cCC_ixn%3D04gA%40mail.gmail.com.

[Phil Bainbridge]

Hi

Just to let you know I have a blog post here for creating Google Calendar events via the Calendar API. It is a downloadable Apps Script file if you want to take a copy but I found it really useful to breakdown/learn what is necessary for creating an event and doing things like setting dates/times and allowing Guests to modify, etc.

My finished solution can be found here - Bulk create Google Calendar events with optional Google Meet - again available for download, lots of comments in the code to help people understand what is going on.

Kind regards

Phil

[gitika srivastava]

So, this is how my code looks like:

function wccschedule(){

  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Apps Script");

  var index=2;

  var lastRow = spreadsheet.getLastRow();

  Logger.log(lastRow)

  var calendarID = "x...@gmail.com";

var eventCalendar = CalendarApp.getCalendarById(calendarID);

for(;index<=lastRow; index++)

{

  var title = spreadsheet.getRange(index,1,1,1).getValue();

  var description = spreadsheet.getRange(index,2,1,1).getValue();

  var startdate = spreadsheet.getRange(index,3,1,1).getValue(); 

  Logger.log(startdate)

  var enddate = spreadsheet.getRange(index,4,1,1).getValue();

  Logger.log(enddate)

  var guest1 = spreadsheet.getRange(index,5,1,1).getValue();

  Logger.log(guest1)

  var guest2 = spreadsheet.getRange(index,6,1,1).getValue();

  Logger.log(guest2)

  var guest3 = spreadsheet.getRange(index,7,1,1).getValue();

  Logger.log(guest3)

  var guest4 = spreadsheet.getRange(index,8,1,1).getValue();

  Logger.log(guest4)

  var Status= spreadsheet.getRange(index, 9,1,1).getValue();

  var guestsCanModify=true;

let guestArray = [guest1,guest2,guest3,guest4]

let guestString = guestArray.toString()

if(Status !="Done")

{

  var events = eventCalendar.createEvent(title, new Date(startdate), new Date(enddate), {description, sendInvites: true, guests: guestString, guestsCanModify}).getId();

    Status = spreadsheet.getRange(index,9).setValue("Done");

}

}

}

It still executes successfully and creates a corresponding calendar event as well, but it still doesn't allow guests to modify events. What am I doing incorrectly.

I have also setup the authorization in the json file.

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/9e21be83-6987-4994-aa00-ce66a1c32491n%40googlegroups.com.

[Phillip Bainbridge]

Hi Gitika

Here's the response I put on my blog post after your comment:

The tool I've created uses the Calendar API where there are many more options available to define.

From your code it looks like you are using the 'Calendar App' which does not allow for details such as Guest modification to my knowledge.

Kind regards

Phil