The thing I really like about spreadsheet applications (Microsoft Excel, Apple Numbers and Google Sheets) is that it’s programming in a different kind of thinking: instead of the standard procedural programming (things happen one after the other) or the event-driven Object-oriented programming, spreadsheets are having all the calculations done at the same time to create a single state. There are no loops or any kind of code-branching, no user-interaction during the computations, it’s just – “given this input, this is the new state”. Of course, when cells refer to one another you can simulate iterations but loops are forbidden – as you can refer back to a cell that was already set. It require a different mindset.

The history of spreadsheet is fascinating on it own and it’s worth listening to Dan Bricklin’s story on Ted, and you can dig even further with Joel Spolsky‘s “You suck at Excel” talk. Excel is (or at least was) amazing and **product designers should learn from it how to create a proper learning-curve application** where anyone can do something with it.

And it was only natural that other major players will thrive to have their own spreadsheet and although Excel is still a major player I think it has lost its lead to google spreadsheet – which is **online**, **free**, (and therefore easy-to-share).

I recently had some fun working on spreadsheet for a friend, who is a dance teacher. He teaches 3 levels (beginners, intermediate and advanced) dance classes on a weekly basis and so – he keeps track of his students and their progress in a simple spreadsheet. Each row is a student and each column is a day; In each cell he will write which class the student took “A” for advanced, “B” for beginners and I for intermediate.

Table was pretty straightforward so he wanted to count how many classes did each student attend and how many attendants he had each day. it’s slightly tricky because each day actually had multiple entries and each class should be counted separately. Of course, we can have each class occupy its own column but it would mean the table will grow 3 times its width. so how can we still count each class? simple, by concatenating the entire row into a single cell and then counting the characters: `=len(concatenate(C3:C18))`

.

If we wanted to count how many students attended the beginners class, that’s also easy, we’ll use the same formula but this time will remove all the characters that are not “b” using regular expressions: `=LEN(REGEXREPLACE(concatenate($C7:$I7),"[^B]",""))`

. And if we want to know how much money did he make each day will just multiple the number of attendees with the fee. Super-simple.

Things got more complicated when he started selling discounted blocks of five entrees for a cheaper price. The question I was faced is how to deal with that. Or more actually, I was asked to add a column that will tell us how many discounted entrees the student hadn’t already use. After some thoughts I couldn’t figure how to simply tell each class how many leftover unused block-entries I have left, so I created another page (called “db.blocks” where I could store additional information per each student’s session – namely, what’s the left-over. Let’s say each time the student buys a block will mark it as “@” and using a similar technique as before will count all the “blocks” characters (times 5, number of entries per block) and deduct the entrees that took place. We’ll also read the leftover entries from previous class and make sure we don’t go below zero (no unused block entries). We’ll end up with this: `=Max(0,C7+LEN(REGEXREPLACE('attendance'!D7,"[^@]","")) * 5 - LEN(REGEXREPLACE(attendance'!D7,"[^BIA]","")))`

.

That worked well for quite some time; but then he decided to introduce different sorts of blocks – longer (with a bigger discount); shorter; and concession (for university students). so looking back at the previous problem, we had to retain to pieces of information per cell – the number of unused entires and the original input. Now, it’s no longer enough to keep the number of unused entries, but we also need to retain their type. so instead of a single number, we should remember an array of types – or better yet – the cost per entry. Let’s say normal fee is 6£ but I bought a block of 4 entries for 20£ so we should retain “5,5,5,5”.

Our Formula was quite complicated to begin with but now, with different kind of blocks, in different sizes and different prices, it became quite unmaintainable. And here is something that is missing from modern day spreadsheets. **I wish I could encapsulate my entire formula into a simplified function**. would it be just great if I could enter in each cell in my “db.blocks” `=process(prevCell,'attendance!c7',blocksDecRange)`

and it will do all the magic behind the scene? To be fair, Google Sheets does that but the function must be super simple and run a small number of times, whereas . I need to run to my formula on every cell in a very table.

But Sheets does have a nice scripting support (so does the other spreadsheets), so instead I used the `onEdit(event) {...}`

hook that happens whenever the user changes a cell. I first verified the targetCell is within the range of my attendance table and then processed it and wrote the remaining entries in one table and the amount spent on that day in another sheet. This allows me to super-easily calculate how much money did the teacher make in a day and how much money each student spent. When reading each day’s set of character, we’d try to figure whether this character represents a block and if so we’d add to our “credit” stack the entries’ costs; else we’d remove entries from the stack and if the stack is empty – it means the student will pay a full price. Here’s the complete code, written in some old version of javascript:

function onEdit(e){ var spreadsheet = e.source, range = e.range, rowIndex = range.getRow(), colIndex = range.getColumn(), blocks = getBlockMap(spreadsheet), attendanceTable = spreadsheet.getRangeByName('attendance'), offsetCell = attendanceTable.getCell(1,1), offsetRow = rowIndex - offsetCell.getRow(), offsetCol = colIndex - offsetCell.getColumn(); // Exit if we're out of range if ((e.range.getSheet().getName() !== 'נוכחות') || (rowIndex < attendanceTable.getRow() || rowIndex > attendanceTable.getLastRow()) || (colIndex < attendanceTable.getColumn() || colIndex > attendanceTable.getLastColumn())) { return; } onEditUpdateCell(spreadsheet, attendanceTable.getValues(), offsetRow, offsetCol, blocks); } function onEditUpdateCell(spreadsheet, values, row, col, blocks) { var dbEntries = spreadsheet.getRangeByName('db.entries'), dbSpent = spreadsheet.getRangeByName('db.spent'), prevCredit = col ? getCredit(dbTabs, row, col - 1) : []; updateCell(prevCredit, values[row][col], dbEntries.getCell(row + 1, col + 1), dbSpent.getCell(row + 1, col + 1), blocks); } // get previous class unused entries as an array function getCredit(dbEntries, row, col) { var value = dbEntries.getCell(row, col).getValue(); if (value === '') { return [] } return value.split(','); } function getBlockMap(spreadsheet) { var map = {}, table = spreadsheet.getRangeByName('blocks').getValues(); for (var i=0; i< table.length; i++) { map[table[i][4]] = { size: table[i][2], price: table[i][0] }; } return map; } function updateCell(credit, value, tabsTarget, spentTarget, blocks) { var spentToday = 0 for (var i=0; i< value.length; i++) { var c = value.charAt(i), block = blocks[c]; if (block !== undefined) { addToCredit(credit, block.size, block.price) } else if (credit.length) { spentToday += credit.pop(); } else { spentToday += blocks['fullcost'].price; } } tabsTarget.setValue(credit.join(',')); spentTarget.setValue(spentToday) return credit; } function addToCredit(arr, times, value) { while (times--) { arr.push(value); } }

So in conclusion, Spreadsheets are super powerful tool and I encourage you to try use them when you want to display your information and the manipulate it easily. It might even be a good introduction for programming, I reckon. Scripting for spreadsheets, at least the way I see it mean going beyond the normal capabilities of the application, but that’s what so great about it – that you **can** add that missing functionality that will help you make you data useful for you.

Finally, for other aspects of spreadsheets you might be interested in Matt Parker’s comedy routine about spreadsheets.