A while ago I was looking at Updating a Google Calendar and Google Site from a Google Spreadsheet (the beginnings of an event booking system). This idea was inspired by Tony Hirst's work on updating a google calendar from a spreadsheet the endpoint being a way to manage a simple event booking system using Google Apps. This all started to unravel as I couldn't find a way to create a custom booking form for each event. Tony suggested that I should look at just using a generic form which was manually updated with new events, the system handling the rest of the booking process. So with that little hurdle out of the way I revisted my script and as well as rewritting most of it I took the concecpt a little further.
So what do we have now? The video below walks through the workflow:
[You might want to enable full screen view to see what is happening]
The core code is at the end of this post and you can access the full spreadsheet and script here (once you open it click File –> Make a copy to allow you to edit). The bulk of this code is actually a reworking of some existing Google Apps Script tutorials:
To use this code yourself some variables need defining (I could have just called these from a sheet but ran out of time). To do this click Tools –> Scripts –> Script editor… and you'll see the variables at the top. The first time you run the script a security dialog will popup. You will need to 'grant access' for it to work.
Instructions
Once you do this here are some instructions for use (in general yellow fields are for user input):
In the 'Events' sheet enter title, description, dates etc. You can enter as many events as you like.
To make an event public enter the text 'Add' in the action column, then click Booking System –> Process events. This will push it to calendar and site and create a unique sheet for the event.
To allow bookings click on Form –> Edit form and add the event to the drop down using the format 'ID#{theEventNumberUsedOnTheSheet} – {theNameOfYourEvent}' – !this format including whitespaces is really important
A limitation of the Google Apps Script is it doesn't yet handle onFormSubmit actions (Google are looking to add this), so for now to process bookings you need to click Booking System –> Process Bookings. This sends an email to your admin to notify them that there is a booking
To approve a booking enter 'Y' in the Action column and again click Booking System –> Process Bookings. (You can approve as many booking as you like in one go). The script will then send a confirmation to the delegate and copy their details to the appropriate event sheet.
When you are ready to send joining instructions you can edit the message in the EmailTemplates sheet (you can also edit the format of the other emails used). When you are ready to send go to the correct Event sheet then click Booking System –> Email joining instructions
Limitations/waiting for Google to fix
Deleting events from calendar and sites is still a manual process (I don't recall anywhere in the API which allows you to do this). You will also see in the code I've commented out a section which would add all delegates to Google Contacts.
dataSheet.getRange(row.rowNumber, 1, 1, 1).setValue("Added by "+Session.getUser().getUserLoginId()); //set the fact that we have updated the calendars for this event
066
067
Browser.msgBox(row.title + " has been published to the calendar and website. IMPORTANT: Add this event to the Form to allow delegates to book in.");
068
}
069
}
070
}
071
072
functiononFormSubmit() {
073
// This function has been designed for when App Scripts automatically runs when a Form is submitted.
Комментариев нет:
Отправить комментарий