How to Convert Google Sheets to a lightweight Database and Build an APP without Coding: Using AppSheet
Previously
You can build an interface of your Google Sheets by Python. It would be good to operate the sheets, but the problem is that you need to code a lot. However, there is actually a new and simple solution for this: AppSheet. You don’t even need one line of code to build a classical APP.
“AppSheet is a no-code platform that enables anyone to build powerful mobile and web applications quickly and efficiently. It stands out for its user-friendly interface, robust integration capabilities, especially with Google Sheets, and the flexibility it offers in app customization without the need for traditional programming skills.”
Go to AppSheet
Go to your Google Sheets and click Extensions -> AppSheet:
You can change the view of the design webpage:
Add Table to AppSheet
Click Data and New Table to add table (sheet in Google Sheets) to the App:
Based on the real demand, you can set some sheets are edible while some are read-only:
Add all the sheets that are needed:
Connect Tables and Build Relational Database
For example, in sheet “03_Transactions”, there is a column “ID_Account” which is the key in sheet “02_Accounts”. In sheet “03_Transactions”, there are columns “Type”, “Last Name”, “First Name”, these fields are from sheet “02_Accounts” and based on “ID_Account”.
We can connect these two tables by the column “ID_Account” to build a relational database.
In the Tables, choose “03_Transactions”, go to View Columns :
Go to “ID_Account”, click the column “Type”:
Choose Ref:
In the settings page, choose “02_Accounts”:
Similarly, in the Table “02_Accounts”, change the type of “ID_Investor” into Ref:
Build View of the APP
Click UX and New View:
The good thing is that in the setting page, what you see is what you get, so you can play with the parameters:
There are two main kind of position: deck and menu. Deck locates the bottom of the App, while menu is on the top left corner: the three lines. If you don’t want too many icons in the deck, you can move them to menu.
Automatically assigned values by Ref
Since there are existing columns in the “02_Accounts” sheet that we want to be uneditable in the AppSheet app and automatically assigned a value based on the selected “ID_Investor”, we can achieve this by setting up AppSheet column behaviors and formulas. Here’s how to do it.
Set up Dropdown box
In AppSheet, to set up a column where users can choose between two or more specific values (“Reinvest” or “Cash Dividend” in our case), we can use an Enum column type. This type of column allows us to define a set of allowed values that the user can select from a dropdown list.
Locate the “Reinvest/Cash Dividend” column, and set the column type to ‘Enum’.
In the column’s settings, look for the ‘Enum values’ property. Enter the two options: ["Reinvest", "Cash Dividend"]. This creates a dropdown list with these two options.
Set Up App Formula
In the sheet “03_Transactions”, there are three columns. After user input “Money In/Out” and “Amount”, we could get the value of “Transaction Amount” automatically.
According to ChatGPT 4.0, here is the method:
IF([Money In/Out] = "Money In", [Amount], -[Amount])
However, it does not work. After a little test, it turns out we should use:
IF([Money In/Out] = "Money In", [Amount], [Amount]*(-1))
