Replace Blank SIS IDs for Users/Courses

[kyle_cole]

IMPORTANT DISCLAIMER: This is a homemade solution and is not Instructure supported.

Have you ever tried to edit a user or a course that doesn’t have an SIS ID and found out you can't use a CSV? You can manually add a SIS ID but doing that in bulk can only be done by the API. Using Google’s Apps Script I developed a Google Sheet that will pull all courses or users without SIS IDs and assign values to them in bulk. I haven’t run this script for over 1k objects but it should still work.

What does it do?

• Fetch All Courses without SIS IDs: This will bring in all courses that do not have a SIS ID assigned to them.
• Fetch All Users without SIS IDs: This will bring in all users that do not have a SIS ID assigned to them. Since the SIS ID is tied to the login_id it will also bring in the id of login_id.
• Assign Random SIS ID: This will generate a random string in the SIS ID column. This makes its easy to assign SIS IDs in bulk.
• Post new SIS IDs to Canvas: This will assign the value entered in the SIS ID column as the SIS ID.

*Tip: If you only want to add SIS IDs to a handful of courses/users, run a provisioning report and copy the Canvas ID to the first column.

What you will need:

• Instructure URL: Your .instructure URL, like canvas.instructure.com, is necessary for API calls. Retrieve it from your admin settings under Canvas Cloud Information. Although vanity URLs are an option, using the Instructure URL is recommended for API consistency.
• API Token: Generate an API token with appropriate permissions. Admins can create API tokens using this guide: How do I manage API access tokens as an admin? 

Video:

Possible Issues:

It is highly unlikely you will run into these, but just in case:

• Google has a rate limit of 300 requests per minute. If you run over that, it will error out. 
• Google also has a per-day limit based on your subscription. If you run into that, you will have to wait it out. 
• There is a 40k limit of rows so you will not be able to go over that.
• There is a runtime of 6 minutes for non edu/corporate accounts but if you are using an edu/corporate account it has a 30-minute runtime limit.

Lastly, here is the link to the sheet and the code used to make this happen. If you have any questions or run into any issues, post in the comments below. 

Link to Google Sheet: https://docs.google.com/spreadsheets/d/1PTVnt1thwUipFwAorOLG9qRWd69cIDXJEyLMFipJRPM/copy

Links to Apps Script: https://github.com/KyleCole90/Canvas-Scripts/blob/main/Apps%20Scripts/Replace%20Blank%20SIS%20IDs.gs

[ChrisMedina]

Hey there @kyle_cole ,

I am only getting a hand full of courses but I know there are a lot more. any ideas what the issue could be? I am an account admin so I have access to everything since I am the lms admin. also I would like to try this on beta but it doesn't seem to pull any info. i was going t use the input of course from the provisional report but since I am not getting many listed I am concerned it wont talk properly to canvas. 

thanks

[kyle_cole]

Hey @ChrisMedina 

This function invokes the API endpoint at https://canvas.instructure.com/doc/api/accounts.html#method.accounts.courses_api. It exclusively retrieves active courses within your instance. To verify the accuracy of the retrieval, you can execute this API call and cross-reference the results to ensure their consistency.

[steve_shishani]

Greetings @kyle_cole

Thanks for sharing and the effort you put into this. I ran the script; it fetched 59 records out of 3000 plus possible ones. It also managed to assign a random set of SIS IDs to all 59 courses.

Then I started getting this message. 

"Exception: The number of rows in the range must be at least 1."

I'm unsure what is causing it, but I did wait according to the "possible issues" you listed in the post.

[kyle_cole]

Hey @steve_shishani !

Sorry for the late response, I didnt get a notification for your reply. Unfortunately, that is a error pertaining to something with your sheet. Im not sure why you would be receiving that. 

[steve_shishani]

Thanks for checking on this @kyle_cole! I went ahead and assigned the course SIS IDs manually. 

[kristy_estrada]

Do you have something like this for section IDs?