Check in / out Google Sheets

using google sheets barcodes and the app scan to web we can create a powerful check in and out spreadsheet here in the app scan the web jerry is going to check out the hammer drill now that he's done he'll check it back in so here we have a bunch of barcodes and it to make to make barcodes is actually pretty easy in google sheets so the first thing we want to do is in this one cell right here uh add your add your item so let's just say a mobile printer okay so now we have this value in this cell so we want to create a barcode for that so we'll take this other cell and we'll just drag that down and put that formula in there and what this formula does is it shows the word blank if the first cell is empty if it's not blank if there's something there it'll create an image using this google api to create a barcode all right the first thing we're going to do is create our forms and we're going to create the checkout form first so we're going to click on new and create a google form it's possible your google form choice is in the more sections so just go ahead and click on google forms equipment checkout give it its full name the first question we're going to make a short answer and we're going to say your name because we want to record who is checking the equipment out we'll make it required we'll add another question here make that barcode make that also short answer it's also required let's uh be a little creative here we'll change the theme to red because we're going out with it it's a it's that direction so we'll change the theme there i want you next to click on responses and click on the create spreadsheet response because that's what we want to do we're going to create a spreadsheet to handle all the barcode scans and we'll go ahead and click on create all right here's our spreadsheet ready for the barcode scanning the to receive the barcode scans so we'll return back to our form and up here we'll click on send we'll click on the link button and we're going to copy this link for now and we're just going to save it we're going to save it over here until we need it later check out all right we'll keep that there for safekeeping next we're going to go ahead and create our check in form i'm going to call this one the check in and we only need the barcode we don't need the name because we're going to get that from the equipment checkout so there's our barcode let's change the theme here too let's make it a different color we'll make this one make this one a nice green there we go and we'll also make that required now here's where it's a little bit different because we want to click on responses we also want to create on create spreadsheet but this is important we want to select an existing spreadsheet so we'll go ahead and hit select list of spreadsheets will appear and we want to use the spreadsheet from our other form and we got it right when we see that there's two tabs form responses one will be the checkout and form responses to will be the check in so this is uh this is what we want to see when we've selected that so now it's uh ready also ready for responses we're going to go also click on send click on the link and hit copy and same thing we're going to put it over here check in form link we'll save that here for when we're ready all right to get ready for scanning we actually need to create a google site to make things a little bit easier so we're going to go ahead and click on new and we'll go down to more and we're going to click on google sites this will be a website we're going to use for our equipment check in and out and check out so we're going to call it equipment scanning and scan in out for our title now down here we're going to actually add a button so we're going to click on this button and we're going to call it check out and under link we're going to go ahead and back to our important links we're going to grab that link from our checkout form and put that in there and insert it and we'll make it nice and make that button nice and wide there we go we're going to add another button and you guessed it check back in we'll go back over to important links we'll get the form link for our check in and we'll also make that nice and big so there is our google site now what we want to do is click on publish confirm we can have the right website here and click on publish and then lastly we can just hit the view button down here on the bottom of the screen or we can also hit this drop down list and go view publish site and here we are and it'll be important now to grab this url from the top so we'll go ahead and put that in links here and we'll call it the home page all right we're ready to go we'll go get the app set up for barcode scanning now all right we're going to go ahead and get the app scanned web setup so the first thing we're going to do is grab our link our homepage link we'll go to the barcode generator site here on barrywing.com and paste our url in there and click on qr code and now we'll go over to app and we'll add that in there tap the gear button on the bottom toolbar then the qr button in the upper right scan in the new home page url we're ready let's check out a few items to get started this will also make creating the status tab easier all right we have our couple sample barcodes scanned in here now we need to add a couple ranges to our spreadsheet so we're going to click on column b and we're going to click on data and then named ranges and we're going to call this one the out who because this is our list of names that have checked equipment out next we're going to add a named range to this column so data and then named ranges and this will be checked out and then lastly we'll go back to our first our check in form and we'll add a name range here data named range and this will be scanned in so what we've done is we've added name ranges to each of those each of those three columns that are most important we're going to go ahead and add a third tab which we're going to rename to status because this is our summary of where things are at okay we're going to add the formula up here in a1 for the items that are out so we're going to just go ahead and paste that in there and we're going to go ahead and scan this down and here we go we have a a list of things that are still out and this formula is somewhat complicated but uh we'll see what all it says it's first saying is uh if if there's nothing scanned in or scanned out just put this not available up here next it's asking is there a count of items in the scanned in over the scanned out if it's equal to zero that means it's still out so we want to show that it's out and show the who or the name who has it and checked out and in this section second part here is if it's checked back in we'll show that it's back in another thing we can do to help out here too is we can go to format and then go to conditional formatting and change the if text starts with out because that's what's in there we're going to change that color to red so that way we can see at a glance the stuff that's still out let's go ahead and scan one of these items back in and there it is it shows that that hammer drill is now in let's go ahead and scan in tablet 2 as well again here we are in real time showing that that tablet is in so at a glance we have uh we have still one item out and we know who it was it's andy who still has a tablet one thank you please let us know if this works for you and if you have improved on our simple design

Channel: The Mobile Sales Engineer

Views: 173,257

Keywords: check in, check out, barcode, google sheets, asset, tracking, attendance, checkin, checkout, tool crib, track equipment, where is, who has

Id: 0PtnVvrVTCY

Length: 12min 5sec (725 seconds)

Published: Wed Dec 23 2020