Managing Loaner Chromebooks Using Google

title

Introduction

My organization is currently preparing for a 1:1 Chromebook implementation.  One of the services we plan to offer is to have a help desk which students and staff can use to support their devices.  We intend to have a stable of loaner devices that students can borrow if their device is broken, uncharged, or forgotten.  We need to have some way to track these devices.  I decided that I would make a solution myself.  This solution had to be:

  • Paperless – It is hard for me to advocate for paperless solutions if I’m not doing so myself (and who wants to deal with all that paper).
  • Efficient – Students need to be able to acquire and return these devices and efficiently as possible so they can get back to what they are here for, learning.
  • Automated – The less interaction with the system anyone needs to have, the better.
  • Google based – We are a G Suite for Education organization, so the solution should be based on Google’s suite of tools.

With these tenets in mind, I wanted to create a system with the following functionality:

  • Reporting – Overuse of the system will be treated as a discipline issue, so the people who will enforce that discipline should receive automated reports on who is using the system too often.
  • Breakage – We use a separate help desk ticketing system.  If we are giving out a loaner due to breakage, a ticket should be created in that system automatically as to not require duplicate entry of information in multiple systems.
  • Receipts – Students should receive electronic receipts when receiving and returning a loaner.
  • Status – Help desk staff should be able to look in one place to find a list of all loaners, their current availability, who has them if they are out, etc.
  • Logging – All events should be logged and errors that need attention should be sent to a defined administrator of the system automatically.

So I set out to create this system using the following tools:

  • Google Forms – I decided to go with two separate forms. I did this to avoid having to ask on the form if the activity was a check-in or a checkout because it would lengthen the process for the ‘customer’ and likely produce a greater number of errors.  One form for the checkouts that would collect the student’s ID number (barcode scan), an identifier for the device being loaned (barcode scan) and a reason for the loan (forgot device, device not charged, broken device).  The other form would be for the check-ins.  Since I would have all the information about the original loan (from the students original checkout), all I should need to collect on the check-in form is the identifier for the device being returned.
  • Google Sheets – Sheets would be the ‘database’ (cough, cough) to house all the required information.  Student information (exported from our student information system), device information (exported from our asset management system), loaner device status, usage history, logging, etc.
  • Google Apps Script – Apps Script would allow me to do all the processing that needs to happen.  Logging activity, sending emails, changing statuses, looking up student and device info, etc.  95% of the work to get this running would be in Google Apps Script.

First the Forms…

  • checkout1Checkout form – This form has 3 questions (in section 1).
    1. What is the student’s ID Number? – We need a key field to look up the students information in the ‘Students’ sheet.  I’m using their student ID for that because our student IDs have barcodes on them and scanning that barcode is the most efficient way to identify the student.  I’ve added data validation to make sure what is scanned ‘looks like’ an ID number (at our organization it is 6 digit numeric).  I did so with the regular expression ‘^[0-9]{6,6}$’.
    2. Which chromebook is being loaned? – We know who is being a loaned a device, but which device are they being loaned?  Again we need a key field to identify the device on the ‘AllDevices’ sheet.   I chose serial number because it is both unique and scannable (barcode).  On our Dell Chromebooks, serial numbers are 6 digit alphanumeric, so I used data validation on the form with the regular expression ‘^[A-Z0-9]{6,6}$’.
    3. checkout2Why is the loaner needed? – We know who, we know which, now we need to know why.  I’m using the ‘go to section based on answer‘ function here.  If the user is borrowing a device because they forgot their device or their device isn’t charged, I don’t need any additional information and the form can be submitted.  If there is breakage however, I’m going to need more details so that we can supply that information to the helpdesk ticketing system.  If ‘broken’ is selected here, the form will be directed to a second section where the broken device’s serial, the category (keyboard, LCD, wireless), and a paragraph field to collect a short description of the problem can be collected.

checkin

  • Check-in form – This form has one question – “Which chromebook is being returned?”.  Again, because we know the details of who borrowed the device and why they did from their previous checkout, we can minimize the check-in process to this one question and let the script do the rest.  This form also uses the same data validation (regular expression, ‘^[A-Z0-9]{6,6}$’) to verify that the barcode being scanned is a valid serial number (or at least appears to be).

Now the Spreadhsheet…

The Google Spreadsheet contains sever sheets as follows:

  • AllDevices – This sheet contains all of the loaner devices.  Columns A-D should be populated with device details, one device per row.  Column E should be initially set to “Y” in each device row (to indicate the device is available, and column “F” should be populated with a 0 (zero) in each row, indicating it has not yet been checked out.  Columns E and F will then update as the devices are checked in and out (along with the remainder of the columns).
  • Checkins – This sheet is the destination for the Check-in form.  Data populated on this sheet as the system is used is pulled off to modify on the other Sheets as the functions run, but is ignored after that.
  • Checkouts – Same as above, only for the checkout form.
  • Students – This is a list of potential “customers” of the loaner system.  This data would likely come from an export from your student information system.  Columns A-D contain student information (populated by you) and columns E and F should be populated with zeroes.  E and F will increment as the system is used.  Column F will return to zeroes when the emailWeeklyReport() function runs, column E is cumulative and would need to be manually reset if you needed to reset your all time use counters.
  • StudentIndex – This sheet wasn’t part of my original plan, but became necessary as I moved beyond test data.  In testing, I was only looping through 10 student rows of data.  Performance was quick.  Once I populated the Students sheet with over 1600 students, searching row by row for an ID number match was just too slow.  I added this sheet to determine a subset of rows to search on the Students sheet instead.  This sheet is populated (automatically) by the indexStudentSheet() function described below.
  • Log – Each checkout and check-in are logged here.  You can use this sheet to get metrics from the system.  Errors (which are reported to the admin via email) are also recorded in the log and marked in red.
  • Totals – If a checkout occurs, the current date will be populated (automatically) in Column A if it isn’t already there. Column B will record the numbers of loaners for each date in column A.  I created this sheet to monitor day to day volume usage.

And Finally, the Scripts…

I’ve tried to comment the code as best I could, so if you are re-using this system (please feel free) you should be able to follow along.  This code could definitely be shrunk down to eliminate some redundancy (if I fix that later I’ll update this post).  I’ll highlight the top-level functions below.

  • setGlobals() – I’m using script properties to store the variables that another user might change if implementing this system.  This includes email addresses and thresholds, and is called when the forms are submitted.  It is really there to prevent another implementer from having to scrape the code to change variables.  This function is automatically called any time a form is submitted.
  • onFormSubmit() – This is the bulk of the functionality.  This function runs any time either of the forms are submitted.  It determines if the form submitted is a check-in or a checkout, updates the device sheet, logs the activity, etc.
  • emailWeeklyReport() – This function creates an email containing a list of users (students) who have used loaners X number of times that week, along with a Y long list list of users who have used loaners more than Z times per year.  These thresholds and the email address(es) to send the message to are defined in the setGlobals() function.
  • emailHelpdesk() – If a loner is distributed due to breakage, the details of that breakage are collected on the form.  Our help desk ticketing system allows for email ticket creation, so in that scenario, the details of the breakage are emailed to the help desk system, and a ticket is automatically created using this function.
  • emailAdmin() – There are going to be errors with the system, and this function reports those errors to a designated system administrator (via email).  A student may check out a device whose ID number isn’t found in the student table, a device could be returned that wasn’t listed as being loaned out, etc.  I’ve tried to handle any errors I could think of.
  • emailReceipt() – This function is called once a check-in or checkout is processed successfully.  It provides the end user (the student) a receipt for their usage of the system.
  • indexStudentSheet() – This function sorts the data in the Students sheet by ID number (ascending) and then indexes every X number of rows.  X can be determined manually by setting the useManualIndexDivisor global to ‘TRUE’ and definining your own value for manualIndexDivisor.  If useManualIndexDivisor is ‘FALSE’, this function will automatically determine this value based on the number of student rows.   Those ID numbers and their row numbers are stored on the StudentIndex sheet.  This data is used to determine the range of rows on the Students sheet that will be searched when looking up a student (for performance reasons).

Other Details

There are a couple scenarios I want to be proactive on avoiding.

  • Unauthorized usage – Because Google Forms can only be locked down to the organizational level, I can’t control who in the organization can fill out the form.  It is unlikely that this form would be used by anyone else, but I should do my best to protect the integrity of its use.
  • People manually interacting with the spreadsheet – Since just about everything is automated, really all anyone needs to have to monitor the system is read access to the spreadsheet.  Ideally, I want to make it difficult for even myself to edit anything so that mistakes are minimized.

I can take care of both of these issues by using service accounts.  Service accounts are accounts that a created for a particular function, and are generally unused otherwise.  By making a service account the ‘owner’ of the spreadsheet, scripts, and forms involved, the files involved couldn’t be modified (or more importantly deleted) inadvertently by the administrator (myself in this case).  I’ll create a service account, transfer the ownership of the folder, then give anyone who needs to see the spreadsheet (help desk staff) read only access.  If I need to edit anything, I’ll have to take an extra step to log into that service account to make a change.

I’ve also created a service account to fill out the form.  On the machine we will use to run the check-in and checkout form at the helpdesk, I’ll log into this service account.  Then in the scripts, I’ve made it possible to ignore any submittals that come from anyone but the service account.  Use of this feature is defined by ‘auseAuth’ and ‘authEmail’ in the setGlobals function, and is off by default.  If enabled, and an account other than the one defined by authEmail tries to fill out the form, their submission will be ignored, and the admin will be notified.

What to Do If You Want to Implement This

You will find a public share of the the 2 Google forms and the Google Spreadsheet (containing the scripts) here.  You’ll need to sign into a Google account to make copies for your use.  Below you’ll find a detailed procedure for getting a working implementation set up for your organization.

  1. 1After opening the link above, make copies of the 3 files. They should automatically go into the root of your Google Drive.
  2. In my testing, I get two copies of each form.  I’m not really sure why this is.  You’ll need to determine which two are correct, then delete the other two.
    1. Open up both of the check-in forms, then select the “RESPONSES” tab.  Mouse over the Google Sheets icon.  If it says2a “Create Spreadsheet”, this is not the linked form.  You can delete this form from your Drive by clicking the 3 ellipses in the top right corner, then clicking “Move to trash”.  Look at the other one now.  Go to the responses tab and mouse over the Google Sheets icon again.  It should say “View responses in Sheets”.  This is the correct Google Form.  You can close it for now.
    2. Repeat the same process for the checkout form.3
  3. You should now have the three necessary items in your Drive.  I recommend putting them in one folder for organizational purposes.
  4. Open your copy of the spreadsheet and go to the ‘Students’ sheet.  Change all the emails in column D to your email address so that you’ll be the recipient for any emails the system generates when you are testing.
    4
  5. From the Tools menu, select Script editor... The script should open in a new tab.  In the setGlobals function (about rows 10-40), set all of the email variables to your email address (for testing purposes) and save the code (File > Save)
    5
  6. Click the Select Function but6ton, then select “setGlobals”.  Click the play icon at left to run the function (shown at right).
  7. You should be prompted with an “Authorization required” dialog.  Click Review Permissions.  Review the required permissions, then click Allow (as seen below).
    7
  8. 8Now you’ll need to create the initial indexing of the ‘Student’ sheet.  Select the “indexStudentSheet” function from the list of functions, then hit the play button to run the function.  Take a look at the ‘StudentIndex’ sheet on your spreadsheet.  It should now look like the image at right.
  9. 9Now that the script is authorized and the initial index is built, we’ll need to setup triggers.  From the script tab, click Resources in the menu, then Current project’s triggers.  The dialog box should say “No triggers set up.  Click here to add one now”.  Click that.
  10. Set “Run” to “onFormSubmit”, and “Events” to “From spreadsheet”, “On form submit”.  This is telling the script to run the onFormSubmit function in the script whenever someone fills out the forms.10-12
  11. Click Add a new trigger.  Set “Run” to “emailWeeklyReport”, and “Events” to “Time-driven” and “Week timer”.  Then select the day and time you want your system usage report to go out (see emailWeeklyReport() in the functions section above for more info).  See above for an image of what your triggers should look like.
  12. Click Save to save your triggers.
  13. Now it is time to test.  I’ve populated the student list and the device list with 9 rows of data each.  As you can see, both ID number for the student and inventory tag for the loaner devices are 6 digit repetitions of 1 through 9 (for ease of entry while testing).
  14. 14Open up your copy of the checkout form, then click the preview button to open the published version of the form.
  15. Fill out the form with “111111” for both short answer questions, select “Device not charged” for the multiple choice question, then NEXT, then SUBMIT.
    15
  16. Go back to your spreadsheet.
    1. On the “AllDevices” sheet, you should see device 111111 is now marked unavailable, and checkout data has been populated at right.
    2. On the “Students” sheet, student 111111 should now have usage data to the right of their student data.
    3. On the “Totals” Sheet, you should have today’s date with a “1” at right denoting the number of checkouts that have happened today.
    4. Finally look at the “Log” sheet.  You should have a log entry recording the activity on the system.
  17. 17Now try checking the device back in.  Open your copy of the the check-in form and click the preview icon to view the live form.  Enter “111111” on the form, then click SUBMIT.
  18. Take a look at the “AllDevices” sheet again.  The device should be marked as available once again.  Then look at the “Log” sheet and notice the new row there.
  19. Finally, take a look at your email.  You should have a check-in and checkout email receipt from the system.

Wrap-Up

If you have read this far, you may be considering using this system.  Please feel free to do so (and hopefully improve it).  I recommend thoroughly testing (and understanding) this system (along with the other functions I didn’t run you through in this tutorial) before putting it into production.  I also encourage you to consider using service accounts with this for the reasons I stated above.

If you do end up using it or are stuck trying to implement it  (or just want to tell me what I didn’t think of, did wrong, etc.), please leave me a comment below and I’ll do my best to reply.  I’d also like to thank @knollmatt for his help with writing and testing some of the functionality.

Leave a Reply