Digitalization of village services is an important step in improving the quality of services to the community. Many villages still use manual systems for managing citizen data, public complaints, and even writing administrative letters. In fact, by utilizing simple and free technology, village governments can build web-based village service applications that are efficient, modern and easy to use.
One very effective solution is to create a village administration application using Google Sheets and Google Apps Script. With this method, villages can have a digital village service system without the need to rent a server or buy expensive hosting. Google Sheets can be used as a database to store citizen data and administrative services, while Google Apps Script functions as a backend that manages application logic and connects the database with website displays. All village administration processes can be carried out more quickly, transparently and organized.
In this tutorial you will learn how to create a free village service application using Google Sheets and Google Apps Script starting from creating a database, creating automatic letter templates, to deploying the application into a web app that can be accessed online.
Step 1: Create a Database Using Google Sheets
The first step in building a village service system is creating a database. This database will store all the data used by the application.
Open Google Drive then create a new Google Sheets file. Give the file a name, for example "Village Services Database".
In the spreadsheet, create several sheets with the names following:
- Login
- Citizens
- Complaints
- Contact
- Facilities ##TAG19# #Document
- Archive
- Domicile
- SKT M
- SKU
- Storage
Each sheets have different functions.
- The Login Sheet is used to store user accounts such as village admins and RT heads.
- The Citizen Sheet is used to store resident data such as full name, NIK, address, occupation, religion, marital status, and telephone number.
- The Complaint Sheet is used to store reports from the community regarding various problems in the village environment.
- Contact Sheet is used to store messages sent by the community via the contact form on the website.
- Facilities Sheet contains information about village facilities such as village offices, village halls, sports fields and other public facilities.
- Document Sheet is used to store important village documents that can be downloaded by community.
- Archive Sheet is used to record incoming and outgoing letters.
- Domicile Sheet is used to store data on applications for domicile certificates.
- SKTM Sheet is used to store data on applications for certificates of incapacity.
- SKU Sheet is used to store data on applications for certificates of incapacity. effort.
- Sheet Storage is used to store Google Drive folder IDs that will be used as file storage.
Step 2: Create a Storage Folder in Google Drive
So that files are stored neatly, create several folders in Google Drive to store documents uploaded by the system.
Examples of folders that can be created include:
- Village Document Folder
- Letter Archive Folder
- Letter Folder Domicile
- SKTM Letter Folder
- SKU Letter Folder
- Facilities Image Folder
After the folders are created, copy the ID of each folder. The folder ID can be found in the Google Drive URL.
Example folder URL:
https://drive.google.com/drive/folders/1ABCDEF123456
The folder ID are:
1ABCDEF123456
Save the ID in the Storage sheet in Google Sheets.
This folder will later be used by Google Apps Script to store uploaded files or documents generated by the system.
Steps 3: Create a Letter Template
To create a letter automatically, we need to prepare a letter template using Google Docs. Create a new document in Google Docs then design the letter format as usual. In certain sections, add a placeholder that will be replaced by the system.
Example of a placeholder in a template letter:
- {{Letter Number}}
- {{Applicant's Name}}
- {{KTP Number}}
- {{PlaceLa hir}}
- {{Date of Birth}}
- {{Address}}
- {{Employment}}
Placeholder this will be replaced automatically by Google Apps Script with data taken from Google Sheets. The same template can be created for various types of letters such as:
- Domicile Certificate
- Incapacity Certificate
- Certification Letter Business
Step 4: Create a Google Apps Script Project
Next, open the Extensions menu on Google Sheets then select Apps Script. Google Apps Script will function as a backend server that manages application logic. Create a file called code.gs. In this file various functions will be created to manage data.
Examples of functions that are usually created include:
- retrieve citizen data from Google Sheets
- save citizen data new
- delete citizen data
- save citizen complaints
- upload documents to Google Drive
- generate PDF letters from templates
- retrieve data statistics village
The main function used to display the website is:
- function doGet()
This function is used to display the HTML page of the application web.
Step 5: Create a website appearance using HTML
After the backend has been created, the next step is to create a website appearance using HTML.
Create a file called index.html. On this page you can create various sections such as:
- Village website header
- Village service menu
- Village information
- Complaint form community
- Village contact form
- List of village facilities
- Village location using Google Maps
- Village admin login
To make the website look more attractive and responsive, You can use several libraries such as:
- Bootstrap for layout design
- Awesome Font for icons
- SweetAlert for notifications
- DataTables to display tables data
- Chart.js for statistical charts
By using this library, the appearance of the website will look more modern and professional.
Step 6: Create a Dashboard Admin
Admin dashboard is used by village officials to manage all data in the system. Some features that are usually found in the admin dashboard include:
- Citizen data management
- Public complaint management
- Contact message management
- Upload village documents
- Village facility management
- Creation of domicile letter
- Creation of SKTM letter
- Creation of SKU letter
- Archive management letter
- User account management
This dashboard can only be accessed after the user has successfully logged in. Login data is taken from the Login sheet on Google Sheets.
Step 7: Create an Automatic Letter Creation System
One of the most important features in the digital village service system is automatic letter creation.
When residents submit an application letters, their data will be saved in Google Sheets.
Next Google Apps Script will carry out the following process:
- take the letter template from Google Docs
- replace placeholders with citizen data
- convert documents into files PDF
- save PDF files to Google Drive
- save PDF file links to Google Sheets
With this system, village officials no longer need to create letters manually.
Step 8: Display Statistics Village
The village services website can also display population data statistics.
Examples of statistics that can be displayed include:
- Number of male residents
- Number of residents female
- Marital status
- Number of active residents
- Number of residents moving
- Number of deceased residents
These statistics can be displayed using graphs to make it easier understood by the user.
Step 9: Deploy the application into a web app
After all the features have been created, the final step is to deploy the application. Click the Deploy menu on Google Apps Script then select New Deployment. Select deployment type as Web App.
Set the following settings:
- Execute as: Me
- Who has access: Anyone
- Then click Deploy.
Google Apps Script will generate a web app URL that can be accessed via a browser. This URL is your village service website.
Step 10: Village Website SEO Optimization
To make the village service website easy to find on search engines like Google, carry out SEO optimization. Some steps that can be taken include:
- using a clear page title
- adding a meta description
- using a neat heading structure
- providing informative content about the village
- optimize website speed
- make the website display mobile friendly
In addition, add village information pages such as:
- Profile village
- Village history
- Village vision and mission
- Village development program
- Village activity news
These pages will help improve the quality of SEO website.
- Google Sheets Database : Download
- Demo App : Village Services
- Template Docs : Folder
Google Sheets-based village service website is a very effective solution for digitizing village services. By utilizing Google Apps Script, villages can have a digital service system without having to buy a server or pay hosting costs.
Through this system, various services such as managing citizen data, public complaints, creating automatic letters, and archiving documents can be carried out more quickly, efficiently, and transparently.
Digitalization of village services also helps improve the quality of public services and makes it easier for people to access various services administration.
By following the steps in this article, you can build a modern village service system that is ready to be used to support village digitalization programs in the future.
