How to make a simple billing/invoicing application with AppSheet
I’m going to show you how to create this simple billing/invoicing app in AppSheet without writing any code.
Let’s start out by focusing on what we want to accomplish.
In plain English: An app where my company can send out and track invoices, see who’s paid and who hasn’t, and also send them reminders if they haven’t paid.
Let’s start with the data.
We’ll want to collect a few pieces of information. These are what I used for the demo, but you can modify these columns to fit your needs. All you have to do to start adding these is go to google sheets, open a new sheet and start adding these columns in the first horizontal row.
The columns I added are listed below.
| invoiceId | amount | description | dateIssued | dateDue | customerEmail | customerPhoneNumber | billingTeamEmail | datePaid | status | invoiceLink |
Let’s go ahead and make a sample row of data on the second row of this sheet. Add in the information in the format you want, this will help us figure out how everything works when we start building the app.
To start building out our app, let’s go over to AppSheet.com which is a free no-code appbuilder that integrates with Google.
Go ahead and click “make a new app” and then “start with your own data”, because yes, we actually just created our own data and data structure.
Name the app whatever you like and pick a category (this category isn’t super important so don’t worry about it).
Then click “choose your data’ and select the sheet that we built in the first few steps.
After you select the data, Appsheet will set you up with a basic blank app configuration.
From here, let’s go to the UX tab on the lefthand side and click on our primary view “Billing App Training” and work on renaming and modifying that user interface a bit. First thing you can do is make this UX a deck.
The end goal here is that we’ll have a list of our invoices sorted first by the unpaid ones.
We can also change the icon for this view and change the action buttons as well (see above video for steps).
The first thing we can easily notice is that the dollar amount for our first item isn’t the correct datatype. We can fix that by going over to the data tab and changing the amount column to “price”, so that we get a nice $ in front of our number. IE – “$600” instead of “600”.
Then let’s just hit on a little bit of housekeeping and turn our app into a darktheme. You can pick any color scheme you want, but I’m partial to dark theme in almost everything I build, so let’s go over to UX > Brand and pick the dark theme.
While you’re here you can also pick a new app logo or upload a custom one. For me, I just went with the basic $ logo for now.
Now that we’ve taken care of those things let’s go work on our data structure a bit.
We’re going to have to create a dropdown option so that we can select either “PAID” or “UNPAID” and ensure that every new invoice added to the system is by default “UNPAID” because we’ll be adding invoices to the system as soon as they are sent. To create this dropdown we’ll need to create what is called an EnumList, which allows us to have a list of values to select from.
Go ahead and then scroll to the right and click on the box for “Initial Value” and make that initial value = UNPAID.
You can do a ton of awesome stuff with expression assistant and we’ll get to some of that later.
Then let’s go add our two dropdown values. Click on the edit button to the far left of the column name.
Now we’ll be able to input our values.
Once we’ve added the values “PAID” and “UNPAID” we can make sure we select the Dropdown input mode as our method for providing these values into the form.
You can also change the display name on your form from “status” to “Status” just to make your form look a little bit nicer and a little bit less like a raw database.
This will make it so that instead of your input field looking like this:
It will now look like this:
Now when you click on the dropdown you’ll have the option to select either of these values. The only weak spot in this input point (which I think appsheet may likely fix in the future) is that you can select both/all of the options in the dropdown instead of just only one value, which is what we want to happen here.
Next we’ll go through and “unshow” a few of these columns, since the person inputting the invoice information doesn’t need to see these. We can set default values for these ourselves.
Go ahead an uncheck the “show” button for any column that you don’t want the user to see on the input form.
Also go ahead and add an “initial value” to the field billingTeamEmail, just go ahead and click on the empty box and add in your team’s email. This will keep the data that you need there without requiring you to see it. In this development scenario, this email never changes so we never need to edit it.
From here, let’s clean up the display names of all the input fields in the form that are still shown. For instance, we’ll take “dateIssued” and change it to “Date Invoice Issued” for it’s display name. We name columns differently on the backend and frontend to make data filtering and queries easier… removing capital letters and spaces on the backend data structure helps us do that, and adding it on the frontend makes our data more readible.
Now that all of our input fields look super readable and beautiful, let’s go write a formula to automate one of the field values.
We want to make it easy for the person inputting the invoice data to calculate the due date as being 30 days out from the date it was issued. The plain English formula for what we want to achieve is “The date the invoice was issued plus thirty days”.
We’ll do this by going to the “formula” section of a given column, then click on that empty cell and enter this formula into the expression assistant: DATE([dateIssued] +30).
Now your due date is 30 days out exactly without any calculations or inputs performed by a human necessary.
Next let’s deal with a default value that might trip us up…. we have a default value already loaded (on creation) for the datePaid field. When we create the invoices they won’t be paid until a later date (the payment can’t come before the invoice, right?). We’ll go ahead and fix this issue by removing the TODAY() value from the initial value on this field.
Let’s also go ahead and require that we have a value input for the description field and the status field. We can do this by checking the “require” box on our field’s row.
We do this because we WANT the user to provide these two values no matter what. This ensures that there’s no way we get an input to our database (that google sheet we made earlier) without those two fields being filled out.
At this point go ahead and start testing your input form by adding in 3-6 new invoices. This will help you moving forward to test functionality in your app prototype.
Make sure a few of your entries are marked as “PAID” so you can test out the next feature we will develop.
After we input all the test invoice data, let’s go ahead and focus on how they’re stored in our main view. Initially, they look like this:
But this sorting method doesn’t make so much sense if we want the person behind this app to focus on mainly the unpaid entries. Let’s try and put those at the top of the app so they become the main focus.
Let’s go into our UX > Views section and click on our main view and then go to View Options. Here we can sort and group by status descending. This will give us a neat grouping of all the unpaid invoices at the top, and all of the paid ones at the bottom.
Let’s move on to our next goal… providing the billing team with a way of sending email reminders to the customer when it’s been over 30 days. We can do this with the Behavior tab by using Actions.
Go ahead and start with these settings and naming your action.
For the Subject and Body fields let’s add these values.
Subject:
CONCATENATE("A friendly reminder to pay $", [amount], " before your due date of ", [dateDue],". "," ",
"Please follow this link to complete your payment " [invoiceLink])Body:
CONCATENATE("Your balance of $", [amount], " needs to be paid. Click ",LINKTEXT([invoiceLink]), " to pay your invoice")As you add this, try typing it out from scratch the first time. This will allow you to see the expression builder populate as you add the various fields and pieces of text together. CONCATENATE basically shoves all of the various static and dynamic values together to create an overall message.
Once you’ve added these values you should generate an email message like this when you click the “send payment reminder” button. This autogenerates all of the pertinent details necessary for you so you don’t have to type out a long email every time you need to send a reminder.
Let’s go ahead and create our second view by heading over to UX and clicking New View. Go ahead and add these settings in the below video.
Now we have an application that allows us to see a simple chart of how many of our currently issued invoices have been paid. We can use this as a simple visual indicator for our billing team to gauge their overall success against.
At this point we’re basically done. The only thing left to cover is exactly HOW the customer pays their invoice. At this point in time, AppSheet does not have a built-in payment solution, so the best way to collect payment for invoices as it relates to this app is by using a PayPal invoice link or something comparable to PayPal.
The only thing left to do is to deploy your app so that it can be used. AppSheet allows you to deploy your application with their paid plan, which is currently structured based on how many users you have using the app. Deployment should be a pretty simple solution, but we won’t cover that in this tutorial.
