Google Spreadsheet to Help Manage special user accounts

[clong]

Hi All,

We occasionally have tutors and instructional aides that need Canvas accounts in our instance but they are not in our SIS so our normal account creation process does not work. And there is a high turnover in these positions so I need to use a spreadsheet to track things.

What, I'd like to do is generate an API access token in Canvas and use it in conjunction with a Google Spreadsheet AddOn/script that I could run and it would create the accounts from the sheet.

I think this is possible, I'm just not sure how to code the script yet.

If anyone has an example or would like to team up with me on this project, I'd appreciate it. In the spreadsheet I am using now, I also use the autocrat script to send the supervisors and new users an email with an Google Doc of their account information along with some help tips.

Looking forward to getting this going!

[clong]

Here's a sheet to start up things.. once I start the script and it runs I'd like it to enter a date/time stamp in the cell and then if that is there it should not duplicate cells with pre-existing entries.

API Script to create new Canvas Users - Google Sheets

[James]

 @clong ​,

Let me get some clarification from you. One thing I like to do, call it the math teacher in me, is to make sure the problem is clearly defined before starting. It helps frame the problem and make sure everyone is on the same page.

Let's start with how the existing functionality doesn't meet your needs. What is wrong with this:

1. Make a spreadsheet in the form described in the SIS CSV Import API documentation?
2. Save it as a CSV file and then run a SIS import when you need it.

You said they're not in the SIS, but then you manufactured a SIS ID. If you do a SIS import, you need a SIS ID. If you're creating through the API, you don't.

Some comments:

Putting a date/time in there is relatively easy from a script.

Unless this script is going to be authoritative, you might need to do queries against all users in it to get their current status. This isn't bad at the beginning, but gets longer as more people are added. With a SIS import, it will ignore existing information that is the same. Even if this script is authoritative, it may not be. At some point, someone in this system might get into the system another way. Maybe not for you as a high school, but to make the script useful for others, you may need to consider those things.

Sometimes it helps to take the process you currently go through and explain the steps. It might help someone find a better way of doing things. For example, if the question is asked, how do I do task B? Then people put all their focus on how to accomplish task B. But it turns out that they really wanted to get from task A to task C and if the collaborator knew that, they could have said "You don't need step B, you can go from A directly to C by doing this."

Some questions:

How do you propose knowing who needs an account created? Is it anyone that doesn't have an account created date or do you plan on executing the script one at a time?

You have a created_at field, but what about when you need to delete an account? Do you remove the created date so the system picks up that there is a change? Most systems keep two dates -- created and updated, but that won't even help here unless you manually update the updated field. The problem is that there's no way in what you have right now to signify that the user has been deleted. You could mark the status as deleted, but there's no way for the script to know that it hasn't been processed without querying all the users to see what the current status is.

Adding users to the system that might eventually be in the SIS can complicate things. They can be updated through the API, but they have to be treated separately through the login API. That information isn't kept in the spreadsheet.

Basically, what I'm seeing in the spreadsheet is the format of a SIS Users import. Which takes me back to why that doesn't work and what you really need to accomplish. That will help determine the information that we need to have and manage. That's kind of along the lines of my comment about you're trying to start with what you think Canvas can do rather than what you really need it to do.

[clong]

Thanks James,

I'll start with what I'm doing now.

1) The teacher that supervises the paid aides and tutors fills out a Google Form requesting a Canvas account. In that form they fill in First Name, Last Name, and district assigned email

2) I get notified when a form is submitted and then go into the response spreadsheet.

In the response spreadsheet, I have added an AddOn called AutoCrat. I set a "mail merge" in Auto Crat up to email a personalized Google Doc to the new account holder and the teacher who they are working under. Here is the template I made for this Canvas Account Request Follow-Up (autocrat template) - Google Docs 

3) I take the information that I need from the Google Responses Sheet move it over to Excel then make an SIS formated csv users file which I then uploaded into Canvas.

4) I run the autocrat mail merge script to send out the emails as described above.

Why Change This?

I can continue to follow this workflow, it's clunky but works. I know API calls can be used to do this and I'd love to learn how to use a Google Sheet to send the API calls. I just figure, if I can see how this is done, it might open the doors for a lot of other use cases.

When it comes time to delete those accounts, I would just change their status to inactive, then eventually delete them by changing the status to delete. I didn't intend on having the sheet do queries against all users. I was thinking the sheet would be a write/edit to Canvas only and it would only update users if a designated column (cell) was empty for that user.

The fields I have on the spreadsheet are based on my experience using csv uploads, I have very little experience using the API. I do like using the SISID field to label certain types of users I create. So I use AVID1, AVID2, AVID3... etc for AVID tutors and this makes it easy for me to identify in reports and searches.

PS - If you have not used autocrat before you gotta check it out. It's amazing. See: autoCrat - New Visions CloudLab

[James]

Okay, now things are starting to make more sense.

Let me make sure I've got this:

1. The layout of the spreadsheet is because it comes from a Google Form
2. The first name, last name, and email are provided by the requesting party
3. You add the SIS ID, password, and status to the Google Sheet
4. You then copy the data to Excel so that you can save it as CSV instead of using the save as CSV that is in Google Sheets under File > Download As.
5. That CSV file is uploaded to Canvas through thee web interface
6. You then go back to Google Sheets and run the AutoCrat script.

And what you would like to do is ...

• Shorten step 3 by only having to add a SIS ID, while having a password and the status automatically assigned.
• Replace steps 4-5 by using the API to automatically create the users and logins and cut Excel out completely

Is that a fair statement of your agenda?

If so, then I see something like this:

1. The teacher makes a request through Google Forms
2. You get notified, go in and add a SIS ID.
3. You then run a Google script that scans for anyone with a SIS ID but missing the password and status fields. For each user that matches that criteria, it:
   1. populates the password, status, and created fields
   2. uses the API to create the user
4. You then run a Google script that sends out the AutoCrat message

I also see a possible column called Force or Update that will force sending the record to Canvas. This can be useful whenever you set a status to Deleted. I'm not sure what you mean by "inactive". That's been a common request and frustration -- the lack of an inactive state for a user. There is inactive for an enrollment. Another option is to create a hash of the information so you can tell when it's been changed. I think I had originally done that with the change all dates Google Sheet and then backed out of it and just decided to redownload the entire assignment list.

How does autoCrat know which fields to process during its mailing?  Is that what the account created on is about? I want to make sure that nothing we do would mess that up.

It might be possible to merge the two scripts, so that the one that creates the account then calls autoCrat

Am I missing or misreading anything?

Do you have any idea what do other people who would be using this want that is different from your approach. It's easier to design stuff at the beginning rather than hack it at the end.

[clong]

I think you've pretty much nailed down the agenda.

I could have used the Google Sheet to csv option but there were more columns in the sheet and I suppose I could have made a formula to move those over to a second sheet in the right order then make the CSV from that sheet, but I found it just as easy to copy and paste to Excel.

I don't mind putting in a default password of my own in each row but it would be way cool if the script made a random alpha/numeric password of 8-10 characters and put that in there for me. Since there is no inactive users in Canvas, I would probably want to mass change their passwords as a first step in deactivation, then later delete them in the status column. That should give most people the options they would need for cases like this.

I created a copy of the sheet I am using now along with a dummy form submission. In it you can see the columns autocrat made (L-O) -- autocrat has logic in it where it checks for values in these columns and if they are empty and there is data to the left of them it will run a new merge job. So if you want it to re-run a job you just delete the data in column L-O. The account created on date, is more just for me to know when I took action and did things.

Sheet: Copy of Tutor/TA Canvas Account Request (Responses) - Google Sheets

Form: HBUHSD Employee Canvas Account Request

As far as what others might want or other ideas... I'm thinking wouldn't it be cool if you could put in a change password or delete account date? There's lots of cases where we only hire someone for a semester or year or we have student teachers that come and go. And remembering to tidy up and revoke their access is tough when they are not in you SIS. So could you set a date that the sheet would trigger an API call to do this?

I can also see how others may want to enroll the users in a course section(s) as a Observer, Student, Designer, TA or Teacher.... but maybe that's another script project?

[James]

Columns L-O are really just the next available columns in the spreadsheet or do you have to specify where they go? Next available makes sense so when you do a sort, it stays with the rest of the data.

The automatic expiration would be limited to when you ran the script. I don't know what trigger functions Google Sheets has available to it, but I would imagine that the spreadsheet would only run when it was active (loaded in a browser). So, if the people are supposed to get their password changed on Dec 15th, but you don't load the program again to make changes or look at things until Jan 4th, that's when the change would take effect.

I'm conjecturing of course. I haven't looked into it. There are other ways to schedule things and it might be possible to write a CasperJS or PhantomJS or similar script, attach it to a cron job that runs once a month or once a week and processes anything that needs processed. But when you get to that level of sophistication, you're probably looking at something that's done through actual server-side programming rather than within a Google Sheet. It would be a whole lot easier.

If you're going to change their password as the first step in deactivation, then you would need to make sure they can't access the "Forgot my password" links. Furthermore, if any of them have created tokens, they can get to stuff through the API and bypass the password check altogether. While some schools may control access to both of those, it's probably not a realistic way to keep someone out. We don't delete anyone from Canvas, what we do is change their login to something that doesn't exist in our active directory setup so they can't login. Then we can change it back if they ever come back. But no one except for a select few of us can use the bypass external authentication option because we don't have Canvas maintain passwords.

The generation of passwords shouldn't be too bad as long as you don't want secure passwords. We have a system that allows students to request a random password (for those having trouble coming up with one), but it's running on a linux box and so it just makes a system call to the pwgen command and returns 20 or so passwords from them to pick from. One option might to look for a site that returns a random word through an API call, generate a couple of those and then join them together with a random digit or symbol. There's a feature request to force a password change that might be useful if you're sending random passwords, but the passwords we would come up with would probably be better than what a lot of people would use if left to their own devices.

I think enrolling them into courses is beyond the scope of this project. Not that it's difficult to do, but I'm having trouble wrapping my head around what that would make the spreadsheet look like. There might be multiple enrollments for one person and then you either use multiple rows, which complicates the adding and autoCrat process, you have to use multiple columns and possibly not have enough, or combine the codes for the courses into a single cell and then parse it out.

At some point, you have to limit the scope of the project and this may be the place. If you don't keep it reasonable, you quickly come to understand why Canvas keeps saying "This won't happen in the next 6 months". The photo roster I did was something they wouldn't be tackling in the next 6 months and it took me most of a week (I had some days off) to get it working. I didn't do any testing to make sure it looked good for everyone and it only worked with Firefox. Canvas can't throw something out like that.

If your faculty didn't have the ability to add the other people to courses, then I might look at something, but I think I saw in your test document that they were responsible for adding the new person once their account was created.

[clong]

Correct, columns L-O are the next available columns on the spreadsheet and are created by the autocrat merge when it is run the first time.

Great point on the password change not being a good way to keep users out. I didn't think that all the way through. In most cases we deactivate their emails used to login to Canvas, but sometimes the emails are outside our domain. As you mentioned, changing the login would be the way to go.

One more vote for force a password change​ that would be a great feature to have. Because I always tell users to change their password but do not have a way to ensure that they do. I did find a Random Generator - Google Sheets add-on I was also thinking a formula could be made to generate a 12 digit random number [=randbetween(lower limit, upper limit)] which would probably be more than good for a temporary password , however it updates every time the sheet is changed which is not good for this case.

I agree that enrollments would be a separate project for the reasons you mentioned. I was just trying to think what else.... but we do have a way to do this very easily once the accounts are created.

[James]

For the password, you have the script that does the processing generate it and store it in the spreadsheet as a value rather than a formula. That way it remains constant.

So, unless other people chime in, it sounds like you've got the scope narrowed down and a defined, solvable problem.

That's the hard part. Now all that's left is the relatively easy task of coding it.

[clong]

Yes now the fun begins, and I'm glad at least one of us thinks the rest is easy

[James]

Oh it is, Chris. The most difficult part of what remains is finding the time to work on it.  @kona ​ and I made a list of the projects for the Community that I'm either working on or should be working on. She said "Chris is a good guy" and put your project in the top 3.

[clong]

Thanks for the props  @kona ​... it's great to have friends like ya'll in the Canvas Community​

[kona]

No problem! I told James that you're always quick to help others and share your resources/ideas/work with the Community.

[James]

So  @clong ​​,

How is this project coming. Are we still waiting for other people to chime in? Have you gotten any the code written yet or did you want to wait and get my updated Google Spreadsheet code before you started?

p.s. - Kona said you had a sense of humor

[clong]

LOL.. I'd love to see your updated seet. I think I can copy the first part where you bring in the Canvas API parameters.

[James]

 @clong ​,

I have a couple more questions.

Are we working from the spreadsheet you originally proposed or the one that gets populated by Google Forms?

I'm thinking the original one would be more useful for other people. I've written the code that identifies the columns based on the headings so that it can be changed with a variable setting rather than changing the main part of the code. But I'm guessing it's going to be easier for most people to use "First" than it is "First name of the employee who needs this Canvas account." That means a little more work for you in copying the information over, but I really think that you'd want to check and verify the information rather than taking another user's input at face value.

What do you think about an action field?

As I was going through writing some code, I was trying to decide how I could decipher what needed done. Here is what I came up with.

1. Default action is to add an account. This would happen when there was a first name, last name, login id, and maybe nothing else (some people may not want a SIS ID). Definitely nothing in the created_at column.
2. There should be some way to update the user information without resetting the password. Let's say that Tom Smith changed his SIS ID, which in reality, he shouldn't, so you may want to consider some other way of keeping track of information -- Tom might change roles, but you're using a SIS ID to indicate a role rather than a role to indicate a role, which is what roles and enrollments are used for. Anyway, you may need to update information after he has already changed his password
3. There should be some way to signify the password has changed -- in case you want to do a soft, but not foolproof reset.
4. There should be some way to delete / rename the user

Then, rather than typing the Status, we could let the action dictate the status.

I'm not saying those should be the codes, but come up with some scheme (I like -1 for delete). But then once the action has been completed, it gets reset to 0. that way, you know that there is no pending action for the user and can skip them when you process the next time.

The automatic disenfranchisement of an account seems a little aggressive to me, but I'm not the one managing it. I was wondering if being able to sort by date added and then manually just putting in a -1 for the action would work? Or maybe you could have an expiration date in the date, but I just don't do anything with it. If you really think it's pretty firm and you won't accidentally kill someone's account, then we could write the code to do it.

Do you think it's worth keeping a history or log on another sheet?

There's a part of me that says we should keep a record of when changes were made. But since multiple changes could be made to the same account, we need would have a many-to-one relationship. That doesn't fit with the current single-page form, but we could create another sheet and use it for logging purposes.

I don't know how useful it would be, but I've tracked down enough problems to know that logs sometimes come in handy.

What do you think about word-based passwords?

There are studies that show for one-time passwords, words are easier than digits. I've got a system created that looked at the most common 500 words from the English language, pulled out all the 4, 5, or 6 letter words, and then randomly picks 3 of different ones. You're not going for secure passwords, you actually hope they change it.

Given the number of words I picked, there are over 200 million possible word triplets. Again, it wouldn't be secure, and possibly subject to brute force attack, but definitely easier for people to remember and type. It also gets away from the issue of whether that is an el or a 1 or possibly an oh or a zero. You know, those ambiguous cases that people should never use in a password?

Anyway, if you're good with that, it's already written.

I haven't written anything for the specific to the API yet, I was trying to flush out the rest of things before getting there.

[James]

A note on SIS IDs. It appears that we should be using something for them, so my earlier comment about role-based still applies, but we shouldn't necessarily get rid of them altogether. Either that or I'm completely missing something in the API documentation.

You can lookup a user by their Canvas ID or by their SIS ID, both of which should be unique (but maybe aren't): Get user profile

But if you want to search by their Login Id / Email, you need to use a different API call: List users in account​. There is not a guarantee of uniqueness among email addresses and more than one account may be returned. If you do anything other than create an account, you need to have some way to make sure you're getting the right user account.

So, we either need to make SIS ID mandatory or add an extra column for Canvas User ID. I've always done everything through that anyway, so I'd recommend adding that column to the table just for making life easier for other people who don't want to create SIS IDs for accounts that aren't associated with the SIS. Programmatically, we could still assign one, I'm just saying for identification purposes that it would be easier to use the Canvas User ID, which every user must have.

[clong]

I see your point! Could you have a column for SIS ID and Canvas ID. or does it have to be just one?

[James]

There's no problem with having both. The sisID is important for entering the information, but the canvasID would be more of an internal thing. The presence of the canvasId indicates that the entry is in Canvas. In that sense, it kind of duplicates part of the role of the created field. But whereas created meets a human need, it isn't useful for the script beyond the initial creation. So, if you want to turn it into a create-only script and then you manually delete entries, then canvasId doesn't matter. But if you want to be able to automatically delete people when their time is up, then sisId or canvasId is necessary to guarantee uniqueness.

In the code, I've marked firstName, lastName, and loginId as required values for processing. I figured some wouldn't use sisId or other columns that you might, so I was trying to define it so that the number of required fields is minimum. The action column needs flushed out, although I've made a stab at some values.

If we wanted to just write a custom script that would work for your institution, then there would be less work involved than trying to write something more general.