hey everyone in today's video I'm excited to walk you through the process of using Google Sheets to create a comprehensive crud create read update delete application based on Google Sheets so let's break it down step by step here you can see I have opened the projects tab in Google Sheets it contains Fields like project name project category deadline date status approval priority and assigned user based on these records I have built a crud application that you can see here it's displaying all the projects in a nice table along with nice and Rich formatting you can also do the Sorting searching and filtering it provides rich pagination and you can also control how many items to display in a single page you can very easily create a new record by clicking on this button called new item this will open up the form let me fill this form click on save you can see our new record has appeared here on the top now to edit this record click on this pencil icon this will open up the pre-populated form you can edit this form and hit save to save your changes here you can see this row got updated to delete it click the trash icon this will ask for your permission to delete it allow to finally delete it and here you can see our record has been deleted successfully you can also sort your records by any field you like to let me show you that as well here I am sorting it by deadline date by priority and so on you can very easily filter or search through your records let me show that to you let's search for Project G let's show only pending projects so it's filtering the records nicely now if you are liking the video then please take a moment to like And subscribe moving on let's talk about how things work in more detail here I have a table with one ID column this ID column is necessary in order to perform crud operations you can name this column anything next you need to have a schema that will dictate how the table should get displayed and it will also be responsible for constructing the form the sheet containing the schema can be named anything you can simply make a copy of this sheet and customize the fields to suit your needs do not change the text in the header the entries in the key column should exactly match with the header in the record tab you can edit the labels for the corresponding Keys here this is how it will appear in the table and in the form also in the type column you can select from the available choices like text number text area select radio and checkbox required column is used to set if the fields are mandatory you can put false against the non-mandatory fields you can provide options for input Fields like select radio checkbox in this options column it should contain items separated by comma you can also pull options from different sheets like here I have pulled project categories from the dropdowns tab I have used the join function to make it a comma separated list if you want to sort your records based on certain Fields then you can set its value to true in this is sortable column if sorting doesn't make sense for a particular field then you can set its value to false for the alignment of text in a particular column you can choose from three values Start Center and end end is suitable mostly for Price or amount start is suitable for plain text and descriptions for number types you can also set its maximum and minimum values once again I am repeating the entries in the key column should match exactly with the table header once your schema is ready move to the app setting tab do not change the name of this tab as it is hardcoded in the scripts here you can provide the app name you can also choose your local and set currency this will be required for formatting dates price and amount in the table you can also change the data entry sheet and schema sheet so it allows you to have multiple data entry sheets in the same spreadsheet and and hook them with your web application at ease let me quickly show you the demo this time I'm selecting records and the corresponding schema sheet let me also change the app name now click on save settings let's go back to our application and refresh it here you can see it has loaded a new table and the app name is also different now let me click this new item button here you see the corresponding form also moving back to app settings if your table has an ID column named different than hash then you can alter that here this refresh sheet list button is meant to refresh these dropdowns containing sheet names click refresh sheet list to update the dropdown with any newly added sheets once again don't forget to choose the data entry and schema sheets and specify the ID column as this is crucial for the application to work properly if you notice here you can see nice formatting in the rating column using stars and in these columns also you can see some nice formatting for status and approval columns so you might be thinking how you can have those formatting Styles in your case for that I have kept a few key names reserved for the application so that when you use them in your schema it gets picked up by the application code to format it the way you have just seen let me show you in more detail what I mean by that here in this schema if you use names like date date 1 or date 2 or all three of them then the dates will automatically be formatted using your set local similarly if you use rating as a key name for some sort of performance field then it will automatically be formatted as stars I have provided the detailed instructions here in this comment you can read it later by making the most of these features you can seamlessly customize and adapt the application to meet your specific needs remember to save settings after any adjustments to make sure they take effect now the most important part is how do you make this yours so first of all make a copy of this spreadsheet from the link in the description then click on extensions and then apps script now click on deploy and then choose new deployment select web app as deployment type give it a name like version one in execute as dropdown select select me in who has access drop down choose any of the three available choices then click on deploy it will then ask to authorize the code go ahead and Grant all the permission it will take some time and then you will get a web app URL this is where your application will live after deployment you can also get this URL from the manage deployment section then open your spreadsheet and go to app settings click on save settings to save your schema and app settings this is important otherwise the applic a won't work now go ahead and open the URL here you got your application now let's do the code walkth through before we do code walkthr I should tell you that this is not necessary to follow as you can do most of the customization from the spreadsheet itself open the script editor by clicking on extensions and then app script you will find two files first one is called backend it contains the code that will run on the server side this will be responsible for sending an HTML page to your web browser when you visit the deployed URL and this will also contain methods that will be called from the browser which in turn will interact with your spreadsheet second file is called index.html and this is the file which gets rendered by the browser it will also contain client side JavaScript code that will call your server side code defined in the backend dogs file now let's open backend. GS this block is responsible for sending an HTML file to the request URL it takes index.html as a parameter and sets the title of the web app also this function saves the app settings along with the schema to script properties script properties store values in the form of key value pairs this is an elegant way to save small siiz data attached to spreadsheet it first fetches the app settings tab reads the top two rows and convert it into JavaScript object in the form of key value pair then it reads schema sheet as defined in the app settings tab convert the data into an array of objects with the help of map and reduce methods then it saves the schema and app setting settings into script properties and gives it a name called app prefs this block simply Returns the above saved properties this will be called by the web app which in turn will be used to construct table header and form this function is used to populate the drop-down list of sheets in the app settings sheet it first fetches all the sheets in a spreadsheet it Maps over all sheet to return sheet names then it applies drop- down data validation to the cells namely D2 and E2 in the app settings tab where we want those sheet names to appear moving on here we got our class defined for constructing lightweight omm object relational mapping that basically Maps JavaScript objects to our spreadsheet database here I have defined several methods for performing crud operations like create read update and delete it defines a Constructor to initialize the sheet as per the app settings it also sets the ID column as defined in the app settings then it exposes the create method to create a new record it first grabs the header and then appends the new row received from the web app form next it exposes the read all method to read the table it does so by writing entire data in the sheet into values which is basically a two-dimensional array then it extracts headers out of it using the zero index then treating header is key constructs an array of JavaScript objects thus turning a two-dimensional array into an array of JavaScript objects let me show you how the response looks by running this piece of code here you see it's an array of objects the object in term is key value pairs next it exposes the read by ID method it first read entire data range into values then it uses for Loop to find path ID and finally return the Matched record now it exposes the update by ID method it first finds the past ID in the data using a for Loop once it finds the Matched record it updates it next is the delete by ID method similarly it finds the ID in the record when it finds the Matched row it deletes that row get next ID is the helper method to determine the next ID while creating a new record it does so by Computing maximum of all the values in the ID column and then adding one to it now these methods defined below are higher level methods that get actually called from the front end or client side JavaScript defined in the index.html file all these methods first create an instance of om then it calls the respective method on that instance or object that's all for the server side code I will also post the relevant links from the Google app script documentation so that you can read more about the methods used in this project now come to this index.html file I won't be able to explain this file in detail as it requires you to have some experience in front-end development and also it will take a considerable amount of time to explain all of it but I will try to explain it in brief here I am using vfi CSS framework which is built on top of VJs which helps me with the CSS for the table and form and app layout and a lot more this is the official site for vuejs a JavaScript based front-end framework and this one is for vfi CSS framework I will post the link in the description I am loading the JavaScript and CSS here within the head tag this is the global div element where our entire app will be mounted by the magic of vue.js with within create app we Define all the data and methods that our app is going to use here you can see variables like header data table prefs Etc this created method which you see here runs when the app is loaded in the browser inside this we fetch schema and then call refresh method which basically loads the entire table here is the implementation of schema fetching and this is the implementation of fetching all the recordss here you can see I am calling read all records that was defined in the server side code linked to or similarly you have got methods for deleting and editing the record you can see I am calling the server side method like delete record here update record by ID here create record here in this line now the next part is the HTML template which which is responsible for laying out different HTML elements like Banner table forms snack bars Etc if you have got some experience in front-end development then you can edit this part to further customize it here you can see all these codes inside the template tag these are responsible for formatting the data in the table like this one for rating and displaying Stars these are ones for currencies and dates here you can see I have used item. date 1 date 2 and date three if you remember these were the reserved key names that you are supposed to use while preparing schema to enable certain types of desired formatting that is what I was talking about those key names are directly hardcoded in this template if you wish you can change it here and make the corresponding changes in the schema also to customize it even further explore the vuetify docs first before making any changes is that's it I know it doesn't help everyone but I am constrained here sometimes it's good to find yourself out of your comfort zone this pushes you to learn new things here it means that I am pushing you to think about integrating front-end development with your regular Google Sheets workflows if you desire an even more customized version then please contact me using my email I will try to provide that at an affordable price if you liked the video then please hit the like button and please subscribe to my channel for more such videos thanks for watching and see you in the next one