Building a Lightweight CRM with Google Sheets and Apps Scripts: What Worked in Practice

Striking a balance between a messy spreadsheet and an expensive enterprise SaaS is always a tricky task. Developing a solution that handled 500 clients gave us a front-row seat to the limitations and tradeoffs of the Google Sheets ecosystem, as well as to its strengths.

A couple of days ago a friend of mine, who runs a gym, showed me their clients tracking system: just monthly papers with payments written on them. You can imagine the problem: it's nearly impossible to keep track of who paid, when, and if it's time for them to pay again. The challenge was clear: build a simple, low-cost UI to streamline the process.

So, my first thought was spreadsheets. The idea was simple: add the clients, monthly payments and a simple script to check who still has to pay the fee.

The basic layout

After defining the business operations, we can set the basic grid system. In this case, monthly payments and a basic info about the client. We didn't have a clear ID to avoid duplications; names, last names or even phone numbers can be shared between users. We decided that if only all those three matched, the entry will be flaggedd as duplicated. For this scale, manually merging rows is enough to solve any doubts.

At this stage we had a classic spreadsheet; headers, footer, some data, and basic formulas.

Scripting and buttons

To add scripts you need to draw buttons, triggers or a custom menu. In my case I decided to go with buttons to simplify the UX. But buttons can't be copied and pasted, don't ask me why. Making all the buttons is a tedious task. I recommend creating them with no text, making them the same height by eye, and then adding the text. If not, text will resize with the button, and you have no way how to set an exact padding. It’s a surprisingly clunky implementation for such a mature platform.

Scripting is fine. You can add JS code and you can use Named Ranges as IDs for rows and columns, or you can target them by number. Adding a row where you want, like in the middle of the table, has to be done by targeting a related row and adding it before or after the target. Adding a column is also done by doing some basic math to target the exact position. Always, the big risk is: If someone modifies the grid, they might accidentally break the logic of your scripts.

To protect your system while allowing other people to run scripts you need a combination of Protect sheets and Ranges, using Installable triggers and use unprotected cells for data entry and triggering. You have lots of combinations here, but keep in mind if you plan to transfer ownership and leave, you need to provide a way to reinstall the triggers to the new owner.

Another positive thing is that you can easily debug your code with the Debugger tool.

UI and speed

You can add sidebars and dialogs (modals) made of HTML , CSS and JS, making them affect the grid and data. This is a safe way to make the table look interactive. The bad part is, running scripts and opening custom UIs is not fast. You can wait seconds until a form is opened because all scripts are executed on Google's servers in a shared infrastructure, and that is not fast. For a better UX, you can use loading spinners to communicate to the user that the form is loading.

The good part

So, we know buttons can't be copied and pasted, and a UI and scripts are not fast to run, and you need a lot of protection in your app. So why to use Google Sheets at all? And the answer is simplicity. You can set up a data grid, headers, footers, formulas and some scripts really fast. You can use JavaScript, CSS and HTML to do it.
Hosting and authentication are handled by Google for free. And if the user commits to interacting with the app through your buttons and menus, everything will work fine. Here, simplicity and velocity are kings.

My conclusion

If you want to create a simple system quickly that you can imagine as a spreadsheet with some automation, go for it. It will work if the users are willing to understand that data is mutable, and trying to modify the grid system can break everything in a single step. However, if you need more control, you can always enforce the structure using Protections, Scripts, Custom Menus, and Triggers.