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.

[James]

@scott_whitney1 

The code is released under the Standard ISC License., which starts "permission to use, copy, modify, and/or distribute this software for any purpose with or without fee is hereby granted" and then has some disclaimers.

The disclaimers are about including the license and not suing me. I don't even really care that much about attribution, I just don't want people suing me. If you do modify it and release it, then please change any help files so you're listed as the support person and not me.

[scott_whitney1]

I go this working for batch editing quiz types. Now I can switch all from "graded" to "practice" in minutes instead of hours.

Thanks again!

[taitjMiami]

Hi James!

First of all, this is a wonderful tool you've made. I've used it the past few semesters to save me hours worth of work. Thank you!

From what I've read in the comments, it's not currently possible to bulk edit dates for various sections. I've got some extra time on my hands and I'm fairly code savvy - is it possible for the end user (me) to adapt your GoogleSheet tool to bulk edit dates for say, 4 sections? If so, how could I start down that path?

Thanks for your contributions!

[mfigiel]

I'm a huge fan of this script file and thank you so much for sharing it with the community!  I've been using it for about 7 years and this is the first time I just can't get it to work.  When I try to configure the API settings, I enter the correct host name and token and it just doesn't take.  Is anyone else experiencing this issue?  The last time I used this was in January 2023 and it worked.  Please share if you have seen this issue or even better if you have a possible solution.

Thanks!

Marnie

Update: Simply switching from Firefox to Chrome seems to have resolved the issue 🙂

[GenevieveHay]

@James, I just discovered several of your scripts as I was searching for ways to improve and streamline my Canvas processes for the upcoming year. Thank you so much for all you've done and shared!!

@taitjMiami did you end up developing a version of the script to adjust dates for multiple sections (i.e. for differentiated assignments)?

[tfilipiak1]

This used to be a time saver.  It has become a time waster...  I can't get this spreadsheet to get/keep the API settings and it keeps bringing up the API settings dialog box.  I've already authorized the spreadsheet to access my Google account.  I started with a new copy from the Internet instructions.  I even had Canvas generate a new Access Token and tried that.  No go.  

What changed?  I can't get this to work any more.  Grrrr.

=============

10 AM, 10 August 2023 edit

Well, OK.  Finally!  I got the API settings to stick.  I tried "WCTC", "wctc.instructure.com" and the entire URL after the https://.  None of those worked.  I think what worked was to use "wctc.instructure.com/"  for the URL.  Nothing else worked.  Adding the slash seems to have made the setting stick?  Will try this again later as I am now setting up courses for fall.

3:41 PM, 18 August 2023 edit

Nope.  Still can't figure out how to make this work again?!  I tried publishing the course and it still doesn't work.  I tried a fresh copy of the spreadsheet. Nothing has worked.    Back to swearing at this...again.

4:26 PM, 18 August 2023 edit

Was using Firefox browser.  Tried logging into my Google account on Chrome browser.  Now this seems to work?  Is this script, etc. setup to only run on Chrome?

[vancej]

@James 

I am sorry for not  reading every detail, but are you willing to point out where in your instructions we able to adjust the  points for  Assignments and Discussion? I did a Find search in the browser, and I see it as  one of the items to be explained. Yet, I am not seeing something a sub-title saying "how to adjust the points," or  something similar.

Thanks

[James]

@vancej 

Just change the value in the Points column to the desired value and then save the due dates back to Canvas.

[Buzz]

@James This is still working swimmingly for me. Very handy at the beginning of every term. Can I buy you a cup of coffee? 

[James]

@Buzz, I mostly only drink water, but knowing that I was able to help someone is my reward. You've already done more than I could expect by saying it helps you do your job.

On the other hand, if someone was independently wealthy and had no heirs or charitable organizations they liked, we could talk more.

[MaureenHickey22]

This is still absolutely brilliant, and I just wanted to say thank you!

(I have used your spreadsheet/script in the past and I am currently teaching a Bio Anth Lab with lots of individual assignments I wanted to adjust, and this made my night a whole lot easier.)

[itojp]

Hi, I've relied on this for many years now, but I'm having a similar problem to those described above in posts from 8/10, updated 8/18.  Every time I try to configure API settings, it doesn't ask me to confirm my institution but the dialog box just disappears and it goes back to the spreadsheet.  I'm on a macbook using chrome.  Any idea how I can resolve this?  Thanks for any help, John

[ChandlWM]

Can you add the option to TURN ON "Sync to SIS" ???

My district uses PowerSchool.  So I send all my assignments and scores over to PowerSchool.

SIS stands for "school information systems."

So after an assignment is published it shows up with the circle x option that I can then click on to change to a green checkmark as demonstrated in the picture below.

By the way I used your spreadsheet api tool.  Thanks.  I discovered that I had to use my personal google sheets.  It wouldn't work on my district google sheets.

I now want to teach myself to do this cool api spreadsheet thing.  I would like to design one much like yours.  I think I would prefer google sheets rather than using a python program to connect to the api.

[lindalee]

Thank you, so much, @James, for this tool, which has saved our team countless hours since we started using it in 2017.

I'm wondering if you have any plans to offer anything similar for pages, or to incorporate pages into this tool, now that we can schedule the publishing of pages in Canvas.

We use your tool extensively for adjusting assignment due dates for all of our communication courses (more than 100 courses each term). And that program just asked us about the feasibility of scheduling the publication of pages, so they can make student resource pages (with links to slide decks, etc.) automatically become available after each class session ends. Doing this manually will be too much work for our team to take on, so I'm hoping we can convince them that no-submission, ungraded assignments with an "available from" date is the next-best thing (which we CAN change using your tool).

But that got me wondering whether you have considering developing any way to automate some of the new page-related functionality.

Thank you again!

[James]

@lindalee 

I do not have a similar tool for pages.

When scheduling pages became available, I considered it, but did not incorporate it for a few reasons. A non-exhaustive list is that it would add a lot of clutter for most users -- those who don't schedule pages. Pages don't work the same way as assignments. The existing script allows people to rename assignments, which could be problematic for pages since you need to make sure to avoid name collision.

In the end, I decided that if someone wanted to schedule pages, it would be a different tool than this one. There is enough functionality for a separate page manager that could include scheduling capability.

I've thought about making expanding this one into a more encompassing tool over the years, but my real job keeps me busy and I don't have time to work on it.

[macevoy]

For anyone having the problem that the API settings won't take.....I closed every Chrome window and restarted my computer.  Then it worked. 

[tfilipiak1]

The only way I have gotten this wonderful, magic spreadsheet to work lately has been to use  the Microsoft Edge browser.  I have had no success running this spreadsheet on Chrome or Firefox.

[doldham1]

@James I was an instant convert to this when I first learned about it, and am still an enthusiastic user years later, even after Canvas added its own version. For my money this version is far more powerful and intuitive. So, thank you! You've saved me untold hours of frustration and tedium.

Now my question (apologies if this is answered elsewhere and I missed it): Have you written or considered writing a version that does the same thing with Calendar entries? Is such a thing even possible?

I ask because I use the Calendar a lot to add readings and other non-graded, non-assignment items that I still want students to see and/or do by a particular date. It's helpful for them, but updating it every quarter is a time suck. I'm considering ditching the Calendar and creating ungraded assignments with no submission--a clunky workaround--just so I can use this spreadsheet for fast & efficient updates every term. But if there were a way to do with the Calendar what this does with assignments, I'd be all over it.

Anyway, thanks again for arguably the single most helpful Canvas-related tool I've encountered.

[James]

@doldham1 

I have not looked into using the calendar for that purpose. I don't know of an immediate reason why it would fail as there is an API call for calendar events and you could put all that you want into the spreadsheet to send it. That's me talking without exploring further. As long as you don't want to do repeated events, it shouldn't be too terrible (famous last words that I know better than to say).

As with many things (and I have considered this), I don't think that it would go into __this__ spreadsheet well. All kinds of things can have dates and this one was designed specifically for assignments. The columns become different and so you have a bunch that are not appropriate (you cannot turn off scheduling a page, for example).

I have thought about adding more functionality, but always get sidetracked with different things that come up. Right now, I have no free time to do more than a quick reply here or there.

[James]

@ChandlWM 

Sorry I missed your question back in December. If people don't @ mention me, there's a good chance I'll miss it.

I cannot answer anything about post to SIS. We don't have that functionality with our SIS integration (we're using a custom SIS integration I wrote not the actual SIS) and our SIS doesn't have assignments in it, just a spot for the final grade. I'm not going to release something I cannot test.

If you can use the edit an assignment API to turn on the post to SIS, then it would be fairly trivial (I hope) for someone else to modify the code. I set up the columns in an array within the source code so it's just a matter of adding a new column there and then any other processing that might need done.

If post to SIS is handled some other way, then it won't be trivial with this spreadsheet. However, it might be quick with something else. I wrote some other bulk spreadsheet tools. If you're wanting to just turn them all on, you don't need a 0/1 flag in the spreadsheet.

[doldham1]

@James Thanks for the quick response. It's more or less what I suspected. If I had time and a lot more skill I'd look into it but alas, I have neither. I'll keep using this spreadsheet in any case because as I said, it's a life saver.

[bsr]

2024: Google no longer has "script editor" under tools. It's now "Apps Script" under Extensions. Sure wish I wasn't spending my morning trying to get this old work-around working, but Canvas still hasn't fixed their own tool so that dates can be adjusted in both directions!

And a question: is there a way to make this work for sections that have different due dates? This solution is awesome for my lecture quizzes, but I have three lab sections, each on a different day of the week, and their lab assignments/quizzes are due on their section's lab day. I've had those quizzes online for the past several years, so that I can incorporate images and take advantage of automated scoring, but the thought of doing all those due dates again this semester has me ready to go back to paper! 

[James]

@bsr 

True, Google has changed more since I wrote this script than Canvas has on the issue. Fortunately, little change means things still work -- I used the spreadsheet for my classes this semester. Unfortunately, the Community software has a limit on how much you can put into a post and since my posts tend to be lengthy, that means I cannot edit them to update them. That means people will need to rely on comments for information when things change. Thanks for sharing that with people.

[lalee24601]

@bsr @taitjMiami @James 

This is indeed an awesome tool! I'd also love to see it work for differentiated assignments. I have a little experience with Google Scripts though I haven't done any hard core coding in about 25 years.

Is anyone interested in working with me on getting the differentiated assignments aspect up and running?

Or should we actually be working in Java through the upload/Themes feature? (I'd have to get it turned on for me since it isn't currently available. I just learned about it today, and I think that's how our admins are standardizing the look for our students.)

I'm a complete newbie at accessing the API - but I'm game to learn. I am on sabbatical until January - so I do have some time. I could really use some guidance on how to start.

Thank you all in advance for your ideas!

(The other project in the back of my mind if I can get through this one would be to figure out how to have Canvas able to reference our rotating schedule - we see 4-6 of 8 sections a day - to allow us to specify 'beginning of period'/'end of day' due dates like Jupiter Grades does. Maybe that could be a calculated field in other sheets of this type of workbook...?)

[Christine_S]

You are my hero!  I was given only 4 weeks to convert 20 years' worth of Blackboard courses to Canvas, and it was a miserable experience.  When setting up my course for this semester, I was really missing Blackboard's Date Management feature.  Finding this and getting a glimpse into the potential with Google Scripts and Canvas has been the FIRST AND ONLY bright spot in the awful, awful, awful forced rapid-"migration" to Canvas!  Thank you so much!

[lalee24601]

@bsr @taitjMiami @James 

Creation and Editing of Differentiated Assignments/Modules via Google Sheet

First of all, a huge thank you to you, James. You opened my eyes to what might be possible. I tried to address the question of creation and modification of differentiated assignments/overrides via Google Sheet.

Here's a link to an approach I'm currently playing with. Please be warned. Unlike a real Canvancement, this is truly a beta, under continual development - and I used Perplexity to generate code. I'm learning about syntax and structures in Google Scripts and can follow and modify what was produced, but it would have taken me months to create this manually - far longer than my sabbatical. The Canvas API token is being stored in a user property to increase security, and I ran all the code through AI as well to try to catch any security issues. I've tested it on a two  school accounts, and it seems to NOT have stored my API key when I make a copy - which I think was the only security risk.

There is a list of functions vetted in the workbook. USE AT YOUR OWN RISK. I guarantee nothing - just sharing a possible approach. This has NOT been cleaned up for distribution. It is just a beta with some extraneous stuff still in it - but I thought it was worth sharing since it seems to be something folks want. It should be OK for user testing, though.

https://docs.google.com/spreadsheets/d/1BdrWr--rFGfFXaSfRt7YVYqdfqhsgHs6QEcakuAVycw/edit?gid=1521218...

Things it does (my current documentation is linked in the Google Workbook and also here😞 

• Creation of assignments from a simple page with a direct link to the new Canvas assignment. If you play with this, change the Config to your instance of Canvas and if you  link the assignment to the schedule when asked (step 4a), be aware that it works ONLY for our school's period system - though it could be modified for yours. I know that many more parameters could be specified, but that would increase complexity. Most teachers will have to go into the assignment in Canvas anyway to add their own content and tinker with the assignment. This is intended to set up the skeleton.
  
• Allows editing of the assignment parameters on a separate tab.
  
• Works with modules and items to help streamline one's organization. and update basic things like textHeaders and module names/order
• Batch creation of modules and Text Headers in the modules, especially for course set up.
  

I have a few courageous beta testers at my school working on it, but I welcome comments and bug finders. At some point, I will produce a finished template for this tool, but that will be after my beta testers have finished with it.

Mahalo nui loa! (Thank you very much in Hawaiian.)
Lara