To get started, open Google Sheets and either create a new spreadsheet or open an existing one. This will serve as the foundation for the data manipulation and automation tasks you want to perform.
Next, navigate to the "Extensions" menu and select "Apps Script." This will open the Google Apps Script editor in a new tab. This editor is where you'll be writing the code to automate your Google Sheets tasks.
Once in the Google Apps Script editor, you'll likely see a default function. Delete any existing code, as we'll be starting from scratch. Now, let's write a simple example script that you can use as a foundation for more complex automation.
// Google Apps Script Code
function onOpen() {
var ui = SpreadsheetApp.getUi();
ui.createMenu('CRUD Web App')
.addItem('Open App', 'showSidebar')
.addToUi();
}
function showSidebar() {
var html = HtmlService.createHtmlOutputFromFile('Page')
.setTitle('CRUD Web App')
.setWidth(300);
SpreadsheetApp.getUi().showSidebar(html);
}
function getData() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
return sheet.getDataRange().getValues();
}
function addEntry(data) {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
// Get the current ID value from the last row
var lastRow = sheet.getLastRow();
var currentId = lastRow > 0 ? sheet.getRange(lastRow, 1).getValue() : 0;
// Increment the ID for the new entry
var newId = currentId + 1;
// Set the new ID in the first cell of the new row
sheet.getRange(lastRow + 1, 1).setValue(newId);
// Convert the date of birth to normal text before setting the data
var dob = data[2]; // assuming "Date of Birth" is at index 2 in the data array
data[2] = Utilities.formatDate(new Date(dob), "GMT", "yyyy-MM-dd");
// Set the rest of the data in the row
sheet.getRange(lastRow + 1, 2, 1, data.length).setValues([data]);
}
function editEntry(row, data) {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
sheet.getRange(row, 2, 1, data.length).setValues([data]);
}
function deleteEntry(id) {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
// Find the row corresponding to the provided ID
var range = sheet.getRange(1, 1, sheet.getLastRow(), 1);
var values = range.getValues();
for (var i = 0; i < values.length; i++) {
if (values[i][0] == id) {
sheet.deleteRow(i + 1);
break;
}
}
}
// Add the doGet function
function doGet(e) {
return HtmlService.createHtmlOutputFromFile('Page')
.setTitle('CRUD Web App')
.setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL);
}
Page.html
<!DOCTYPE html>
<html>
<head>
<base target="_top">
<style>
body {
font-family: 'Arial', sans-serif;
margin: 20px;
background-color: #f4f4f4;
}
header {
text-align: center;
padding: 20px;
background-color: #007BFF;
color: #fff;
}
header h1 {
margin: 0;
}
header p {
margin: 5px 0 0;
}
div {
margin-bottom: 10px;
}
label {
display: block;
margin-bottom: 5px;
font-weight: bold;
}
input {
padding: 8px;
width: 100%;
box-sizing: border-box;
}
button {
padding: 10px;
background-color: #007BFF;
color: #fff;
border: none;
cursor: pointer;
transition: background-color 0.3s ease;
}
button:hover {
background-color: #0056b3;
}
table {
width: 100%;
border-collapse: collapse;
margin-top: 20px;
background-color: #fff;
box-shadow: 0px 2px 5px rgba(0, 0, 0, 0.1);
}
th, td {
border: 1px solid #ddd;
padding: 10px;
text-align: left;
}
th {
background-color: #007BFF;
color: #fff;
}
</style>
</head>
<body>
<header>
<h1>CRUD WEB APPLICATION</h1>
<p>A project by RKSDTC</p>
</header>
<div>
<label for="candidateName">Candidate Name:</label>
<input type="text" id="candidateName">
<label for="documentNumber">Father's Name</label>
<input type="text" id="documentNumber">
<label for="dob">Date of Birth:</label>
<input type="date" id="dob">
<label for="jobName">Address</label>
<input type="text" id="jobName">
<label for="mobileNumber">Mobile Number:</label>
<input type="text" id="mobileNumber">
<label for="email">Email:</label>
<input type="email" id="email">
<button onclick="addEntry()">Add Entry</button>
</div>
<div>
<table border="1" id="dataTable">
<tr>
<th>ID</th>
<th>Candidate Name</th>
<th>Father's Name</th>
<th>Date of Birth</th>
<th>Address</th>
<th>Mobile Number</th>
<th>Email</th>
<th>Action</th>
</tr>
</table>
</div>
<script>
google.script.run.withSuccessHandler(updateTable).getData();
function updateTable(data) {
var table = document.getElementById('dataTable');
// Clear existing rows
while (table.rows.length > 1) {
table.deleteRow(1);
}
// Populate table with new data
for (var i = 1; i < data.length; i++) {
var row = table.insertRow(-1);
for (var j = 0; j < data[i].length; j++) {
var cell = row.insertCell(j);
cell.innerHTML = data[i][j];
}
var actionsCell = row.insertCell(data[i].length);
actionsCell.innerHTML = '<button onclick="deleteEntry(' + data[i][0] + ')">Delete</button>';
}
}
function addEntry() {
var candidateName = document.getElementById('candidateName').value;
var documentNumber = document.getElementById('documentNumber').value;
var dob = document.getElementById('dob').value;
var jobName = document.getElementById('jobName').value;
var mobileNumber = document.getElementById('mobileNumber').value;
var email = document.getElementById('email').value;
var data = [candidateName, documentNumber, dob, jobName, mobileNumber, email];
google.script.run.withSuccessHandler(() => {
alert('New entry added successfully!');
google.script.run.withSuccessHandler(updateTable).getData();
}).addEntry(data);
// Clear input fields after adding a new entry
document.getElementById('candidateName').value = '';
document.getElementById('documentNumber').value = '';
document.getElementById('dob').value = '';
document.getElementById('jobName').value = '';
document.getElementById('mobileNumber').value = '';
document.getElementById('email').value = '';
}
function deleteEntry(id) {
if (confirm('Are you sure you want to delete this entry?')) {
google.script.run.withSuccessHandler(() => {
alert('Entry deleted successfully!');
google.script.run.withSuccessHandler(updateTable).getData();
}).deleteEntry(id);
}
}
</script>
</body>
</html>
.png)
.png)
.png&description=05. Crud Web App to Sheet){kind=link}