Check out and check in

Scan to Web · Posted on

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 ][+

13 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; [email protected]
      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.

      1. Hello, I have the same problem as well. Were you able to figure out how to correct it. when I tried at first set to check in and out each of the tools, it works fine. But the second time I need to check it out because eventually it was scanned it again after being checkout-it always appear in status tab as “IN” I checked the name range carefully and nothing is wrong. Thanks to feedback on this matter.

  2. Good Afternoon, I stepped through the list and it works perfectly however the google api to generate a QR code tied to an individual item does not seem to be working properly.
    below is what I have copied into cell A2:

    =if(isblank(A1), “BLANK”, image(“https://chart.googleapis.com/chart?chs=150×150&cht=qr&chl”&SUBSTITUTE(A1,” “, “%20”)))

    the app scans the code however it does not register it as Item 1.

  3. Everything worked great on the first check-in and out but after that, if I checked out a device again it would say checked in instead of checked out, I have cleared the cache and recreated the name ranges but it’s still not working.

  4. Trying to see how we can implement this as a student pick up option. In the data collected in the app, can we add a timestamp?

  5. Hello!

    We tried this as well, and are having similar issues to previous commenters. Worked great at first, but then anything checked OUT showed up as IN on the spreadsheet. I followed to previous suggested steps of clearing cache and history as well as reformatting the date range names. Still having issues with it swapping the is in and is out functions on the STATUS sheet. Any Advice would be great!

    1. 50 barcode scans. Then you will need to purchase at $0.99 for one month of unlimited use or $9.99 for yearly unlimited usage. We also offer direct purchase licenses and private label options.

  7. I’m having the same issue as the others. I love the solution, but after an item has been checked out/back in more than once… it no longer works.

    My hope was to use this for tool management, as we have lots of guys with our tools (renovation company) and I’d like to know if a tool is checked out, and who has it if we need it!

    1. This is a great find. Thank you Kristin. We’ll publish a step by step blog about this soon too.

Comments are closed.