From startups to large multinational corporations, every organization needs a tool or an application to keep a track of expenses. These apps usually help different departments, for example, the CEO’s office or finance teams to keep track of salaries, reimbursements and other company expenses.
There are several tools that one can find to manage expenses, however, these are either expensive or lack some crucial features that do not serve a large number of use-cases and finance goals.
We think that building expense managers shouldn’t be too complicated. So, we built an expense management dashboard using Appsmith and Google Sheets, which can be used by an organisation to manage reimbursements expenses based on their monthly budget limitations. Additionally, admins of the app can also either approve or reject it based on the reimbursement request that is raised by an employee.
Here’s a sneak peek of what the app looks like.
Appsmith is an open-source framework that lets developers build dashboards, workflows, and CRUD apps with only the necessary code. You can connect to any API or databases like MongoDB, PostgreSQL, or MYSQL and get access to multiple widgets, including charts, tables and forms, for building a UI fast.
Following are the table of contents:
Follow the steps below to use Google Sheets with Appsmith:
Awesome! Now that our Google Sheets Plugin is set up, let’s create a new Google Sheet and add the necessary fields required for managing expenses.
Following are the fields we’ll be considering:
To make this more precise, we’ve made a sample Google Sheet with some mock data here. We’ll be using the same Sheet throughout this guide, and you can either follow with this or create your own based on our requirements.
Now that we are connected to our Google Sheets data source, let’s connect to our Google Sheet and query all the data onto a list widget in Appsmith. To do this, navigate to the created data source under the APIs section and click on the New API button on the top right. Next, follow the below steps:
Awesome, now that we have our data from the Google Sheet, let’s put this in a list; drag and drop a new table widget and paste the following in the Table Data property:
Quick Hack: For making the application more readable, let’s change the colour of the text based on the Reimbursement status. Appsmith allows us to write JS for styling widgets, now open the Text3 property pane and toggle JS in the text colour property and paste the following code:
With this, our list widget is colourful and feature-rich.
Here’s how it looks like:
In this section, we’ll create a new form that will allow users to post new reimbursement requests to the admins. For this, let’s use the Form and Input components and Google Sheets integration on Appsmith. Follow the below steps:
We’ll add the select widget onto the form to set the type of reimbursement. Open the property-pane of the select widget and let’s add the options by pasting the following code:
Here we define an array of all the options and iterate through a for-loop and render them all in the select widget.
Now that we have our form ready, let's write the logic to push the values from the form to the Google Sheet whenever submitted. Follow the below steps:
Here, the keys are the column names in the Google Sheet, and the values associated with them are the names of the input widgets. The .text method is used to pick the text that's written in the input widgets.
Lastly, in the form below for the submit button, set the on click property to Call an API and call the postReimbursement query from the options. With this, we should be able to add new rows to Google Sheet from Appsmith.
In the next section, we will build an Admin Dashboard where admins can approve or reject the requested reimbursement.
The expense manager admin can be only viewed by the admins of the application. Here we’ll be displaying all the information regarding the reimbursement’s and add an option to approve and reject them with a comment.
To build this, follow the steps below:
With this, we should see all the reimbursement requests with pending status on the table widget. Now, add to add options to approve and reject follow the below steps:
Now create a new API from the Sheets Expense Manager data source and rename it to aprroveReimbursement, the idea here is to update the status of reimbursement whenever the approve button is hit on the Table. Now, in the query set the method to Update sheet row and row object to the following:
Similarly, create another query for rejecting the reimbursement (rejectReimbursement), use the following code snippet in the Row Object property:
Now, go back to the Table Widget, and set the Approve button onClick property to execute a query and select the aprroveReimbursement query.
For rejecting the query select the rejectReimbursement on the Reject button’s onClick property.
Similarly, add two more tables to show all the approved and rejected reimbursement for future references, drag and drop two table widget’s and set the Table Data to the following:
Table2, Table Data:
Add some additional metrics to track expenses!
Our dashboard is almost ready, now to give the admins an overall picture of expenses, let’s add some metrics that’ll help quickly look at the pending, paid, and rejected amounts. For this, we’ll need to drag and drop the text widgets and use the following snippet to do the calculations from the table widget.
In this code snippet, we’re iterating over the Table1 data and counting the summing up the Amount column. As the text widget accepts a string, the function will be returning the same data type. Similarly, let’s drop two more text widget’s and calculate the total amount approved and total amount rejected.
Use the following snippets:
Total Amount Pending
Text Widget Value:
Total Amount Rejected
Now, finally, this is how the admin view looks like:
Building this app from scratch, including writing snippets of code is likely to take 30 minutes! Isn’t that simple?
If you liked this tutorial, and are planning to build this, let me know. I’d love to help you make it as complex as you’d like. Write to me at email@example.com
The rich text element allows you to create and format headings, paragraphs, blockquotes, images, and video all in one place instead of having to add and format them individually. Just double-click and easily create content.
The rich text element allows you to create and format
A rich text element can be used with static or dynamic content. For static content, just drop it into any page and begin editing. For dynamic content, add a rich text field to any collection and then connect a rich text element to that field in the settings panel. Voila!
Headings, paragraphs, blockquotes, figures, images, and figure captions can all be styled after a class is added to the rich text element using the "When inside of" nested selector system.