Error: Building a room booking system using Google Forms with Sheets

[Colin Kagame]

Hi All, 

Really need your help. 

I am constructing a room booking system for a health center I work.There are only 2 booking rooms at this building.

I followed a tutorial from this gentleman (linked here) but really couldn't get 2 things to work i.e. Email sent confirming status of booking after user submits info via Google Form. Secondly, the status column indicating whether a booking was successful or a conflict also couldn't work.

What works is the data input into the form is stored into the corresponding Google Spreadsheet but that's all. 

I have also been notified of a ReferenceError: "cal" is not defined. (line 32, file "Code"). Please have a look at the script code and system. Please see attached images of error.

Link to editable FORM - Click here.

Link to editable Google SHEET - Click here.

Thank you loads,

Colin.

[Clark Lind]

Looks like you don't have any connection between room numbers 100 and 102 and Upstairs and Downstairs. Something along these lines might do it:

At row 24 in code:

var tempRoom = sheet.getRange(row, 8).getValue();
if (tempRoom == "Upstairs Meeting Room") {
  this.room = 102;
} else {
  this.room = 100;
}

[Colin Kagame]

Thanks for your response,

I have done as requested but unfortunately I receive another error. Please see image:-

The email confirmation and status column still don't show up after the trial.

Hope I placed the code in as you suggested?

Thanks, 

[Clark Lind]

Yes, sorry. Comment that line (34) out for now. That is what we were replacing.

[Colin Kagame]

I've done that and commented out line 34. 

It just delivers a new error now stating: ReferenceError: "calundefined" is not defined. (line 40, file "Code")

If required, feel free to test it via the link I provided by the way.

Thanks. (attached image of error after disabling line 34)

[Clark Lind]

I think I'll go back and start from scratch and see if I can get it working with me having full access to everything, then copy/paste the code if I can get it working.

[Colin Kagame]

Okay, really appreciate the support. Looking forward to your feedback.

[Clark Lind]

Well, I started from scratch and still couldn't make it work. First it was hanging up in his makeEmail function, so I got that all fixed. Then it keeps hanging up when trying to get any conflicts because something in the time conversion isn't working properly (the whole getEndTime() function is not setting the request.endTimeString value). 

One of the major problems with your code was simply not having the room numbers on the end of the room names on the form :)  That is where the roomInt comes from.

I'll keep playing with it, but looks like many others had issues with this code too (youtube comments). Sorry for not being more helpful.

[Colin Kagame]

Many thanks, really do appreciate all your help. Someone managed to fix the problem. Do take a look and feel free to create a copy. It's all working now. 

Only sad thing is Google's calendar doesn't synchronize well MS Outlook. 

[Clark Lind]

Awesome, will do!

[Clark Lind]

What was I missing? Just curious for my own learning.