How to use Google Sheet as a CRM with Google Cloud Script automation

Cohort 6 Production is a web development company that focuses on helping SMEs to increase revenue and profit by offering web/app development services. This case was created as part of the automation project where I built a cost-effective, lightweight CRM for Cohort 6 Product to streamline their sales operation. The result?

• 95% reduction of time spent on task per staff: Reduced time spend from 10 minutes → 30 seconds
• Error-free: 100% elimination of human operation error
• Cost-effective: Low-cost, tailor-made, highly standardized, full automation

What was the problem?

Pitching clients and managing project quotations are time-consuming and onerous, especially when the team is focusing on value-adding business activities, C6P wanted a CRM with automated document management functionality that helps them streamline their sales process.

• Either building bespoke systems or existing alternatives are expensive
• Need the solution to be flexible yet robust
• Do not want to be tied down by subscriptions

Going through the process

As to keep the system simple, here are the business requirements.

• Content management system with access permission control
• Simple dashboard, easy to use
• Use pre-built templates as a replica for all operations

In order to identify what I need to build, I created a flow diagram of the business process to identify where in the process the user needs to interact with the system, and what kind of results the user can expect at the end of the operation.

My personal takeaways

The project was a success, not only it helps Cohort 6 Productions get their feet off the ground, but it is also a great way to introduce Google Script to the business, where the business can leverage the power of Google’s API to solve all kinds of business problems without investing into bespoke systems that build from scratch. Here’s what I’ve learned:

• Code smarter — Use recursive for reading folders and generate templates for scalable growth without being limited by the codebase.
• Allow user confirmation and self-correction —  By allowing the user to confirm their input before committing, not only reduces the frustration when mistakes are made, but it also reduces the chance where the user has to redo the procedure.