I am creating a solution where I essentially put all rules regarding communication with customers (including automatic invoicing, reminder emails, welcome emails, etc.) into a Google Sheets and use Ultradox to create emails and PDFs based upon Google Docs templates. For the three automatic emails I have currently implemented this is working out really well, the whole thing is very transparent to our organization since even non-technical people can inspect and correct the “Excel”-formulas.
My concern is that in 2-3 years we will probably have 200 unique emails and actions that we need to send out for the various occasions and given the various states that customers can be in. Of course I could aim at limiting the number of emails and states that our customer can be in, but this should be choice based upon business realities and not be limited by the choice of technology.
My question is therefore, what are the limits of complexity (when will it become unmaintainable) that can be reasonably implemented in a solution based upon Google Apps Scripts and Google Sheets, given that I will attempt to expose as many of the rules as possible to Google Sheets? And what pitfalls should I be aware of when basing myself on a spreadsheet formulas, and what strategies should I follow to avoid the pitfalls?
Some of my own strategies
So far I have come up with the following strategies to increase maintainability:
- Using several Google Sheets, each with its own purpose, each with its own dedicated “export” and “import” sheets so it is clear, which columns are dependent on the Google Sheet. Such sheets also help maintain referential integrity when inserting columns and rows.
- Using multi-line formulas with indentation for formula-readability
- Experimenting with the “validation” function to reduce the variability of data
- Experimenting with Arrayformulas to ensure that formulas will work even if additional rows are added
- Potentially offloading very complex formulas to Google Scripts and calling them from spreadsheet formulas
- Using Named Ranges to ensure referential integrity
Please notice that I am not asking about performance in this question, only maintainability.
Also, I am unsure of how software complexity can be measured, so I am unsure of how to ask this question in a more specific way.
0
Bad idea.
Google Sheets should be considered as disposable software:
-
Google can (and has) changed the internal workings on a fairly frequent basis. You can have something abruptly stop working.
-
Documenting a spreadsheet is non-trivial. Even well commented spreadsheets are difficult for someone new to figure out.
-
I’ve yet to find a tool for profiling google sheets. Where is the program spending it’s time? Debugging is not trivial.
-
Even a fairly simple spreadsheet, with 6 tabs and cascading data where a single entry causes the recalculation of a pivot table that uses 5000 rows of data can result in recalcs that take minutes long.