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.

[mechecketts]

Thank you so much for doing this.  This saved me so much time when I needed to change my course dates for next Semester.  Seriously, thank you!  If you want to force all users to make a copy of the Google Sheet, make the document all users can edit.  When you paste the link, delete the word edit at the end of the link and replace with the word copy.  Once you replace the word with copy, when someone clicks the link it will automatically force them to make a copy and they will not be able to open the document unless they make a copy.  

[James]

I'm glad you found it useful. I just used it myself tonight after making doing a grade import with nothing but the assignment names and point values to create the assignments. Then I went in to this Google Sheet to set all the due dates.

Thanks for sharing the copy trick, I was not aware of that and just took the share URL that Google gave. I'm not sure that I followed what you wrote, but it sounded like you said that all users would need edit capability on the document. If that's the case, then there's no way I would do that since someone could come in and maliciously change something and then it would be distributed to those trusting individuals who don't read the code before they run something.

Even if I misunderstood that part of it, I think I'll leave it as edit because some people may want to look at it first before deciding whether or not to make a copy. If they're not logged into Google when they click the link, then it would keep them from seeing it until they did. Although it might make it easier for some, I think in the long run it would cause more issues than it solves. The most common thing I see is people requesting access to it, which indicates they're not reading the instructions (strangely, professors for one subject matter seem to have more difficulty than any other), and if I made it automatically make a copy, even fewer people would read the instructions. I wish it was easy enough for people to use without needing to read the instructions, but the need to get the access token isn't going to make allow for that.

[rao_3]

I am still struggling to learn to use this properly. To answer some of the earlier questions: OSU is not self-hosting (and access tokens do not seem to be blocked). With one of my courses, it seems to work OK with one of the courses. With another, I got the dates one day. But since then, it does nothing when I try to save/load the dates. It works OK up to and including "specify course". . I have tried restting and putting in the API settings. that gives the success message, and I can properly specify the course again. But that is where it stops. The error log has just two lines:

"Not a correct permission"

"No data returned. Cowardly refuse to do something stupid"

-Nath

P.S. The spreadsheet fetches data fine from ended courses for Autumn 2017. It is just one course that is not working.

[rao_3]

Added note: I am trying to use "Canvas Update Tool" by "appa" (Johanna Hardner) so that I can try to change points, and deal with files as well. Looking through the source code of the script, I think the problem is that the script does not expect to find a folder that is hidden, but the files inside it to have availability dates. Setting the containing folder to have availability restricted by dates seems to fix the problem.

[James]

Please direct all questions about the tool that Appa wrote that are file related to the page for that tool. I don't have anything to do with it other than they started with my code as a base.

[James]

The second error you listed happens when it tries to look up a course and no information is returned. This could happen if there are no assignments in a course or if you don't have access to that course.

Another thing to check besides the log files is the execution transcript (run the program until it stops, then go to the Script editor, click on View, and choose Execution Transcript). It can give an idea of what is or is not happening.

For the course that's not working, are you inside the course in Canvas and then copy/paste the URL into the input field when it asks you to specify the course or are you manually typing it? Copy/pasting the URL is the safest way.

The first error message is not from my code. It may be something that Appa added and since it occurred before the error in my code, it may be something in Appa's tool that is throwing an error that is then causing mine to thrown an error. That is, it may be file related rather than assignment or access related.

You might try running my script and see if you get access there. It won't allow you to modify the dates on files, but if it works, then we know the issue is in the other script.

[rao_3]

I apologize. I should have looked more closely at the problem before posting. (As I get older, I find myself less willing to look through code, especially that of others. :-^)

I just clicked on the link in the sheet marked "Help", which still points here. [and it took a while to connect "Appa" with the 'Hardner', which is the search term that works.]

[shadwin]

I found using the script and spreadsheet very easy and setup some practice courses with it.  I already had a spreadsheet with assignments due that matched our school calendar and was able to cut and paste into the course and was pleased it updated flawlessly.   You  @James ‌ mentioned you purposely ignored differentiated assignments.  Is it even possible to configure to work this?   Example.  I have students start a course in January, each assignment requires 2 days to complete and I have imported all the dates into the course.  I call them Section 1.  In February, I receive two new students and they start Day 1 and same assignment for them would be 30 days later.  In March, I receive 4 more students, and it goes on and on like this.  I would really love to see a spreadsheet that I can manage offline of Canvas and then import the due dates.   I would be glad to beta test..  Thanks for the initial script, hope there is more to come.

[James]

 @shadwin ,

This sounds similar to an open-enrollment situation, just with fixed starting dates. At least you're blocking them into sections already, and hopefully that's a real section in Canvas terms and not just your word to describe it. If they are placed into different sections in Canvas, then you can do the differentiated assignments by section. If that's just what you're calling them, then you'll have to add each student manually to each assignment -- which is a major pain the rear.

You're using courses in a way that they weren't designed to be used. That's not to say that there's not a demand for what you're doing, just that's not the way they were designed. Many of us try to push what can be done or do something differently -- hence the Google Sheet to change the dates.

An alternative approach, since the students don't seem to interact with each other would be to make a course for each month rather than trying to put them all into the same course. That can make a lot of courses depending on what your school does with old ones, but it makes it easier to set the dates and possibly some other housekeeping chores.

The problem with the differentiated assignments and this spreadsheet is that I can't think of a good way to incorporate differentiated assignments. They are handled in a different section than the regular assignment, it's called assignment overrides. You may have none, one, two, or a hundred overrides per assignment. It can be for a section or an individual student or combinations of the two. You can fetch that information, but the one issue is how to display it so that each assignment takes a single row?

The bigger issue, though, is how to assign it to people. You would need to enter some kind of identifier for each entity receiving a different due date. If you go by name, there's no guarantee that names would be unique. If you have two John Smiths, you want to make sure that the right one gets the assignment. You could make base it off of IDs, but that's even worse from a human perspective -- try typing 12567182 instead of John Smith. That means that you would have to have some kind of lookup system, meaning that you would have to fetch all of the names and sections from Canvas. That can be done, but not in real-time as some classes can be large and scripts make synchronous AJAX calls, which means that one call has to finish before another one can begin executing. It would be possible to download this and store it on a page and then offer the option to refresh it on demand. You might even be able to write an interface that would present a list of possible overrides where someone could choose from them and it would populate a list for you.

Then there's still the issue of getting the information about the override into the spreadsheet. If you allow the overrides as columns, then you can have an indefinite number of columns to process. Each one would need to have a human friendly form for the user and a computer friendly form for the computer. If you add extra rows for the overrides, then you need one row for each override, but just two columns. That means duplicating the row for the main assignment and then putting something in the override columns -- the human and computer forms, which you might be able to auto select from a dialog.

As I write this, one possibility might be to have the entire spreadsheet be an override and you save it multiple times based on who is getting the override. If there was no override supplied, it would apply to the entire class. If there was an override supplied, it would apply to just those people or sections and not change the class defaults. You may have to add a bunch of logic that Canvas already has programmed in about when there are conflicting overrides and that complicates things. If you were writing a spreadsheet for just your consumption and you were the only one who would ever use it, you can get by with doing some hackish things. When you write something to share with others, you need to consider that they may not exercise as much caution or even understand the need for caution the way you do.

Having a single sheet per override might actually fit in better with what you want to do, but I still think your situation might be better addressed with different courses. When trying to arrive at the best solution, we sometimes have to set aside our naming scheme and understanding of things to understand how that term is used within the framework of Canvas.

The initial comment about not considering overrides was because I didn't consider overrides -- at all -- as I simply wasn't using them. It's not that I haven't given it thought since then; I've considered them several times now. It's just that I couldn't get it to work well with the everything on a single page concept. If I was to write a different spreadsheet, then it might be worth considering -- assuming all the technical issues could be overcome in a way that wasn't so complicated that only a few could use it. That's not happening anytime soon for me, I've got too many other things going on and haven't been able to contribute as much to the Community since August 2017 as I had before then. But my code is open source if someone wants to take it and modify it and a couple of people have already done that with this script and shared their results back to the Community.

[shadwin]

Yes, we have open enrollment and I use Sections in the true sense they were designed for.  I teach a program that has 10 courses that takes 9 months to complete (normal school year).  The courses are setup to be sequential and the program has its own syllabus.   During the year I teach adults and HS students in same program.  The adults may attend all day, therefore could complete entire program in 4.5 months.  So I  have a Section called AllDay, a Section called AM for my AM HS students, a Section called PM for my PM HS students.   Each Section may have 1-6 students.   Next month, I get the two more new students, I create a new Section called AM II or AllDay II and enroll those students in their respective Section/Course.   Now I have to manually go and add the section to each assignment in each course to reflect the adjusted due dates.   It is a pain and was hoping I could manage it with a spreadsheet instead of opening each assignment and creating maybe 5 due dates for 5 different sections.   Multiply that by 10 courses and it can take some time to modify.  If I could use a handle or column name to adjust only a section at a time. It would be much easier to do that verus how I am doing it now.  I have attached an image of my spreadsheet.  The one section named JM is a student that started in the Spring Semester.  Column A are the 10 courses my students take to complete my program.  Each course is approximately 30 -120 hours in duration.

[raul_pineda]

Hello everyone, is there a way to manage due dates for specific sections for assignments? I have a course that has many sections and would like to change the due dates based on when the students meet with the instructor and not for the whole assignment. Please let me know, thank you!

[James]

 @raul_pineda ,

There is not a way to do that with this software as it stands. Differentiated assignments, which is what you're asking about, are handled differently than regular ones and it would take some major programming changes to make it work. I haven't been able to come up with an adequate solution that doesn't make this script harder to use, so it may need to be a different script.

Somewhere in the pages of comments that people have left about this script, there is a commercial LTI solution that allows you to assign for each section, but it doesn't use a spreadsheet to do it, so I don't believe you're able to use formulas to set dates or times.

[kmartin6]

I'm a bit late to this game, but we are just now in the process of changing to Canvas (from D2L). I've gotten access to the spreadsheet, got the API settings configured, and had no trouble specifying the course and downloading all the existing dates from the course. To see how the process works, I made a couple of changes to dates for some of the assignments (to update to a new semester) and then clicked on Save Due Dates. The Running Scripts note pops up, and then it shows that it has finished. However, when I look at my Canvas course, no changes have popped in. I exited the course and got back in thinking that Canvas might need to "reset" the course, but that didn't do anything either. Do I need to leave Canvas completely, or is there another step I missed? 

Thanks in advance for any suggestions!

Kris

[James]

That sounds like the right steps. The thing I would question is how you are entering your dates and whether or not they are within the time period for the course (from the course settings page). Also make sure that you're checking the right course (if you have multiple tabs open, you might close all but one of them, copy/paste the URL from the browser into the script), run the program, type dates in as numbers not as text or use the pop-up calendar / date picker to get them.

You can check the format on the date by going to another cell and entering a formula. For example, to check the date in cell A2, go somewhere where you don't have anything and enter =A2+1   and if it doesn't come back with a date, you've got the values entered incorrectly.

[jdw5170]

Would it be possible to include bulk importing, editing, and exporting of events too? For my courses, the assignments and quizzes are only half of my calendar dates and it would be great to have everything easily editable in one spot. Thank you for your time!

[James]

 @jdw5170 ,

The script is open source, so you are welcome to modify the script to do that, but I do not anticipate adding that capability at any point. The idea is to keep it simple and events are handled in a completely different way with a different set of parameters (the concept of available_until, available_from, due_at don't directly apply) that make it not work with the layout of the spreadsheet.

Some people have already modified it to handle dates on files and others have asked for differentiated assignments to be included. Those, as well as calendar events, do not fit in with the structure of the page that is already there, so the only way I would feel comfortable adding them would be as a separate sheet with their own layout. To me, that eliminates some of the benefit of having everything on one page, but if I was to include all of the events and files, it would make finding the assignments difficult.

I will probably need to do something, though, as Canvas is making it so content pages can have due dates (August 4, I think), and my guess is that it will be similar to other kinds of assignments, so it may be able to be incorporated into the existing structure. I really haven't had time to look at it yet and other projects are more pressing.

One other thing I would suggest is looking at why you have so many events on your calendar. I've tried adding events to the calendar without much success as it didn't put it on the To Do list. Canvas is changing that with the new Dashboard list view, but I have gone to making ungraded assignments instead of calendar events for things like two dates for a post-first discussion. I spent a lot of time talking to students about how they use Canvas and asking them for ways to make the course better this year and my take-away was that all of the carefully constructed items designed to hack Canvas to make their life easier don't have the desired impact. They don't view it unless there unless it shows up on a To Do list or a module prerequisite so that they can't move on until they've completed things. Hopefully you have a different type of student than I do and that isn't your experience.

[mjennings]

James,

Would there be a way to easily pull in ungraded discussion available from & until dates? Or does that factor into some of the other issues you have mentioned?

[James]

That's new enough I haven't looked into it yet so I don't know. Kona wants pages added. I'm thinking the solution might be to separate a few things that don't fit the norm onto their own sheets. A sheet (tab) for pages, one for files, etc. I would not want to complicate my list of 50 assignments with the 500 files or 200 pages in my course (those are made up counts)

[ProfessorBeyrer]

Thank you  @James  for your work not just in creating this tool but especially on maintaining it and updating the template for the rest of us. It had been a while since I used this tool, it didn’t work, but when I came back here I found version 2a and voila! Your light shines very bright.

[James]

 @mjennings ,

I took a look at this today. Understand, of course, that easily is a relative term.

Currently, I'm not pulling any discussion information from discussions. All of the information for a discussion comes from the associated assignment, which ungraded discussions do not have. That explains why they're not showing up.

To get them to show up, one would need to make an extra call to fetch all discussion topics through the API. I'm not seeing a way to filter by just "ungraded", so the information for the graded discussions would end up being downloaded twice. The fields are different, for a discussion, the unlock_at date becomes delayed_post_at. This means that there would need to be a conversion like I did with some of the quiz information.

Unfortunately, I'm not going to be working on this anytime in the foreseeable future. I've got too many other crises, fires, and requests that have a higher priority this semester. Right now, this is pretty much in maintenance mode.

[mjennings]

Thanks James, I figured it would be more of a stretch. This is a particularly busy time of the year for most of us so I appreciate you taking the time to look into this and getting back with me.

[lindalee]

James, I was about to ask if adding content pages (which can now have dates) was on your radar. But you note above that  @kona ‌wants you to add them, so I trust that a solution will come in time. (Though perhaps not quickly enough to be used for our fall setups this year.)

Thanks again for such an incredibly valuable resource! (And honestly, just one of many that our team uses regularly.)

-Linda

[jfile]

I was a little bit leery of doing this, but frustrated at having to edit every. single. due. date. every. single. semester.

I just followed your instructions & it worked like a charm.  Thank you very, very much.  I wish Canvas would make it this simple.

Again, thank you.

[mjarvi17]

 @James ‌,

A colleague shared your API tool with me a year ago, and it has saved me a LOT of time.  THANK YOU!

FYI: This semester, I have had something odd occurring.  For some of my assignments, the imported dates appear as 12am instead of 11:59pm.  In one of my courses, I was importing several quizzes, and the dates all appeared as 23:59 in my Google Sheet, but some of the quizzes imported the dates as 12am while others imported the date as 11:59pm.  This issue hasn't occurred for me in previous semesters, so I didn't know to watch for it on the individual assignment pages.  I spent some time trying to figure out the issue(s).  Eventually, I figured out that double-clicking in the "offending cells" and re-running the script fixed the problem; all dates then transferred as intended (23:59).

Just thought I would mention that in case the issue is associated with the 8/10/18 update to Version 4 in some way.  I don't see any other commenters mentioning the problem, though, so perhaps others are not experiencing it.  (I shared the tool with a colleague, and she reported the same, but that is only two of us, so I don't know whether it's a widespread issue.)

Again, thank you for the amazing tool!

[James]

I had not heard of this issue before, so thank you for pointing it out. 

There was a "bug fix" to Canvas on 2018-09-15 that is more of a change than just a bug fix. I have not modified the script to accommodate this, but it might have been related to that.

Canvas has decided that now, contrary to what was documented, due dates should not have a minute precision, but should have a second precision, defaulting to 23:59:59 for the end of the day instead of 23:59:00. Then they came up with some kind of wonkiness about if the minute portion ends in 59 then it will be due at hh:59:59, but if the minute ends in anything else, then it will be due at hh:mm:00. There was a comment about they were going to revisit that and requests for clarification have not been answered.

In their description as a bug fix, they said that values have always been set at hh:mm:59 for the due date, but once they were edited, then it became hh:mm:00. They also said this only applied to things that were entered through the web UI and not things entered through the API.

I am aware of the issue, but haven't made adjustments to the script. This script is more difficult to change than others as it does not automatically update for people, they have to download a new version and put in a new access token if they don't have it saved somewhere. Most of the stuff I write except for Google Sheets has an auto-update capability in it to recognize changes. Since I'm not sure where Canvas is going to end up on this, I don't want to release too quickly and have people download a script just to have a new one come out 3 weeks later.

I've also been busy with my school work and haven't had time to do anything but put in maintenance tweaks to things recently. I knew the changes had the potential to break something, but I haven't done anything beyond participating in the discussion about how Canvas should be consistent and not do one thing for times ending in :59 and something different for times that don't end in :59. My impression, from the discussion, was that the old script would continue to work, but that students would have their due dates set to hh:59:00 if people continued to use it.  That made it not a high priority, given the other stuff going on at the time. That major project had a deadline of Thursday (2 days ago) and so now I'm a little more relaxed, but trying to scramble to get caught up on all the things that didn't get done while I worked on the one thing -- mainly grading.

The 2018-08-10 update to version 2a didn't change any functionality, it cleaned up some code that was causing it to not run for some people, so I don't think it was the cause of the problem. I do need to update the script to represent the hh:mm:59 thing, but I need to test and see what Canvas is doing before I can make changes.

[mjarvi17]

I can't even IMAGINE keeping up with all of Canvas' changes!  You're amazing for trying!

[Shar]

I'm just going to put this little note here for what I noticed reading through this document again for updates to v2b. Under Troubleshooting, Update versions, 2nd paragraph I came across a curious sentence.

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 got an immediate chuckle in reading this sentence and tried to think of some funny dryer-comebacks, but it was all funnier in my head.

Ok, carry on.

Cheers - Shar

[James]

ishar-uw, lint might also help explain the hearing problems I've been experiencing.

[swinkler]

Ah, James.  This is a beautiful thing!!! 

Would love to have this work by sections.  Any idea if this is in the works anywhere?

I will figure this out if it kills me (and it probably will)...

[shadwin]

James Jones, have you come across any methods to use differentiate by sections only.  I use your tool for the initial schedule for everybody, but when I add students or sections later, I still manually have to edit each assignment in Canvas.  It would be great to update a spreadsheet. :smileygrin:  Again, thank you for the script you provided.

[James]

swinkler,

I'm aware of a couple of alternatives out there that are not spreadsheet driven.  @kona ‌ pointed me to Webcourses@UCF Due Date Changer - UCF CDL  a couple of weeks ago, but I can't tell with an initial glance whether or not it supports differentiated assignments. I think it requires installation, so it may need the assistance of the local Canvas admin and/or IT department. Someone else pointed to a commercial package Adjust-All HQ - Etudes  that does support differentiated assignments.

A couple of people have taken the script I wrote and modified it to work with files or to programmatically fill in dates.

That's the extent of my knowledge. That doesn't meant that there's not something else out there that I don't know about.

[James]

 @shadwin ,

Two very similar questions about the same thing within an hour of each other -- and on halloween, too. Eerie. 

Basically, the same thing I just wrote Sue a few minutes ago applies here. I don't know of any spreadsheet version that supports differentiated assignments. It's too messy. If someone could come up with a clean way, there probably needs to be a different spreadsheet for it as I'm not sure how to make it fit with this one. Although I do have some ideas about what the next generation one might look like, I still haven't been able to figure out where differentiated assignments fits in. I think people would really want the ability to add differenation to existing assignments, rather than just change the dates on them and that makes it a lot harder to work within the framework of a spreadsheet.

[michelle_chrism]

Thank you for this!  You have saved me so much time and frustration 🙂

[michelle_chrism]

 @James ‌

Thank you for this.  You have saved me much time and frustration 🙂

[psimon3]

I don't know why you don't support moving events as well. This seems like a pretty big oversight.

More generally, I can't believe that I'm writing this, but Blackboard made this very easy with a simple date-management interface. Why not create a simpler, GUI-based tool? Requiring this many steps seems like a big-time fail. 

[tross]

Important to remember that this isn't a function of Canvas but of a talented Canvas user who wrote the program and contributed to the rest of us who use it.  

[psimon3]

That's totally fair and kudos to that talented individual. I just wonder why Canvas doesn't ship with this functionality. It seems like something that most of us would want. 

[llane2]

I'd been waiting to try this, and I finally did this evening.

Just to add my huge THANK YOU,  @James . Brilliant and, as with your other hacks, saves me huge amounts of time.  

[ssb]

I have attempted to download the spreadsheet, but the make a copy option is grayed out. If I download it, the logic is lost.  I am logged in and so not sure why it will not allow me to make a copy.

[James]

 @ssb  

If you're logged into Google Drive, then my best guess would be that your institution has restricted what you can access. We get this sometimes with our students trying to use their high school Gmail accounts at the college. I would try using a personal Gmail account rather than one supplied by or connected with the school.

[ssb]

The system sends me to the school Canvas log in whenever I attempt to log into the community. I am trying to find a way around that, but so far nothing has worked.

[James]

 @ssb  

I think there's a disconnect here. When I say logged in, I'm talking about Google Drive / Gmail. not the Canvas Community. How you log into the Canvas Community has absolutely nothing to do with whether or not you can make a copy of the file and being logged into the Community is not the same thing as being logged into Google Docs.

When you are not logged into Google Drive, then it looks like this: There will be a sign in button and the Make a copy is grayed out.

Click on the Sign In and use your Gmail account. If you're not logged in at all, it may not be an issue of your school locking things down, it may just be that you're not logged in.

Once you get logged in to Google, then it will look something like this, except the J should be your initial.

[ssb]

When I click on the link to log in I am redirected to the school Canvas log in. I have logged into Gmail and it has not made any difference. I will keep trying.

SSB

[ssb]

Got it. I was clicking on the sign in for the group and then saw the sign in when the spreadsheet opens the first time. I was able to make a copy . Thanks for the help.

[fcurry1]

This is very useful, but I was wondering if it could be modified so that it also includes the ability to change the open dates of modules and send-dates of announcements?  This would truly allow me to set up all of the due dates on one page.

Yet another feature that would greatly ease work for instructors would be a way to mass-edit would be able to adjust all of the accommodations for a particular student on one page.  As it stands now, if a student gets 1.5x the normal time on a quiz one has to moderate every quiz for that student.  Being able to moderate all quizzes for a particular student would add functionality that I am sure would benefit many of us working with Canvas.

Why Canvas doesn't have these features itself is beyond me. 

Thanks,

FC

[James]

 @fcurry1 ,

Those would be require separate spreadsheets. Google sheets has a time-out limit on execution of a script and the more you add, the more likely you are to run up against that limit. It was also designed to pull similar items with shared functionality together.

It's open source and people are free to take it and modify it and a couple of people have. I have some ideas about where to go with it that would make it more beneficial and cover more things, but it's definitely a different spreadsheet than what I have here if that ever happens. My real job responsibilities have picked up and I do not foresee any time to work on this before at least June or July 2019.

As for the giving time and a half, Canvas is implementing this with Quizzes.Next and are not, as a general rule, doing any more development to the legacy quizzes. Here's their page that explains how to do that: How do I adjust timer settings for a student's assessment attempts in Quizzes.Next? 

[don_bryn]

Hi James,

This google app is still the most important tool in my entire teaching toolbox!   It save me hours of prep time every semester.    

I've started working on Spring courses and I can no longer connect from google to Canvas.   I get the error:  

Unknown Course

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

My first thought is that my college has changed something like blocking api tokens (they OFTEN change things and don't tell anyone).  But before I start down that path which will take weeks to get answers, I though I'd see if anything had changed in the Canvas API in the past 4 months.  Just in case, I tried to copy the google spreadsheet again, but it's completely empty, both before and after I copy it.   There is no Canvas menu. . .

Do you know anything that might have chnaged in Google or Canvas that might be the cause?   I don't want to waste your time trying to figure something out, I just want to know if you are already aware of something that might be the problem.

Thanks again.

[James]

 @don_bryn  

I just used it on Christmas with an unpublished course and it worked fine for me. If you are manually typing the course ID and got the wrong number somehow, then that could happen. What I do is copy the URL from the browser and paste it into the box.

[jlondon]

Yes, just worked for me yesterday as well, fwiw. This one is a huge lifesaver. Thanks so much for making it! Definitely don't understand why Canvas does not incorporate more of these kind of layouts, so you can do bulk tasks in one place and see the whole forest, rather than having to trim each tree separately. 

The inability to edit html files inside the site is another source of frustration. Sometimes, if you just want to change a couple of things, it's a pain to have to download, edit, and then reupload. At least WebCt had WebDav, which made the process of working on files more seamless. But I digress.... 

[llane2]

Love this, use it all the time!

Went in tonight to see that although everything in the Canvas course was set at 11:59pm, in the spreadsheet it showed at 1:59am next day. I figured out that I had to go to File -> spreadsheet settings (where I saw it was set at Central Time for some reason rather than Pacific Time).  I corrected it to my time zone, then did Load again (to make sure I wouldn't save the 1:59am by mistake back to Canvas). Fixed everything.

Just in case it might help someone else.