We hope you find your planner intuitive and easy to use, but to get you started here is a basic overview as well as a look at some of the features we've added to version 6.7 to enhance your experience. Because each planner is uniquely customized, yours might look slightly different (and some features might not be available in older versions), but the underlying principles will be the same.
Jump to: Getting started, Next steps, Beyond planning, and Tips and tricks
Here we see the main view of the hymn section of the planner. Each row represents one service, and each column an element of that service. Starting at the left we have date, time (if you have multiple services on the same date, otherwise leave blank), and service, represented by an abbreviation corresponding with a lectionary designation. (If you have a gold or platinum "Service Details" sheet, that sheet will control the data in this column.) The general format of these abbreviations is: lectionary cycle year (A, B, or C) plus season or observance (e.g. Epi for Epiphany, or PCost for Pentecost). Links to your preferred lectionary website (changeable in "Preferences") will also appear here if applicable: simply hover over or click the cell and a small box with the link will appear.
Next are columns for each hymn, followed by the colored info/redundancy check cells. Let's look at these in more detail.
To enter a hymn, simply start typing the number or a word in the title. A dropdown will appear, at which point use the up/down arrows on the keyboard to select the hymn you want and hit tab (to move to the cell to the right) or return (to move to the cell below). Every hymn you enter here will be automatically synced with your output/Service Details sheet, so your colleagues will see your choices without any extra action required on your part!
Any word that appears in the title can be used to look up the hymn. It doesn't have to be the number or the first word of the title: typing a word will show every hymn whose title contains that word. Do note, however, that due to the nature of some of the underlying code in Google Sheets, the default straight apostrophe character that appears when you type " ' " is replaced with a curly apostrophe to ensure it is always interpreted correctly – this is what most word processing programs will do anyway, but it does mean that, for example, if you type "Lord's" (including the " 's ") to look up "Hail to the Lord's Anointed," it will not appear.
The colored cells show information about the corresponding hymn's usage. The color of each cell will be a range from green to yellow to red depending on how recently the hymn (or another hymn that uses the same tune) is used, or blue if it is a custom hymn or otherwise excluded. Here the cell for hymn #7 is displaying orange because this hymn was used only two months prior (in the separate planning sheet for 2019), and selecting that cell shows the date the hymn was used in the upper-left corner (underlined here in red) The cell for #117 is displaying red because it was used two weeks prior, while #667 shows as green because it has not been recorded in the planner. The time frame in which hymns will be flagged in this way can be set in "Preferences," but the default is 25 weeks. Finally, the cell to the right displays blue because the hymn was not found in the database, and is therefore not tracked. (Adding custom hymns will be explained later).
A link to more information in the database about each hymn can be found by hovering over or clicking on the associated colored cell. Following this link will take you to the Hymnal data sheet to view info such as the total number of times it has been used, the associated tune name, and a place for notes to be added.
Finally, an entire service can be excluded from tallies and redundancy checks by checking the "Exclude?" box. This is helpful for services which are outside of the regular sequence, such as weddings or funerals, which don't need to be catalogued in the same way.
Scrolling to the right, we see columns for the rest of the music in the service. Your planner will, of course, be customized for your needs, but this example planner has the following columns: the first three are simply the same three frozen columns from before: date, time, and service. Next up we have a column which automatically displays the key of the first hymn: many users find this helpful in case they want to coordinate adjacent music to be in the same or similar keys, e.g. the prelude and the opening hymn. This feature is currently only available for certain hymnals, so if your hymnal isn't supported yet let us know and we'll see what we can do! (Key columns in gold/platinum only)
Next up we have columns for the prelude, offertory, a column for entering the reference or source for the offertory (in this case, the catalogue number in the library), another key column for the closing hymn, and finally the postlude. You'll notice that only the prelude, offertory, and postlude columns have arrows (↗) next to them: this indicates that they are "linked" to your output/Service Details sheet which your collaborators view. The source column and key columns are, in this case, mostly helpful for internal planning usage, and so they are not synced with the linked sheet. (Please note that the arrow was placed there manually during your customization to indicate which columns are linked, and adding or removing arrows does not have an effect on the functionality of the sheets – if you ever need to change the number or type of linked columns, let us know!)
Unlike the hymn columns, this area contains only cells for free input of text rather than auto-fills and dropdowns – however, if you have a separate database (say, of your choral library) which you would like to incorporate into the planner so you can select from that database in the same way as you select hymns from your hymnal(s), let us know and we'll work with you on it!
Finally, we have columns for service music, followed by reference columns that show the tunes of each hymn. Just like the hymn columns, service music columns can be auto-filled with the service music in your hymnal(s) by typing the number or a word in the title. These are not tallied in the same manner as hymns, but later we'll see how to view when they were used. (Service music columns in gold/platinum only)
Lastly, to the far right are columns that are automatically filled with the tune name (if applicable) associated with each hymn entered in the hymn section. While the planner keeps track of tunes in the background to alert you of possible redundancies, we know that it can sometimes be helpful to double-check which tune a hymn is using, especially if the same text appears in your hymnal(s) with different tunes!
So far we've just been looking at the main planner sheet, where you will likely spend most of your time. This sheet represents the calendar year you're working in, but the rest of the planner's functions are spread out among the other sheets, which can be found at the bottom of the window (see above). In addition to sheets for other years as applicable, the archive is where you can enter data from prior years (as many years as you want!) so all your data can be in the same place, and hymn usage and tallies can include dates before you started using the planner. The archive looks just like the main sheet, but with blank dates that you can add yourself. If you need assistance with data entry or migration, we can help!
Use the Search sheet to find information about a hymn, tune, or any other music you track. This sheet is all about the two yellow cells: use the top one to search for any hymn or tune in your database, and the bottom one for any other piece of music (e.g. voluntaries and service music) that you've entered into the planner.
For hymns, entering a title will show you the last date it was used, a list of all dates it was used, the key (if applicable), whether it is marked as service music/excluded from being tallied, and a link to its entry in the database. You can also enter a tune name rather than a title for information on all the hymns in your hymnal (or across all your hymnals) that use that tune. For the other pieces of music, the date or dates in which they were entered will be shown. This feature is new in v6.7, and was a response to the haphazard nature of using the built-in "find" command in Google Sheets, which by default is restricted to only the sheet you're currently viewing, and if expanded to include all sheets will bring up many unhelpful matches from hidden sheets/columns that are best left behind the scenes. Using this feature, you can easily see if and when a given piece of music was used without jumping through a bunch of different sheets.
The query sheet allows you to quickly look up a specific calendar date or lectionary designation. Have you ever wondered "what did I do last time these lectionary readings came around?" The query sheet can tell you all that and more! Just like the Search sheet, you'll want to focus on the cells highlighted in yellow: here, the query type is set to "Lection," so in the two yellow boxes at the top you can select a lectionary abbreviation and (if applicable) whether you want to view all years of the three-year cycle or just year A, B, or C.* In the above example, all years of the third Sunday after Epiphany are entered into the query, which displays in the bottom portion of the sheet. This allows us to see that the same prelude was chosen for both instances of the Third Sunday after Epiphany in year A – what are the odds? In addition to the query output, links to your preferred lectionary webpage are shown under the yellow boxes up top. (more on those links later)
* N.B. one set of exceptions are the three different selections for Christmas Day, labelled XmasI, XmasII, and XmasIII: to view just one select the one you want, or to view all of them at once select "All years."
Here, the query type has been set to "Date" – this switches which cells are highlighted in yellow to the top cell, where every date logged in the planner appears and can be selected for searching. All the info for that date will then appear in the same manner.
Some additional notes: keep in mind when using the search and query sheets that all information flow is one-way: these sheets display information logged in the planner or the database, but cannot be used to edit that information. Also, sometimes with large data sets the query can be a bit sluggish or appear to only show partial information: as far as we have been abl to ascertain, this is due to circumstances beyond our control regarding the way that Google Sheets runs queries, so we included the yellow check box to toggle the query off and on: we found this can help it come to its senses and display everything.
The Hymnal data sheet is the main reference database for your planner. Information about each hymn in your hymnal(s) will be loaded into this sheet as part of your personalized customization and used to populate the various lists in your planner. At the top of the sheet is an overview of your usage of the hymnal, including how many hymns you've used, how many are heretofore unused, and information about the ten hymns you've used most frequently. If you have multiple hymnals loaded into your planner, each one will have its own section like the one above.
This friendly little warning isn't meant to scare you, but it is important. As you scroll past the hymnal usage reports, before you get to the database portion, you will come across this message. As it says, changes in the subsequent portion of the Hymnal data sheet could have rippling effects throughout your planner, so – with a couple of exceptions which we will go over next – changes should be made only with extreme caution.
Scrolling down (or clicking a link in the planner) to a hymn's entry in the database shows you its full information; here you can add notes or mark it to be excluded. The notes can be whatever is helpful for you, and any hymn you've added notes to will have a blue highlight in the planner (shown below: this can be toggled on and off in the Preferences sheet, which we'll discuss in a moment). Checking the "Service music (exclude)?" box excludes this hymn from tallies and redundancy checks, but it will still show up in the dropdown list for your hymns. This will also cause it to show up in the Service music list, for cases where you might use a hymn in place of a piece of service music.
Towards the bottom of the Hymnal data sheet is an area for entering custom hymns. Although one-off custom hymns may be entered into the planner at any time, if there are hymns outside your hymnal resources that you frequently use you may find it useful to keep track of them in the same way your other hymns are tracked. All you have to do is enter a title – the tune and key are optional, the "last date" and "# of times" cells are filled in automatically, and the exclude and notes columns can be used just like other hymns. Remember way back at the top when we added "Custom Hymn" to the example planner? Doing so caused it to show up in the list at the bottom of the large title cell – any custom hymns you've already entered in your planner will show up in this way. If we then add "Custom Hymn" below...
...it will disappear from the cell above and officially be a part of your tracked hymns! (Notice it already shows the one instance of it in the planner.) In the next section we'll discuss an easy way to access this part of the sheet without scrolling all the way down. Additionally, there is a separate area where custom service music can be added in the same manner.
Lastly, we'll take a look at the Preferences sheet. Although your planner comes customized to your specifications in a variety of ways, we've found that there are some settings that we're able to automate so you can easily change some of the behavior of the planner yourself via this sheet.
First notice the links to the right: these link you to the places in the Hymnal data sheet where you can add custom hymns and service music, as mentioned above.
The first two preferences are related to the behavior of the redundancy check: the first number is the approximate number of weeks after which a repeated hymn will be colored green: in other words, a larger number means hymns used further in the past will be flagged as repetitions (i.e. less lenient) and a smaller number means hymns closer to the date will be marked (more lenient). The second parameter sets how quickly the color scale will show yellow and red: "aggressive" means yellow, orange, and red will appear sooner even for dates farther away, "relaxed" means less orange and red, and "default" is a more even scale.
The next preference selects which online lectionary resource your automatically-generated links in the planner will link to. The two resources currently supported are the Vanderbilt Lectionary Page (RCL) and thelectionarypage.net (The Episcopal Church RCL). If you have a different resource you'd like us to add, let us know!
The next preferences set the formatting of hymn titles and tunes as they will appear on the output/Service Details sheet. (N.B. this will not change their appearance in the planner; only as they appear on the linked will change)
The next preferences set some color formatting options as well as advanced options for linked sheets. The first two affect the automatic shading of dates in the planner to show dates in the past as gray and the current week's date(s) as green. The next one toggles whether or not to highlight hymns with anything in the "notes" field blue, and the following three have to do with the automatic coloring of the lectionary abbreviations in the planner to the appropriate liturgical colors.
The last two have to do with how linked columns send their data to the output/Service Details sheet. By default, any cell in a linked column that begins with "*" or "TBA" will not be sent to the linked sheet – this allows you to note about possible future ideas or other notes to yourself without having them link to the other sheet that your collaborators can view. Both of these can be turned off, or text other than "TBA" can be substituted.
Whew! That's it for the main functions of your planner! It's worth noting here that there are other hidden sheets, namely "Formulas" and "Template (do not delete)," which contain vital functions to the operations of the planner, but are hidden by default. The benefit to this is that you have full administrator access to the "source code," as it were, and if you like you can figure out how everything works and make changes yourself! However, this should be done with extreme caution, as even slight changes can have drastic negative effects on the function of the planner. As always if you have any questions, contact us!
Let's check our calendars. In the gold or platinum tiers of customization in version 6.7, the Service Details sheet acts as the master calendar, meaning if a service is added to the S/D sheet that needs information pulled from the planner a row would need to be added for that service. Since there is currently no way to automate this process (again, for reasons we won't go into, but if you want the full explanation let us know – we love to geek out about these things when prompted!), the planner will now show a list of any dates that appear in the S/D sheet that may need to be added to the planner. Rows for additional services are easily added at any time by right- or control-clicking a row next to where you want to add one and select "Insert a row above" or "...below." As long as a valid date is entered in the leftmost column, the planner will recognize it and track it accordingly. If the date is a duplicate (without different times in the Time column) or out of order, it will be highlighted in dark red.
Copying and pasting without disturbing the formatting. Early versions of the planner were not set up to easily allow copying and pasting for very arcane reasons which we won't bore you with here: luckily, we have developed a way to avoid the main issue, and now copying and pasting cells is easy and won't cause strange side effects. Simply select the cell you want to copy, hit Ctrl-C (Windows) or ⌘-C (Mac) and then select the cell (or shift-click to select multiple cells) and hit Ctrl-V (Windows) or ⌘-V (Mac) as you would in other programs. Alternatively, we recommend a slightly different process to paste without losing the cell formatting (in the example above, the thick line that denotes the end of the month) by using Ctrl-Shift-V or ⌘-Shift-V and all the formatting will be preserved, like so!
Oh no, now the redundancy check is yelling at me for using this hymn multiple weeks in a row, but we decided we were going to do this for the whole season! Luckily, there is a way around this: the Gradual checkbox. By default, this affects the second hymn in your planner, but we can easily modify it to affect whichever hymn or hymns you like – in this case, checking it will release that hymn from the watchful eye of the redundancy check, allowing you to use the same hymn (or perhaps single verses of the same hymn) for multiple weeks in a row without having the red jump out at you. See below for the result of checking the column in this example:
Much better!
Or, the redundancy check can be turned off altogether. This check box will temporarily turn off the redundancy check, in case you want to only refer to it occasionally. You can also hide any columns or rows you don't need to refer to at the moment.
Hiding rows and columns, you say? Yes! Simply click the header (the numbers along the left side, or the letters along the top) to select a whole row or column, shift-click to select multiple rows or columns if desired, and then right-click (or control-click) to bring up a contextual menu and select "hide rows" or "hide columns." One particularly useful use case for this is hiding rows of services that represent past services, so the upcoming services are closer to the top. These hidden areas can be reshown by clicking on the small arrows facing away from each other in the header, shown below.
Some things should stay hidden. The red arrow on the left shows the two arrows that can be clicked to unhide the rows we just hid. The red arrow on the right shows arrows which will unhide columns which are hidden by default: these columns are "helper columns" which compile what you've entered into your planner into a structure which can be interpreted by the formulas in the backend...this is a long way of saying they're hidden because they're important and not intuitive or pleasing to look at, and should probably stay hidden in all but the most drastic circumstances.
Heads up! Certain areas of the planner have been "protected" (Google's word, not ours) so that when they are edited this warning comes up. The only areas that have this protection are areas that, as the warning says, shouldn't be changed accidentally: areas that are filled in automatically by formulas or that are part of the inner workings of the planner. Although you may want or need to make changes to these areas occasionally, the majority of the time you'll be editing areas that aren't protected, so you might only see this warning occasionally when accidentally editing a cell you didn't mean to – we encourage you to heed it! Or, if you are popping under the hood and don't want to see the warning all the time, you can check the box and hide the warning for five minutes at a time.
And if anything untoward happens, you can always undo with Ctrl-Z or ⌘-Z!
Shake things up. New in v6.7, the column layout is much more flexible and it's possible to shift things around. Above are two of many possible layouts: the first has the redundancy check columns right next to the hymns they refer to, and in the second one the columns are in service order rather than grouped by type. If a different layout ends up being better for your work style than the default layout, it's easy to try something new!