In this article, you'll learn to build an app that will let you manage all your job applications right from your own personal dashboard.

In this tutorial, we will be building a CRUD app that will let you create, read, update, and delete the data from the datasource (here Google Sheets). For building this app, I have broken the workflow into four steps:

1. Authenticating the datasource
2. Building the UI
3. Creating the CRUD queries
4. Linking the queries to the UI

💡 ToolJet’s Import App feature allows you to import the app into your ToolJet account using the exported source code. Here’s the exported source code in case you need to import the app.

Authenticating the datasource

Let’s start by creating a new application in ToolJet (https://github.com/ToolJet/ToolJet) and giving this app a name from the top left corner.

Next, connect Google Sheets to the app as the datasource by clicking “add or edit datasource” and then selecting Google Sheets from the list of available datasources. We need to authenticate with “read and write” permissions so that our app is able to perform CRUD operations. Once authenticated, close the tab and select “save datasource”.

Building the UI

So now that we have added the datasource let's go ahead build the user interface of our app. We will drag the components from the right sidebar i.e. widget manager onto the canvas. To give our app a header and description use the Text widgets.

💡 You can use HTML within widgets to customize the text.

Let’s use two container widgets - one for showing all the applications and the other for building the “Modify Applications” section. For showing all the applications we will use the Table widget and for Modify Applications section we will need widgets like Text Input, Dropdown, Date Picker, and Buttons.

💡 Check the documentation to learn more about customizing the widgets and making the UI more beautiful. 🌟

Once added the widgets, the final version of the app’s user interface should look similar to this:

Creating the CRUD queries

So now that we have built the user interface of the application, let’s create queries that will perform the create, read, update and delete operations on the table.

Reading data from the spreadsheet

So, the first thing that we need to do now is to create a query that will populate the table with the existing data in the Google Sheet. To do this, go to the query editor at the bottom and then click on + button to create a new query. Select the Google Sheets from the Datasource dropdown. In the Operations dropdown, select “Read data from a spreadsheet”. Enter the Spreadsheet ID and then click on the Preview button to quickly test the query.

💡 You'll find the Spreadsheet ID in the URL of your Google Sheet.
Let say the URL of Google Sheet is:
https://docs.google.com/spreadsheets/d/1gIfeXMmgJAKrWmm_MtivOj68zZM_H9D8qR5mzVvzo6c/edit?usp=sharing
In this URL, 1gIfeXMmgJAKrWmm_MtivOj68zZM_H9D8qR5mzVvzo6c is the Spreadsheet ID

As you can see the query has successfully fetched the data from the Google Sheet in the form of JSON. Now, go to the Advanced tab and toggle “Run this query on page load?” to enable the query to automatically run every time you load the app. You can rename the query from the center and click on the Create button at the top-right corner to save the query.

Adding data to the spreadsheet

Now for adding new rows to the spreadsheet, we’ll create a new query in exactly the same way we created above for reading the data. All we need to do is just set the Operation to “Append data to a spreadsheet”. In the Rows field, enter [{"id": "{{components.textinput4.value ?? ''}}", "company": "{{components.textinput1.value ?? ''}}", "position": "{{components.textinput2.value ?? ''}}", "date-applied": "{{components.datepicker1.value ?? ''}}", "status": "{{components.dropdown1.value ?? ''}}"}] - here we are using JavaScript to get the dynamic data from the widgets like text input, date-picker, and dropdown. Everytime a user will input a new data in these widgets, the query will use the data to add new row to the linked spreadsheet.

Once entered the details, rename the query and click Create.

Updating a row in the spreadsheet

Create a new query, select Google Sheets as the datasource from the dropdown. Enter the following details in their respective fields:

• Operation: Update data to a spreadsheet for updating the data
• Spreadsheet ID: ID from the URL of Google Spreadsheet
• Where, Operator, and Value: For Where we will use the id which is the key of a column, for Operator we will use === (used to check equality), and for Value we will use {{components.dropdown2.value}} i.e the value from the dropdown including row numbers.
• Body: {{({id: components.textinput4.value, company: components.textinput1.value, position: components.textinput2.value, url: components.textinput3.value, 'date-applied': components.datepicker1.value, status: components.dropdown1.value})}}

Deleting a row from the spreadsheet

Now finally create our last query for deleting the row from the table. Just follow the same steps mentioned above to create a new query for the Google Sheet datasource and enter the Spreadsheet ID. For deleting a row in the table we just need the row number, that we will take as an input from the user using the dropdown widget. So, we will use JavaScript to get the value from the dropdown widget. Just enter {{components.dropdown2.value}} in the Delete row number field.

Now, we have successfully created the four queries to perform the CRUD operations.

💡 You can add the event handler from the Advanced tab to perform an action whenever the query is successful. Like for example, you can create an event handler in each of the Append, Update and Delete queries to run the Read query - this will reload the table with the new data every time the user adds, deletes, or updates data.

Linking the queries to the UI

So now that we have built the user interface and are done creating the CRUD queries, we will now link the queries to the user interface to make our application fully functional. To do this, we just need to make changes to the properties of all the widgets that we have added. Let’s do this:

Configuring the table to display the data

• Click anywhere on the table to open the inspect panel on the right sidebar.
• In the Table data property, input {{queries.googlesheets1.data}} - this will use the data that we are getting from the query googlesheets1 and will populate into the table.
• We have six columns in our Google Sheet(mock data), so we will create 5 new columns to our table for Id, Company, Position, Date Applied, and Status. For the URL column, we can add an action button to open the URL. For every column, we need to provide a Column Name which is the name of the column that will be displayed on the UI, and the Key which is the name of the column from the google sheet.

• Create an Action button, add the button text, and set the position of the button. Create an On Click handler for this button and set Open webpage as the action. In the URL field, enter {{components.table1.selectedRow.url}} which will get the URL from the selected row in the table.

Now, run the first query to perform the read operation and it will populate the data into the table. ✨

Configuring the widgets to add, update or delete row

While building the user interface, we built a sidebar for Creating, Updating, or Deleting rows. We used widgets like text-input, date-picker, dropdown, and buttons for this section. Since we have six columns in our table, we need six components to get the user input. We used four text-inputs for Row ID, Company Name, Position, and Application URL respectively. For Date Applied we used the date-picker widget and for the Status column, we used the dropdown widget. We used an extra dropdown for selecting the row number if we want to update or delete the specific row.

Let’s configure these widgets:

Text-inputs: For all the four text-inputs (Row ID, Company Name, Position, URL) we need to just click on them to open the inspect panel and enter the placeholder.

Date-picker(Date Applied): You can keep it with its default settings.

Dropdown (Status): Add the Option values and Option label - {{["wishlist", "applied", "interview", "offer", "rejected"]}}. These are the values that will be taken by the update and append queries to update the Status column in the table.

Dropdown(For updating or deleting the row): Enter the Option values and Option label - ****{{queries.googlesheets1.data.map((datum) => datum["id"])}}. This will use the map JS function to populate dropdown options with the row numbers fetched by the googlesheets1 query.

Button1 (for appending the row): We will add an On Click handler to this button so that googlesheetsAppend query is run every time the button is clicked.

Button2 (for updating the row): Just like the button for appending the row we will add the On Click handler to this button and link the googlesheetsUpdate query.

Button3 (for deleting the row): Lastly, we will add the event handler to this button and link it to googlesheetsDelete query.

💡 You can always customize the widgets from the Style tab in inspect panel.

Great! you just built your own personal job application tracker app. You can click on the Deploy button on the top right corner, save a version and deploy it.