Writing a Simple QR Code Stock Control Spreadsheet

At Theatre, Film & TV they have lots of equipment they loan to students, cameras, microphone, tripod etc. Keeping track of what goes out and what comes back is a difficult job. I have seen a few other departments struggling with the similar "equipment inventory" problems.

A solution I have prototyped uses QR codes, a Google Spreadsheet and a small web application written in Apps Script. The idea is, that each piece of equipment ( or maybe collection of items ) has a QR code on it. Using a standard and free smartphone application to read QR codes, the technician swipes the item and is shown a screen that lets them either check the item out or return it.

The QR app looks like this.

The spreadsheet contains a list of cameras. It has links to images and uses Google Visualisation tools to generate its QR codes. The spreadsheet looks like this.

The Web Application

The web application, which only checks items in or out and should be used on a phone in conjunction with a QR code scanner iphone app, like Scan.
The application is written in Apps Script looks like this below. It shows the current status of that particular camera and lets you check a camera out.

Building This Application

There's a strange "chicken and egg" situation about building this application. In order to create a QR code that would lead to an individual camera, you first need to "Publish" your web app so that we have the URL to your application. Once published we can use the URL for our application and start populating the spreadsheet with cameras.

Populating the spreadsheet

First, I added some URLs to pictures of cameras, and titles and descriptions to my spreadsheet.
Next, in the spreadsheet in cell C2 you can see the formula...

=CONCAT("https://script.google.com/a/macros/york.ac.uk/s/YOUR_URL_GOES_HERE/exec?id=",A2)

… this formula creates the URL that is appended with "?id=231" etc and will be used to generate a unique QR code image.
In cell D2 I have the formula …

=make_QR(C2)

… this formula calls an Apps Script function that looks like this...

function make_QR( url ) {
  /*
  Note: If you add a number to the domain, as per the documentation, the https starts erroring. I think.
  Note: URLs are limited to 2K in length 
  From: https://google-developers.appspot.com/chart/infographics/docs/overview
  */
  
  var size = 150 // The height and width needed.
  var encoded_url = encodeURIComponent( url )  
  var image_url = "http://chart.googleapis.com/chart?chs=" + size + "x" + size + "&cht=qr&chl=" + encoded_url
  return image_url
}

… this takes our unique URLs and uses Google Visualisation Tools to create an image for the QR code, one of which looks like this.

We can display the image in the spreadsheet using the formula...

=Image(D2,1)

Deprecation Warning!

Although the Charting service is being deprecated, it's probably good to use until April 2015. See https://developers.google.com/chart/terms. I imagine it would be easy to generate and download all the QR code images you need, finding an alternative QR creation API in a few years time.

Creating The Web Application

The first part of the application gets the data from our spreadsheet, finding the relevant row. It then builds an application using UiApp objects for the values we want to display.

function doGet(e) { 
  if (typeof e.parameter.id  == 'undefined'){
    return no_id(e) // The URL doesn't have an ?id=345 on the end! 
  }
  
  var id  = parseInt( e.parameter.id ) // This is the id of the row in the spreadsheet.
  
  // Get the data from the spreadsheet and get the row that matches the id
  var this_spreadsheet_id = ScriptProperties.getProperty('this_spreadsheet_id')
  var ss = SpreadsheetApp.openById(this_spreadsheet_id)
  var sheet = ss.getSheetByName("Sheet1")
  var range = sheet.getDataRange()
  var last_row = range.getLastRow()
  var last_column = range.getLastColumn()
  
  for(i = 2; i <= last_row ; i++){
    var this_row = sheet.getRange(i,1 , 1, last_column)
    var values = this_row.getValues()[0]
    var row_id = parseInt( values[0] )
    if ( row_id == id){
      var title = values[5]
      var details = values[8]
      var status_txt = values[7]  
      Logger.log( "STATUS: " + status )
      var image_url = values[4]
      }
  }
  
  // Create an application
  var app = UiApp.createApplication().setTitle("Check in/out").setHeight(250).setWidth(400)
  
  // Create the layout
  var grid = app.createGrid(8, 3 ).setStyleAttribute(3, 1, "width", "420px").setCellPadding(5).setBorderWidth(0).setId('grid')
  grid.setStyleAttribute("margin-left", "auto").setStyleAttribute("margin-right", "auto")
  grid.setStyleAttribute("margin-top", "100px")
  
  
  var image = app.createImage(image_url).setWidth(100).setHeight(100).setStyleAttribute("margin-left", "auto").setStyleAttribute("margin-right", "auto")
  grid.setWidget(0, 1, image)
  ... and so on...  and so on ... 

We then need to create the buttons, like so...

  // Check in button
  var handler = app.createServerHandler('check_in').addCallbackElement(grid)
  var check_in_button = app.createButton('Check in', handler).setStyleAttribute("font-size", "24px")
  check_in_button.setId("check_in_button")
  grid.setWidget(4, 1, check_in_button)

… Later in the code we create a function ( or handler ) for the button. It updates our spreadsheet with CHECKED IN or CHECKED OUT, and also updates a few interface elements like this....

function check_out(e){
  var id = parseInt(e.parameter.id)
  Logger.log( "id: " + id )
  
  try{
    // Update the QR spreadsheet
    var this_spreadsheet_id = ScriptProperties.getProperty('this_spreadsheet_id')
    var ss = SpreadsheetApp.openById(this_spreadsheet_id)
    var sheet = ss.getSheetByName("Sheet1")
    var range = sheet.getDataRange()
    var last_row = range.getLastRow()
    var last_column = range.getLastColumn()
    
    for(i = 2; i <= last_row ; i++){
      var this_row = sheet.getRange(i, 1 , 1, last_column)
      var values = this_row.getValues()[0]
      var row_id = parseInt( values[0] )
      
      if ( row_id == id){
        var title = values[5]
        //var status_txt = values[7]
        var range = sheet.getRange(i, 8)
        range.setValue("CHECKED OUT")
        Logger.log( "Spreadsheet: CHECKED OUT")
        break
      }
    }
    
    var app = UiApp.getActiveApplication()
    
    //Update infoBox
    var infoBox = app.getElementById("infoBox")
    infoBox.setVisible( true ).setText( title + " has been checked out")
    
    var status = app.getElementById("status")
    var status_css = {'background': 'red', 'color': 'white'}
    status.setText("CHECKED OUT").setStyleAttributes(status_css)
    
    var grid = app.getElementById("grid")
    
    grid.setStyleAttribute(3, 1, 'background', 'red')
    
    grid.setStyleAttribute(5, 1, 'visibility', 'hidden')
    grid.setStyleAttribute(4, 1, 'visibility', 'inherit')
    
    
  }catch(e){
    Logger.log(e)
  }
  
  return app
  
}

… I used CSS to show and hide the non-relevant button. It's a bit clunky, but it does the trick ( please make better code available if you know how, .setVisible(false) didn't seem to work ).

And To Finish...

I added a listing application, if somebody might accidentally view the app without using one of the unique URLs in the spreadsheet. It looks like this. It displays all the cameras and might need some pagination if there were too many to display easily.

Generating a Google Document To Print Off the QR Codes

It was easy to take the QR codes and make a Google Document to easily format and print off onto labels. Like this...

function make_a_document(){
  var this_spreadsheet_id = ScriptProperties.getProperty('this_spreadsheet_id')
    var ss = SpreadsheetApp.openById(this_spreadsheet_id)
    var sheet = ss.getSheetByName("Sheet1")
    var range = sheet.getDataRange()
    var last_row = range.getLastRow()
    var last_column = range.getLastColumn()
    
    var doc = DocumentApp.create("QR Example Printable Document")
    var body = doc.getBody()
    //var table = body.appendTable()
    
    for(i = 2; i <= last_row ; i++){
      var this_row = sheet.getRange(i,1 , 1, last_column)
      var values = this_row.getValues()[0]
      
      var id = parseInt( values[0] )
      var title = values[5]
      var image_url = values[4]
      var qr_code = values[3]
      
      // Get the images of the cameras and the QR codes
      var response = UrlFetchApp.fetch(qr_code)
      var image_blob = response.getBlob()
      
       var response = UrlFetchApp.fetch(image_url)
      var qr_blob = response.getBlob()
      
      var paragraph = body.appendParagraph('')
      var text = paragraph.appendText(id)
      text.setFontSize(24)
      
      var image_paragraph = paragraph.appendInlineImage(image_blob)
      image_paragraph.setHeight(150).setWidth( 150 )
      
      var qr_paragraph = paragraph.appendInlineImage(qr_blob)
      qr_paragraph.setHeight(150).setWidth( 150 )
      
      var paragraph = body.appendParagraph(title)
      paragraph.setFontSize(12)
      
      body.appendHorizontalRule()
      
      
      }
 }

… The Google document with QR codes in looks like this.

… and the application on my iPhone looks like this...