SharePoint Calendar Printing?

Question

I've been researching for hours and haven't come across a really elegant solution for printing a SharePoint Calendar (SP2013) that has multiple overlays in different colors.

The current options are:

Option 1. Print from the web page itself and then use Javascript/CSS to make the calendar as big as possible on the screen and then resize all of the elements to show full Calendar Title Text (multiple items per day)

Option 2. Export to Outlook, use Calendar Printing Assistant.

Option 3. Hope to find some software that can interface with SharePoint that my company will let us buy a license for

Option 1 could be nice because the user would be able to just print from the page and not have to download legacy software like Outlook Calendar Printing Assistant. However, I've played for several hours with Javascript and CSS and I cannot get the Calendar to wrap text and display the full calendar titles and even if I do get it looking somewhat okay, when I go to print it, the actual calendar items don't display in the right box. They start to overlap over multiple days. This was kind of a dead end option for me.

Option 2 actually is really close to working. It's easy to sync to Outlook, see all the items from the calendar. The Print preview looks good. The only problem is on our SharePoint Calendar, we have multiple overlays (which are basically just Views from the calendar itself) to color code things based on a particular field value. Those overlays and their color codings are lost when imported to Outlook and every item from the Calendar on the Month view looks exactly the same.

I thought maybe of creating a bunch of workflows that basically creates copies of all the items from the list into separate calendars based on that specific color-coded field and then import all those Calendars as overlays into Outlook because it DOES let you overlay multiple SharePoint calendars in Outlook and lets you choose different colors for each calendar. But, that would be pretty laborious making sure all items created in one calendar creates a copy in the other and then Title/Date changes always stay in sync, and then also syncing Deletions of items with a workflow.

Option 3 I've only done a little searching and have yet to find some software that would really work. Was hoping maybe someone here would have recommendations?

I've thought about telling the team that wants these calendars printed to just go all digital and throw the calendar up on the big screen during meetings. But the funny thing is, even the SharePoint calendar monthly view is a compressed mess and doesn't let you see the full titles of events especially when you have multiple items in one day. They currently just use a Calendar document in Excel and manually type in the items each month and print that out. Takes them a few hours every week because items are constantly moving around on the calendar and changing.

Any other options you've pursued? Would love some advice here. Thanks!

Answer 1

Like you, I've sought a solution to this for a while - I ended up at a combination of your Options 2 and 3 as discussed below. I end up with a pretty sustainable process that I can build into automated almost turn-key.

You could start with Option 3 using a direct export from SharePoint to Excel (view your calendar, there should be three tabs "Browse", "Events", and "Calendar", the "Calendar" tab allows you to "Connect and Export" to Excel or Access - since your Option 3 is an Excel approach, we went here). The team wouldn't have to retype anything, and some simple Excel manipulations would allow color and formatting the list.

But, you can also use that Excel file to execute Option 2. Outlook can import CSV. With a tiny bit of trial and error you can look at the exported file and use formulas like CONCATENATE(MONTH(A2), "/", DAY(A2), "/", YEAR(A2)) to break down the date/time in the start and end columns of the exported file into date and time entries that Outlook understands. Do this 4 time to create appropriate columns named "Start Date", "Start Time" (format HH:MM using a concatenate of HOUR and MINUTE of A2), "End Date", and "End Time". Now rename the 'Title" column as "Subject", and rename the original imported date and time fields to something that Outlook doesn't already use (e.g. "Bad1" and "Bad2"). your file now has columns Outlook likes and some others it will not understand and thus ignore. Save this as both ".csv" and ".xlsx" (because the ".csv" format will lose your formula and you are going to want to come back and play in a bit). Note that if you take the time to get the column names to match what Outlook wants, the import goes easier. Be sure the ".csv" file is not open when you proceed to the next step, but you might want to be able to flip back to the ".xlsx" file and update your formulas if you see things you don't like as you import.

A word of caution - it is hard to remove groups of Calendar items, so create a new calendar, maybe "SharePointCalendarItems". This can be your play space for now, and eventually you may want to make this separate calendar visible so Outlook users can overlay it.

Go to Outlook "File" -> "Open & Export" -> "Import/Export" -> "Import from another Program or File" -> "Comma Separated Values" and select your ".csv" file using the "Browse" capability. Import this to your play space calendar on the next screen. Now you can "Map Custom Fields": if you named the columns correctly you should see a default mapping that works well. "OK" -> "Finish" will get you back to Outlook and you should see the event you tried to input. (Remember to select your play calendar)

If all that went well, you can go back to your ".xlsx" file and play around with some of the other mapping opportunities. Go through the Import process and when at "Map Custom Fields" you will see the Outlook field names, pretty obvious. If you don't like that all day events from SharePoint turn into 12:00 AM to 12:00 PM events and shade the columns, you can do some Excel scripting to detect that and compute a column "All day event" that will make the imported events use the Outlook all-day convention of not shading. Text parsing would also let you set Categories. Now you can VBA automate the processing of the ".csv" or even the Export and Import in SharePoint and Excel - beyond me to explain here.

A final note: the above doesn't deal with the dynamic change issue you mention, but an easy solution for this approach is to either delete and recreate the play calendar each time you want to print, or use the "List" view of the calendar to select and delete all entries before you import the new (updated) one each . You can also simplify that problem by filtering your calendar inputs as you go from ".xlsx" to ".csv" by only taking events for the current week or some other appropriate interval - keeps you from having to clean up so many entries on the play calendar that you don't really want to print because they are in the past or far future.