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 strategiesSo 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-readabilityExperimenting with the validation function to reduce the variability of dataExperimenting with Arrayformulas to ensure that formulas will work even if additional rows are addedPotentially offloading very complex formulas to Google Scripts and calling them from spreadsheet formulasUsing Named Ranges to ensure referential integrityPlease 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.
Complexity limits of solutions created in Google Spreadsheets
complexity;google app engine;business rules;spreadsheet