2022 W49 - Gizmos

Rows is now "Building in Public". Every week I'll post about one thing that happened! Check them all here.

---

We build spreadsheets, day in and out.

Some of them are cool challenges because they require creativity in solving them. Last week we had 2 small spreadsheets that were... kind of a pickle.

🤓 Nerd post ahead.

1️⃣ The operations tool - C.A.R.

The first tool was for an operations team in a factory. The job is quite simple:

1. Warehouse workers carry mobile scanners that are smartphone-like;

2. They want to check bar codes of items, 2 at a time, and check they're the same. If not, they want see an alert;

3. Then they want to move on to the next 2 items to scan fast.

Now in a spreadsheet, it's easy to compare two numbers. You just do =IF(first_cell=second_cell, some_alert, nothing). The problem is the other 2 requirements:

• Doing the alert that is visibile for a couple of seconds for the operator to see it;

• After the alert, clear up the cells automatically so that the user can move to the next scan automatically without having to click a button, or refresh the page.

For the first problem I had to be creative, as we don't have a DELAY() function (yet). I solved this by calling an API that returns the message.. after a certain delay! Note to self: There's always an API for anything!

For the second problem, I used the function CLEAR(row) that clears the data in a row (after the message with the delay is received ofc).

The tool is called C.A.R., which stands for Check, Alert and Reset. You can play with it and duplicate it in my community page.

2️⃣ A Time of giving - Secret Santa

The second tool is a Secret Santa creator. The task is also easy to describe:

1. Add a list of names;

2. Randomly assign each person someone else (to give a gift to);

3. A person can't give to the same person who get them, or to self.

To actually spreadsheet-code it, was a bit harder. I tried multiple approaches and settled with the simplest algorithm I could implement:

1. Get the list of people;

2. Randomize the list;

3. Then everyone gives to the next person.

This can be created with the following pseudo-code:

1. Add the list of people on a table.

2. Create a list of the people who can still be "givers", which you can create by removing the previous Giver from the same list in its previous iteration. If it's the first iteration, get the whole list.

   1. I used IF(Row()=2, to check if it's the first iteration, and in that case RANGE2JSON() to build the first list with everyone.

   2. For other iterations, I used PARSE() with a query selecting Name!=<previous Giver>.

3. Randomly select a person from that list, who's this iteration's giver;

   1. I used a PARSE() on the list computed in the step 2, selecting at random with a RANDBETWEEN() until the size of the list, size that I got with a length() parse.

4. That person will give to the Giver of the previous line. If it's the first line, give to the last Giver.

   1. For the first Giver, I got the person they're giving to (which is the last Giver) by using an [-1] index (last) on a parse.

   2. For the other Givers, I just selected the row above.

The result is this little Secret Santa tool! Play with it and copy away too!

---

I hope you liked this tools. DM us on twitter, Linkedin etc with more crazy challenges.

See you next week! Humberto