Mirrors of confusion – Check Out/In app fail so far

I saw this great YouTube video about QR Attendance Using Google Drive with QR Codes Generator Add-ons:

I thought it was a great workflow and also an elegant solution. The autoresponse from scanning the QR code to fill out the form. How easy is that?

Then I got to thinking how I’d use it. I also started to upload a few Google Sheets Add-ons to test them out, and one that I liked was a Check Out /Check in form for a set of things. When you checked something out it removed it from that part of the form and shows it on the Checked Out part of the form. When returned it puts it back so that it can be checked out again.

Quite a cool add-in. It also populates a Google Sheet with the data. 2 columns one saying what has been checked out and by whom,. Then when its returned it shows it in the ” Check in Item ” column, along with who checked it back in.

I thought, this would make a great pp for say, a site where equipment was being checked out and in.

You could do some smart things with this like have a button that took you to the form. Also , on checking something out, an automatic email could go to the company telling it that it had taken equipment out, and was therefore responsible for it. Then when it was returned an email to the company’s project manager confirming that it had been returned.

You could also have a safety checklist for that item to check its condition so that you knew it was safe (eg drill with raw wire).

Also in NZ they Tag electrical equipment to say that items have been inspected within a specific time frame, just to ensure that wiring and power tools gt a regular safety check.

CheckItOut Google Form add-in

This is a cool add-in. It allows you to have a list that you create (multiple Choice/Tick boxes/Dropdown). It then creates 2 lists, so that the items can be shuffled between them.(all items start in, then when checked out they move to the other list, so the items that move across are no longer available in the Check Out column). So if you have Hammer 1 & Hammer 2 and you check out Hammer 1, then only Hammer 2 is available for someone else to check out until you have returned Hammer 1, at which time it becomes available for someone else to check out.

Another elegant part of this Add-In is that you can have several lists. This works in with my idea of Site equipment, as you’ll have the item of equipment, eg a Drill, and you will also need some safety equipment, eg Safety Goggles, so you can have a second checkout list.

Another issue is multiple items to check out in the same list. The Equipment store is on the ground floor and you are working on level 10, so you do not want to keep coming back for different items, rather you want to check out multiple items at the same time. In the forms there is an option for this using CheckBoxes, so that is handy, although it then displays a very long list. It may be better to break into multiple forms to navigate more easily.

This brings through the issue of a very long form. A way to manage this is to break it down into sections, so that small chunks can be managed more easily.

There is also an overview page in the Form (in Edit mode) showing data about the form

Back in the Spreadsheet

The plan!!!!

In the Form, there are the 2 lists, the in list and out list. As soon as you check something out it appears on the out list and disappears from the in list (sometimes you need to refresh form to make items appear on the 2nd list). Great. So I want to replicate this in the Sheet. I have original list, and if I check an item out I want that item to disappear from the initial list, just like in the form.

This would then allow me, in the Glide App to have an IN list and an Out list and have switches to move things between the 2 lists.

Very hard to do though.

The form creates a tab

The add-in/form creates a tab to send the data that is checked-out/in to. It is a new row each time. If you have other check-out/in sections they will also land on the sheet.

I of course, had to make it more complex. If you wanted to take several things out at the same time then all those items go into one cell. This is similar to the issue in the Photo upload App. See below:

So we have to use =SPLIT(E2, “,”) to break these out into separate items in the adjacent cells to the right , so we now have, instead of one column of data a few. I decided to limit it at 4. This was a bit of an issue with trying to get it back into one column to review.

The count

If you see on the sheet above, there are 2 columns Check out items, check in items. So if you have the original list of all the items in the list (all checked in) then something is checked out, you can look at that first item and delete(move) it out of the list. Fine, so far. But then you take another item out , and then another, then you return the 3rd item, then maybe you return the first item. Its all rather messy. The 1st item on the return list may not be the first one you took out, so how are you going to keep a record of what is out/in on the original list as it changes, also remember that there are multiple columns now, as someone takes out 3 items and returns only 2. All very messy. But wait, there is a way.

First you have to split your first column, to make sure you have one discrete item in an individual cell. Then you have to go to another sheet and pull all those items back into a single column (you cannot do it in this tab as there will be a new row entry of something taken out or returned, and as you have possibly multiple items on the same row, it follows that when you pull them back into one column then that column will be longer than all the existing rows so far, so if its a longer, as soon as a new row is created you will mess up the data. So new tab to work in.

So messy formula in new tab calling the data from the form tab : =Arrayformula(if(ISBLANK(CoCiE!A2:A),””,Split(CONCAT(CoCiE!B2:B,CoCiE!C2:C),”,”, TRUE,TRUE)))

We have to use the if(ISBLANK(CoCiE!A2:A), with the Arrayformula or it propagates down the whole sheet. This will mean that the next new row for a new form entry will be at the end of the sheet, 100’s of rows down. We do not want that.

So after dragging the data across to a new tab we can now order it into one column:

={filter(G2:G, len(G2:G)); filter(H2:H, len(H2:H)); filter(I2:I, len(I2:I))}

So the filter grabs all the data in the 1st column, counts the number of Non Empty rows in that column and populates the new column with that number of entries, then it goes to the next column over and does the same and on until its finished. So we end up with one column of data.

We can either do this twice, one for outgoing items and one for incoming items, then append one onto the other.

Count odd/even number of items – if odd, still out, if even then returned

What we want to do is get the count of each item. If its an odd number, then its still out, if its an even number then it has been returned. This is the case when an item has been returned then taken out again, its count will be 3 (so still out).

Not so fast, that sounds too easy. One of the issues is where there are multiple items all taken out together, there is a “comma” and a “space” before the next item. As we are splitting on the comma, the next string is the next item but with a space in front of it. So that string does not match anothe of the same name without the space. So we have to clean the column with : =ArrayFormula(TRIM(J2:J)) which will remove the “space” at the start of the word (I couldn’t get this to work in combination with Split, and split is supposed to have switches that will remove the space but I couldn’t get that to work either).

So, I use a query next: =QUERY(K:K,”Select K, count (K) group by K”,1)

This takes the column, counts the number of times an item occurs and shows a new column with the count (see below)

So, with that, we can use a formula to show true/false for if its odd or even.

=ARRAYFORMULA(if(isblank(M2:M),””,(ISODD(M2:M))))

and from that can then make a list of only items that are out :

=ARRAYFORMULA(if(isblank(N2:N),””,(IF((N2:N)=False,””,L2:L))))

So we now have a list of items that are still out. I tidied the column so no gaps by using: =UNIQUE(O2:O)

So at this point 2 columns, original list and items that are currently out. Then the wheels fell off the trolley!!

A tortuous process, but now have original list and items to be deleted. In searchjes, I can find lots of examples of matching items in 2 columns, but few (and I coudn’t get them to work) for items only in one list.

These are the 2 formulas I was using, I could et neither to work:

=QUERY(A2:Q50,”Select Q,A where A != Q “,0)

=IFERROR(ArrayFormula(vlookup($O$2:$O,$A$2:$A,1,0)))

So I couldn’t get my list of only items still remaining.

Then I reflected on the process, that was hard work to just replicate what was in the CheckItOut add-in and I still hadn’t got to where I wanted to be. So I thought, too complicated, I need another method for such a basic thing.

And I thunked some more and in GlideApp for Defects I take from tab a row and add a row in another Tab.

Wait, wasn’t that the point of the exercise?

End comment

This was a classic spiraling down to crash. The formulas started to get more complicated and smaller increments in the steps and I kept on plugging away at it. I was trying to use the formulas in Google Sheets. I didn’t want to revert to scripts.

But, I think, maybe Scripts are the way to go on this one. And I wasn’t even thinking about doing this app, its only because I saw the cool CheckItOut add-in that I decided to play with it. Munch, munch. An eater of time.

The lesson? I think KISS. If its getting complicated then it could end up messy, buggy and unreliable. The idea is simple so why is it difficult? Not sure, I’ll have to think about it.

Anyway, an abject failure, apart for some learning about some formulae I didn’t know and Query too in Google Sheets.