Use Google App Scripts to create a calendar event from form responses

Kevin Cunningham: [0:00] I have a simple Google form here, which is scheduling a meeting between me and me. I have a date of that meeting and the time for the meeting. Let me submit my form.

[0:10] Here I have the responses being collected. I'm going to add an extra column called...In the moment, I'm going to leave that blank. I want to create a script. To do that, I'll go to Tools and head to Script editor. My function is to create new events.

[0:26] First, I want to get the sheet, and I do that with the SpreadsheetApp class and specifically to getActiveSheet() function. Then, I want the range. I want everything in the sheet, so I'm going to ask the sheet what is the data range. Then, I want the data and the data I'm going to get from the range, and I'm going to get the values.

[0:45] I'm going to iterate over that data now. I'm going to start at i = 1 because I don't want to include the header row. I'm going to keep going until I get to the end of the data, and I'll increment by 1 each time. I want to count counter every row.

[0:57] The first thing I'll do is to find out whether or not the cell is booked. I'll go to the range and I'm going to get the cell. The data, which is returned from getValues, is zero-indexed. getCell is not, so where I'm dealing with the ith row, for this function, I want the i + 1. One, two, three, four, five, six. I want the sixth column from that cell.

[1:18] I'm going to hit Content, and if that cell is booked, then we won't process the row, we'll continue through our for loop.

[1:26] Now I want to get the people. Person1 is in the ith row that we're in at the moment and is the first column. Remember, these are zero-indexed, so that's 01 for Person1. Person2 is in the second column. That's in the ith row, second column.

[1:43] Now we want the dates. As you can see, this is a date, and this is a time. I'm going to get the date by making a new date object from the data in the third column. I'm going to get the time by making a new date object from the data in the fourth column.

[2:00] Now I'll use those to construct a booking date. We got a new date. I'll get the full year from the date, I'll get the month from the date, and I'll get the date from the date. From the time, we'll get the hour, the minutes, and the seconds.

[2:19] The endtime, so, the booking end, is going to be all that except by default, I'm going to allow my meetings to last one hour.

[2:27] Now we have all the information we need to be able make our booking. What we need is the CalendarApp class now, and we're going to getCalendarById().

[2:34] We get our calendar id by going to our Settings menu in calendar, selecting the calendar we're interested in, and scrolling down to the bottom, where it says Integrate Calendar. We see our calendar id, which is normally the email address that's associated with it.

[2:48] Once we got a calendar, now we're going to create our event. I'm going to use a template string for the title for the event. That's a meeting between Person1 and Person2. As a second argument, I'm going to pass in the booking date. As a third argument, I'm going to pass in the booking end.

[3:05] The final thing I do today, set the book cell to true, so that we don't book this event more than once.

[3:11] If we run that script now, the first thing it will do, it will ask me for some authorizations. I'll review my permissions. Here we're getting, "App isn't verified." I need to click Advanced, and go to Book Events on CF, and I want to allow that to be able to access my Google account.

[3:26] Let's try to run it. We're executing. You can see true has now been placed in book. We can see that the meeting has been scheduled through myself. I don't want to have to do that every time, so what I'm going to do is I'm going to create a button.

[3:41] There are these three dots here, so I'm going to assign a script to that, create the event. If I get rid of that true and delete that event again, let's try pressing our button. True has appeared here, calendar event has appeared here.