Build CRUD App using React, Google Sheet and Google Apps Script

hello everyone in this video I will show you how to build and customize crud applications using react and Google app script we will use Google Sheets as the database I have designed this application based on a task management system that displays a list of tasks with their due dates and statuses the entire application can be customized simply by editing the schema which I'll demonstrate later this is the spreadsheet being used here what's great about this application is that you don't even need to create these sheets like records or other sheets the application will create them for you when it does not exist and add the missing headers you can create a new task like this what's great about this form is that it includes a creatable select feature what does that mean here you can see the project drop down you can create a new project item directly from here let me show you a complete demo by filling out this form I'll create a new project now and as you can see a new project has been added to the dropdown now I'll complete the rest of the form and click the save button here you can see that a new task record has been created successfully next let's try editing this task as you can see the task record was edited successfully to delete any record you can click the trash icon confirm to delete it you can also manage all your dropdowns from this page this page is automatically generated based on the schema provided it filters fields in the schema that require Google Sheets as the drop-down source and builds individual tabs corresponding to those fields here you can perform standard crud operations to manage your dropdowns add a new item edit it or delete it if you are liking this video then please consider subscribing to my channel and hit the like button now let's talk about how to set this up if you want to use this application as it is start by making a copy of the spreadsheet from the link in the description below then go to extensions and select app script this will open the script editor in a new tab next click deploy and choose new deployment set the deployment type to web app in the description box type anything like version one in the execut as dropdown choose me in the who has access dropdown select only myself or anyone if you want to make it public then click the deploy button you'll be prompted to authorize the script Grant the necessary permissions once deployed you'll receive a web app URL open it in a new tab you'll now see the application live if you want to modify the application click the GitHub repository Link in the description box download the zip file and open it in your code editor now we need to attach this script to a spreadsheet you can create a new spreadsheet or you can use my spreadsheet make a copy of my spreadsheet from the link given in the description box then open the script editor by going to extensions and then app script go to the settings tab and copy the script ID now in your code editor rename the configuration file to clasp. Json and replace the script ID with your copied ID now in the terminal run npm install to install all the packages you may need to install clasp if haven't done already this is the command to install clasp you can watch my previous video for the detailed instructions then deploy it by executing npm run deploy once the code is pushed go back to the script editor you'll need to deploy it again just as you did before once deployed you'll get a web app URL open it in a new new tab this time I'll use the dev URL which always points to the latest code to get the dev URL click on the deploy button and then select test deployment this will give you the dev URL this way you won't need to redeploy the app every time you make a change now the application is live to customize the application you'll need to understand the code and file structure this project is built on top of my previous project I have provided its Link in the description below let's go through a code walkth through this gas folder contains the Google apps script files these are deployed directly within the script editor of Google Sheets api.js file contains serers side apis exposed to the client for example functions to fetch or manipulate sheet data apps script. jsn is the Manifest file that defines project metadata like permissions code. JS it includes core functions that are responsible to deploy this application as a web app css. HTML and js. HTML it holds CSS and JavaScript Snippets to be injected into index.html index.html this is the main entry point of the Google app script web app front end .js this implements an abstraction for managing Google Sheets as a database this Source folder contains the react frontend code inside the source directory we have a folder for storing all the components like form table and navbar form compon component will be used by different application pages to render our form based on the provided schema it supports Dynamic fields and validations navbar renders menus based on the roots file let me show The Roots file this contain rootes to different pages in the application the title of this route object is used to construct the nav bar with corresponding menus smart table is a dynamic table component tied to the schema for displaying and interacting with data it's a complete crud table that allows us to perform create edit and delete operations you can have Rich formatting of different columns by editing the schema you won't need to modify files in the component folder you may edit those if you want to make it even more functional and Rich lib folder contain a file named udal that can contain helper functions used across the application for example formatting data transformation Etc a Pages directory is the most important one you will create different pages for your application here only like here it contains three different pages namely home dropdowns and settings we will import these pages in the routes file let me show you that file here you can see the routes file that Imports the different pages and also provides titles for those pages for example this home folder contains the schema for generating the homepage let's see the schema first and here you can see different fields for the task application you can see Logic for formatting due date and Status columns finally we generate the page in the page. jsx file now let's look at the dropdown manager page you can manage all the dropdowns belonging to different pages here to configure that first import the schema from different pages like this then uncomment this line and include all the schemas with the spread operator then it will generate different tabs corresponding to different d dropdowns like you have seen in the dropdown manager the settings page has been left empty for future modification server folder contains a file named gas. jsx this file is a bridge between the react front end and the Google app script backend functions in this file use google. script. run to call serverid Scripts now let's add one more page to this application to see how easy it is to modify the application make a duplicate copy of this folder I will name it budgeting to record the budget for different project heads and then modify the schema I will just paste the new schema here you can see new Fields like project account category budget amount spent amount and Etc now we will have to import this page into the roots file let's do that now we will deploy it the code is pushed now let's refresh the application and here you can see our new page for budgeting let's create a new record here and here you see our new record was created successfully that's it please subscribe to my channel thanks for watching and see you in the next one