Google Sheets CRUD Application Tutorial

Introduction

• Topic: Creating a CRUD (Create, Read, Update, Delete) application using Google Sheets.
• Features Discussed: Project management, sorting, filtering, pagination, form creation, schema management, app settings, and deployment.
• Tools Used: Google Sheets, Google Apps Script, Vue.js, Vuetify CSS framework.

Application Overview

• Components:
  • Fields: Project name, category, deadline, status, approval, priority, assigned user.
  • Features: Table display, sorting, filtering, pagination, item creation, editing, and deletion.
• User Actions:
  • Create a new record: Click 'New Item' to open a form.
  • Edit a record: Click the pencil icon to open a pre-populated form and save changes.
  • Delete a record: Click the trash icon, confirm deletion.
  • Sort records: By any field, e.g., deadline, priority.
  • Filter/Search records: E.g., search by project name or status.

Detailed Process

1. Setting Up the Table

• ID Column: Necessary for CRUD operations; can be named anything.
• Schema: Dictates table display and form construction.
  • Must match key columns with header in the record tab.
  • Type Column Choices: Text, number, textarea, select, radio, checkbox.
  • Required Column: Indicates if fields are mandatory.
  • Options Column: For select/radio/checkbox fields; can pull from other sheets.
  • Sorting: Set sortable fields in 'is sortable' column.
  • Text Alignment: Choose from start, center, end.
  • Number Constraints: Set max and min values for number fields.
• App Setting Tab: Set app name, locale, currency, data entry sheet, schema sheet, etc.

2. Application Deployment

• Steps to Deploy:
  • Copy spreadsheet.
  • Go to Extensions → Apps Script → Deploy → New Deployment.
  • Select Web App as deployment type, execute as you, set access permissions, deploy, and authorize.
  • Save settings in the app settings tab.
  • Open the given web app URL.

Code Walkthrough

Backend (backend.gs)

• Handles server-side operations, interacting with Google Sheets.
• Key Functions:
  • Send HTML file to request URL.
  • Save app settings and schema to script properties.
  • Populate dropdown lists of sheets.
  • CRUD Operations: create, read all, read by ID, update by ID, delete by ID.
  • Helper Methods: Determine next ID.
  • Methods called from client-side JavaScript.

Frontend (index.html)

• Manages client-side operations using Vue.js and Vuetify CSS.
• Components:
  • Global element: Mounts app.
  • Create app: Defines data and methods.
  • Schema and records fetching.
  • Delete, update, create records.
  • HTML template: Lays out table, forms, snack bars.
  • Template formatting: Dates, rating, currency.
• Libraries Used: Vue.js, Vuetify CSS framework.

Customization

• Adjustments can be made directly in the Google Sheets.
• Further customization requires front-end development knowledge.
• Contact for advanced customization services.

Conclusion

• Call to Action: Like, subscribe, and contact for custom solutions.
• Encouragement: Integrate front-end development with Google Sheets workflows.

Resources

• Links to Vuetify, Vue.js documentation for deeper learning.
• Google Apps Script documentation for method references.

Contact

• Email for customized solutions at an affordable price.