Leave Approval Application using Apps Script #googlesheets

hello everyone and welcome to this video today I'll be walking you through a demo of a leave approval application which is built on top of my previous project a crud application with authentication this leave approval app can be easily adapted for other use cases involving an approval workflow for instance you could modify it for project approvals task approvals or even budget approvals basically any system where a user submits a request EST and a manager or admin is responsible for approving it now when you open the application You'll See Fields like requester from date and to date comments approver status and approvers comments let me quickly show you the spreadsheet that serves as the database for this app in the request tab we have Fields such as let me quickly show you the spreadsheet that serves as the database for this app in the schema tab you can modify the structure of the application itself this includes Fields like key labels form field types required Fields And even options for drop- down selections to learn how to edit this schema and configure the app be sure to check out my previous video I'll drop the link in the description below next we have the invitations tab which lists users who have been invited to the application and the users tab where all user data is stored back to the application interface I'm currently logged in as the owner which means I have full access on the sidebar you'll see menu buttons like home send invitation manage invitations and manage users when you click Send invitation a small form pops up with fields for the email enroll you can assign either the admin role typically for approvers or the standard role for requesters once you send the invitation the user will receive an email with an invitation code and a signup link once they sign up their details will be recorded in the user database the manage invitations tab allows you to handle any pending invitations while the manage users section gives you control over deleting users or changing their roles now let me show you the entire workflow of how this application operates I'll first log out and relog in as a standard user now logged in as a standard user I can see two existing requests let me create a new request by clicking the new request button no notice I can only select my own email in the requester field I'll enter a request for a holiday and camping trip in the comments section in the approver field I can select from a list of users with the admin role right now there's only one admin available you'll also notice I cannot edit the status or approvers comments Fields these can only be updated by the approver now let's hit the save button to generate the new request once saved you'll notice that you can modify or delete the request for requests that are approved you won't see the delete or edit options but for requests on hold both buttons are available I'll show you how to modify this Behavior later in the video next I'll log out and sign in as an admin user here you can see I'm logged in as an admin and I can view all the requests assigned to me or the ones I'm responsible for approving let's go ahead and update the status of one of these requests I'll click the edit button and notice that as an admin I cannot modify any of the requesters fields my role is only to update the status so let's approve this request once approved you'll see that I can no longer delete the request but I can still edit it however a standard user won't be able to modify any request that's been approved or rejected now I'll log back in as a standard user to show you the final workflow here you can see that my request has been approved and I don't have the option to edit or delete it anymore however for requests that are put on hold I still have the ability to modify them and resubmit this makes sense right this is how the approval workflow functions in this application now let's go through how to set everything up first make a copy of the spreadsheet using the link in the description below once you've done that go to the extensions menu and click on app script to open the script editor next click on the deploy button and choose new deployment set the deployment type as web app and give it a description like version one choose me for the execution role and under who has access select anyone after that click deploy and it will ask for authorization go ahead and Grant the necessary permissions once that's done you'll get a URL you can open it in a new test alternatively you can use the dev link so you won't need to redeploy after making changes click the test deployment button and open the URL in a new tab this URL will end with Dev now back to the spreadsheet click on the leave approval custom menu where you'll see options like setup and AD test users let me explain these options when you make a copy of the spreadsheet it will include the invitations and users tabs you can delete the existing data if you want a fresh start the setup option will automatically add a new invitation in the corresponding owner user so you can begin using the application immediately by default the password is set to 1 2 3 4 as the owner you you don't need to enter your email you'll see a role like super admin assigned to the owner and the invitation code will be generated automatically if you invite a new user their status will be set to new unlike the owner whose status is inactive because they're already in the system the next option ADD test users allows you to add sample users for testing let me show you how it works I'll click this and you'll see that it added one owner and three test users you can log in as Tom priia or Julie using the password 1234 to test the application finally go to the app settings and click save settings to ensure your schema and app settings are saved after that refresh the application I'll log in again as the owner to show you how it looks once everything is set up I'll also show you where to change these default passwords to change the password simply go to edit profile and click change password so that's the basic setup in workflow of the leave approval application you can now start using it with real users and requests now let me show you how to send an invitation and sign up a user first I'll go to the send invitation section set the roll as standard and hit send the invitation email has been sent so let me open my email to retrieve the invitation code once I have it I'll use the code to complete the signup process sign up completed now a log in but since I haven't created any requests yet everything appears empty let's go ahead and create a new request the new request has been created successfully that's how this application works now let me briefly explain the code and guide you through the customization process to start open the script editor everything you need to modify this application can be found in the approval mixing file to fully understand all the code you can refer to my previous projects but in this video I'll focus on the parts responsible for the approval workflow when you open the approval mixing file at the top you'll see the logic that controls which fields are disabled for standard users and which fields are disabled for admin users for example you'll see that for standard users the status and approver comments fields are disabled while for admins Fields like requeste from date to date and comments are disabled it's important to ensure the field names match exactly with what's in the spreadsheet pay close attention to the spellings next there's logic to filter data based on the user's role whether they are a standard user or an admin you'll see that when the role is standard I'm populating options for Fields like requeste and approvers you can modify this Behavior based on your needs by editing the logic here for example when the user role is admin the logic defines what actions the admin can take you can add your own custom logic to control how the application behaves depending on the user's role similarly the function for displaying the add new button is set up so that only the requeste or owner can add new requests but the admin cannot this ensures the right behavior for each role in the same way you can edit the visibility of the edit and delete buttons for example when a request is approved or rejected and the user is a standard user I disable the edit button you can modify this Behavior by updating the logic in the show edit button and show delete button functions to customize how data is filtered based on the user role you can edit the function filter data on role basis this is where you can Implement your own rules for displaying data based on who's logged in now let me show you how to customize the status formatting in the status field you may have noticed that the statuses are formatted with different colors and icons to modify this go to the table. HTML file around line 223 you'll find the code that handles the status formatting here you can change how the status is displayed based on its value for example if the status is approved I'm applying a teal color if it's on hold it's orange and for any other status it's red you can change this color scheme or the logic as needed by updating this block of code similarly I've added formatting for other fields like priority and tags you can modify these as well to match your custom requirements for authentication related files you can refer to my previous video titled crud app with authentication other files related to basic crud operation ations have been covered in my previous video titled crud app if you watch my videos in sequence crud app crud app with authentication and this leave approval application you'll get a solid understanding of the code flow and how everything works together that's it for this video if you found it helpful please hit the like button and subscribe to my channel for further customization or assistance feel free to reach out to me I've included my WhatsApp number in the description below thank you for watching and I'll see you in the next video