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?
Purpose
Below is the list of things this Spreadsheet will accomplish:
- Change due dates/times
- Change available from and until dates/times
- Change quiz show and hide answer dates/times
- Change the point values for discussions and assignments. The point values cannot be changed for classic quizzes.
- Publish/Unpublish assignments
Instructions
- Open up the Google Spreadsheet: Course Due Dates
- This will open the spreadsheet in view-only mode, you need to make your own copy. Go to File > Make a Copy. Once you have made a copy, close the original and work with your copy instead. If the Make a Copy is disabled, then you need to log into your Google account before you can enable it.
- Check your locale and timezone settings under File > Spreadsheet Settings. There is a second place to set the time zone as well. Click on Tools and choose Script Editor. Once that window opens up, click on File and Choose Project Properties and set the timezone. Once you update it there, you can close the Script Editor window.
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.
- There will be new Menu item called "Canvas" that will appear after 20 seconds or more. It contains the menu. The first thing to do is choose "Configure API Settings". Then put in the hostname of your Canvas instance and an API access token. See How do I manage API access tokens as an admin? for information about how to get your access token. See the "Specify Course"
Note that the first time you use the script, Google will ask you for Authorization.
- At the "Authorization Required" prompt, click Continue.
- At the "Choose an account" prompt, select your Google account.
- You will be presented with a "This app isn't verified" prompt. I am not a Google developer and haven't gone through the necessary hurdles to configure this. Click Advanced to move forward.
- Click the Go to Course Due Dates (unsafe) link.
- After the list of what access the program needs, scroll down and click Allow.
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.
- After you configure that, go to the Canvas menu and choose "Specify Course".
- Then choose Load Due Dates from the Canvas menu.
- Watch the magic occur on a new sheet called "Dates"
- Change the dates and times to their desired values. See below for additional information.
- After you're happy, choose "Save Due Dates" from the Canvas API menu.
- Go into Canvas and hopefully things will be changed.
Notes
- At some point, it will ask you to authorize Google Spreadsheets app to run. Be sure to grant access when it does or you won't get anything. I am not a professional developer so Google will make you jump through extra hoops to get it authorized.
- It gets your timezone from Google, but can be adjusted to handle other time zones. If you need a different timezone or your times are coming out wrong when you save them back to Canvas, then see step 3 in the instructions.
- The Show Answers and Hide Answers columns are only valid for quizzes and you must have the "Show Correct Answers at" item checked in Canvas.
- If things don't happen like you think they should, it's probable than an error of some kind occurred. Go to Tools > Script Editor and press Ctrl-Enter after it loads to look for any error messages or warnings.
For additional help see the Videos and feature information below.
Videos
The following videos provide additional information and tips/tricks on how to get the most from this spreadsheet.
Setup and Configuration
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.
Introduction and Basic Editing
This video provides a general introduction to how to use the spreadsheet and do basic editing.
User Interface
This video shows some of the nuances of using the user interface.
Changing the Dates
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.
Shifting Dates (Spring Break)
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).
Changing the Times
This video shows how to change the times on the dates.
The Canvas menu
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 Course
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.
Load Due Dates
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.
Save Due Dates
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.
Hide Times
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.
Show Times
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.
Due at End of Minute
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.
Configure API Settings
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.
Forget API Settings
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.
Show Help
This loads a help page that has an abbreviated version of the information contained in this document.
Configuring the API
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.
Canvas Hostname
This is the name of your Canvas instance and can be specified in up to three ways.
- If your Canvas instance is hosted by Instructure and does not use a custom domain, then your hostname will be something like schoolname.instructure.com. If this is the case, then you can enter schoolname into the Canvas Hostname field and it will fill in the .instructure.com for you. If you are using a custom domain name, you cannot use the short form and must look at another option.
- If your Canvas instance uses a custom URL like canvas.myschool.edu, then you need to enter canvas.myschool.edu into the Canvas Hostname field. You can supply the full hostname even if you're not using a custom URL, but it is required if you are.
- You may also paste a URL for your Canvas instance into the box. This URL is given in the Location window of your browser. Note that the example below is from the dashboard, but it will recognize URLs from within a course as well. In fact, you could use the same URL here that you're going to use as your course URL when you specify the course.
Access Token
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?
Confirming the Canvas Hostname
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.
The Spreadsheet
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.
Initial View
After Hide Times
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.
Custom Date-Time Formats
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.
Invalid Data
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.
- 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. 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.
CanvasLive Presentation
@kona gave a CanvasLive presentation on this tool on January 13, 2017.
Troubleshooting?
Canvas menu won't appear
Force a browser refresh
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.
Updated versions
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.
Make a new copy
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.
Manually run the script
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.
- Click on Tools > Script Editor
- On the left side there is a list of modules. Click on DueDates.gs
- The editor has a toolbar with a dropdown called "Select function". Choose the function from there.
- Click the Run button (arrow to the right). It won't be clickable until you select a function.
It then prompts you for authorization and you should go through with the authorization process.
