Adjust all assignment and quiz dates on a single page

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) 

172 Comments
James
Community Champion

harris60​,​

I've tested it since the original post and it does support formulas. Here are some notes.

It seems the timezone may be copied over with the spreadsheet. If you are not in Central Time (America/Chicago), then go to File > Spreadsheet settings and change your timezone.

Dates are stored as floating point numbers, where the integer portion is the day and the decimal part is the time.

  • A minute is 1/60th of 1/24th of the day, so a minute is represented by 1/24/60 or 1/1440.
  • Midnight can be found by using the =FLOOR() function, which drops the time (decimal) part, just leaving the date (integer) portion.
  • Days are integers, so to add a day and keep the same time, just add 1. To add a week, add 7.

That allows you to do things like this (the 10 in all of the examples is row 10, change it to match whichever assignment you want to change). My notation is this: E10=B10+1 means go to cell E10 and then type the formula B10+1

To have the show correct answers start at the beginning of the next day, I can put the formula E10=FLOOR(B10+1).

If you want the correct answers to be hidden 1 week after the due date (keeping the same due time), then put F10=B10+7.

Let's say you want the due date to be 1 minute before you show the correct answers (I would use this to put a whole number in for show answers for the start of the day and then back up one minute for the end of the previous day), use B10=E10-1/24/60 or B10=E10-1/1440

Let's say you want the due date to be at 5:00 pm on the third day after it becomes available at 7:00 am (Example: Opens Monday at 7:00 am, Due Wednesday at 5:00 pm). Use B10=C10+2+10/24 (that's 2 days and 10 hours later)

One note about show correct answers (I think I made this in the original post). If you don't set Show Correct Answers inside Canvas, it doesn't matter what you put in the spreadsheet. I think I could modify the code to automatically set the show correct answers if someone puts a value in there, but I was trying to get something functional out there quickly so I just went with the basics. The documentation was said show_correct_answers is only valid if hide_results=null and show_correct_answers_at is only valid if show_correct_answers=true. Then you've got show answers after last attempt, etc., and it was just spiraling in complexity.

smithr2
Community Participant

James, this worked for me! Thank you so much!!

James
Community Champion

 @smithr2 ​,

I'm glad it worked for you. Hearing how someone actually used it and it accomplished what they needed makes producing it worthwhile.

I used a variant of that alternative product that you mentioned for a summer course this year and thankfully never had to go through and change the dates I had setup at the beginning. It was the first time using it so I wasn't copying from one course to another. I have used systems that have due dates based on times relative to the start or end of classes, which is nice in some ways because I don't have to worry about accidentally scheduling a due date on Tuesday when the class meets Monday, Wednesday, and Friday. My take away from this summer was that I wish Canvas would do more so that I never had to use that system again.

What I have really grown to appreciate about Canvas, though, is the presence of a published API. It's not perfect and there are things I wish it did that just aren't there, but it allows people like me (or people who know what they're doing) to write add-ons and provide functionality not originally there. Not every feature request can be met that way and Canvas would definitely come up with something slicker than I what I did on this one and make sure it plays well with all devices. My spreadsheet is not well-designed because quizzes should probably be separated from assignments and then you could provide a lot more functionality for them like the ability to control individual settings, but the request was to have assignments and quizzes listed on the same page.

However, I have not even been able to find a mention of an API (let alone published and reasonably well-documented API like Canvas) for either of the other two systems I use and that means I am forced to do everything through their clunky web-interface. I've got some programming skills and I've got a spreadsheet with all of my assignments and their due dates, but then I have to manually type all those into the other systems when creating spreadsheets. What a terrible duplication of effort and waste of time. With Canvas, I was able to write a script that would go through and automatically create the assignments, including the text on the page for the assignment, and set the the proper dates without messing with the web-interface. This was before I created the Google Spreadsheet we're talking about here.

The API and the power it unleashes is just one of the things that make Canvas awesome. The feature requests are a way to let Canvas know about things that would make their user's lives easier. The fact that Canvas is listening to us, even if they don't always do what we think they should, is another reason.

harris60
Community Contributor
Author

James,

I can't use this yet, because I'm waiting for a token. I suspect I will have to wait until after the weekend. Anyway, while I was waiting I was looking at your code and one additional idea occurred to me.

Part of your code reads:

   if (!matched) {

    throw 'You are trying to replace a quiz/assignment that is not in the system. Look for Canvas Id: '

      + canvasId

      + ' in row '

      + (i + 1)

      + ' of the spreadsheet.';

   }

It would be cool if you could create an assignment if it appeared to be a new (non-matching) assignment. This would let you quickly create a shell for a course by adding in 10 assignments at once. I recognize that this could become a big programming issue if you were to code for full assignment creation with description, submission type, etcetera. Keeping it simple, it would be useful to just be able to layout ten assignments and their dates and then to go into Canvas to flesh out details for each.

I know how busy you are. If you don't have time to look at this, then I may take a look at it week after next. Unfortunately, I'm not a programmer, just a fumble-fingered hacker, but I may try to work from your code to do something.

Michael

James
Community Champion

You shouldn't have to wait on a token. It's pretty instantaneous -- unless there's something I'm not aware of. Anyone in Canvas, even students, can get one.

Log in, click on Settings at the top, scroll down and click on the blue New Access Token. Put in something like Canvas API for the description and click generate new token. Then copy/paste the token somewhere. You could put it directly into the spreadsheet, but you may want to save it somewhere else in case if I make updates to the sheet and it needs the token again.

As for the other request - you're on the right track, but in the wrong place.

The code you listed is to make sure that someone doesn't just put an ID in there or modify an existing one. Canvas works based off those ids and it can't do update the information if you can't find the information. So that is an error that hopefully no one will ever see because they didn't screw around with that column.

That said, I've already got plans under way to enhance the spreadsheet to accommodate two other requests that are in the community:   and

My thought is to add a "Copies?" column to the existing spreadsheet. If it's 0, then it doesn't do anything. If it's a positive integer, then it will make that many copies of the assignment. If it's a negative integer (or maybe just -1), then it will delete the assignment. Both of those would adjust the rows in the table.

It works well since you can rename the assignments directly from the existing spreadsheet (before enhancements), so it's a logical place to make copies and change their names all from one screen. It seems to be a logical fit.

The code to do it isn't actually that difficult. In theory, you make a GET call fetch the existing object, change one or two items, and then make a POST call to create the new one. I just need to find a suitable naming scheme -- do you keep all the copies the same name and be confused? do you just add the word "copy" to the end and let people rename them as they adjust the dates? do you add a sequential number at the end (a problem if they other assignments already exist)?

I spent several hours last night making the first screen more useful (the one where you put in the API settings) by pausing to let people enter the information, only to realize that it really works better if you use the method I'm using and make sure it's configured before making the other calls (rather than trying to do it on the fly).

So, although I'm hopefully within a few hours of having that capability, I can't guarantee any timeline. Classes start on Monday and I really need to get those ready rather than play with things for the Community. I know, it sucks being the responsible adult sometimes.

harris60
Community Contributor
Author

I think its something from my university. When I click on get access

token, I have to fill out a form. On the form it says that this token is

for my university's instance of Canvas.

I'm guessing they've implemented an approval process as a security

feature to lockdown canvas???

James
Community Champion

That would qualify as something else going on that I wasn't aware of.

Good luck on the token, it's a shame to not have access to your own information, but some people could do serious damage to their courses with it.

ajohnson
Community Contributor

One way to implement this is to have a course start date, and you can update the course dates by changing the start date. (This is how Blackboard does it. There's a special tool you go to change the start date and update all the dates based on that date. It is AWESOME. It then displays all the updated dates and you can change them selectively there, such as for holidays.)

harris60
Community Contributor
Author

Well, I have had the answer from my university for now. They have locked down access to tokens because they don't want someone doing something locally that might expose private user data.

The bottom line is that I can't use the wonderful spreadsheet that  @James ​ developed. So, first I must say thank you to James and I'm happy he has helped so many of you. From my point of view I hope he has speeded up the development process by offering a prototype of the functionality.

Finally, I am asking each of you to vote and have your friends vote so I can see this feature delivered as part of the system. [grin].

Thx

Michael

James
Community Champion

Bummer. Sorry to hear that. Maybe you could get them to go in and modify all your dates for you and then they might see how useful it would be.

Do they allow you to have LTI integrations? I don't think it's reached that level in Canvas yet, but when LTI2 comes out, it sounded like you would be able to do a lot more without having to get a token. A lot of the stuff I was trying to get done in Google Docs has been around trying to make it as seemless as possible for the user. That's difficult with the getting the API token, but if LTI2 allowed access, you could make it look like it was a part of Canvas itself.

Anyway, thank you for writing the feature request that helped so many others. I'm just sorry you didn't get to benefit from it yourself.