CTL GuidesTeaching and Learning ToolsGoogle SuiteGoogle SuiteHow do I use Google Sheets to update course Assignments and Quizzes in bulk?

How do I use Google Sheets to update course Assignments and Quizzes in bulk?

In this tutorial you will learn how to use Google Sheets to make updates to your Canvas assignments and quizzes.

From the pre-made spreadsheet, you can edit assignment names, due dates, available dates, publish items and more!

1. Open the pre-made Google sheet

Click on the link to open the sheet:   Course Due Dates

2. Copy the spreadsheet

This is a read-only spreadsheet and you will need to make your own copy to use in your Google Drive.

Go to File > Make a Copy

Select make a copy from the file menu.

3. Name the spreadsheet

Name your copy ( perhaps with the course title included ) and click OK.

Name the new spreadsheet.

4. Check the timezone settings

In the document settings check the timezone to make sure it is set appropriately. 

1) Select Spreadsheet settings... from the File dropdown menu.

Spreadsheet settings button

2) Select the appropriate time zone.

3) Click Save settings.

Spreadsheet Settings window

5. Generate an Access Token

From within Canvas, navigate to your Account Settings.

Select settings in you Canvas Account.

Scroll down to the bottom of the Approved Integrations list and click on + New Access Token

Select New Access Token

"Name" your Access Token in the Purpose textbox ( You may want to include the course name here as well )

Leave the expiration date blank and click Generate Token'

Select generate token.

Highlight and copy the generated access token.

  • For Windows users: right-click the link and select copy or use keyboard shortcut <ctrl>+c
  • For Mac users: right-click (<ctrl>-click) the link and select copy or use keyboard shortcut <cmd>+c
Copy the token number.

6. Configure API Settings

Return to the Google Sheet you named in the above steps, then authorize the sheet for your account.

1.   Click Configure API Settings from the Canvas drop down menu.

2.   Click the Continue button to authorize.

Select canvas api settings from the canvas drop down menu.
Select continue.

3.   Select your Google account.

Select your google account.

4.   Click the Allow button.

Allow access.

7. Fill in hostname and access token

For Humboldt State University users,

  Canvas Hostname:   canvas.humboldt.edu

  Access Token: <Paste your access token here>

  • For Windows users: right-click in the text area and select paste or use keyboard shortcut <ctrl>+v
  • For Mac users: right-click (<ctrl>-click) the text area and select paste or use keyboard shortcut <cmd>+v

Then click Submit.

Submit the configuration form.

8. Specify your Canvas course

From the Canvas dropdown menu, choose Specify Course.

Select specify course from the canvas drop down menu.

From your Canvas course home page, copy the contents of the address bar.

It should look like this: https://canvas.humboldt.edu/courses/12345

Make sure you are on the course home page, and there is nothing following "/courses/#####"

Check the URL.

In Google Sheets, paste this web address into the textbox to specify the course ID.

Paste the course URL.

Click on Yes in the pop-up asking if you would like to proceed.

Click yes to proceed.

9. Load the current assignment data from Canvas

From the Canvas menu, choose Load Due Dates to populate the spreadsheet with the current information from Canvas.

Make sure you always Load Due Dates before making changes to the sheet to ensure the most current dates have been imported from Canvas.

Select load due dates from the canvas drop down menu.
10. (OPTIONAL) Reformat dates

From the Canvas menu, choose Reformat Dates to fix the display / auto-adjust column widths / re-sort the items.

Select reformat dates from the canvas drop down menu.

11. Modify the assignment data

You are now able to modify the contents of the spreadsheet accordingly.

Date format: mm/dd/yyyy hh:mm:ss

Publish format: 1 = published , 0 = unpublished

Use the function bar (labeled fx) for modifying dates.  You can enter dates in the cells in mm/dd/yy format, then use the Reformat Dates (step 10 above) to format the dates before reloading the data to Canvas in Step 12.  The default time will be 12 AM.

Show/Hide Answers columns are for quizzes only. They expect a date to be input and the changes will only be saved if the quiz is already set to show correct answers.

Assignment due date in the text field.

12. Upload your changes to Canvas

When you are satisfied with your changes, choose Save Due Dates from the Canvas dropdown menu to upload them to Canvas.

Select save due dates from the canvas drop down menu.

13. Check Canvas for your changes

It is always a good idea to double check that your changes have been reflected in your Canvas course.