Celebrate Excellence in Education: Nominate Outstanding Educators by April 15!
Update March 15, 2020.
Version 2d is now available. The only changes were to remove the mute column (Canvas deprecated that functionality in January 2020) and add a points column to change the points on an assignment (you cannot change point values for classic quizzes).
There has been a lot of discussion about the need to . I created a Google Spreadsheet to attempt to address this feature request, but the announcement and instructions got lost among the almost 100 comments. This document is an attempt to pull the relevant information into one location so it's easier to find.
This document and script were written at a time when Canvas did not have a solution. In the April 2020, they released their own solution. It may be enough for most people and I recommend that you check it out to see if it will meet your needs. How do I bulk update due dates and availability dates as an instructor?
Below is the list of things this Spreadsheet will accomplish:
Update: April 14, 2019. It turns out that there are two places where the time zone needs set. This only affects you if the difference between your timezone and Central Time (America/Chicago) is equal to the difference between when you set the due date and midnight. For example, if you are in Mountain Time (1 hour behind Central Time) and you set your assignment to be due at 11:00 pm (1 hour behind midnight). I will work on getting a fix for an upcoming version, but I want to do extensive testing first.
Note that the first time you use the script, Google will ask you for Authorization.
If the Canvas menu item does not appear, wait a few minutes and try a forced refresh (Shift+Reload) in the browser. Testing suggests intermittent timeout issues with Google services that fix themselves. I also updated the script on August 10, 2018, to help fix some of those issues, so making a new copy may help as well.
Notes
For additional help see the Videos and feature information below.
The following videos provide additional information and tips/tricks on how to get the most from this spreadsheet.
This video shows how to make a copy of the master Google spreadsheet, configure the API settings, and get your list of course due dates.
This video provides a general introduction to how to use the spreadsheet and do basic editing.
This video shows some of the nuances of using the user interface.
This video shows how to change the dates based on other dates. It uses the due date as the available until date and opens up the answers the next day after the assignment is due. It then hides the answers 2 weeks after the answers become visible.
This video shows how to shift dates based on the existing date. It shows adding 1 week to everything after spring break. The idea is to create a temporary column that holds the new date and then copy the temporary value back over the original one (be sure to Paste Special as Values).
This video shows how to change the times on the dates.
There is a Canvas menu that is added to your Google Docs spreadsheet. It contains all of the menu items related to this spreadsheet.
The Canvas menu takes some time to appear, about 20 seconds in my testing. If the Canvas menu item does not appear after more than a minute, it probably won't. There may be a intermittent issue with Google's spreadsheet services. Wait a couple of minutes and try a forced refresh (Shift + Reload) and see if the problem resolves itself. In some cases, you may need to come back later. If you are still running version 2 (v2), I did some tweaks on August 10, 2018, and that version seems a little more stable. You might download a new version (v2a) of the spreadsheet and try it.
Specify courses is used whenever you want to specify or change courses.
It will take either the Canvas course ID or a URL from a page within your course.
After you put in the Course ID, it will display a confirmation screen.
Click Yes if this is the right course or No if it is not. There may be error messages that occur as well. Be sure to read them to figure out what is going wrong.
Once you have specified your course, it will automatically run the Load Due Dates feature for you.
This command is ran when you specify a course for the first time. There may be times when you want to reload the information to either confirm that the changes have been made or to start over. This command will wipe out any existing information on the sheet.
The Load Due Dates also fetches the start and end dates for the course. You cannot change those within the spreadsheet, but if you change them through Canvas and reload the due dates, they will be reflected in the sheet as a validation rule.
This command will save any changes to Canvas. It compares what is in the Google Sheet with what is in Canvas and saves any changes. If you delete columns from the spreadsheet, they are ignored in the comparison and subsequent update.
This command will go through and hide the times for any date/time combinations that correspond to the start or end of the day. For the Due or Available Until columns, any times ending in 11:59 pm (23:59) will show just the date and not the time. For the Available From, Show Answers, and Hide Answers columns, any times ending in 12:00 am (00:00) midnight, it will show just the date and not the time.
If there are times in the middle of the day, then it will not hide them, but continue to show them.
This is the counterpart to the Hide Times command. This will show the times for every date/time combination, including those that are at the start or end of the day.
This formats the date in "yyyy-MM-dd hh:mm" format, such as "2016-08-15 21:34". I thought about formatting it in the locale of the user, but getting the default format for the locale out of Google Sheets wasn't easy and so I decided on an unambiguous format.
In September 2018, Canvas decided to fix a bug that broke documented behavior. The due_at time now adds 59 seconds, essentially giving the students to the end of the minute rather than the beginning of the minute to turn in material. This menu option sets the seconds for all due at and available until times to be 59 seconds. Note that Canvas does not default the available until time to 59 seconds, so you can have an available until date that is up to 59 seconds before the due at date.
This command should be ran first to authorize Google Sheets to talk to Canvas. If you try to specify the course before you configure the API settings, it will run this command to configure the API settings first and then you will need to re-run the specify course command.
Most people will never have a need to use this, but if you want to remove your access token from Google Sheets, then you can use this option to forget your configuration.
This loads a help page that has an abbreviated version of the information contained in this document.
If you have not previously authorized this Google Spreadsheet to execute, then it will ask you to grant the spreadsheet access to do what it is intended to do. Agree or you won't be able to use the spreadsheet.
After that, you will be presented with a Canvas API configuration screen.
This is the name of your Canvas instance and can be specified in up to three ways.
The access token is a string of characters that identifies you to Canvas. It is basically a username and password rolled up into one item and allows anyone who possesses it to do anything that you could do through the Canvas API. That's pretty extensive, so basically never share it with anyone.
However, Google Sheets needs the access token to talk to Canvas and so we need to share it with Google Sheets. It is stored for future use, so that you only have to enter it once (unless it expires). The good news is that it is stored in the User Properties portion of Google Sheets, which means that each user of the spreadsheet has their own storage area and one user can't get access to another user's access token.If you share your spreadsheet with someone, the access token is not shared with it, they will still need their own access token.
For more information about access tokens, including how to obtain them and revoke them, see the Canvas documentation How do I manage API access tokens as an admin?
Once you have entered your Canvas Hostname and Access Token, you'll be given a screen that confirms that it correctly interpreted the information.
First is confirms the host name.
and then the user.
Just click OK to confirm these.
If you don't get those, something went wrong and you should read the error messages to determine what it was. After correcting that, then try the process again.
When you execute the Load Due Dates command, you'll get a spreadsheet that looks like this. My examples only contain quizzes because that's all I had in the class when I did these tutorials, but there will be assignments mixed in with the quizzes. Note that discussions are included as part of assignments.
If most of your times are at the beginning or end of the day, then you may benefit by running the Hide Times command. Notice that most of the Available From times are still showing. This is because they are at 17:30 (5:30 pm) and I didn't want people to think they were the beginning of the day because the time wasn't showing.
You may also choose your own date formats using functions built into Canvas. For example, if you prefer the day of the week and a short date, then you can highlight the cells and then go to Format > Number > More Formats > Custom Number Format.
For example, setting the custom number format to ddd, mmm d will give you the day of the week, the month, and the day of the month.
Notice that this hides the times, but it does have the benefit of giving you the day of the week and stripping off the redundant year.
The codes are based off of the principle that each letter represents a piece of information and the more times you use the letter, the longer the information. For example, m represents the month. A single m gives you the month between 1-12 while mm gives you the month, padded with a 0 in front of single digit months: 01-12. Using mmm gives you the three letter month abbreviation, like Aug. Using mmmm (4 m's) gives you the full name of the month such as August. Providing mmmmm (5 m's) seems counterintuitive, but it provides the first letter of the month.
d is for day, m is for month or minute depending on context, y is for year, h is for hour, s is for second.
If this sounds terribly confusing, it is. Luckily, Google Sheets has a date-time format builder that you can use.
Go to Format > Number > More Formats > More date and time formats. Then click on the pull-down arrow next to the Apply button.
Pick the item you would like from the menu and then repeat this for each piece of information you want to show.
Each of the items has a pull down menu that allows you to select the specific format or delete that item.
For example, if you want the AM/PM to show up as a single lowercase letter, you would first add AM/PM to the custom format, then click on the AM/PM button and choose Shortened lowercase (p).
Here's what it would look like to have a 2-digit hour, 2-digit minute, and a single digit AM/PM identifier.
Some cells may have a small red triangle in the upper right-hand corner.
This is an indicator that something is wrong. In this case, the dates 2016-12-21 and 2016-12-23 are after the class has ended. The spreadsheet gets that information about the course start and end dates from Canvas and you can't change it in the spreadsheet and have it take affect in Canvas. You'll need to go into Canvas Course Settings and change them, or have your Canvas Admin change them if they are locked down, and then reload the due dates.
All dates have that validation rule attached to them, but only the invalid dates show up with the red triangle. If you mouse over the current cell, then it will show you the validation rule.
Also note that these invalid content warnings are advisory in nature. Canvas will allow you to set the dates outside of the course start and end dates through the API. If you are inside the web interface, then the Due, Available From, and Available Until dates are required to be within the course dates.
The Published column has validation rules as well. It requires Boolean values, but typing in True and False is tedious for the user, so instead it uses 1 for Yes and 0 for No.
Information about Setting Dates
Dates are stored as floating point numbers, where the integer portion is the day and the decimal part is the time.
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. 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.
@kona gave a CanvasLive presentation on this tool on January 13, 2017.
There were several people commenting that the Canvas menu would not come up. I made some tweaks (see "Update versions" below) and did some extensive testing and found that it seemed to be intermittent issues with Google not executing. I would make two copies of the file within a minute of each other and one would work and the other one wouldn't. If I waited a bit and forced a browser reload (hold down the shift key while pressing the reload icon), then it would work. I recommend waiting a few minutes and then trying the forced browser reload.
The first thing to try is all the normal stuff we tell people when there are browser issues. Clear the cache, reload the page, etc. I would not suggest switching to Firefox, though. Chrome really works better with Google Sheets.
On August 10, 2018, I updated the code to version 2a. There is no new functionality with this release and if your current spreadsheet is working you do not need to do anything.
As part of the testing to see why there were loading issues, I put the code through a lint checker to look for problems. I found some where I had not declared a variable (making it global) or using a variable out of scope and fixed them, but since it was working before that doesn't seem to be the problem. I also found some people suggesting that having global variables was causing the (), which is what loads the menu, to not execute. I reorganized that part as well so that there are no global variables.
There is now a version v2a out there. That doesn't have any new functionality over version v2 and if your version v2 is working, you shouldn't need to do anything. However, if you're still struggling to get the menu to appear, try making a new copy using the same URL as before and then testing it. I was able to get the menu to appear every time with the new version while there were still some cases with the old v2 version that the menu would not appear no matter how many times I tried it.
Version v2b, released on October 12, 2018, now supports second resolution on all dates and times and sets the due date to end at 59 seconds after to match the behavior of Canvas. It also sets the available until date to end in 59 seconds, which is contrary to Canvas behavior, but it is the behavior that should occur when the due date is set to the end of the minute. Without doing this, the available until can be before the due date, which is normally disallowed. The adjustment was poorly implemented, had unseen ramifications, and is a big hack by Canvas. It's still not right, so look for this to change in the future. If you are using version 2a, there is no need to update unless you want the end of minute behavior.
Version v2c, released on December 25, 2018, changed the handling of the due at and available until dates. Canvas changed their rules to make it end at 59 seconds whenever the minute portion of the time ended in 59 and at the beginning of the minute for other times. This version now matches that behavior. It also removed the onEdit() function that I had added with version v2b that checked input as you enter it. It turned out that had some problems with copy/pasting information as well as slowing things down. It removed the option to adjust the seconds since it is no longer necessary. I also sped up the process by writing all of the information to the spreadsheet at once instead of one row at a time. If you wish to patch your version instead of starting over, the dueDates.gs and utils.gs files changed.
Making a new copy sometimes fixes things as well. This happened with the old version v2, but I had one copy that would not run. I made a copy of that copy -- one known not to work -- and the new copy worked, even though the original one did not work, not even with a refresh. Perplexed? I can't explain it, either.
We did find another work around if you are still having problems. This is kind of techy and it's received much less testing than the other stuff, so I would try downloading the new version first or reloading the page. This does appear, in limited testing, to be a one-time thing. Once the script works and you get the menu, you seem to be good.
It then prompts you for authorization and you should go through with the authorization process.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
I'm James Jones. My full-time job is teaching mathematics at a community college, with an extra-duty assignment that involves Canvas. I hate repetitive tasks and will spend 13 hours writing a computer program to automate something that takes 5 minutes to do. This often benefits others in the form of Canvancements, which are my Canvas Enhancments that I freely contribute to the Canvas Community.
To participate in the Instructure Community, you need to sign up or log in:
Sign In