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 data source (here Google Sheets). For building this app, I have broken the workflow into four steps: Authenticating the datasource Building the UI Creating the CRUD queries Linking the queries to the UI 💡 ToolJet’s feature allows you to import the app into your ToolJet account using the exported source code. Here’s the in case you need to import the app. Import App exported source code Authenticating the Data source Let’s start by creating a new application in ToolJet ( ) and giving this app a name from the top left corner. https://github.com/ToolJet/ToolJet Next, connect Google Sheets to the app as the data source by clicking “ ” and then selecting Google Sheets from the list of available data sources. We need to authenticate with “ ” permissions so that our app is able to perform CRUD operations. Once authenticated, close the tab and select “ ”. add or edit datasource read and write save datasource Building the UI So now that we have added the data source let's go ahead build the user interface of our app. We will drag the components from the right sidebar i.e. onto the canvas. To give our app a header and description use the Text widgets. widget manager 💡 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 “ section. For showing all the applications we will use the and for section we will need widgets like . Modify Applications” Table widget Modify Applications Text Input, Dropdown, Date Picker, and Buttons 💡 Check the to learn more about customizing the widgets and making the UI more beautiful. 🌟 documentation 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 operations on the table. create, read, update and delete 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 from the dropdown. In the dropdown, select “ ”. Enter the and then click on the button to quickly test the query. + Google Sheets Datasource Operations Read data from a spreadsheet Spreadsheet ID Preview 💡 You'll find the Spreadsheet ID in the URL of your Google Sheet. Let say the URL of Google Sheet is: In this URL, is the Spreadsheet ID https://docs.google.com/spreadsheets/d/1gIfeXMmgJAKrWmm_MtivOj68zZM_H9D8qR5mzVvzo6c/edit?usp=sharing 1gIfeXMmgJAKrWmm_MtivOj68zZM_H9D8qR5mzVvzo6c As you can see the query has successfully fetched the data from the Google Sheet in the form of JSON. Now, go to the tab and toggle “ ” 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 button at the top-right corner to save the query. Advanced Run this query on page load? Create 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 “ ”. In the Rows field, enter - here we are using JavaScript to get the dynamic data from the widgets like text input, date-picker, and dropdown. Every time user will input new data in these widgets, the query will use the data to add a new row to the linked spreadsheet. Append data to a spreadsheet [{"id": "{{components.textinput4.value ?? ''}}", "company": "{{components.textinput1.value ?? ''}}", "position": "{{components.textinput2.value ?? ''}}", "date-applied": "{{components.datepicker1.value ?? ''}}", "status": "{{components.dropdown1.value ?? ''}}"}] Once entered the details, rename the query and click . Create Updating a Row in the Spreadsheet Create a new query, select as the datasource from the dropdown. Enter the following details in their respective fields: Google Sheets : for updating the data Operation Update data to a spreadsheet : ID from the URL of Google Spreadsheet Spreadsheet ID , , and For we will use the id which is the key of a column, for Operator we will use (used to check equality), and for we will use i.e the value from the dropdown including row numbers. Where Operator Value: Where === Value {{components.dropdown2.value}} : 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 in the field. {{components.dropdown2.value}} Delete row number Now, we have successfully created the four queries to perform the CRUD operations. 💡 You can add the event handler from the tab to perform an action whenever the query is successful. Like for example, you can create an event handler in each of the queries to run the query - this will reload the table with the new data every time the user adds, deletes, or updates data. Advanced Append, Update and Delete Read 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 property, input - this will use the data that we are getting from the query and will populate into the table. Table data {{queries.googlesheets1.data}} googlesheets1 We have six columns in our (mock data), so we will create 5 new columns to our table for . For the column, we can add an action button to open the URL. For every column, we need to provide a which is the name of the column that will be displayed on the UI, and the which is the name of the column from the google sheet. Google Sheet Id, Company, Position, Date Applied, and Status URL Column Name Key Create an , add the button text, and set the position of the button. Create an handler for this button and set as the action. In the URL field, enter which will get the URL from the selected row in the table. Action button On Click Open webpage {{components.table1.selectedRow.url}} 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 rows. We used widgets like and 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 respectively. For we used the date-picker widget and for the 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. Creating, Updating, or Deleting text-input, date-picker, dropdown, buttons Row ID, Company Name, Position, and Application URL Date Applied Status Let’s configure these widgets: For all the (Row ID, Company Name, Position, URL) we need to just click on them to open the inspect panel and enter the placeholder. Text-inputs: four text-inputs : You can keep it with its default settings. Date-picker(Date Applied) Add the and - . These are the values that will be taken by the to update the column in the table. Dropdown (Status): Option values Option label {{["wishlist", "applied", "interview", "offer", "rejected"]}} update and append queries Status Enter the and - **** . This will use the map JS function to populate dropdown options with the row numbers fetched by the query. Dropdown(For updating or deleting the row): Option values Option label {{queries.googlesheets1.data.map((datum) => datum["id"])}} googlesheets1 We will add an On Click handler to this button so that query is run every time the button is clicked. Button1 (for appending the row): googlesheetsAppend Just like the button for appending the row we will add the On Click handler to this button and link the query. Button2 (for updating the row): googlesheetsUpdate Lastly, we will add the event handler to this button and link it to query. Button3 (for deleting the row): googlesheetsDelete 💡 You can always customize the widgets from the tab in inspect panel. Style Great! you just built your own personal job application tracker app. You can click on the button on the top right corner, save a version and deploy it. Deploy First Published here
