Skip to content

Instantly share code, notes, and snippets.

@diki-haryadi

diki-haryadi/AppScript

Last active December 14, 2022 01:08
Show Gist options
  • Save diki-haryadi/aacddaa063baf1c5a6a4a9f30d9fdec8 to your computer and use it in GitHub Desktop.
Save diki-haryadi/aacddaa063baf1c5a6a4a9f30d9fdec8 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
AppScript
# GS Appscript
```
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);
}
```
# Postman
https://documenter.getpostman.com/view/5282315/2s8YzUyhQK
$ References
https://gist.github.com/ex-preman/e892f72404a0e4779999f8b439cec1b1
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment