Managing Loaner Chromebooks Using Google



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.


  • 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.
  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)
  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).
  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.
  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.


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.

Mass Data Entry into Web Forms Using Google Apps Script

Recently I was presented with a scenario where a large amount of data that was stored in a spreadsheet needed to be entered into a system with no mass upload feature.  Since the alternative was having someone wear the paint of their keyboard copying and pasting, I needed to automate it.

Most systems provide a way to do this via the uploading of a CSV file.  Some small niche products either lack this feature or require the user to pay for the provider to add the data on the back end.  This system, which shall remain nameless, had this issue.  Here is the interface I was working with:


I have hundreds of names that need to be entered for each year, going back several years.  There were two problems I needed to solve.

Problem #1 – There are only 10 fields to enter data into.

Problem #1 is really easy to fix using the iMacros for Chrome extension.  iMacros allows you to record and play back interactions with a web browser (like clicking a button several times).  Here are the steps I used to get the number of text boxes I needed.

  1. Install the iMacros for Chrome extension.
  2. Go to the page that contains the web interface you need to interact with.
  3. Click the iMacros extension icon to open the interface.
  4. Click Record then Record Macro.
  5. Click the button in the web interface that you would rather not repetitively press (in my case, the “Add More Name” button).
  6. Click Stop.
  7. The iMacros editor will open.  You should have 3 lines of code.
    1. Line 1 is version information, ignore it.
    2. Line 2 tells the web browser to go to the page you are on.  Since we are going to loop this code, we don’t want it opening the web page every time.  To prevent that, simply delete this line.
    3. Line 3 is what “clicks” the button.
  8. Once you have removed line 2, click the Save & Close button.
  9. With the web interface of the system you are using, select #Current.iim (the default macro you just created) in the left column.
  10. Click Play Macro.  This should perform the click, and the result should be displayed (in my case, 2 input boxes are added).
  11. I want to add 100 more boxes however.  Because each click adds two boxes, I’ll need to run this macro 50 times.  Under “Play macro repeatedly”, change “Max” to “50” and press Play Loop.  The macro should run 50 times, and the desired result should be shown in the web interface.

Here is a short video of this in action:

Problem #2 – Get the information from the spreadsheet into the web form.

Here is what my spreadsheet looks like:


I have all the names I want to enter in column ‘A’ of a Google Sheet – all I need to do is automate their entry into all the fields I generated above.  The way I chose to do this is with a keyboard emulator.  I often use keyboard emulators to perform repetitive tasks – they allow you to write code that emulates the pressing of keys on your keyboard.  One example is a physical keyboard emulator I use (USB Rubber Ducky) to automate the process of enrolling Chromebooks.

In this case, I am going to use a software emulator.  WinKeySim is the emulator I chose for this project, there are several other freeware versions available.

In order for this to work, we need to create the list of commands to put into WinKeySim.  Based on WinKeySim’s syntax, the pseudo-code would look something like this:

1 open the command with ["
2 set x to 1
3 add the name from row x
4 if x != the last row
  wait for 200ms [!200] and send a [tab] to go to the next field
  repeat from 3
5 else this is the last row so close the command with "]

I won’t go over the intricacies Google Apps Script here.  I will tell you it is a very powerful tool, and if you use Google Apps and have a need to automate, it will prove very useful to you.  Based on my spreadsheet above, here is the code I used to build the command I used with WinKeySim.

function myFunction() {
 //get the active spreasheet
 var ss = SpreadsheetApp.getActiveSpreadsheet();
 //get the sheet named 'Sheet1'
 var sheet = ss.getSheetByName("Sheet1");
 //get the row number of the last row
 var lastRow = sheet.getLastRow();
 //create a new google doc to store the emulator commands in
 var doc = DocumentApp.create('Honor Roll Students');
 var body = doc.getBody();
 var text = body.editAsText();
 //start the document with [" (syntax for WinKeySim)
 //loop through each row of the spreadsheet
 //get the name in column A
 var name = sheet.getRange(i,1).getValue();
 //append the name to the sheet
 //if we are not at the last row, add a 200ms delay and
 //send a tab to change fields (syntax for WinKeySim)
 if(i !== lastRow){
 //if this is the last row, end the docuemnt with "]
 if(i !== lastRow){

When executed on the spreadsheet of names, this will spit out a new Google Doc into my Google Drive containing the emulator command I will use to input the data.  It will look something like this:


I can then paste that command into WinKeySim and the data will be entered automatically.  Check out the video below to see it in action.

If you have any questions about this, please leave a comment below!