Adjust All Assignment Dates on One Page

[James]

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

1. Open up the Google Spreadsheet: Course Due Dates
2. 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.
3. 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.

4. 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.

   1. At the "Authorization Required" prompt, click Continue.
   2. At the "Choose an account" prompt, select your Google account.
   3. 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.
   4. Click the Go to Course Due Dates (unsafe) link.
   5. 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.

5. After you configure that, go to the Canvas menu and choose "Specify Course".
6. Then choose Load Due Dates from the Canvas menu.
7. Watch the magic occur on a new sheet called "Dates"
8. Change the dates and times to their desired values. See below for additional information.
9. After you're happy, choose "Save Due Dates" from the Canvas API menu.
10. 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.

1. 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.
2. 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.
3. 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.

1. Click on Tools > Script Editor
2. On the left side there is a list of modules. Click on DueDates.gs
3. The editor has a toolbar with a dropdown called "Select function". Choose the  function from there.
4. 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.

[ahenschel]

Now that "Pages" in Canvas have student "to-do" dates, is there anyway to update dates and times for the "to-do" dates?

[James]

 @ahenschel , it really needs to be it's own thing or part of a page management spreadsheet like Bulk Publish / Delete Pages. That script doesn't look for changes, which not an trivial thing to implement, but it would be easier to do in that script than here since it would only be one type of content rather than three. If you were adding code to alter the To Do date, then you could also look for changes in the names of the pages and use it to change names of pages as well.

Those are actually some useful suggestions, but they won't make it into this version of Adjust All Due Dates and I've got too much going on at my full-time job to do much Canvas development right now. I've mentally sketched out a replacement that would have separate worksheets for the other types and let people handle files, pages, quiz settings, and other things from a Google Sheet. But the need for something quick and simple, which this one was supposed to be, doesn't fit when you start throwing in all those other things. There are lots of things that can be schedule or restricted, but I don't want the page to get so long that it becomes less helpful.

[nschutz]

Thanks to James, the code for this Google sheet is well written and not hard to follow. We have added many additions to it to customize to our needs. I add a new tab to it every time we need to write something similar, shown below.

[ahenschel]

I have not written code before, but I am willing to try!  Thank you for your feedback!

[James]

 @ahenschel , I would recommend finding someone who knows how to program. It's not a matter of just changing a line or two of code, the call to the Pages API is a different one than other types of assignments. There's a definite learning curve and I wouldn't make this your first coding experience.

[ahenschel]

Yes, the cost-benefit ratio is quite high for me on this one.  I appreciate your insight.

[rachel_page]

Thank you so much.  What a fabulous time saver!  Much appreciated.

[James]

Incorrect Date and Time Handling

Several people have experienced incorrect date and time handling in all versions of the spreadsheet through version 2c. Thanks to a user who did some testing for me, I have determined the cause and solution.

Problem

Certain users who were not in the Central Time zone (America/Chicago) were experiencing problems. It was a very narrow bug and only affected people who set the time something was due to a specific time, so most people didn't experience it and it was hard for me to track down.

Here are some instances where it would happen.

• Someone in Mountain time (America/Denver) would make an assignment due at 11:00 pm.
• Someone in Pacific time (America/Los Angeles) would make an assignment due at 10:00 pm.

Solution

It turns out that there are two places where the time zone is specified in Google Docs. When they don't match, the problem can occur. When they do match, it works correctly.

After you have made your own copy of the spreadsheet, do the following.

1. Go to File and choose Spreadsheet Settings. Set your time zone.
2. Go to Tools and choose Script Editor. A new tab will open with a bunch of code. Ignore the code, but go to File and choose Project Properties. Set the time zone. You can close the Script Editor tab after updating the time zone.

You do not need to download a new version as no code has changed. This is just a setting in Google Sheets. You may still want to update if you're using anything prior to version 2c.

I hope to update the code at some point, but my initial attempt is a terribly inefficient way and I want to optimize that before making the changes public.

Technical (optional)

When the dates are downloaded from Canvas, they are delivered in Coordinated Universal Time (UTC). This is a time standard, not a time zone, and does not change for daylight saving time and all other time zones are expressed as an offset from UTC.

I live in Illinois in the Central time zone. When daylight saving time is in effect, we are UTC-5 hours and when it is not in effect, we are UTC-6 hours. The project settings in the script editor was set to Central Time.

The Spreadsheet Settings option is used to determine how to convert the UTC that Canvas keeps time in to the local time so it is displayed properly. If your offset is UTC-5, then I take something like "2019-04-14T04:00:00" and convert it to "4/14/19 11:00 pm" for the user so that it shows up as being due at 11 pm rather than 4 am the next day.

The problem comes when reading the date and time and converting it back to UTC so I can send it to Canvas. This takes the date and time and converts it from the timezone specified in the spreadsheet settings to the timezone specified in the project settings. This is the step that I was missing. I thought it was giving me the date and time in the timezone specified by the spreadsheet settings.

Let's say that you're in Mountain Time and your offset right now is UTC-6; the spreadsheet settings are Mountain Time, but you haven't changed the Project Settings and it's still in Central Time.

1. You type 4/14/19 11:00 pm (April 14, 2019 at 11:00 pm) into the spreadsheet cell for 11:00 pm Mountain Time
2. When you go to save that date to Canvas, the script gets it in Central Time, which is 12:00 am on April 15th.
3. The script determines that the due date is at 12:00 am (midnight) at the start of the day, April 15th and thinks you have given a date without specifying a time. For the due date and available until date, it adds 23:59:59 to it to make it due at the end of the day. It is now due at April 15, 2019, at 11:59:59 pm.
4. When I go to format the due date to make it be in UTC, I use the Utilities.formatDate() function, but it uses the time zone specified in the spreadsheet, not the project (code). It takes the date 2019-04-15 23:59:59 but treats it as it was Mountain Time as 2019-04-15 22:59:59 (April 15, 2019 at 10:59:59 pm). It then converts that to UTC and Canvas accepts it and shows the due date as the next day, but one minute less than what was specified.

This happens whenever the difference between your spreadsheet timezone and project timezone is equal to the difference between your timezone and midnight.

If your spreadsheet time zone was 1 hour ahead of Central Time (or whatever the project settings were) and you set it to 1:00 am then it would have a similar problem. Most of the people affected would likely be in the Mountain timezone (setting their due time to 11 pm) or Pacific timezone (setting their due time to 10 pm)

[Shar]

Phew! Thanks  @James ‌!

The last time I used the script was September 5, 2018 back before Daylight savings time. But I've now updated the timezone in the Script Editor > Project Properties so that when I do use this script again I'll be A-ok! (My Timezone in the spreadsheet was already correctly set.)

Very much appreciate you and those who helped to track down the problem!
Cheers - Shar

[matthew_cleaver]

Your input looks like it should be a course, but Canvas does not return any information about it.

 @don_bryn , I received the same error and realized that the Access Token I had originally created years ago expired. I created a new access token and everything is working correctly. Did you ever solve your problem?

[richard_kleer]

I just finished using the program for a course I will be teaching this fall. I had put in days of work getting the course ready in Canvas. Then, literally a few minutes before I thought I was done, I realized that I needed to insert a special lecture on the third day of classes and that this would mean pushing back the due dates for all subsequent assignments. I had dozens of assignments and for each was going to have to adjust the usual three items: due, available by and available until. This was going to be a nightmare and my heart sank. Then I found your program and in less than half an hour, I had everything all fixed up to be just the way I wanted it. Your program, because it sorts the assignments in order of date, even helped me spot a mistake I had made on one of the assignments. So I'm tremendously grateful and want to congratulate you for putting such a slick product together!

[kraft_42]

Thank you SO much--you just saved me hours of work!

[shadwin]

@James Jones  I had pretty good success with the v2c version last year.   For some reason this year, importing my quiz assignments from last year to this year course, and then load due dates, adjust due dates, then saving the revised due dates. it will show the new dates on the assignment page.  I can see that assignment one will have a subscript stating the correct dates available and due and the point value.  If I click on the assignment to edit it, such as assign a section to the date, there is no date shown in the edit screen.

[James]

 @shadwin ,

That is very strange. I just tried it in Chrome on a PC and I was unable to duplicate what you're seeing (ie, it worked for me, both for a regular assignment and a quiz), which makes it hard to fix. Since it's correct in one spot, but missing in another, it almost sounds like an issue with Canvas or getting the information to you.

This also means that what I'm throwing out here is a general troubleshooting approach rather than anything specific to the script.

Have you tried clearing forcing a reload in your browser, clearing your browser's cache, installing any OS updates and rebooting (if you're using Windows), trying a different browser?

If you enter an assignment date manually on the quiz page, does it keep it?

If you open the developer tools in the browser (F12 for Chrome and Firefox) and go to the console tab, are there any warnings or errors that are occurring when you reload the page. The only thing I'm getting are XHR and Fetch messages, which are normal. If you look at the Network tab, are there any files that failed to load, possibly because of blocked third-party cookies?

Has your school done anything with custom JavaScript or CSS that is keeping it from displaying?

Do you self-host or are you hosted by Instructure? When I tried to lookup sotc.edu (based off your email), my ISP interrupted with a search page saying it didn't know who that was (I hate it when they do that). Google seems to think it's sotech.edu and it looks like sotc.instructure.edu is your Canvas site (making it hosted by Instructure).

Specific things to target:

What your default language and locale are set to? Canvas has hosed some things recently as they try to expand support for internationalization. Right now creating formula questions is broken due to changes Canvas made so they can support "83,29" instead for forcing people to use a dot for a decimal point and typing "83.29."

Are you using the new gradebook and have a default late and/or missing policy set? That's another thing that has recently changed and I'm testing it on a course with the new gradebook but no policies set.

These last two are stretches on my part. I haven't seen any evidence of dates being broken anywhere else, but since it works for me, I'm trying to figure out what's different about yours.

[cwilshus]

This is a great tool. I would like to share with our faculty, however our campus does not allow faculty to configure API tokens, only admins. Do you have a suggestion on how to share with them? 

[James]

 @cwilshus ,

I would talk to the powers that made that decision, plead your case, and seek an exception.

That would be a better route than trying to circumvent their security policies and get an API token. If all your admins are doing is hiding the link to generate a new token, then I could (I'm not going to) give code that you could paste into your browser's developer tools console that would generate a token for you. It is a much better approach to get their blessing rather than going around them. The admins have a report that lists user access tokens and can see who is using access tokens. They may come back at you if you bypass their efforts to keep you from getting an access token.

As a side note, there are lot of user access tokens -- our institution has 15,085 of them. Of those, only 84 were user generated, which is the kind that we're talking about here. Of those 84, only 12 have a "last used" date.

Developer Key Name	Count
Canvas for Android	4690
Inst-FS (Production)	3589
Canvas for iOS	2046
Google Drive LTI IAD-Prod	1612
Rollcall	1514
Office365 Prod Iad	954
Respondus	264
Commons	116
User-Generated	84
NotSofia	53
Canvas Teacher for IOS	46
Pearson	29
Cengage Learning	26
Canvas Teacher for Android	19
SpeedGrader	18
Analytics 2 IAD Prod	8
scone-prod	6
Respondus Quiz Builder	2
Canvabadges	2
Analytics 2	2
OneNote Class Notebook Add-in	1
Chuck Stangor (UMD)	1
Papersocrer	1
Canvas Polls	1
Quiz LTI (moderation) IAD	1
Grand Total	15085

Back to your issue ...

An admin cannot generate a token and give to them or put it into the document and then share it with them. The first is terribly stupid (it would give the user the rights of an admin) and the second is prevented by Google's security where the information is scoped to the user and not copied when people share scripts.

I have not used it or researched it, but UCF wrote a Due Date Changer and released it on GitHub. That would require that your institution install the LTI on a server and it doesn't have the power of a spreadsheet to do calculations, but it does allow you to change the dates all on a single page. As an LTI, I imagine it uses oAuth to get the tokens rather than requiring people to generate them manually.

[lherdan]

Thanks James. My department frequently uses your spreadsheet. Is it possible to add a columns for the point value and setting "Do not count this assignment towards the final grade"? We support a large school in a university. Many instructors use existing course but do not want some assignments to count toward the grade. Currently you have to edit each assignment individually.

[rmcdonald]

Howdy!  I'm starting from scratch, as Canvas is new for our school.  When I use this incredible document, the dates are all blank (because we have none input in the classrooms yet) and it is up to me to input the dates for ALL assignments for ALL classrooms for our school.  I wondered if there was a formula that would assist me in filling in/spacing the dates?  For example, if we put in the start and end dates for the term, is there a way to assist me in spacing out the assignments/quizzes?  Thank you much!

[ProfessorBeyrer]

Yes,  @rmcdonald ‌ this is an awesome resource. Earlier today I shared it with a colleague and he too was impressed with this resource.

I have encountered your challenge too. I take your question to mean that you know when a given class begins (say August 26) and want to space out the assignments on a regular frequency (like weekly).

Within your copy of James's awesome Google Sheet you can use formulas to refer to the cells and whole numbers to add days. So if quiz 1 (in cell A2 of the sheet) has a due date of Wednesday, Aug. 28 at 11:59pm (2019-08-28 23:59 in cell B2 of the sheet) and you want quiz 2 (in cell A3) to have a due date a week later, you can write the following in cell B3:

=B2+7

In this example quiz 2 would be due on Wednesday, Sept. 4 at 11:59pm (2019-09-04 23:59). If cells A2 through A15 are quiz 1 through quiz 14, you could copy that formula from cell A3 to A15 to make those quizzes due weekly after that.

The same formula copy can be used for each of the columns (available from, available until, etc.).

[James]

It is possible to add those You will need to check and make sure it's not a quiz since those don't support the two options you mentioned. I already do a check to see if it's a quiz because the show answers options are only available for quizzes. Muted is an option that was only available for assignments, so I would look at what I'm doing there and duplicate it.

You can get to the source code by (inside Google Sheets) going to Tools > Script Editor > dueDates.gs. At that point, there are some configuration options at the top and you would need to figure out what values controlled those two settings and add in the code later on. Again, follow the muted option for an example of what to do.

[rmcdonald]

For some reason, when I open a classroom in the document, it re-sorts all the assignment titles into alphabetical order, rather than in the order they actually occur.  Is there a workaround here?  It makes it super hard to use the doc, since I have to enter one assignment date at a time, and then search for the next occuring assignment.  For example, assignment one may be on line 38, assignment 2 on line 12, assignment 3 on line 61.  I have SO much work to do.  Help!  

[James]

I'm not positive that the order that the assignments and quizzes come from Canvas have any particular meaning. Canvas often does not sort things when they deliver it in the API calls, they sort it after the fact within the browser. That means that leaving it in the original order may not make any sense, either.

If you go to Tools > Script Editor and find the dueDates.gs file on the left. Around line 384, you should find code that mentions sort. It's the only time "sort" appears in the code, but it looks like this:

    dsheet.getRange(2, 1, rowCount, colCount).sort([ {
      'column' : 1 + hdrs.due.c2,
      'ascending' : true
    }, {
      'column' : 1 + hdrs.title.c2,
      'ascending' : true
    } ]);
‍‍‍‍‍‍‍

You can comment that out by putting a double forward slash // in front of each line, then it won't sort.

I do know a place that gets the assignments in the order that they appear on the assignment page and am thinking about incorporating that into a future version. It's also a single call for both quizzes and assignments rather than multiple calls for each. As a bonus, it doesn't use pagination and you can exclude the descriptions so there is less information to load.

With the muted option going bye-bye in January, I may take that time to rewrite it with the new function and change the muted to the points possible that  @lherdan  asked about. I've been toying around a complete rewrite, but I'm not finding much time to code for pleasure right now.

[grether]

James, I have used this several semesters in a row and this is a wonderful tool. That you so much.

In my brief searches, I have not found a way to similarly adjust multiple assignment point values. Could this approach be used to do that? If so, what could I modify to make that happen.

Thanks again.

Marc

[grether]

Actually, it looks like I may have figured this out. I edited the dueDates.gs on about line 57 with:

  'title' : 'Points',
        'field' : 'points_possible',
        'type' : 'number'
      }, {

and then saved the edit. This added a Points column to the Google Sheets. Though this did not pull in the info from Canvas with the current number of points when I then Loaded Due dates, when I changed the Google Sheet and then "Saved due dates" the change did go back to Canvas without breaking anything I've found yet.

You may have mentioned this earlier or on another thread, but the API information on assignments, including "title", "field", etc. information can be found at:

Assignments - Canvas LMS REST API Documentation 

My two cents.

I'll add that I'm sure I'm missing something that would allow the Load Due Dates to bring in the information, but at least in one instance, I got this to work.

[James]

 @grether ,

Congrats! A couple of thoughts that might improve the process. The second one is probably more important than the first.

1. Add 'location' : 'Assignment' to the header since it's not appropriate for quizzes.
2. Around line 204, add 'points_possible' to the list of items obtained from the assignmentList. What's given contains a filter and if it's not in the filter, then it's removed. That could explain why it wasn't showing up when you load the information.

[mstimers1]

Works at one school but not another (two different Canvas instances).

I have been using this at my home school for a year, works flawlessly there. I tried it last term at a second school where I teach and it worked fine. Now, at that second school, it does not. Google thinks the app is not verified, so it makes me go through the "are you sure you want to proceed" before I can access the CANVAS menu in the sheet. Once in, I load the API settings, submit, then go to "Specify Course" and all it does is shoot me back into the "Configure API Settings" window. I have tried deleting the sheet and adding a new one, no luck.

[James]

 @mitchel_stimers ,

The behavior your describing is what happens when it is unable to validate the API Token. It cannot get past that part since it cannot look up any information through the API. It should verify your identity when you put in the API token -- if not, it didn't like it.

Have you verified the API token is still valid and for the second school? In Canvas for the second school, go to Account > Settings and make sure it's still listed and valide.

Each school would have its own access token. It may be possible when you deleted and reinstalled it that you used a token from the first school.

[mstimers1]

Hi James,

It worked eventually. I deleted the old token and added a new one twice, no luck. Then I noticed I was logged into another Canvas instance (different school) in another FF tab. I logged out of that school, re-configured the API, and it worked fine. Is there something in AS:

var ui = SpreadsheetApp.getUi();
    var profile = canvasAPI('GET /api/v1/users/self/profile');
    if (typeof profile === 'undefined') {

That has trouble connecting using the token if more than one instance of Canvas is open? Just a thought.

Thanks!!!

[James]

That may be the issue. I don't have a good way of testing that right now since I only have one account to play with. My wife has multiple accounts and she uses this, but I don't know if she's logged in to more than one at the same time.

[haug]

This is amazing! Thanks a lot for sharing this  @James  Thank You. But I'm wondering whether it would be possible to also include the to-do dates that you can add to any page (Ceck box "Add to students' to-do list" or something like that). It would be great if those dates could also be synced to the google doc!

[James]

 @haug  

You're welcome. The add to student's to-do-list has been brought up before, but it's easy to miss in the 8 pages of discussion. The same thing applies for announcements and files.

Adding pages could be done, but I don't plan on adding it to this version of the spreadsheet. Someone who knew how to code could add it, but it's not a trivial change since pages are handled differently than assignments, which are already handled differently than quizzes. Adding pages to the worksheet for every course would make it really crowded for the majority of people for the benefit of a few. There's also an overhead with the API calls and the limit of how many you can make per day and how much time you can spend with a script running (those are Google limits, not Canvas limits). Adding too much to the one page may run into some of those issues. Basically, this version will provide it's existing functionality, although I need to update it to remove the mute column since that's being deprecated soon.

I do have some plans for a different spreadsheet where I split up the types of items into different sheets in the workbook. That would also allow for some extra functionality that isn't common to assignments, discussions, and quizzes. It loses the ability see everything on one page, though, so I think this spreadsheet will continue to exist and the other would be an alternative. A second possibility is to have a dialog box where you check which items you would like to include. The first one would avoid the Google script limitations while the second one would provide additional functionality at the risk of running up against the limits.

Creating a new workbook that made some of those changes and hopefully including differentiated assignments for sections (which is the most popular request) were some of my great plans for the summer. I even took the summer off teaching for the first time since I've been teaching and then life happened and I didn't get any programming done this summer.

[thomasbennett]

Good evening Mr. Jones, 

Thank you so much for this! This just saved me about an hours worth of work. Enjoying looking at the code too.

Question, is it possible to add the ability to change Assignment Groups? I am wondering from a programming stand point; I have been programming a script with AutoIT to create modules, assignments, etc.. and finding your solution is so much cleaner than what I have at this point. 

Thank you again for this and saving all of us a lot of time, 

Thomas E. Bennett

[James]

thomasbennett,

The code is contained inside the Google Sheet itself (Tools > Script Editor). It is possible to modify it to change the assignment group, but it's not trivial and not in keeping with what this was originally designed to do. Given my load right now and how I want to keep the script minimal, I don't think I'll implement it in the current iteration of the script. I have a vision for a complete rewrite, but still no time to get it done.

I'll give some hints as to how to tackle it.

When updating an assignment through the API, it needs an assignment_group_id, which is an integer. That means that you somehow need to download a list of the possible assignment group IDs, save their names and IDs, and then verify that someone has entered a correct assignment group before resubmitting the information to Canvas. That makes an extra call to the API when you load the data and when you save it. The program might be changed to reduce the number of API calls. Right now, I'm using the get a list of courses and get a list of quizzes calls, but it might be possible to combine that into getting a list of assignment groups. I didn't use that when I wrote the script many years ago, but it's what Canvas uses for the assignments page and I started using it for other things a year ago.

The need to convert between human friendly names like "Classroom Activities" and the Canvas ID means either saving it to a separate sheet in the spreadsheet or re-fetching it when it comes time to send the information back to Canvas. Saving it to a separate sheet may lead people to think it can be changed, so I avoid that solution with other things. There is some storage for a sheet and it might be enough for the assignment groups, but then you run the potential of stale information (changed in Canvas between runs of the script).

You can enforce the entry of a proper assignment group by using the requireValueInList method of the DataValidationBuilder class. This will make it easier for people to type in a legit name without having to worry about typos.

Part of the reason you have to save the numbers is that each run of the script within Google doesn't remember values set in a previous run. That is, if you save the data in a local variable when you fetch it, it won't remember that when you go to save it.

I use autohotkey instead of autoit for some things. Automating something like this in autohotkey would be nightmarish. I would resort to the browser's console where I can manipulate the DOM. There is an internal (non-API) call that allows you to reorder an entire assignment group with a single call. Using the API, it updates the assignments individually, but that call doesn't allow you to specify the order within the assignment group. That is, if you modify the code to my script to handle assignment groups, it will (I think) put everything that gets changed at the end of the assignment group. Then the people who care would have to go through and manually specify their order.

[Shar]

Marc and James,

My first foray into editing a Google Script was a success! Thanks for the tips guys! Works like a charm!

Cheers - Shar

Edited: My comment didn't show up inline with the original comments.

Here's what I gathered from the comments:

in dueDates.gs around line 57

  'title' : 'Points',

        'field' : 'points_possible',

        'type' : 'number'

      }, {

Add 'location' : 'Assignment' to the headerInfo

Around line 204, add 'points_possible'

More specifically:

I added in the points into the headerInfo right before the due date starting at line 12.

   'title' : 'Points',
   'field' : 'points_possible',
   'type' : 'number',
   'location' : 'Assignment'
}, {    
  'title' : 'Due',
  'field' : 'due_at',
  'type' : 'date',
  'daysEnd' : true,‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

I also added 'points_possible' to the quizList starting at line 193.

    var quizList = canvasAPI('GET /api/v1/courses/:course_id/quizzes', {
      ':course_id' : courseId
    }, [ 'id', 'title', 'points_possible', 'due_at', 'unlock_at', 'lock_at', 'show_correct_answers_at', 'hide_correct_answers_at', 'published', 'assignment_id' ]);
    var assignmentList = canvasAPI('GET /api/v1/courses/:course_id/assignments', {
      ':course_id' : courseId
    }, [ 'id', 'name', 'points_possible', 'due_at', 'unlock_at', 'lock_at', 'published', 'muted' ]);
‍‍‍‍‍‍‍‍‍‍‍‍‍

And my sheet ends up with:

[scottdennis]

\0/

[mmoore1]

We are not worthy!  We are not worthy!  Thank you!  I must unlearn what I have learned. 

[lindsay_carr]

Hi, thank you so much for creating this amazing spreadsheet! My courses have 7 cohorts which all have unique due dates. I am wondering if I could add columns to my spreadsheet to allow for assigning multiple due dates? Any chance this might work?

[James]

 @lindsay_carr  

That will not work with the current version of the spreadsheet as it doesn't support differentiated assignments.

I have started thinking about how to make it work with differentiated assignments and have began rewriting my development version towards that goal. I do not have an estimated time on when I can get back to working on it.

The differentiated assignments is not trivial and it won't be as simple as a single "for" column. There are 3 columns needed -- one to identify the type (section, group, or student), one for the IDs in that group, and one for the name to use. That last one might be omitted, but the first two are necessary because someone might name a group the same thing as a section and IDs could be either for groups or for sections. Those kinds of things can be done inside the Canvas web interface since they can do autolookup and people can tell which one they're selecting.

[fcurry1]

I managed to get it to work.  If anybody else has problems with unvalidated dates showing up in improper fields, delete those items.  this seemed to fix it.

Query: What would be needed to alter this file so that it can change the dates for announcements and modules?   Every semester I have at least one announcement for each of 16 modules I need to set up as well as the dates for which each module opens.

Can this script be easily modified to produce versions that allow us to change those items?

Thanks,


FC

[James]

 @fcurry1  

It would take additional API calls for discussions (announcements are treated as discussions) and modules. I've started working on a rewrite, but have no estimate of when it could be done and I don't really see having any time to work on it for a while.

I won't be adding it to this script because it's going to start getting slow if you start adding in pages, announcements, files, modules, differentiated assignments and any other dates that might be out there. I may release a new version that removes the mute option for the January 18, 2020, release, but that's about the only change that will happen there.

As for the invalid dates, that's something that I noticed Canvas has started to enforce recently. It used to be that you could send a date outside the course through the API, just not in the web interface itself, but now you cannot send them through the API either.

[jlondon]

Sorry if this is off topic and a little bit of a long-shot, but a question:

Have you made google sheets/scripts that would allow somebody to change the unpublished/published status of multiple Canvas files in one go on our sites? I had hoped the page manager sheet might do this, but it doesn't look like it does. 

A Canvas feature was apparently turned on by accident that rendered all of the files in my course unpublished (this has affected other faculty at my institution as well). It would be great if I could just bring up a list of all my files in a google sheet and change the published/unpublished status to the relevant files all in one go, rather than having to do it one at a time manually.

Your date changing sheet and discussion counts sheet have been lifesavers for me the last few years since I discovered them. Thanks so much for making Canvas better for us!

[James]

 @jlondon 

No, but some other people have taken my contribution and adjusted it to work with files: Adjust All Assignments + Files 

[jlondon]

Thanks! Tried it Adjust All Assignments + Files, but unfortunately I couldn't get it to load my class. Seemed to do the API settings fine with the token, etc. Got the Canvas menu, configured the courses, but when the script ran the spreadsheet was blank. Tried Load from Canvas, still the same problem. Oh well, would have been super cool if it had worked. Your spreadsheets seem to work fine. So not sure what's up. Perhaps my institution has certain things locked down or something. 

[dmansfield1]

This document has been so life changing! I teach students in an ansychronous format, but I really want to be able to give some students their own "due dates" because... procrastination. Is there any way to modify this document so it only applies to a singe student, or a group of students?

[James]

 @dmansfield1  

This question has been asked many times before so I won't repeat the lengthy answers I provided. The short answer is "not easily," but I started work on a different version in December 2019. It will not be compatible with the existing spreadsheet, it will be a new one. There is no ETA on it and I haven't had a chance to work on it since December.

[dmansfield1]

Thanks James, really appreciate the quick response. I wish I had the programming skills to figure it out, but I can be patient until you get a chance to iron it out. I know it is a somewhat unusual use case, but sounds like its not just me! Thanks again

[James]

March 15, 2020 Update: Version 2d is now available.

This version removes the mute column and replaces it with a points column. I imagine there will be a renewed interest in this script due to the coronavirus so I wanted to get an update out there.

The ability to mute an assignment was deprecated in Canvas in January 2020. Having it in the spreadsheet didn't do anything but confuse people and give them false hope that it would actually work.

The points possible is now contained in the points column. It can only be set for assignments, including discussions, but not for classic quizzes. The possible points for classic quizzes are determined by the points on the questions inside the quiz and cannot be set at the assignment level.

I renamed the only sheet in the master copy to be "README" instead of "Dates." This means it will remain after you run the script. The Dates sheet will be created when you load the due dates.

I tweaked the instructions on this page inside Canvas, but did not update any videos.

This is a minor update and people using version 2c can continue to do so, just with reduced functionality. If you upgrade versions, it is essentially starting over with a fresh copy and you will need to repeat all of the instructions.

[ssb]

I am missing something here, but am not sure what. I have copied the spreadsheet and renamed it. I select the Configure API and provide it a token, cannot change the URL address though. I click submit but the form comes back and indicates I must Authorize the account. I have gone into my Google account settings and made sure the spreadsheet was allowed. But I am not able to get past this point.

Scott

[James]

 @ssb  

I'm not sure if you meant authorize with Canvas or authorize with Google, but since you mentioned running the Configure API, I'm going to guess it's the Canvas part.

It won't authorize with Canvas if you do not specify the Canvas Instance. If you cannot get that part to work it will keep returning you to the Configure API screen. I made a quick video to show what it looks like as of today. There's about 20 seconds I cut out while waiting on the Canvas menu to appear. You can paste in the URL from your Canvas instance where I typed mine in.

The Google authorization process has changed and now you have to jump through hurdles to get it to authorize.

You didn't say what browser you're using. If the video doesn't help, you might want to make sure you're using Chrome. I don't know about Safari, but my students have issues with Google Docs and Edge and in the past I've had some with Firefox.

Another possibility is that I told it to use the new V8 JavaScript engine. That worked for me, but it may not for everyone. Google is moving people over since February 2020 from the old JavaScript engine, so I went ahead and told it to just use the new engine.

[ssb]

Feeling a bit sheepish. I was trying to erase the instructure entry instead of just typing my entry in. I kept trying to press the delete key to replace the first part of the entry. Thanks for the video.

Scott