Check out and check in

By VERY POPULAR request we want to present a check in / check out system using Scan to Web and Google Sheets.

FORMULAS

For quick reference; here are the formulas used.

CREATE BARCODE

=if(isblank(A1), "BLANK", image("https://chart.googleapis.com/chart?chs=150x150&cht=qr&chl="&A1))

STATUS TAB 

=IF(ISBLANK(CHECKEDOUT),"--NA--", IF(COUNTIF(SCANNEDIN, CHECKEDOUT)=0, "OUT: "&CHECKEDOUT & " STILL OUT " & OUTWHO,CHECKEDOUT&" IS IN"))

STEP BY STEP

Create CHECK OUT form

  1. New Google Form
  2. Title: Equipment Check Out
  3. Short answer: YOUR NAME, required
  4. Short answer: BARCODE, required
  5. Theme Change color to RED
  6. Click Responses
  7. Click Create spreadsheet
  8. Click Create new spreadsheet
  9. Use the default or change the title to “Check in and out”
  10. Click SEND button
  11. Click the Link button
  12. COPY and keep is a safe place, like another Doc or Notepad

Create CHECK IN form

  1. New Google Form
  2. Title CHECK IN
  3. Short answer: BARCODE, required

(NOTE: only the 1 field is needed; we’ll get the WHO from the CHECK OUT)

  1. Theme Change color to GREEN
  2. Responses <- THIS IS VERY IMPORTANT
    1. Click Create Spreadsheet button
    2. Select existing spreadsheet
    3. Select Check in and out from step 9 in CHECK OUT form
    4. Note there are 2 TWO tabs in the Check in and out (Responses) spreadsheet
  3. SEND
  4. Link
  5. COPY and keep is a safe place, like a Doc or Notepad

Create a Google Site

  1. New Google Site
  2. Give the site a good title
  3. Insert, select Button
    1. Name: CHECK OUT
    2. Link: the link from step 12 in Check Out above
  4. Make button wide
  5. Insert, select Button
    1. Name: CHECK IN
    2. Link: the link from step 8 in Check In above
  6. Make button wide
  7. Click Publish… Publish
  8. Click View published site
  9. Copy this link
  10. Create barcode of this link
    https://berrywing.com/barcode
  11. Setup scan to web with the Google Site as the home page

Setup Scan to Web

  1. Start the Scan to Web app on your iPhone, iPad, or Android device
  2. Tap the gear button
  3. Tap the barcode scan button in the upper right above Home Page
  4. Scan the Google Site URL barcode
  5. Exit settings
  6. Tap the HOME button on the bottom toolbar
  7. Tap the CHECK OUT button
  8. Scan a few barcodes to get some data into the spreadsheet. This will make creating the Status tab easier.

Status Tab

This is the best part. The reason why you probably need the Check in / out system in the first place. A summary of what is still out and who has it.

  1. Open the Google Sheet with the Google Form responses; 
  2. Open the Check Out tab
  3. Select ALL of Column B “Your name”
  4. Click Data… Named Ranges…
  5. Title this named range as OUTWHO
  6. Select ALL of Column C “BARCODE”
  7. Click Data… Named Ranges…
  8. Title this named range as CHECKEDOUT
  9. Open the Check in tab
  10. Select ALL of Column B “BARCODE”
  11. Click Data… Named Ranges…
  12. Title this named range as SCANNEDIN
  13. Add a third tab with the title STATUS
  14. Copy and paste this formula into cell A1 of the STATUS TAB

=IF(ISBLANK(CHECKEDOUT),"--NA--", IF(COUNTIF(SCANNEDIN, CHECKEDOUT)=0, "OUT: "&CHECKEDOUT & " STILL OUT " & OUTWHO,CHECKEDOUT&" IS IN"))

  1. Grab the handle on the cell
  2. Drag the handle down several rows; at least as many as there are items to scan in / out
  3. Report is done.

You can be creative now; modify the formula. Also use Conditional formatting to make the status report easier. Sort by column A to make only the OUT items appear at the top.

Scan to Web
Founder and Developer of Scan to Web. I wrote my first program on an Apple ][+

2 thoughts on “Check out and check in”

  1. Testing this out for a small library system. I like the concept. Easier than I thought it would be. Worked great at first, but then anything checked OUT showed up as IN on the spreadsheet. In addition to this, anything pasted from the clipboard into the barcode creator (i.e., pasting the book title in the column next to which I create the QR code instead of typing it in) would cause an error message in the scan-to-web app later on. This is not that big a deal, but it’s another way for things to go wrong… especially if I have volunteers processing new books. Any idea on how to fix the “IS IN” bug?

    1. Hi Ben, thank you and we are glad you found the procedure useful. It’s not perfect, our goal is to have it serve as a primer for your own customizations.
      Create a Scan to Configure barcode and send it to us; softwaresupport@berrywing.com
      We’d like to see how you have everything configured.
      As for the IN and OUT discrepancy, check the named ranges. You might have to clear your web browser cache then recreate the named ranges again. This could just be a quirk of Google Sheets as we’ve seen similar unusual behavior creating complicated sheets like this.

Leave a Reply

Your email address will not be published. Required fields are marked *