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.

[jeneen_hill]

Thank you so much!  Worked great!

Jeneen

[hockin]

 @lgaughan ‌ let's try this out for summer session!

[olexar]

I gave this a go today while setting up my summer classes.  @James ‌, you win the internet for at least a month with this.  

‪https://giphy.com/gifs/iiS84hOJXh1Pq?tc=1‬

Now I really want one to do time accommodations. That wins the internet for 6 months!            

[James]

olexar, I'm not sure I know what you mean by time accommodations. Do you mean giving certain students extra time on a quiz or are you talking about differentiated assignments where the dates are different for groups or something completely different.

The second one I have no intention of tackling because I haven't been able to determine how to do it in a way that isn't complicated.

The first one I haven't given enough thought to -- but if I used Canvas quizzes that were timed, I would probably write myself a script that would go through and take a list of students needing 1.5 time accommodations and have it automate the process. Perhaps it could read in a list of students and a list of quizzes and iterate through them, setting the extra time.  The current setup isn't too bad for a small class, especially with the ability to select multiple students and apply the actions, but I hate repetitive tasks, so I would at least find a way to automatically select the students based on a list.

I've only thought about the last one (something completely different) in the context of Monty Python. Well, maybe something more, but I'd have to know what it was first.

[olexar]

It's definitely about giving certain students extra time on a quiz. When disability accommodations come in, I (and I'm sure a ton of others) have to go in and add time for each student for each quiz. If you come up with a way to do that in a similar way to your date adjustment, a package of your favorite adult beverage has your name all over it!    

[James]

I already won the internet for a month, why would I want a case of water?

[olexar]

Florida water is delicious! Just sayin'...

[olexar]

dreesd‌, Have you seen this? Works great!

[DaleDrees]

Hi olexar‌,

I have been using it for loading all of our assignment dates in our eCert course. 

[hockin]

Hi  @James ‌,  @lgaughan ‌ and I tried running this today.

1.  We are using Lindsay's personal gmail.com address and Google Chrome.

2.  We were able to add the token and "load due dates."

3.  When we "Save the due dates,"  the scripts run and it says "Script finished." (There is no "success.")

The due dates did not update in the course.  (I did check Canvas status and everything seems to be okay.)

Any ideas?  

I have the execution script log:

Any thoughts?

[James]

 @hockin   and  @lgaughan  

This is going to sound silly, but do you have the right course?

I took a look at my logs and what happens when you click Save. I noticed that the Utilities.formatDate() calls are made only for information that is in your spreadsheet. Your changing dates to Oct 17 2016, Oct 24 2016, and Oct 23 2016.

That suggests that you didn't run Specify a Course first and you're still using a course from last fall or that you're trying to change dates to last fall. If that's in a current course, then Canvas may have fixed the part about allowing dates outside the course dates (if those are specified).

If you try to do that, the spreadsheet warns you

If you didn't get that message, you can still mouse over the due date to see what the validation rules are

If your course dates don't match up with what you expect, then there's a really good chance you're still using an old course, in which case you were attempting to change the dates on three items in an old course.

If you really mean to change the dates on an old course and you weren't able to, it might have something to do with not having access to the course anymore.

Now, with that is another concern. When I save mine, even with a bad date from last year, it still wrote it for me. I've split up the long lines, but notice the method=put in the third entry from the bottom? (this picks up right toward the end).

[17-05-12 19:18:38:345 PDT] HTTPResponse.getResponseCode() [0 seconds]
[17-05-12 19:18:38:346 PDT] HTTPResponse.getAllHeaders() [0 seconds]
[17-05-12 19:18:38:347 PDT] HTTPResponse.getContentText() [0 seconds]
[17-05-12 19:18:38:370 PDT] Utilities.formatDate(
  [
    Sun May 15 21:59:00 PDT 2016, UTC, yyyy-MM-dd'T'HH:mm:ss'Z'
  ]) [0 seconds]
[17-05-12 19:18:38:371 PDT] PropertiesService.getUserProperties() [0 seconds]
[17-05-12 19:18:38:404 PDT] Properties.getProperties() [0.033 seconds]
[17-05-12 19:18:39:035 PDT] UrlFetchApp.fetch(
  [https://richland.instructure.com/api/v1/courses/2054971/assignments/11847461, 
  {
    headers={Authorization=Bearer redacted}, 
    method=put, 
    payload={assignment[due_at=}
  }
  ]...) [0.629 seconds]
[17-05-12 19:18:39:035 PDT] HTTPResponse.getResponseCode() [0 seconds]
[17-05-12 19:18:39:098 PDT] Execution succeeded [16.042 seconds total runtime]
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

It's that PUT that actually writes the information and yours isn't getting that far.

That happens if, after comparing the dates, there are no changes from the data that is already there and nothing to update.

If there are differentiated assignments for an assignment, it failed to update the on one I was testing, but I know it doesn't support differentiated assignments. It still tried to save it and yours isn't getting that far, which happens when there are no changes.

So, I would start by checking the course -- do the Specify Course and check the course number. Then make sure you look at the executing logs when there are changes that have been made.

I'm not convinced that's the right answer, just two things that jump out and seem reasonable based on testing.

[hockin]

Hi  @James ‌,

 @lgaughan ‌ and I created a template course in the Default term (start whenever to ends whenever).

She then copied a master template (with course dates set for Fall 2016) into the new course site (in the default term).  We didn't auto adjust the dates.

She specified that new course then loaded the dates.  We then changed the date  (but apparently not the year since we were just doing a test run) in the spreadsheet.  Then we tried to put it back into the same new course.  I guess that is where we failed and should have specified a different course?  Or does the course have to reside in a date-limiting term?

- Access is not an issue since it is a default term.

-Maybe the issue is that we tried to push the dates into a course that already had dates? Or into the same course?

We will next try to load the course dates from the master template, then specify the new template course.  Ideally, she would like to just reload the dates into the master template as she is tasked to make all the live copies each semester.  I think she would love to just update the master template each semester with the new dates after the instructor has revised.  Then push it out to the live courses (in a live or future term).

Thanks for your help!

[James]

 @hockin ,

I've got a better idea of what you're trying to do now. Thanks for clarifying.

You definitely cannot use the tool to put dates into a different course than where you loaded the dates from. The ID numbers would be all wrong and nothing would happen.

You didn't make a copy of the existing data to another worksheet before changing it did you? The code looks for a sheet with a specific name "Dates", so if you copied the sheet and then made the changes on the new version, it would still read the information from the old version, detect nothing had changed, and then stop before attempting to write the data back. In the example below, I made a new sheet called Fall2017 and copied Dates  to it, then changed the values for Fall2017, leaving the original one alone and so no changes got written back to Canvas.

If you want to go that route, you should make a copy of the old stuff as Fall2016 and then change the dates on the "Dates" sheet.

In either case, only the dates on the sheet named "Dates" is used.

I don't think having an undated term would matter to the spreadsheet because 1) it grabs the dates from the course not from the term and 2) those should be used in an advisory role for validation, but they're ignored when the information is written. That said, at the time I wrote it, Canvas was not enforcing dates submitted through the API be within the course dates (like it does through the UI), but that may have changed. I also don't think that's the issue because the script never got to the part about sending the information back to Canvas -- which means that it either crashed (going to the Tools > Script Editor > View Logs would probably show a message after a failed execution) or it couldn't find any changes from what's already inside Canvas. It's that second one that I'm working off of - trying to find ways that you feel you've changed things but the program doesn't think you have.

Another question -- do you only have 3 dates in the course that you're changing? If not, then there may be an issue with the way that you're changing the dates and so they're not getting picked up and the three that it does find haven't changed.

[fisher1]

 @James ‌, I would like to thank you for all of the hard work you've done to help make our lives easier in Canvas. You are truly a blessing!

I know that on another discussion thread somewhere else, I asked you about the ability to change section due dates all at once. You told me that it wasn't realistic due to the nature of the script you wrote and the spreadsheets functions.

Recently, our school just started subscribing (fairly cheap too!) to the EduApp called Adjust-All HQ - by Etudes. This external tool has just added the ability to adjust section due dates all at once. I am not a rep for Etudes, but I just wanted to let others know what I found as a solution. Below is a screenshot I took as a sample when I was playing around with it.

[James]

I'm glad you were able to find something that works for you. You're right in that I still don't see how to make it work with differentiated assignments and be simple and have the power of a spreadsheet along with the limitations that invokes. For example, you would need an ID for the assignment and another one for each assignment override. Then you start getting into the sparsely populated spreadsheet with lots of blanks. Part of my original design was that you could delete information that you didn't want to change, and if someone removed the override ID column without removing the assignment ID, then it would could have multiple dates for the same assignment. Yes, I know, all that could be programmed around and the user warned, but in the web interface they've created all that can be maintained and just the tabular form of the data presented.

On the other hand, it looks like you lose the ability to do formula-based date changes and perhaps some other things you can do with a spreadsheet. I'm not seeing the whole program and I don't have experience, so I may be wrong there, but since I know spreadsheets, it's easy for me to set the available from and until dates based off the due date without having to manually enter them. Maybe that's masked under the select checkbox and another menu somewhere.

In return for giving up some formula-based things that many wouldn't use anyway, you get more flexibility and the ability to handle different types of things (like locking dates on modules that just wouldn't fit in with spreadsheet stuff). Mine is about changing assignment dates and I think theirs is about changing all dates.

I think they take different approaches to meet a need and it's nice to know that people who need something the spreadsheet doesn't allow have an alternative to consider.

[debra_mansperge]

Woohoo!  Thanks so much for this! You are my hero  @James ‌

[lindsay_kruse]

About showing the correct answers, if I've got it marked in Canvas to show correct answers, and I set my date as the same date of the test, would the students be able to see the answers immediately after selecting a choice? 

[kona]

 @lindsay_kruse , I'm not 100% sure what you are asking, but students would see the answers after submitting the quiz. They wouldn't see the answers after answering each individual question. For more information I'd check out the following - https://community.canvaslms.com/docs/DOC-3313-quiz-settings-to-maximize-security?sr=search&searchId=...‌

[jbreslow]

Is there anyway to do this type of thing for the "Available From" and "Available Until" dates for Files?

Thank you.

[James]

I believe there is. Someone actually emailed me privately about that right before InstructureCon and I briefly looked at it. Not far enough to get into which API calls were necessary, but to think about how it would work with the spreadsheet.

[awilliams]

 @jbreslow , if I select more than one file at a time, I am able to manage access to all of them in one fell swoop using the "Manage Access" button in the toolbar at the top of the files area. I'm not sure if that helps you out at all but figured I'd pass it along just in case.

[James]

Thanks for that awilliams! That would have made my summer class easier to manage. Having it in a spreadsheet form would have been beneficial so I could see the whole class in one shot, but this could definitely have saved me time.

[jbreslow]

Thank you awilliams‌. It isn't exactly what I was hoping for, but it is close and will be very helpful. I was envisioning something like that, but would allow me to select different dates for different files, but doing it all on one screen. Your suggestion is a huge help though.

[Beth_Young]

I'm pretty sure this is possible, but I don't know how to do it right now . . . I'm just throwing it out there . . . it would be great if this spreadsheet could somehow flag the assignments where the "available" dates are mismatched to the due dates. At least one of my courses imported poorly this semester, and assignments were out of order. I dragged them around on the calendar and now I'm trying to fix all the "available until" problems that resulted. I can do it by eyeballing the dates, but if the assignments that had problems would be highlighted somehow, it would be easier.

For example, an assignment might have a due date of Oct 2 but the availability might close Oct 1, because the due date changes when you drag something on the calendar but the available until dates do not change. Maybe this spreadsheet could highlight those problems in yellow or something? 

[laurakgibbs]

Hi  @Beth_Young ‌, I will defer to  @maxjjoness ‌ on this, but my experience with using his magical spreadsheet is that you can do all those spreadsheet things like formulas and such. I use formulas when I recalculate the dates for all my assignments (because of the way my classes are structured I have over 150 "quizzes" in each class). So I think it would be safe to add a column to the spreadsheet where you insert a formula with IF that will return "need to fix" or something like that for a date pair that is out of whack.

 @James ‌ am I correct that you can add a column to the spreadsheet for a formula like that so long as you make sure to delete the column before sending the spreadsheet data back to Canvas...?

[James]

Yes, you can add extra columns to the spreadsheet. I don't even think you have to delete them, as long as they don't have a recognized header. I'm going from memory on that one, but I'm pretty sure you can delete any columns you don't want and reorder the others, so it's looking for a header at the top before it does anything with it.

I regularly go through and automatically assign my availability based on the other dates using spreadsheet formulas.  That's probably easier than trying to manually fix them. The "Changing the dates" video above shows how to do that (starting at about 1:15) and there's probably documentation for it as well.

The other option is to add the extra column with something like =IF(D2<B2,1,0) in row 2. Check the columns in the formula, of course, but then copy that formula down and look for 1's. For me, the first option is easier since the due date and available until date are the same.

If that's all you need, though, you can just copy the due date and paste it into the available until date.

[saltman]

First, James and  @kona ‌, you are A.MAZ.ING.

Ok, I really want this to work... My Canvas class starts tomorrow.  But I get this error.

Even when I went in and changed the date format again to allow time, and set available from Sept. 05, at 12:01 am, I got the  same message.

I thought maybe it was a time-zone issue, but I went back and made sure to adjust time zone before anything else.  Same error.  I can make the work available for the 6th.  Just wondering what I'm doing wrong here.  

Thanks  @kona ‌ and James Jones !!!  You guys rock!

Shauna

[James]

 @saltman 

The first thing I would check is in Canvas. Go to your Course Settings page and check the date and time the course is set to open. The validation message only displays the date, but I'm pretty sure it checks the time. So if your course was set to open at 8:00 am on 9/5, it would throw an error if you tried to set it to midnight on 9/5. If you change it in Canvas, you'll need to reload the due dates from the spreadsheet's menu to get it to recognize the time change. Even if it is set to 8:00 am, I am not sure that's a problem (read the last paragraph).

I can't tell from your screenshot exactly what's going on, so here's some general advice until you respond with more specifics

Make sure you're typing in the date as a date, like 9/5/2017 or just 9/5 and Google will add the 2017 to it.  You can also use ISO format like 2017-09-05. You can double click in the cell and it will pop up a date picker.

Do not type "Tuesday, September 5, 2017". It may take it, but it's more work than needed.

The other thing is that's not an error, it's a validation check or a notice. It pops up for cells that are expecting dates -- iIn fact, it's popping up on an empty cell. Here's what mine looks like on an empty cell.

However, if you actually put in an invalid date, the message changes and it adds that triangle in the top right corner when the date is invalid. 

Now as to what you have, when I type "Tuesday, September 5, 2017" it works.  Notice the lack of triangle on this one:

But it's there on yours, which means there's a problem. Either it's not a date at all or it's a date outside the range.

You can also verify that it interpreted whatever you typed as a date by looking at the top-left (right above cell A1) of the spreadsheet where the cell contents are shown. This is what Google took when I typed "Tuesday, September 5, 2017".

That may have a different format based on how you typed it in. For example, when I typed "Tue, Sep 5, 2017" into a cell, that's what showed up above cell A1, but it was still recognized as a date.

The first thing I would do is check your date as that triangle means Google doesn't like it. Either it was an invalid date format or it was in the wrong interval.

The second thing is to not worry about the Orange Validation notice, but do worry about the Red Invalid Warning.

Finally, that notice and warnings are just that, not errors. It will still continue to process even if you get the warning. The last I checked (granted, it's been a few months), Canvas would allow you to specify an illegal date/time through the API that it wouldn't accept through the web interface. So even if you put 12:00 am (midnight) and the course wasn't set to open until 8:00 am, it would still take it. Again, I haven't double checked that recently.

Let us know what you find out.

[saltman]

Thanks!  The problem seemed to go away after I changed the start date in Canvas to the day before I wanted the first assignment due.  Since it is working now, I will probably wait to try your ideas until it stops working again.  Thanks for pointing out the difference between validation and invalid.  I am sure I will have to problem solve some bits as I just shared this with 100 teachers.

[jlondon]

Boy, wish I'd known about this a week ago. Really would have save me some time. 

[bdiamond]

Hi! This looks like exactly what I need, but when I copied the Google Spreadsheet, I still don't see the Canvas menu. Any ideas on how to make that show up so I can continue? Is there a way to do it manually? I tried searching for an Add-on with no luck. Thanks!

[James]

 @bdiamond ,

When you say you "Copied" the Google Spreadsheet, you do mean by going to File > Make a Copy, right? If you just try to copy/paste, it doesn't get the underlying code. Also if you try to download it to Excel it won't work either.

I just opened it up on my end to make sure it was still there. It took about 5-6 seconds for the Canvas menu to appear, but it finally did.

[bdiamond]

Yes, I meant Make a Copy. I did not move to Excel. It has been over an hour for me at this point:

I also just checked, and it did not show up for me in the original. Is it possible my system is blocking it?

[James]

Are you using a Gmail account you created on your own or one supplied by your school? If it's just a regular Gmail account that you create on your own, it shouldn't be blocked. If it's a school-supplied one, then I don't know for sure (we don't do that), but we have seen restrictions placed on other things in Google by the school's IT administrators. We get some students who still have their high school's Gmail accounts where they cannot communicate or collaborate with people outside their school. I have them create a new account when that happens.

[bdiamond]

Switching to a personal Gmail account did the trick. Thanks! It works like a charm!

[don_bryn]

I've created some extended functions for  @James ‌ google sheet that allow you to create a list of dates for a term and then apply them to all assignments in a course.  It just takes what he was working torwards and adds one more tool for those of use who use the same dates for multiple classes.  Read about it here:  Canvas Bulk Date Editor (Google Sheet)

[taverett]

Outstanding tool, saved our University a ton of time when we transitioned over to Canvas and had to fix migration issues in numerous courses.  

[ekutoff]

Love the spreadsheet, I can see this saving me a ton of time.  Kudos!  I do have one question, can I copy the sheet in the same workbook and rename them for the multiple course that I have?  This way when I want to change one course I don't have to reload it every time.  It would be one workbook, with multiple sheets each renamed with the name of the course and this way it's a one stop shop without having to reload it.

[James]

I'm glad you found it helpful.

You can rename the sheets. However, any time you load the due dates, it will go to a sheet called "Dates" and create it if it does not already exist. Anytime you save the due dates, it will take the information from the page called Dates.

Load the dates and rename the pages as you desire, but if you ever want to make changes back to your course, you will need to rename it back to Dates -- at least temporarily -- to do so.

[ekutoff]

Can it be modified to behave as I mentioned in my email?

On Oct 26, 2017 11:45 PM, "james@richland.edu" <instructure@jiveon.com>

[James]

It could be if you know how to program JavaScript. The quick part of the fix would be removing the check for the "Dates" page, it's creation when not there, and the delivery of the information back to Canvas from that page. You could just make it use the active sheet rather than the one named "Dates"

The bigger problem is the handling of the course ID. That's not stored on the page anywhere, it's held in the spreadsheet's user properties. You would need to modify that to either store the course ID on the page itself and retrieve it from there or you would need to modify the code to associate the name of the page with the ID once it's downloaded. That can be problematic with the way things are done, so you would have to rename the page, then specify the course and load the due dates. If you renamed the page after the dates were loaded, it would break the bond. You could conceivably associate the position of the page with the course ID, but then dragging the pages around would break it.

In general, storing the dates long term is potentially problematic as well. If you changed any date in Canvas and then went to the spreadsheet and made other changes without first updating the list, it would overwrite the changes you made in Canvas with the stale information.

The simpler (non-programming) solution would be to make a copy of the "Dates" page as you indicated and name it after the course. Add the course ID somewhere on the copy of the page. Always leave a page called "Dates". Then, when you want to update changes, go to Specify the Course and put in the Course ID. Copy your page back to Dates. Save the due dates.

I would still caution against not loading the most recent version of the dates before doing that, though.

While it could be modified to do this, I won't be the person doing it. Sometimes simpler is better and making it handle your use case will make it easier for unsuspecting people to inadvertently screw things up. However, the code is released under a license that allows you to take it and modify it and retask it and pretty much do anything you want to with it except sue me if it doesn't work.

[jah1145]

I've been working on a version of this that also lets you edit points, submission type, and grading type, as well as permissions of files: https://community.canvaslms.com/docs/DOC-13223-adjust-all-assignments-files 

[don_bryn]

I've added another extension to this doc that allows the editing of announcement publish dates.  It works similarly as my previous extension that allows the creation of a list of dates for a semester that can then be applied to as many courses as needed.   It's very helpful for me as I copy a series of announcements with each course that get published on specific dates throughout the semester.   You can read about it at Bulk Announcement Date Editor (Google doc).

Sidenote:  Because avatars are stripped when copying courses, I have hacked a way to put my avatar inside each announcement so even when copied, students know they came from me.  You can read about that at Using your avatar in other places.

[jimmypage]

Thank you SO MUCH!!!  I just found this amazing tool.  This will save hours of editing.

[awilliams]

Hey  @jimmypage  Welcometo the Commuity! I'm thrilled you're already finding some of the amazing resources on offer here. I look forward to seeing you around.

[rao_3]

I have tried to use this and other versions of this that I found by searching the community. None of them work. Everything seems to go smoothly, and I get messages saying the script has finished running. But in the end, nothing happens.  When I try to see if there is an error message, all I get is this:

[laurakgibbs]

Hi  @rao_3 ‌ I have used this successfully, so I know it can work (and it is fabulous when it does). I am going to ping  @James ‌ here just to make sure he sees the error message you shared here. Hopefully he will be able to help!

[rao_3]

I think that I figured out one of the problems: I need to have a >fresh< sign-in to osu.instructure.com (which takes me to Ohio State's single sign-on). I am not sure if it needs to be same window as the Google Sheets, but different tab is certainly OK. If the sign-in has timed out, or if I have logged out, the script does not work. Is it worth figuring out how to add error reporting to the script for this sort of thing?

I guessed if was some sort of sign-in problem, but I don't know why the script editor menu item also kicked up an error message. It seems to be OK now. [It may have been a cache problem, but in that case, it may be impossible to reproduce it on purpose.]

Thanks for all offers of help, especially on a Saturday.

Regards

Nath

[James]

 @rao_3  ,

Is OSU self-hosting Canvas (as opposed to running on the cloud) or do they have a non-standard URL setup or perhaps a proxy server? The original requester of this feature had an issue at Indiana University where the information had to be specially formatted to work and he had to modify the code to work with that. Your Canvas administrator may be able to answer that question for you.

[James]

Thanks Laura. I've been so busy recently that I often miss things unless I'm mentioned on them.