Just wanting to share a sacrament meeting speaker tracker that I think is ready for general use now. Some sample data is provided to show you how it could be used.
To preview and get a feel for how it works:
https://docs.google.com/spreadsheets/d/ ... sp=sharing
If you want to implement it, then use this to copy it to your google drive, and follow the provided instructions to set it up for your ward:
https://docs.google.com/spreadsheets/d/ ... Rci7U/copy
If you do implement it, please feel free to let me know where you are located - I would love to know where it gets used and if it's helpful.
Huge thanks to Robert Swan who provided the hours and hours of work to create the original spreadsheet! I first saw his work from this thread and with his blessing used that as a basis to add the functionality I was looking for, so be sure to thank him for his wonderful spreadsheet! In fact, it was seeing the thought and effort he had put into his work that made me want to use it over all the others in that thread.
Hopefully this is useful for you!
Steven Van Gemert
More info if interested...
Some of the features:
- Tracks sacrament speakers and provides an automatic list of the speakers who have not spoken for a long time.
- Can track speakers, topics, hymns, prayers, as well as the music director, organist and the meeting conductor and meeting type (in our ward we chose not to track prayers, so those columns are turned off by default in the sample spreadsheet)
- Any column can be turned on or off in the Preferences tab if you do not want to track them (so you could just track meeting date and speakers only, like the original spreadsheet had, if you would like)
- Up to 7 hymns and 3 speakers can be captured on a single meeting line. You can always add a second or third line with the same meeting date if you need another 3 speakers (or 7 hymns/choir pieces/musical numbers lol).
- When new batches of hymns come out, simply add them to the Hymns tab, and they will automatically be added to the relevant dropdown lists
- Various meeting types are recognized and automatically gray out columns that are not necessary to provide (no speakers, prayers or hymns for stake and general conference, and no speakers for fast sunday, etc.)
- Highlights youth and primary speakers so you can get a visual feel for how many times you are having them speak
- Each user can select what columns they want to see, and the spreadsheet will reconfigure to only show them the columns they want to see when they open it.
- Multiple roles exist that provide the default list of columns that role would want to see (Bishopric, Exec Sec, Music Chair, Organist, Bulletin Coordinator etc.)
- Provides both a mobile-friendly and printable version of the Last Talk report
- Report to show the most popular speaker and hymns used (not that useful, but kind of fun to see)
- Extensive scripts written to accomplish various of these features
- General user instructions included in the spreadsheet
- Owner instructions included
- Exclusions for out of ward speakers so they do not appear on the reports (it was these exclusions that Robert had in his one that convinced me his was better because these are absolutely necessary to have once you realize why)
- Exclusions for members temporarily not available to speak (such as going on their mission or being away at school)
- Hymn entry can be done by number, or by typing in any portion of the title, and selecting it from the drop down list that auto filters to your search text
- Hymns can be marked as Preferred, and also as hymns that are "in the organ" for those using the automatic feature of their organ for sacrament hymns (one of the wards in our stake does this)
- Multiple date formats are offered, so European wards can easily use it by changing one dropdown in the Preferences tab, and all dates will be automatically formatted using that mask
- The shared access, collaborative nature of the google sheet means that multiple users can make edits at the same time.
Note that as the owner you will have to authorize the project in your google account. Step by step Instructions are given to walk you through doing this.
You can view the apps script code yourself in the Extensions menu -> Apps Script. I purposefully did not include oAuthScopes that would require the user to have to authorize, so, for instance, the script does not request access to the users name or email. We track users with the temporary user key automatically generated by the apps script engine itself so that I do not need to request permissions beyond what was needed to access the spreadsheet itself. The apps script has to send API calls to read and update the spreadsheet, and that requires certain permissions, but no other permissions are requested. Here are the permissions we use:
"oauthScopes": ["https://www.googleapis.com/auth/spreadsheets",
"https://www.googleapis.com/auth/script.container.ui",
"https://www.googleapis.com/auth/script.scriptapp"],
My purpose was to streamline sacrament meeting planning while complying with Church data policies. Only member's names exist in the spreadsheet, and you could also choose to use first initial only (or last initial only).
Full disclosure, I created this while I was a bishopric counselor, and I have since been released and am working on a spreadsheet for my new calling as stake exec sec, so I am not actively adding any more features to this project. However, if you can think of a good idea, I'll be glad to devote a few hours to making it happen for you, if you can convince me it's a good idea.
