Had to add another column (fixing the formula to make sure it places the edit Form URL in the correct column) called: “IF Condition”
We discovered that formMule ran faster than the script to insert the URL. Now that I added the formula [=IF(H2=“No”,IF(E2=“”,“No”,“Yes”),“No”)] and change the trigger in formMule to run hourly, there shouldn’t be any problems (famous last words).
***Update Part Deux*** K.I.S.S. (Keep It Simple Stupid)
We eliminated the event title column in the Google Sheet and modified the multiple choice question on the number of kits needed so the Event-o-matic would not have to rely on the copyDown formula to create a title. We found that it initially would create an event with ‘no title’ until the one hour re-sync would replace the text.
***Update*** It’s only been a couple of days and we already have made adjustments.
Huge thanks to @Gallagher_Tech for being game when I again ask… what if we… Rarely is it easy, but he manages to find solutions!
— Kim Lowden (@ms_itech) February 24, 2017
The embedded items will be updated to reflect the various changes.
- Yes/No question added to Form with the answer going to a specific section. This way if a teacher says, “I don’t know which breakout I want to do” they can still reserve a Breakout kit(s) and edit the Form submission later with lock combinations.
- With the question above, another section is created to describe what happens next. The intent will be that either an email is sent with the link to edit their Form responses or an email will be sent sharing the Doc created by autoCrat. With this in place, response receipts can be turned off.
- Another column is created for the unique Form URL to go back in and edit. This was setup through a script discovered here.
- Google Doc template is altered to include unique Form URL in case of changes. Update autoCrat to recognize the new tag as a hyperlink. [One drawback, the Doc will not update to reflect any new changes submitted]
- Trigger is set in autoCrat for creating & sharing the Doc to the submitter. It is activated by text appearing in the 4-digit lock (NOT NULL).
- formMule add-on is setup to send email template with the link to re-submit the information when they know which Breakout they want to do.
One of my colleagues, Kim Lowden, recently received some Breakout Edu kits for her building. She wanted to create a means to manage it so teachers could see how many are available and sign them out for use as well as give them a document of what they receive (locks, box, hasp, etc.) w/ the combinations set. We developed the system below (the components are all a part of a Google Site).
***Note: all items embedded are copies of the original so to not disrupt the functions of the system described in this post***
The Form below is used for teachers to sign-out kits as well as submit the combinations for the various locks and request extra features (key lock, invisible ink pen/flashlight, etc.). This Form is crucial to the rest of the system.
The data collected is dumped into the Google Sheet below. Within the Sheet I have the following add-ons copyDown, autoCrat, & Event-o-matic running for various tasks. copyDown repeats any and all formulas located in row 2 when data is submitted through the Form (since a Form creates a new row of data in the Sheet). One sort of formula is creating the event title for the Calendar, pulling together the number of kits and who they are for into a cell via =E2&” kit(s) for “&B2
autoCrat takes a formatted Google Doc with tags (<<referenced to Form questions>>) associated from the Form data to create a new document so each requester gets a copy to keep track of what they will receive and to what the locks are set. One of the coolest features they offer is the ability to set how the <<tags>> respond. Typically, when set to ‘Standard’, what ever is submitted through the Form will be included in the Doc as text. But autoCrat also offers ‘Hyperlink’ or ‘Image’ which is useful in this case for the extra features. We are able to take a URL to the image of a key lock and have that inserted to the Doc as an image.
Finally, Event-o-matic creates calendar events from each Form submission. Underneath the Sheet, I have embedded the Google Calendar associated with this system as well as an example created document.