4 simple steps to make the perfect spreadsheet to power your no-code app

Making data accessible, useful and actionable in different contexts is one of the main reasons we build apps. Typically, this involves a lot of engineering, but thanks to no-code app development, even those with no coding experience can create apps from the most commonplace data repository of them all: the spreadsheet or Google Sheets.  

For example, if employees in the field are recording data by hand and inputting it into Google Sheets later, all of that work could be simplified with a no-code app. Users would simply input data into the app, which would automatically sync with spreadsheets in the cloud. For many line-of-business workers, the power to replace spreadsheet maintenance with an app can lead to powerful efficiency gains. Likewise, for many enterprises, the ability to empower non-technical workers to build such apps themselves, without significant IT resources, can be transformative, opening entirely new avenues for accelerating innovation. 

Getting this process started can be as simple as organizing spreadsheets such that a no-code app platform like Google Cloud AppSheet can ingest them and generate useful prototype apps for users to further build on. Though no coding is required, great no-code apps still rely on a well-designed data structure. In this post, we’ll go over the fundamentals of preparing a data source in Google Sheets to effectively build applications using AppSheet. 

Here are 4 steps that can help you reduce friction in the no-code app prototyping process, simplify the maintenance and management of your app, and create a more performative and efficient data structure.

Step 1: Use clean table layouts

Before we begin laying out our Google Sheet, let’s consider a few questions:

• What do I want my app to do? 

• What data is required to perform those functions?

• What data will my users need to access?

The critical data above needs to be captured in a column or table in your Google Sheet. When grouping your columns into tables, think about what the simplest conceptual grouping would be. Imagine we’re building an app for a grocery store which keeps track of inventory. Initially we might be inclined to create a separate table for each category of item like in Example 1 - but this schema is inefficient as it creates a large number of tables we would need to manage and update individually. A better solution would be to contain all of our inventory items in a single table, and include a column which designates item categories like in Example 2.

Key Takeaway: This simplicity is important because if you have too many tables, your app will become harder to maintain and modify, and it may face sync time issues and additional potential failure points. 

Step 2: Name and format your data simply and consistently 

Column and table names should be kept short and easy to understand. Avoid creating long names, names with special characters, or names with strings of numerics. 

Column naming and formatting conventions should persist across data sources if possible. That is, if your app is pulling data from multiple Sheets, naming and formation should be consistent across all of them. Following these guidelines will ensure that your data is easy to understand, increase the likelihood that the prototype app interprets data as desired, and make it easier to employ expressions within your app.

Step 3: Use columns for categories and rows for entries

When new data is added in an AppSheet app, it will be appended to your sheet in the form of a new row. It’s important to ensure that the structure of your spreadsheet is uniform, and that is built such that each row represents an entry and each column represents a category for type of entries. To understand what this means, let’s look at three examples.

Example. 1 - Inverted inventory table

In this first example, we can see that the desired structure is inverted. Data headers are arranged vertically, and new entries representing inventory items are added as columns. This arrangement will prevent AppSheet from adding new rows; it should look more like the “ideal table layout” in the previous section. 

Example. 2 - Interrupted inventory table

This second example spreadsheet contains a section that is meant to be used as a calculator. Because this interrupts the structure of the spreadsheet, however, it will prevent new rows from being added. Tables must remain structurally consistent across their entirety, and they must consist of only column headers and ensuing rows of data. If you have a section such as a calculator which interrupts the overall structure of the table, you’ll need to either integrate it into the original table structure like Example 3, or relegate it to its own independent table.

Example. 3 - Integrated Calculation Fields

Key Takeaway: Duplication is a related structural pitfall to watch for. If you notice that large blocks of data are being duplicated in multiple tables, this is a good indicator that your data structure may not be as efficient as it could be, which could contribute to extended sync times for apps.

Step 4: Add a key column

Every table must include a key column that serves as a unique identifier for each row of data. For some types of data, each row will naturally contain a column which is unchanging and unique for each row. A good example of a naturally unique key is a product ASIN or SKU - no two products will have the same. Similarly, an employee identification number or email are good unique keys, as no two individuals will have the same one. For data that does not have this property, we can easily generate one using the UNIQUEID() function in the AppSheet editor. If you want to learn more about keys, see our help documentation on the subject.

It’s time to create your first no-code app

If you’ve been following each of the above mentioned steps to structure your data, you should be ready to turn your data into a powerful no-code app. To get started, open up Sheets with your data, and go to Extensions>AppSheet>Create an App. From there, Appsheet will automatically read your data and turn your spreadsheets data into a no-code app to drive powerful efficiency gains.

For a deeper dive into this topic, be sure to see our article on spreadsheet best practices and this webinar, and feel free to jumpstart your no-code journey with our library of sample apps. If you run into any issues building your apps, check out our help articles or ask a question on the AppSheet Community.

We’ve been truly inspired by the apps already created by tech-savvy creators like you, and can’t wait to see what you create next. 

Ready to use AppSheet? Get started now.