How to Create a Simple CRM: Turning a Google Sheet into an AppSheet App

Customer relationship management (CRM) systems are invaluable for managing interactions with customers and prospects. However, many small businesses and teams cannot justify the cost and complexity of enterprise-level CRM platforms.

The good news is that you can create a simple, customizable CRM easily and affordably by building an application from Google Sheets using AppSheet. With some spreadsheet best practices and AppSheet’s no-code platform, anyone can turn a Google Sheet into a mobile app their team can use to track leads, manage accounts, log activities, and more.

In this post, we’ll walk through a step-by-step guide to building your own CRM app with Google Sheets and AppSheet.

Why Build a Custom CRM App?

Off-the-shelf CRM platforms can be expensive and overloaded with complex features most small teams don’t need. Building your own CRM app allows you to:

• Customize it for your specific workflow and needs – Skip the features you won’t use and focus on what matters most to your team.
• Make it easy to use – Design an intuitive interface perfect for your team rather than forcing them to conform to another platform’s paradigm.
• Own your data – No being locked into another vendor’s platform. You control the app and the data.
• Save money – Avoid expensive licensing fees for capabilities you don’t need.
• Integrate seamlessly with Google Sheets – Tap into Sheets’ powerful functions and collaborative capabilities.

Step 1: Set Up Your Google Sheet

Your Google Sheet will serve as the database and backend for your CRM app. Setting it up correctly from the outset will save headaches down the road.

Follow these best practices when structuring your Sheet:

• Include columns for all data points you want to collect. Don’t leave out important fields if you think you can add them later. Adding or removing columns after launch may break your app.
• Leave room to grow – Add some extra columns for expansion. Resist the urge to only include what you need today.
• Use one sheet for reference data – Put static lists, like lead statuses and account types, on their own sheet that you can reference via dropdowns.
• Follow naming conventions – Use lowercase, hyphens instead of spaces (e.g. “first-name”). Avoid special characters.
• Enter consistent data – Use the same terms and formats, like state abbreviations instead of full names. Garbage in, garbage out still applies.
• Include ID columns – Unique, auto-incrementing IDs will be useful for relational capabilities later. Fortunately, these are easy to add with Sheets.

Taking the time to thoughtfully structure your Sheet from the start will pay off in a smooth app creation process.

Step 2: Add Filters, Formatting, and Formulas

With your base Sheet setup, add in filters, formatting, and formulas to make it CRM-ready:

Filters

• Apply filters to columns to easily sort and find records.
• Allow filtering by key fields like owner, status, and date fields.

Conditional formatting

• Highlight columns like status or priority with color coding for quick visual scan.
• Visually identify blank or missing values that need follow-up.

Formulas

• Add a formula to auto-populate the date a record was created.
• Concatenate first and last name columns into a single name column.
• Create an ID column with the row number as a unique ID for each record.

These extra touches will make your Sheet highly functional and ready to turn into a smooth CRM app experience. The effort is well worth it.

Step 3: Integrate the Sheet with Apps Script (Optional)

You can use Apps Script to integrate custom logic into your Sheet for additional functionality. Apps Script allows you to add features like:

• Email alerts and notifications on form submit or status changes
• Validation to prevent bad data from being entered
• Automated data formatting
• Lookup and linking records across multiple sheets
• Writing data to multiple destinations like email, a separate spreadsheet, or Google Drive

The scripting possibilities are nearly endless. Even starting with simple automations will remove daily manual processes for your team. But keep it focused on your most high-value needs and tackle one script at a time.

Step 4: Connect Your Google Sheet to AppSheet

Now that your Sheet is prepared, it’s time to connect it to AppSheet to build your CRM app UI.

First, sign up for an AppSheet account. AppSheet offers a free forever tier that lets you build up to 5 apps with up to 1,000 records each.

Once registered, click “Create an app” and select Google Sheets as your data source. Authorize AppSheet’s access to your Google account and select the Sheet file you want to use.

AppSheet automatically scans your Sheet and allows you to preview the app structure. You can specify which sheet to use as your primary data table. You can always change these settings later as you build out your app.

Step 5: Model Your App Objects

With your Sheet connected, you can start modeling your app objects – these represent tables or views in your Sheet that you want to expose in your app interface.

For a CRM app, typical objects include:

• Accounts – Customers and prospect records
• Contacts – Individual people associated with accounts
• Activities – Interactions like emails, calls, meetings tied to accounts and contacts
• Users – People on your team that will use the CRM

Map these to sheets or views in your spreadsheet. Contacts and activities may come from the same table but represent different object types.

Pro tip: Model objects based on how your team thinks about the data rather than mirroring the Sheet structure.

Step 6: Define Your App Views

With your objects mapped, now define the views that will expose that data in your app.

Typical CRM views include:

• List views – Searchable, filterable lists of all records.
• Detail views – Full record details for a single object like an account or contact.
• Dashboards – At-a-glance summary of metrics and KPIs.
• Calendar – Activity timeline views.
• Kanban – Drag and drop workflow boards to manage statuses.

You can create multiple views of the same data optimized for different functions. List views for high-level scanning and detail views for data entry and editing.

Step 7: Design Your App Interface

Now it’s time for the fun part – turning your Sheet into a polished, branded app your team will love using.

AppSheet makes it simple for anyone to build professional quality UIs without coding. Follow these best practices as you design:

• Organize logically – Group related objects together under intuitive tabs or sections.
• Highlight important actions – Make primary actions prominent with colors, size, and placement.
• Use white space – Don’t clutter the interface. Give breathing room between elements.
• Keep it simple – Only expose the most used fields and functions. Hide advanced settings under ‘more.’
• Make forms easy – Arrange fields from top to bottom in a logical flow.
• Guide the eye – Use visual hierarchy, color, and contrast to direct users.
• Use your brand – Incorporate colors, fonts, and logos to match your style.

Follow CRM and mobile best practices but customize the interface to best suit your team’s needs. The app will continue to evolve over time as you observe usage patterns.

Step 8: Add Logic with AppSheet Formulas

The real power comes from using AppSheet formulas to add logic to your application. These are similar to Excel formulas but allow you to:

• Perform calculations on record values
• Show or hide fields conditionally based on values
• Set read-only fields
• Populate values on create or update
• Validate data entry
• Link and aggregate data across objects

You can write formulas at the app, object, or field level. This lets you handle many use cases that previously required Apps Script.

Start small and build up your formulas over time. AppSheet provides many samples from which to learn in their Formula Reference Guide.

Step 9: Automate with AppSheet Workflows

AppSheet Workflows allow you to model business processes and logic visually via a drag-and-drop workflow designer.

You can use workflows to:

• Update field values automatically
• Notify users with in-app messages or emails
• Run actions like approve/reject based on rules
• Link workflows across multiple objects

This replaces many manual processes and provides a visual way to represent your team’s workflows directly in your CRM app.

Step 10: Lock Down Access with Users and Groups

With your app built, you need to make sure users only see and edit what they need to. AppSheet has robust built-in security capabilities.

You can:

• Restrict access at the app, object, and field levels.
• Create user groups with defined access permissions.
• Limit record visibility based on the owner.
• Make fields read-only unless the user owns the record.
• Mask confidential fields like credit cards.

Take time to think through your security model and apply progressive access. Don’t just make everything wide open internally. Security groups allow your app to grow with your team’s needs.

Step 11: Deploy Your App

Once you complete design and testing, you’re ready to deploy your CRM app live to your team!

AppSheet apps can be accessed via:

• Native mobile apps for iOS and Android
• Progressive web app (PWA)
• Embedded directly into your site with iFrames

You can set up single sign-on using Google, Office 365, or SAML providers so users can access with their existing credentials. No need to manage custom user accounts.

Now, put your new CRM app in the hands of your users! You’ll gain valuable feedback to iterate on the design and add new capabilities over time.

Going Further with Your CRM App

Congratulations, you’ve built the foundation of your custom CRM without coding or complex IT projects!

Here are some ways to build on that momentum:

• Integrate with other tools – Connect to other systems like email, calendars, billing platforms, and more via AppSheet’s expansive integrations.
• Send Meta leads to Google Sheets via LeadSync’s integration.
• Add reports and dashboards: Build custom reports and dashboards to expose key metrics.
• Incorporate AI: Use AI to analyze data, deliver insights, and recommend actions.
• Develop custom functionality: Leverage webhooks and APIs to connect advanced custom code.
• Scale up: Growing data volumes? AppSheet easily scales from startup to enterprise.

The possibilities are endless. Using AppSheet, you can build upon your application over time to meet your team’s evolving business needs.

The bottom line? You don’t need an IT team or a big budget to benefit from a purpose-built CRM app. With Google Sheets and AppSheet, anyone can build an elegant custom solution tailored to their specific workflows and processes.

Conclusion

Creating a custom CRM from Google Sheets and AppSheet is a game changer for small teams and businesses. You can build an app perfectly tailored to your workflows at a fraction of the cost and complexity of traditional CRM platforms.

The key is taking the time upfront to thoughtfully structure your Sheet, apply best practices, and think through your team’s needs. The investment pays off with a polished app your team will adopt and benefit from immediately.

Start small, solicit feedback from users early and often, and enhance the app over time. Soon, you’ll have a custom CRM that streamlines your processes, increases productivity, and provides value to customers – all without ever having to code.