Originally published at: Discovering the Hidden Power of Google Sheets - TidBITS
Faced with the problem of wanting to build a budget calculation spreadsheet for his running club, Adam Engst embarks on a journey during which he learns that modern spreadsheets can go far beyond basic row and column calculations.
This is at most an indirect comment on the article.
I’m baffled by the lack of adoption of what Excel calls R1C1 notation. $A$2 would be represented as R2C1, where the numbers without brackets indicate an absolute reference (as the dollar signs do in A1 reference). But the beauty of R1C1 is in relative references. Rather than getting something like M42, where I struggle to figure out how far away that cell is from the cell with the formula, R1C1 would present something like R[-14]C[26], making it immediately obvious that the referenced cell is fourteen rows above and twenty-six rows to the left. For me, this is especially important when I want to make sure I have the same formula in several cells in a column or a row. I can arrow down (or across) and the formula stays the same, where in A1 notation the formula would change appearance in each cell. (Or at least that’s my recollection. I started using R1C1 when doing engineering analyses in Excel, and now the habit is ingrained.)
Huh! I’ve never used R1C1 notation, which from your description sounds better if you think positionally. Numbers likes to name cells instead, which makes for easier reading but somewhat throws me when I’m trying to type formulas.
Adam, ChatGPT is excellent at REGEXMATCH! (You never responded to my story about learning to use ChatGPT to write Python… where I used it for REGEX. :-( )
Disclosure: I used to work for Smartsheet and still maintain a position in SMAR.
Just posting to say there is a lot going on behind the scenes of a modern spreadsheet interface. In Smartsheet, the interface attempts to reveal the ways in which you can share/limit data with others so the automations can inform people when updates (to the data) occur. You can also build dashboards to abstract the spreadsheet or reveal only the relevant parts of it on the dashboard.
Just suggesting that the spreadsheet has come a long long way, while also maintaining all the lovely formula magic that made them what they are.
Don’t like Smartsheet? Asana, AirTable, Monday, etc. are all about the same but emphasizing slightly different UI elements. The spreadsheet grid is still the foundation that underlies all of them. Only the relative newcomer ClickUp has the most adaptable UI for making workflows more visually distinct.
p.s., Paid vs. free: I am not speaking to value since software and Internet anything require humans to maintain. You will/are pay/paying, full stop.
I’ll have to give ChatGPT a try next time I fuss with REGEXMATCH. All this work was taking place before ChatGPT was released.
i was surprised not to see the word “privacy” anywhere in the article, but I guess if you’re willing to put your docs on Google’s cloud, being the product is not a consideration for you. Probably saves a lot of worry — in the short run.
1 Like
Wow, the formatting of the exported QuickBooks statement makes it very difficult to work with. (I assume that the following screenshot from the article is the exported statement?)
It will be ideal if the data can be exported in “tidy data” format, i.e.
- Every column is a variable/an attribute
- Every row is an observation
- Every cell is a single value
This reduced form is essentially the original expense entries instead of the summary, which can be formatted as:
| Expense description | Expense category | Race event | Date | Amount |
If such a report can be exported from QuickBooks, then a summary table can be created using tools such as Pivot Table or sumifs(). Year filters can be added so new yearly statements can be created easily. If an event filter is added, then each race director can also access statement of accounts for their races. The archiving of the expense entries is another benefit, too.
For instance, I’ll need to add a 2024 column for each sheet next year. In an ideal world, abstraction capabilities would be easily applied to multiple sheets within a spreadsheet.
In Excel, you could select the affected worksheets, from the worksheet tabs along the bottom, then on one of them, insert a column and add the 2024 header in that column. It will be repeated in all the selected worksheets. Make sure you select the correct worksheets !
Looks like Google Sheets would have a similar function.
2 Likes
Have you tried opening your spreadsheet in Numbers?
Pivot tables may help to simplify your design, Apple implemented them better than others.
1 Like
Another method would be to add some extra columns to your Utility sheet that contain the years of interest, and then have each race’s sheet reference those Utility cells instead of having hard-coded years. That seems the most programmatic and doesn’t require inserting new columns for each race’s budget. You can also start removing past years (if you want to limit your loopback period) by modifying the Utility cells.
Just be careful to only modify the Utility cell contents. If you insert or delete columns in that range, you probably won’t get what you want. For example, a reference to cell D4 (or even $D$4) will change to E4 (or $E$4) if you insert a column anywhere before column D.
The only hitch we’ve encountered is a race getting data in a previously unused account; I have to refresh the filter by selecting all and then deselecting zero again. That’s happened only once so far.
I think you could resolve that by using “Filter by condition” and selecting “Is not equal to”, “0”.
Yeah, I didn’t want to get into this because I have no choice in its usage, but QuickBooks Online makes me crazy. Ugly, awkward, and hard to navigate for everything.
Ooo, that would be helpful, but sadly, unless I’m missing something, you can make changes in only a single sheet at a time. I can select multiple sheets in Google Sheets, but as soon as I click to make a change, the others deselect.
No, because I’ve standardized everything the club does on Google Docs and Sheets to be accessible to the most people on whatever platform they use.
Huh. That’s an interesting concept! I have to ponder how best to do this, but now that you’ve triggered the neurons, I’m seeing some avenues… Thanks!
That’s a better way to filter, thanks! But even it requires manually refreshing the filter—it doesn’t pick up when data changes under it.
(Is there an online integration that would move the data between QuickBooks Online and Google Sheets automatically?
I’ve not used it for a couple of years, but I see that IFTTT features both Google Sheets and Quickbooks Online. (I used to use IFTTT to log front-door security camera events to a Google Sheet and while I never need to go in and query/analyse the data, it basically seemed to work fine in that context.)
Perhaps, rather than doing your monthly batches, you would be open to acquiring the data in real time? If not directly to the main Sheet, then perhaps to an interim, buffer sheet?
Zapier, which you also mentioned here, lists both Sheets and Quickbooks too, though I have no experience with this service whatsoever.
