Google Tables and Formulas
While Google Tables looks promising, there are no formula capabilities. This is not ideal and while I get it - Tables is about information and workflow - there are cases where given a specific state or a value-pattern, you need certain columns to self-populate.
To overcome this I envisioned a unified bot->webhook interface to perform computations and other types of formula handlers for Tables. Simply, I needed a way for my clients to create a bot that was able to run javascript snippets with arguments.
Examples...
- Assessing the state of two drop-down columns to compute a percentage
- Calculating a new value based on two-column values for a third column value
These are each possible using simple formulaic javascript embedded in bots. Given the selling price and cost of goods, for example, it should be easy to construct and utilize a formula like this to populate the Net Profit column in a table.
((({{Sales}} - {{Cost of Goods Sold}}) / {{Sales}}) * 100).toFixed(1).toString() + "%"
By passing this formula to a universal javascript runner service, Users are able to create some very complex formulas without actually writing or deploying code.
Based on my experiments with Google Apps Script and the Tables API, I see a bright future for using Tables in deeply complex and comprehensive information solutions that embrace all that Google Docs and G-Suite has become over the past 15 years and vastly better process automation.
Here's a video with the details and a demonstration.
Great workaround! I too see the great potential GTables has - let's see where it ends up. Formulas need to become native, if only to avoid the bot count - I haven't yet become accustomed to think of actions as limited per max uses per table per month...
The other thing they could use: Gantt charts
Bill - great work, interesting workaround! Thanks for sharing.
But I hope they introduce a native way to do formulas, so it's as easy as the rest of the product to use.
I love your optimism in the final paragraph. I could see it being a super valuable tool for existing G Suite customers too. Really hope we see Tables graduate to core product.
Cheers!
Indeed. Until then this is one way to create responsive computational behaviours. But it's also likely the case when an integrated formula capability arrives it will be incomplete; formula consumers have really diverse needs. We saw formula capabilities stagnate at Airtable for a half-decade and even when they began in earnest to enhance them, they continue to fall short even today with math and other powerful string handling methods.
One way to avoid this is to create a framework for integrated formulas and make it possible to craft them as plugins using javascript. Build all the basics and allow the community to build everything imaginable.