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.

[Stef_retired]

What a timely gift,  @James ; thanks!

[scottdennis]

Here! Here!  Thank you very much!

[clong]

NICE!! :smileylaugh:

[DaleDrees]

Alright, that was absolutely Magic!!!! Thanks  @scottdennis ​ for sending the Tweet on this and a big thank you to  @James ​!  Hope everyone else sees this. I followed your 10 steps and it worked as advertised!  Game-changer for me!!!

[snelson6]

 @mevans1 ​ @eoneill ​ @tcrandall ​ Depending on where we land with courses and instructors, this might be a lifesaver for us.

[kathy_arnold]

I am so glad to have found this- just began the process of due dates for spring semester and going crazy! Before I did a google search and located this. Will try it on T or W and see what happens. Thank you!!!! Kathy

[fairwea]

Thank you  @James   for sharing this resource and providing excellent documentation.  It worked perfectly and will be such a great tool for efficiency! 

[RhondaB]

Thank you for this useful tool! I am unable to view the videos. I tried in Chrome and Firefox. Is it just me?

[James]

I saw this too, yesterday when I was presenting the information. When I logged into the Community, it worked. I'll see if I can get it relinked.

[James]

 @RhondaB ​, it is fixed now.

What happened was that it was written in a private area so it could have multiple authors. Then when it was copied to a public area, the videos referred to an internal code from the private area that you didn't have access to. I was able to see it when I logged in because I had access to the private area, but if other people, like you, logged in, you still wouldn't be able to see it. I basically just relinked all the videos from YouTube and now it works since the internal code refers to the public version of the page.

Thanks for pointing out the problem.

[RhondaB]

Thank you, James!

[cdavis1921]

How does the export/import handle multiple due dates for quizzes and assignments? I have a few assignments with various due dates for students, usually within 1-2 days of the primary due date and time. Does the spreadsheet export bring all of the dates, or just the primary?

[kona]

Do you mean due, available from, & available until dates? If so, then yes, this spreadsheet manages all of those plus the ability to change the "show correct answers at" and "hide correct answers at" dates.

[cdavis1921]

No, I was referring to selective release.  Students can be given different due dates and availability dates on a case by case basis. So, does this spreadsheet show the multiple due dates and availability dates?

[kona]

Unfortunately no, the spreadsheet doesn't show different dates for different students. This would still need to be set up manually.

[James]

 @cdavis1921 ​,

 @kona ​ is correct.

I purposefully ignored differentiated assignments when writing the script. The goal was to get it to be one page with one row per assignment. That's why I have one page that handles both quizzes and assignments, although in Canvas the quizzes have some dates belonging to the quiz and some dates belonging to the assignment. I had to merge the two records to get it on one line. Their have been requests in the Community to have a page that allows you to edit all quiz options or all assignment options and this could be used as a basis for writing that, but you would need to split it up into two separate pages as the information is too dissimilar beyond what I've included. That's also not the point of this spreadsheet, which was a response to get all dates on one page, not get all quizzes on one page and all assignments on another page.

Differentiated assignments would require multiple rows and would have to have other information included that doesn't fit in the tabular form with non-differentiated assignments -- namely the "who" the assignment is for. That would most likely have to be a read-only field since requiring people to exactly match the names in Canvas would be difficult (and isn't unique unless you use SIS IDs, which aren't available to everyone, or Canvas IDs, which aren't convenient). It could also be the names of entire sections. Canvas has autoSuggest to help you fill in that information, but in the spreadsheet, it would be checking after you had entered the entire thing, so there wouldn't be a list of names to pull from -- that's why I say it would basically have to be read-only. The reason it doesn't fit with the other is that each of those overrides would require an extra row per override and an extra column for the who and one to identify the override.

[gjarrett]

Just saying "Thank You!!!!" You have saved me many minutes of tedium already, and that is ten minutes into using this great tool.

[laurakgibbs]

 @James ​ I am SPEECHLESS. Seriously, absolutely and completely speechless.

YOU ARE A GOD.

Because of the way I design my classes (smorgasbord, students choose what to do each week from menu of appx. 20 choices and declare their points via  true-false completion quiz for each assignment), I have hundreds of quiz items. Last semester, I manually changed dates on every one of them. It took me 7 very stressful and tedious hours to do that.

But your magical API scripts worked perfectly: I just now accomplished the same task in 30 minutes, and I also learned some things about APIs and spreadsheets that inspired me to learn more.

Thank you thank you thank you.

This is the only thing that has truly impressed me about Canvas so far. You have let us non-programmers use the power of the Canvas API, and I am so grateful!!!!!!!!!!!

[Stef_retired]

For anyone interested in this topic, be sure to RSVP to the CanvasLIVE event Adjust all assignment due dates on one page, which is coming up this Friday, January 13, 2017. RSVP “yes” if you will be there--and if you’re interested, but your schedule doesn’t allow you to attend in real time, RSVP "no" or "maybe" to receive all event updates. Your RSVP ensures that you will receive a notification should the event be cancelled or changed.

[pkingsley]

This is all kinds of AWESOME!  Thank you!

[Beth_Young]

My campus URL is https://webcourses.ucf.edu/   I can't get this to work . . . I enter the URL and the access token, but I never get a success message, and when I try to Specify Course, the script asks me to configure the API access again. I've tried both with and without the https:// part.  (Now that I've seen the demo I want to use it even more!)

[James]

 @Beth_Young ​,

There are two things I can think of. The first is an easy fix and make sure that you do the "Configure API" before you do the "Specify Course". If it doesn't come back with the "Success!" statement it's not going to work. Based on how you wrote your comments, I don't think that's the issue, but I thought I'd document it in case someone else is reading this later.

That leads to the second issue and this one's tougher. The original person who requested this turned out to be at an institution that had everything behind a web proxy (like some libraries use) and they had their own URLs and extra headers for the API calls that had to be made instead of the documented ones at Canvas. He made some changes to the older version of this code so that it would work, and then I released the updated version right after that. You might contact your local Canvas Admin and ask them if they're self-hosting or using a proxy.

Other schools block access to the access tokens completely, but that doesn't seem to be the problem for you.

One thing you might be able to do (a little techy) is to go to run the Script, then go to Tools > Script Editor inside Google Sheets, and press Ctrl-Enter to display the error log. It might have a message of what went wrong.

This script is different from the User Scripts I write -- they piggy-back off the authorization you've already done to get into Canvas. That means those might work while this one doesn't.

That's the first round of debugging.

[Beth_Young]

Thanks! I'll go through these steps and let you know what happens. Might not finish today. I appreciate your troubleshooting help (and I appreciate your writing these scripts!!!)

[Beth_Young]

OK, here's where I am:

1. I definitely was running Configure API before Specify Course

2. My LMS admin says, "Our Canvas instance is hosted by Instructure. We are not self-hosted."

3. I ran Configure API again, did not get a "success" message. Opened script editor, pressed ctrl-enter and got the message "No user logs found. Please run your script and try again." Then I tried running Specify Course, pressed ctrl-enter and got the message "[17-01-13 13:41:48:001 PST] InternalError: Cannot find method alert(string,string)."

[James]

 @Beth_Young ​,
I just found a Execution Transcript in Google that shows every call I make to Google's functions. Debugging would have been so much easier had I know that was there before.

Anyway, I've reached out to you via email until we can figure it out.

[Beth_Young]

Everyone--the script is working for me now! I am very happy. It's a great tool and I know it will save me hours of clicking. THANKS, JAMES!!!

[James]

Yay!  @Beth_Young ​, I'm glad it started working. And the best part is that I slept through the whole thing.

[craig-just]

I get the same error that Beth was getting. I can't find any evidence of a proxy server - we are simply uiowa.instructure.com. How did Beth's problem get resolved? Thanks!

[brennerm]

James, If I were to send this to my faculty to use, do I send them the token I used and not put an expiration date or can they generate their own token? Not sure I want them to have my rights but I don't want to do them all either so just curious....

[Beth_Young]

What seemed to do the trick for me was switching browsers. In the past, I'd had trouble getting Chrome to cooperate with GoogleDocs (crazy, I know) so I was using Firefox anytime I was dealing with a Google Form or Sheet or Doc. At James' suggestion, I gave Chrome another chance, followed everything from step one, and the "Configure API" script worked. Now that the access token has been imported, I find I can specify the course from either Chrome or Firefox with no problems. (I hope the solution is this easy for you!)

[James]

 @brennerm ​,

You absolutely do not send them your token. They need to get their own token or they will have access to everything you do.

If you put your token into the spreadsheet and then give them access to the spreadsheet (share it through Google), they will still need to get their own access token. The access tokens are shared using what Google calls User Properties. It's only accessible by you for this script. If you use a different sheet or you pass it off to someone else, it will require the token to be re-entered.  You can re-use the token if you happened to save it, but it's not shared between scripts or users.

If you watch Kona's presentation, you'll see she shared her sheet with me so I could help out. What you didn't see, because it was on my computer, was that I had to enter my credentials to run the script. I could edit the dates and the spreadsheet, I just couldn't interact with Canvas until I configured the API.

[James]

craig-just​,

As  @Beth_Young ​ wrote, we didn't find anything wrong with the program itself, so I don't have any great advice for you. I actually slept while she tried things and when I woke up, I had the message it was working.

Suggestions are: Make sure you're using Chrome. Be sure to follow the steps in the order given. If it doesn't come back with "Success!" then click on "Tools > Script Editor" and when you get there go to "View > Execution Transcript" and/or "View > Logs". The logs are messages I put in there when I know something went wrong. The execution transcript keeps track of every call to one of Google's functions that I make and kind of will show how far it got before it broke.

Make sure that you're using a gmail.com account. I don't have any hard evidence, but I've had problems in my classes with people using a school email address instead of a Gmail.com one. I think it might be Google Apps for Education related where schools lock down what can be done. Again, just throwing out ideas here.

You might also make a fresh copy of the Google Sheet and start there. Make sure you get the version 2 that's linked off this page and not an older one from another discussion in the community.

If your execution transcripts show something useful, then shoot me an email (mouse over my name here in the posts to get what it is). Otherwise, I'm in the problem spot of trying to track down a problem that I can't replicate (which is very hard to do since it's something subtle).

[kona]

Below is the response that  @James ‌ provided to Crystal -

See the section in "The Canvas Menu" on "Specify Course" for the specifics of how to put in the Course ID. The easiest way is to copy/paste the URL from the browser, but there are instructions there on what it will accept.

That particular error message happens when it tries to look up a course and it can't find it. That could be you missed a digit when copy/pasting or you mis-typed the number. Basically, it recognizes the thing as something that should be a course ID, but Canvas doesn't return any information about it. I would double check the number and try again.

If it's not that, it's going to be more difficult to track down.

[kona]

James is better at this type of trouble shooting, but have you tried a different course to see if you get the same error? Also, what is your role in this course? Are you listed as the Instructor of the course?

[James]

Computers sometimes do weird things. That's often why we suggest users reboot their computer as a first step - sometimes that just fixes it and we never know why. In a similar vein, we may never know what happened to make it work, but I'm glad it is.

[tross]

I really love this tool and so does everyone on our staff who has seen it.  We use 'master courses' and then import content to the course created from the SIS so this is really wonderful for them use.  The question I have gotten is do you have a similar one for events.  I looked at the api and see the calls exist so I am going to work on it but thought I would ask if it was already there bore I spent time on it.  Thanks for all the great things you share.

[James]

tross

I have not done anything with events except to look at them long enough to explain how the event API calls may not function they way that you think they should function: https://community.canvaslms.com/thread/8733

Feel free to develop away -- you might be able to build off what I did here.

[mjevins]

This is probably the best automated resource for Canvas I have found. Saved me hours of clicking through every assignment in my course to edit dates. Thank you!!

[jason_edington]

Thank you so much for this!  I can't tell you how much time this and importing rubric script has saved me.  

I'm going through your Google Script to see if I can figure out how to use Google Sheets to create new assignments, discussions, and quizzes as apposed to just changing them.  We have some courses that have a ton of assignments, and it would nice to be able to create them in bulk in a spreadsheet like this with the title, assignment type (assignment, discussion, quiz), points, due date, available from, etc.

If you have any suggestions or ideas to get started with this kind of thing I would be eternally indebted to you.    

Thanks for your wonderful contribution to the Canvas Community!

[James]

 @jasoned ‌,

Creating is a vastly different process from updating and quizzes are different from assignments which are different from discussions. There are options for some of the items that do not lend themselves to the spreadsheet format, where you would need a different column for each field and then hopefully someway to validate that information being put in so that it worked correctly because you can't "just click" on it like you can through the web interface. There are too many enumerated items that should not be entered free-form by a human. And even then it sounds simpler than it is -- depending on the type of assignment (external tools, online upload, etc) there are different parameters that must be specified.

I have programmatically created assignments and wiki pages and one time added 1350 quiz questions to question groups (although not constructed the entire quiz programmatically). The content was so specific that it wouldn't have worked well as a spreadsheet. It also wouldn't work well because spreadsheets have great constraints on what goes into a cell. Some of the items you're creating accept HTML and then you're losing out on the HTML editor and manually coding it. When I created the assignments, it was a very simple assignment where all of the instructions were essentially "Homework for chapter X" or "Notation for chapter X". There wasn't anything complicated there.

Every time, and it has been several, that I've thought about writing code to "Copy" something, it's turned into more of an appreciation for why Canvas hasn't done it. So, while you could modify this Google Sheet to create shells of content for a particular type (maybe have one page for assignments, one for discussions, one for quizzes, one for pages, etc -- or different sheets for each), you would still most likely need to go in and modify the pages afterwards.

While that would be a killer app, I would not attempt it in a spreadsheet. The spreadsheet works well for structured data so I might use it to create shells. If you have so many items that you're looking at doing it programmatically, then you're probably going to want it to do more than just create a shell and actually make the entire assignment. Unless it's the most basic things, you'll want more power and flexibility than a spreadsheet could provide.

I might be missing something with how involved you want these things to be, but it sounds like a case of using the wrong tool. You might be able to use a screwdriver to pound nails, but there are other things that would work better.

Warning aside ... If you just want the shells created then you'll need to look at the Create endpoint for each of the assessment / content types to see what is required. What I do sometimes when I can't figure something out through the API documentation is go into the Canvas page and fill out the form that they have. I open up the developer tools in the browser (F12) and switch to the Network tab. Then I submit the form and look at the parameters that are sent and how they're formed.

[jason_edington]

Thank you for the reply.  This is very helpful! For now, even an assignment shell would speed things up quite a bit.  I'll continue to dig deeper using the developer tools and see if I can figure it out. Thanks so much!  

[laurakgibbs]

 @James  Thank You This Assignment Dates solution solved the only problem I had not solved in my D2L-Canvas transition. I am so grateful!

[Robbie_Grant]

laurakgibbs, Have you seen this gem from  @James ‌? 

https://community.canvaslms.com/docs/DOC-8844-importing-rubrics-from-a-spreadsheet 

Robbie

[laurakgibbs]

 @Robbie_Grant  Yes! I heard about it from someone else, maybe  @llane2 ‌? But I don't use rubrics... and, uh, I don't grade, ha ha. Seriously. I use a kind of weird hack to let the students grade themselves, and this summer I am going to see if I can use a rubric approach to perhaps improve my system.  Details here: true/false quizzes with checklists that I call "Declarations"

Points-Based Grading: Student Gradebook Declarations – Teaching with Canvas 

Here's my hack for repeated quiz questions (which was the other D2L migration problem I faced, but this one I solved on my own):

Canvas Hack: Repeated Quiz Content – Teaching with Canvas 

I'm pretty happy with the true/false system ... but I might see if I can use rubrics to give me (i.e. give the students) more flexibility. It's on my to-ponder list! 🙂

[jeneen_hill]

Great spreadsheet-  thank you so much for sharing. I have a couple of questions.  I wanted to change some due dates so a few students could submit an assignment, but when the student goes to the old assignment, with the new due dates, the student does not have the ability to submit the assignment.  Am I not seeing or doing something correctly?   @kona James Jones 

Thanks in advance for your help,

Jeneen Hill

[kona]

 @jeneen_hill , Can you show (post a screen shot of) the date/time settings for one of the assignments? That would help us to trouble-shoot the issue.

[tross]

You have the available until date as a date before the due date.  The students can't access after the until date even if the due date is later.  

[James]

 @jeneen_hill ,

The first thing I see that you have an available until date that is March 31. That's past, so the students don't have access to it. If that's not it, let us know.

[jeneen_hill]

[kona]

Based on these settings students can't submit because of the until date. The Until date is what allows them to submit to an assignment, discussion, or quiz. If you extend that out then you'll be fine.