Adjust all assignment and quiz dates on a single page

[harris60]

The Problem:

Managing dates one item at a time is tedious when you are planning an entire semester. This is true for new classes, but also for existing classes since the ability to globally adjust imported dates never works for the way my institution designs new semesters.

I would like a spreadsheet format that shows all date related events on one page. I could then set up all events and synchronize them where necessary. This could be an online form, or the ability to download a csv template and reupload the dates.

I have attached a simplified snapshot of this kind of spreadsheet.

This idea has been developed and deployed to Canvas

Please read through the Ready Release Notes (2020-04-18) 

[James]

 @tls7 ​,

There is a working solution on this page for some people using Google Sheets. The comments are so voluminous that they're sometimes easy to miss.

It starts with https://community.canvaslms.com/ideas/2214#comment-16943 but there are several posts about it as we worked through things. It might be easiest to just use your browser's Ctrl-F and look for "James Jones"

I used it last week to adjust for spring break week, which was different this term than last year.

This post specifically talks about adjusting times, although it assumes that you're basing the availability and other times off the due date: https://community.canvaslms.com/ideas/2214#comment-17461

If you need to change the times on a date without changing the date itself, then you need to skip over to the unused portion of the spreadsheet.

Let's say this is what it looks like after you load the dates:

What I want to do is change the times on "CH1 Q1" in row 3 (the quiz not the assignment in row 4) to be 3:40 pm.

I would go over to a blank column (J is where they start) and in cell J3, I would type: =FLOOR(B3)+(15+40/60)/24

The 15 is 3 pm on a 24 hour clock, the 40/60 is 40 minutes out of 60, so 15+40/60 is 3:40 pm. The /24 is to because the spreadsheets store dates as integers and times as the decimal portion of the number. The FLOOR(B3) grabs just the date portion and then the rest adds the 3:40 pm to it.

It returns the value 42391.65278

If you don't trust that value, you can to Format > Number > Date Time and it will display as (in my US locale) "1/22/2016 15:40:00"

Anyway, copy those formulas around as needed on the right side (column J or later). Do not mess with the original values in columns B-F

Finally, when you have everything looking like you want it, it's time to replace the existing values with the new ones.

Highlight the cells from the right (in this case J3) and choose Edit > Copy (or I just press Ctrl-C)

Then go to the cell you want to replace (in this case B3). Choose Edit > Paste Special > Paste values only. This is also available with a right click of the mouse.

Here's what it looks like now (I hid columns C-I)

Because you pasted special as values, it kept the date format that it started with.

Although I've only shown changing one number, you could do the entire course at one time.

When you're done fixing the dates, then you go back to the Canvas API menu and choose Save Due Dates.

It's not polished, but if you know a little about spreadsheets, it can be a huge timesaver over going in and modifying each date separately.

[kona]

Hey  @James ​! In your copious free time it would be AMAZING if you could create a document for this work around! I'd love to direct faculty to this information and it would be much easier to direct them to a document with everything on it versus a few different posts in a decent size feature idea discussion.

[tls7]

Thank you so much. I will give it a try!

Tiffany MacQuarrie

English Instructor

Penn State Beaver

[awilliams]

James Jones wrote:

It might be easiest to just use your browser's Ctrl-F and look for "James Jones"

This quote could be applied to so many things!

[kona]

Wish I could have a recording (with transcript) of everything he's ever said... I'm sure at some point he's solved just about all of life's mysteries...

[James]

No document and no new video yet, but I did spend all night going through and updating the spreadsheet. There are some changes coming down the line where Google is deprecating features in June and this one should continue to work past then.

The main difference is the removal of the setup page -- everything is now menu driven. The other major change is that quizzes that are also assignments are not duplicated, they only show up as quizzes.

The URL is the same, so it's possible that Google will remember your settings even if you make a new copy. If you run into trouble, then you may want to choose the Forget API Settings from the menu.

[Stef_retired]

How does date adjustment during the course import process not meet these needs?

One issue that immediately comes to mind is how difficult it is to adjust for breaks, as we have some kind of anomaly in our schedule every semester. See:   Our semester lengths also vary: sometimes they're 14 weeks long, sometimes 16 (or 15 1/2), or 15. The date adjuster is simultaneously nifty and overly basic.

Are there other options required in this tool: mute, publish/unpublish, copy, delete, rename, differentiated assignment, attempts limits, grading settings, etc.?

The original poster of this idea provided a screenshot of a spreadsheet that captures the use case beautifully.

[lbouthillier]

Looks, from the description, like an amazing tool!  I've gone to the Google URL, and what's there now is a blank spreadsheet. If the sheet is still available, please let me know!

[smithr2]

If you follow the instructions in the original post, you’ll find how to make it load with your data! You might need to get a token, which is also described in a post on the page!

[James]

 @lbouthillier ​,

 @smithr2 ​ is correct, it comes blank until you put in your information and run it. The post she's talking about is the one mixed in with all the other stuff on the page, but it's now kind of identifiable because it has a big red "January 9, 2016 Update" at the top. Here's the direct link.

Several people are after me to write a document about it, but finding time to do it right has been the challenge.