Skip to content

Instantly share code, notes, and snippets.

@moorthy6369
Forked from ex-preman/Code.gs
Created January 17, 2023 04:28
Show Gist options
  • Save moorthy6369/a01193e5c761c1ae3424df1fd0ccb2ad to your computer and use it in GitHub Desktop.
Save moorthy6369/a01193e5c761c1ae3424df1fd0ccb2ad to your computer and use it in GitHub Desktop.
CRUD Using Google Apps Script

CRUD Using Google Apps Script

I want to learn some google apps script, found some tutorial here: https://www.crazycodersclub.com/appscript/crud-operation-on-google-spread-sheet-using-google-app-script-html-jquery but the code is not work well, so here my modified code based on newest guide from google docs: https://developers.google.com/apps-script/api/reference/rest

Don't forget to create your own Google SpreadSheet and Apps Script URL then change the code

YOUR_SPREADSHEET_ID --> https://docs.google.com/spreadsheets/d/YOUR_SPREADSHEET_ID/edit#gid=0
YOUR_SHEET_NAME --> usually "Sheet1"
YOUR_SPREADSHEET_LINK --> https://docs.google.com/spreadsheets/d/xxxxxx/edit?usp=sharing

YOUR_APPS_SCRIPT_URL --> https://script.google.com/macros/s/xxxxxx/exec

After all settle then just open the html code on your browser. Have Fun !!

function doGet(e) {
Logger.log(e);
var op = e.parameter.action;
var ss = SpreadsheetApp.open(DriveApp.getFileById("YOUR_SPREADSHEET_ID"));
var sn = "YOUR_SHEET_NAME";
var sheet = ss.getSheetByName(sn);
if (op == "insert")
return insert_value(e, sheet);
//Make sure you are sending proper parameters
if (op == "read")
return read_value(e, ss, sn);
if (op == "update")
return update_value(e, sheet);
if (op == "delete")
return delete_value(e, sheet);
}
//Receive parameter and pass it to function to handle
function insert_value(request, sheet) {
var id = request.parameter.id;
var name = request.parameter.name;
//add new row with received parameter from client
var d = new Date();
var currentTime = d.toLocaleString();
var rowData = sheet.appendRow([currentTime, id, name]);
var result = "Insert successful";
result = JSON.stringify({
"result": result
});
return ContentService
.createTextOutput(request.parameter.callback + "(" + result + ")")
.setMimeType(ContentService.MimeType.JAVASCRIPT);
}
function read_value(request, ss, sheetName) {
var output = ContentService.createTextOutput(), data = {};
data.records = readData_(ss, sheetName);
var callback = request.parameters.callback;
if (callback === undefined) {
output.setContent(JSON.stringify(data));
} else {
output.setContent(callback + "(" + JSON.stringify(data) + ")");
}
output.setMimeType(ContentService.MimeType.JAVASCRIPT);
return output;
}
function readData_(ss, sheetName, properties) {
if (typeof properties == "undefined") {
properties = getHeaderRow_(ss, sheetName);
properties = properties.map(function (p) { return p.replace(/\s+/g, '_'); });
}
var rows = getDataRows_(ss, sheetName),
data = [];
for (var r = 0, l = rows.length; r < l; r++) {
var row = rows[r],
record = {};
for (var p in properties) {
record[properties[p]] = row[p];
}
data.push(record);
}
return data;
}
function getDataRows_(ss, sheetName) {
var sh = ss.getSheetByName(sheetName);
return sh.getRange(2, 1, sh.getLastRow() - 1, sh.getLastColumn()).getValues();
}
function getHeaderRow_(ss, sheetName) {
var sh = ss.getSheetByName(sheetName);
return sh.getRange(1, 1, 1, sh.getLastColumn()).getValues()[0];
}
//update function
function update_value(request, sheet) {
var id = request.parameter.id;
var flag = 0;
var country = request.parameter.name;
var lr = sheet.getLastRow();
for (var i = 1; i <= lr; i++) {
var rid = sheet.getRange(i, 2).getValue();
if (rid == id) {
sheet.getRange(i, 3).setValue(country);
var result = "value updated successfully";
flag = 1;
}
}
if (flag == 0)
var result = "id not found";
result = JSON.stringify({
"result": result
});
return ContentService
.createTextOutput(request.parameter.callback + "(" + result + ")")
.setMimeType(ContentService.MimeType.JAVASCRIPT);
}
function delete_value(request, sheet) {
var id = request.parameter.id;
var flag = 0;
var lr = sheet.getLastRow();
for (var i = 1; i <= lr; i++) {
var rid = sheet.getRange(i, 2).getValue();
if (rid == id) {
sheet.deleteRow(i);
var result = "value deleted successfully";
flag = 1;
}
}
if (flag == 0)
var result = "id not found";
result = JSON.stringify({
"result": result
});
return ContentService
.createTextOutput(request.parameter.callback + "(" + result + ")")
.setMimeType(ContentService.MimeType.JAVASCRIPT);
}
<html>
<head>
<style>
table,
th,
td {
margin: 10px 0;
border: solid 1px #333;
padding: 2px 4px;
font: 15px Verdana;
}
th {
font-weight: bold;
}
#loader {
border: 16px solid #f3f3f3;
border-radius: 50%;
border-top: 16px solid blue;
border-bottom: 16px solid blue;
width: 60px;
height: 60px;
-webkit-animation: spin 2s linear infinite;
animation: spin 2s linear infinite;
visibility: hidden;
}
@-webkit-keyframes spin {
0% {
-webkit-transform: rotate(0deg);
}
100% {
-webkit-transform: rotate(360deg);
}
}
@keyframes spin {
0% {
transform: rotate(0deg);
}
100% {
transform: rotate(360deg);
}
}
</style>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.2.0/jquery.min.js"></script>
<script>
var script_url = "YOUR_APPS_SCRIPT_URL";
// Make an AJAX call to Google Script
function insert_value() {
$("#re").css("visibility", "hidden");
document.getElementById("loader").style.visibility = "visible";
$('#mySpinner').addClass('spinner');
var id1 = $("#id").val();
var name = $("#name").val();
var url = script_url + "?callback=ctrlq&name=" + name + "&id=" + id1 + "&action=insert";
var request = jQuery.ajax({
crossDomain: true,
url: url,
method: "GET",
dataType: "jsonp"
});
}
function update_value() {
$("#re").css("visibility", "hidden");
document.getElementById("loader").style.visibility = "visible";
var id1 = $("#id").val();
var name = $("#name").val();
var url = script_url + "?callback=ctrlq&name=" + name + "&id=" + id1 + "&action=update";
var request = jQuery.ajax({
crossDomain: true,
url: url,
method: "GET",
dataType: "jsonp"
});
}
function delete_value() {
$("#re").css("visibility", "hidden");
document.getElementById("loader").style.visibility = "visible";
$('#mySpinner').addClass('spinner');
var id1 = $("#id").val();
var name = $("#name").val();
var url = script_url + "?callback=ctrlq&name=" + name + "&id=" + id1 + "&action=delete";
var request = jQuery.ajax({
crossDomain: true,
url: url,
method: "GET",
dataType: "jsonp"
});
}
// print the returned data
function ctrlq(e) {
$("#re").html(e.result);
$("#re").css("visibility", "visible");
read_value();
}
function read_value() {
$("#re").css("visibility", "hidden");
document.getElementById("loader").style.visibility = "visible";
var url = script_url + "?action=read";
$.getJSON(url, function (json) {
// Set the variables from the results array
// CREATE DYNAMIC TABLE.
var table = document.createElement("table");
var header = table.createTHead();
var row = header.insertRow(0);
var cell1 = row.insertCell(0);
var cell2 = row.insertCell(1);
cell1.innerHTML = "<b>ID</b>";
cell2.innerHTML = "<b>Name</b>";
// ADD JSON DATA TO THE TABLE AS ROWS.
for (var i = 0; i < json.records.length; i++) {
tr = table.insertRow(-1);
var tabCell = tr.insertCell(-1);
tabCell.innerHTML = json.records[i].ID;
tabCell = tr.insertCell(-1);
tabCell.innerHTML = json.records[i].NAME;
}
// FINALLY ADD THE NEWLY CREATED TABLE WITH JSON DATA TO A CONTAINER.
var divContainer = document.getElementById("showData");
divContainer.innerHTML = "";
divContainer.appendChild(table);
document.getElementById("loader").style.visibility = "hidden";
$("#re").css("visibility", "visible");
});
}
</script>
</head>
<body>
<div align="center">
<h1>CRUD OPERATION ON GOOGLE SPREAD SHEET, WEB APPLICATION USING GOOGLE APP SCRIPT .</h1>
<p>This is simple application, You can develop your own logic based on your requiremnets.<p>
<p>Be careful with the parameters you are sending, it should match the parameters that are recived in google app script.</p>
<form>
ID
<input type="text" name="id" id="id">
Name
<input type="text" name="name" id="name">
</form>
<div id="loader"></div>
<p id="re"></p>
<input type="button" id="b1" onClick="insert_value()" value="Insert"></input>
<input type="button" onclick="read_value()" value="Read" />
<input type="button" onclick="update_value()" value="Update" />
<input type="button" onclick="delete_value()" value="Delete" />
<a href="YOUR_SPREADSHEET_LINK"
target="_blank">Click here to open Spread Sheet </a>
<div id="showData"></div>
</div>
</body>
<html>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment