Website CRUD Table Dynamic Menggunakan Skema Pada Web Apps Script
1. Copy Spreadsheet (Klik Disini)
2. Pada Spreadsheet terdapat beberapa sheet deafult yaitu :
- Skema_CRUD
- Skema Dropdown (Sebagai contoh)
- App Settings
3. Sheet Skema_CRUD
Pada sheet ini terdapat beberapa kolom yang berisikan skema yang akan menentukan form inputan dan tabel.
Note : Baris yang berwarna biru adalah skema yang paten/tidak boleh dirubah, dikarenakan menghubungkan script dan tampilan pada website.
4. Sheet Skema Dropdown
Pada sheet ini terdapat beberapa daftar nama-nama yang bisa di panggil ke sheet skema.
5. Sheet App Settings
Sheet ini merupakan kontrol untuk Website, CRUD dan Tabel yang akan di tampilkan. Pada sheet ini terdapat beberapa kolom yang harus di sesuiakan antara lain :
- AppName : Merupakan nama website yang bisa anda sesuaikan.
- Locale dan Currency : Lokasi dan Mata Uang apabila pada form terdapat matauang
- DataEntrySheet : Pilihlah sheet untuk menyimpan data
- SchemaSheet : Pilihlah sheet yang berisi skema untuk diterapkan
- IdColumn : Pada saat membuat sheet baru untuk menyimpan data, silahkan beri tanda (#) pada kolom pertama untuk id nomor
- Refresh Sheet : Merupakan tombol refresh sheet aapabila ada perubahan skema pada sheet.
- Save Setting : Merupakan tombol untuk menyimpan pengaturan yang telah dibuat.
6. Untuk membuat Website CRUD Tabel Dynamic nya silahkan buatlah lembar kerja Apps Script dengan cara klik menu Ekstensi/Extensions lalu pilih Apps Script.
7. Pada lembar kerja Apps Script, terdapat 3 file default yaitu :
- Code.gs
- index.html
- visit.html
8. Copy dan pastekan script di bawah ini ke Code.gs
Masukkan Password Untuk Melihat Script (Password ada di dalam video)
// www.javabitpro.com
function doGet() {
return HtmlService.createTemplateFromFile("index.html")
.evaluate()
.setTitle("Google Apps Script - Web App Template")
.setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL);
}
function include(filename) {
return HtmlService.createHtmlOutputFromFile(filename)
.getContent();
}
const saveAppPrefs = () => {
const obj = {};
const settingsSheet =
SpreadsheetApp.getActiveSpreadsheet().getSheetByName("App Settings");
const appSettings = settingsSheet
.getRange(1, 1, settingsSheet.getLastRow(), settingsSheet.getLastColumn())
.getValues();
const appSettingsHeaders = appSettings.shift();
const appSettingsJsonArray = appSettings.map((row) => {
return row.reduce((obj, value, index) => {
obj[appSettingsHeaders[index]] = value;
return obj;
}, {});
});
obj.appSettings = appSettingsJsonArray[0];
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(
obj.appSettings.SchemaSheet
);
const data = sheet
.getRange(1, 1, sheet.getLastRow(), sheet.getLastColumn())
.getValues();
const headers = data.shift();
const jsonArray = data.map((row) => {
return row.reduce((obj, value, index) => {
obj[headers[index]] = value;
return obj;
}, {});
});
obj.schema = jsonArray;
// save to script properties
PropertiesService.getScriptProperties().setProperty(
"appPrefs",
JSON.stringify(obj)
);
return jsonArray;
};
// This function is used to get the appPrefs from the script properties
function getAppPrefs() {
const appPrefs =
PropertiesService.getScriptProperties().getProperty("appPrefs");
return JSON.parse(appPrefs);
}
// This function is used to populate the dropdown list of sheets in the App Settings sheet
function getAllSheets() {
const sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
const sheetNames = sheets.map((sheet) => sheet.getName());
const rule = SpreadsheetApp.newDataValidation()
.requireValueInList(sheetNames)
.build();
const sheet =
SpreadsheetApp.getActiveSpreadsheet().getSheetByName("App Settings");
sheet.getRange("D2:E2").setDataValidation(rule);
}
// ----------------- ORM Class -------------------
class ORM {
constructor() {
this.sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(
getAppPrefs().appSettings.DataEntrySheet
);
this.ID_COL = getAppPrefs().appSettings.IdColumn;
}
// Create a new record
create(data) {
const id = this.getNextId() || 1;
data[this.ID_COL] = id;
//Get 2 dimensional Array Data from dataset
const headers = this.sheet
.getRange(1, 1, 1, this.sheet.getLastColumn())
.getValues()[0];
const newRow = [];
for (const header of headers) {
newRow.push(data[header] || "");
}
this.sheet.appendRow(newRow);
}
// Read all records
readAll() {
const dataRange = this.sheet.getDataRange();
const values = dataRange.getValues();
const headers = values[0];
const records = [];
//Returning data from multi-dimensional array
for (let i = 1; i < values.length; i++) {
const record = {};
for (let j = 0; j < headers.length; j++) {
record[headers[j]] = values[i][j];
}
records.push(record);
}
Logger.log(records);
return records.reverse();
}
// Read a specific record by ID
readById(id) {
const dataRange = this.sheet.getDataRange();
const values = dataRange.getValues();
const headers = values[0];
for (let i = 1; i < values.length; i++) {
if (values[i][0] === id) {
const record = {};
for (let j = 0; j < headers.length; j++) {
record[headers[j]] = values[i][j];
}
return record;
}
}
return null;
}
// Update a record by ID
updateById(data) {
const dataRange = this.sheet.getDataRange();
const values = dataRange.getValues();
const headers = values[0];
for (let i = 1; i < values.length; i++) {
if (values[i][0] == data[this.ID_COL]) {
for (const key in data) {
const columnIndex = headers.indexOf(key);
if (columnIndex !== -1) {
values[i][columnIndex] = data[key];
}
}
dataRange.setValues(values);
return true;
}
}
return false;
}
// Delete a record by ID
deleteById(record) {
const id = record[this.ID_COL];
const dataRange = this.sheet.getDataRange();
const values = dataRange.getValues();
for (let i = 1; i < values.length; i++) {
if (values[i][0] == id) {
this.sheet.deleteRow(i + 1);
return true;
}
}
return false;
}
// Get the next ID
getNextId() {
const dataRange = this.sheet.getDataRange();
const values = dataRange.getValues();
let maxId = 0;
for (let i = 1; i < values.length; i++) {
const id = values[i][0];
if (id > maxId) {
maxId = id;
}
}
return maxId + 1;
}
}
// Create a new record
function createRecord(data) {
const orm = new ORM();
orm.create(data);
}
// Read all records
function readAllRecords() {
const orm = new ORM();
const allRecords = orm.readAll();
return JSON.stringify(allRecords);
}
// Read a specific record by ID
function readRecordById(recordId) {
const orm = new ORM();
const specificRecord = orm.readById(recordId);
}
// Update a record by ID
function updateRecordById(data) {
const orm = new ORM();
const isUpdated = orm.updateById(data);
}
// Delete a record by ID
function deleteRecord(record) {
const orm = new ORM();
const isDeleted = orm.deleteById(record);
}
function SixHourlyMaintenanceRun(){
seedDataToActiveSpreadsheet(fake_employees,"Records");
seedDataToActiveSpreadsheet(fake_projects,"Projects");
}
const seedDataToActiveSpreadsheet = (data = fake_projects,targetSheetName) => {
data.shift();
data = data.map((row,i)=>{
row = [i+1,...row];
return row;
})
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(targetSheetName);
sheet.getDataRange().offset(1,0).clearContent();
sheet.getRange(2, 1, data.length, data[0].length)
.setValues(data);
};
// www.javabitpro.com
function doGet() {
return HtmlService.createTemplateFromFile("index.html")
.evaluate()
.setTitle("Google Apps Script - Web App Template")
.setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL);
}
function include(filename) {
return HtmlService.createHtmlOutputFromFile(filename)
.getContent();
}
const saveAppPrefs = () => {
const obj = {};
const settingsSheet =
SpreadsheetApp.getActiveSpreadsheet().getSheetByName("App Settings");
const appSettings = settingsSheet
.getRange(1, 1, settingsSheet.getLastRow(), settingsSheet.getLastColumn())
.getValues();
const appSettingsHeaders = appSettings.shift();
const appSettingsJsonArray = appSettings.map((row) => {
return row.reduce((obj, value, index) => {
obj[appSettingsHeaders[index]] = value;
return obj;
}, {});
});
obj.appSettings = appSettingsJsonArray[0];
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(
obj.appSettings.SchemaSheet
);
const data = sheet
.getRange(1, 1, sheet.getLastRow(), sheet.getLastColumn())
.getValues();
const headers = data.shift();
const jsonArray = data.map((row) => {
return row.reduce((obj, value, index) => {
obj[headers[index]] = value;
return obj;
}, {});
});
obj.schema = jsonArray;
// save to script properties
PropertiesService.getScriptProperties().setProperty(
"appPrefs",
JSON.stringify(obj)
);
return jsonArray;
};
// This function is used to get the appPrefs from the script properties
function getAppPrefs() {
const appPrefs =
PropertiesService.getScriptProperties().getProperty("appPrefs");
return JSON.parse(appPrefs);
}
// This function is used to populate the dropdown list of sheets in the App Settings sheet
function getAllSheets() {
const sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
const sheetNames = sheets.map((sheet) => sheet.getName());
const rule = SpreadsheetApp.newDataValidation()
.requireValueInList(sheetNames)
.build();
const sheet =
SpreadsheetApp.getActiveSpreadsheet().getSheetByName("App Settings");
sheet.getRange("D2:E2").setDataValidation(rule);
}
// ----------------- ORM Class -------------------
class ORM {
constructor() {
this.sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(
getAppPrefs().appSettings.DataEntrySheet
);
this.ID_COL = getAppPrefs().appSettings.IdColumn;
}
// Create a new record
create(data) {
const id = this.getNextId() || 1;
data[this.ID_COL] = id;
//Get 2 dimensional Array Data from dataset
const headers = this.sheet
.getRange(1, 1, 1, this.sheet.getLastColumn())
.getValues()[0];
const newRow = [];
for (const header of headers) {
newRow.push(data[header] || "");
}
this.sheet.appendRow(newRow);
}
// Read all records
readAll() {
const dataRange = this.sheet.getDataRange();
const values = dataRange.getValues();
const headers = values[0];
const records = [];
//Returning data from multi-dimensional array
for (let i = 1; i < values.length; i++) {
const record = {};
for (let j = 0; j < headers.length; j++) {
record[headers[j]] = values[i][j];
}
records.push(record);
}
Logger.log(records);
return records.reverse();
}
// Read a specific record by ID
readById(id) {
const dataRange = this.sheet.getDataRange();
const values = dataRange.getValues();
const headers = values[0];
for (let i = 1; i < values.length; i++) {
if (values[i][0] === id) {
const record = {};
for (let j = 0; j < headers.length; j++) {
record[headers[j]] = values[i][j];
}
return record;
}
}
return null;
}
// Update a record by ID
updateById(data) {
const dataRange = this.sheet.getDataRange();
const values = dataRange.getValues();
const headers = values[0];
for (let i = 1; i < values.length; i++) {
if (values[i][0] == data[this.ID_COL]) {
for (const key in data) {
const columnIndex = headers.indexOf(key);
if (columnIndex !== -1) {
values[i][columnIndex] = data[key];
}
}
dataRange.setValues(values);
return true;
}
}
return false;
}
// Delete a record by ID
deleteById(record) {
const id = record[this.ID_COL];
const dataRange = this.sheet.getDataRange();
const values = dataRange.getValues();
for (let i = 1; i < values.length; i++) {
if (values[i][0] == id) {
this.sheet.deleteRow(i + 1);
return true;
}
}
return false;
}
// Get the next ID
getNextId() {
const dataRange = this.sheet.getDataRange();
const values = dataRange.getValues();
let maxId = 0;
for (let i = 1; i < values.length; i++) {
const id = values[i][0];
if (id > maxId) {
maxId = id;
}
}
return maxId + 1;
}
}
// Create a new record
function createRecord(data) {
const orm = new ORM();
orm.create(data);
}
// Read all records
function readAllRecords() {
const orm = new ORM();
const allRecords = orm.readAll();
return JSON.stringify(allRecords);
}
// Read a specific record by ID
function readRecordById(recordId) {
const orm = new ORM();
const specificRecord = orm.readById(recordId);
}
// Update a record by ID
function updateRecordById(data) {
const orm = new ORM();
const isUpdated = orm.updateById(data);
}
// Delete a record by ID
function deleteRecord(record) {
const orm = new ORM();
const isDeleted = orm.deleteById(record);
}
function SixHourlyMaintenanceRun(){
seedDataToActiveSpreadsheet(fake_employees,"Records");
seedDataToActiveSpreadsheet(fake_projects,"Projects");
}
const seedDataToActiveSpreadsheet = (data = fake_projects,targetSheetName) => {
data.shift();
data = data.map((row,i)=>{
row = [i+1,...row];
return row;
})
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(targetSheetName);
sheet.getDataRange().offset(1,0).clearContent();
sheet.getRange(2, 1, data.length, data[0].length)
.setValues(data);
};
9. Copy dan pastekan script di bawah ini ke index.html
Masukkan Password Untuk Melihat Script (Password sama dengan di atas)
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<link href="https://cdn.jsdelivr.net/npm/@mdi/font@4.x/css/materialdesignicons.min.css" rel="stylesheet" />
<script src="https://unpkg.com/vue@3"></script>
<script src="https://cdn.jsdelivr.net/npm/vuetify@3.0.5"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/vuetify/3.4.8/vuetify-labs.min.js"
integrity="sha512-5xeIAXqNP/DWGkolQzdPAL042aA4Lb8SCMy/Ju+9yzvf9SzfsbzICQwYyMrhbN8pG8m0LWhMl9BISpIDs8RquQ=="
crossorigin="anonymous" referrerpolicy="no-referrer"></script>
<link href="https://cdnjs.cloudflare.com/ajax/libs/vuetify/3.4.8/vuetify-labs.min.css" rel="stylesheet" />
<!-- font-awesome@6.2.0 icon Visit -->
<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.4.1/css/bootstrap.min.css" integrity="sha384-Vkoo8x4CGsO3+Hhxv8T/Q5PaXtkKtu6ug5TOeNV6gBiFeWPGFN9MuhOf23Q9Ifjh" crossorigin="anonymous">
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.2.0/css/all.min.css">
<?!= include('visit')?>
</head>
<body>
<div id="app"></div>
<script type="module">
const { createApp } = Vue;
const { createVuetify } = Vuetify;
const vuetify = createVuetify();
createApp({
template: "#app-template",
data: () => ({
dialog: false,
dialogDelete: false,
schema: null,
prefs: null,
search: "",
headers: [],
rules: {
required: (value) => !!value || "Required.",
},
dataTable: [],
editedIndex: -1,
editedItem: {},
defaultItem: {},
snackbar: {
text: "",
show: false,
timeout: 2000,
color: "success",
multiline: true,
vertical: true,
right: true,
bottom: true,
},
}),
computed: {
formTitle() {
return this.editedIndex === -1 ? "New Item" : "Edit Item";
},
},
watch: {
dialog(val) {
val || this.close();
},
dialogDelete(val) {
val || this.closeDelete();
},
},
created() {
this.fetchSchema();
this.refresh();
},
methods: {
fetchSchema() {
google.script.run
.withSuccessHandler((res) => {
this.schema = res.schema;
this.prefs = res.appSettings;
const schema = res.schema;
this.headers = schema.map((item) => {
return {
title: item.label,
key: item.key,
sortable: item.sortable || true,
align: item.align || "start",
};
});
this.headers.push({
text: "Actions",
key: "actions",
sortable: false,
});
console.log("Headers", this.headers);
this.editedItem = schema.reduce((acc, item) => {
acc[item.key] = item.defaultValue || "";
return acc;
}, {});
this.defaultItem = this.editedItem;
})
.withFailureHandler((error) => {
console.log(error);
})
.getAppPrefs();
},
initialize() {
google.script.run
.withSuccessHandler((res) => {
this.dataTable = JSON.parse(res);
console.log(this.dataTable);
})
.withFailureHandler((error) => {
console.log(error);
})
.readAllRecords();
},
refresh() {
google.script.run
.withSuccessHandler((res) => {
// this.dataTable = JSON.parse(res);
let data = JSON.parse(res);
// convert date strings to date objects
const dateFlds = this.schema.filter(
(fld) => fld.type === "date"
);
data = data.map((item) => {
dateFlds.forEach((fld) => {
const dt = new Date(item[fld.key]);
item[fld.key] = `${dt.getFullYear()}-${(
"0" +
(dt.getMonth() + 1)
).slice(-2)}-${("0" + dt.getDate()).slice(-2)}`;
});
return item;
});
this.dataTable = data;
})
.withFailureHandler((error) => {
console.log(error);
})
.readAllRecords();
},
editItem(item) {
this.editedIndex = this.dataTable.indexOf(item);
this.editedItem = Object.assign({}, item);
this.dialog = true;
},
deleteItem(item) {
this.editedIndex = this.dataTable.indexOf(item);
this.editedItem = Object.assign({}, item);
this.dialogDelete = true;
},
deleteItemConfirm() {
this.showSnackbar("Deleting item...", "warning");
google.script.run
.withSuccessHandler((res) => {
console.log(res);
this.dataTable.splice(this.editedIndex, 1);
this.closeDelete();
this.refresh();
this.showSnackbar("Item deleted successfully", "success");
})
.withFailureHandler((error) => {
console.log(error);
})
.deleteRecord(this.editedItem);
},
close() {
this.dialog = false;
this.$nextTick(() => {
this.editedItem = Object.assign({}, this.defaultItem);
this.editedIndex = -1;
});
},
closeDelete() {
this.dialogDelete = false;
this.$nextTick(() => {
this.editedItem = Object.assign({}, this.defaultItem);
this.editedIndex = -1;
});
},
save() {
this.showSnackbar("Saving item...", "info");
if (this.editedIndex > -1) {
console.log("Edited Item", this.editedItem);
google.script.run
.withSuccessHandler((res) => {
this.showSnackbar(
`Item saved successfully!\nRefreshing data..`,
"success"
);
this.refresh();
this.resetForm();
})
.withFailureHandler((error) => {
console.log(error);
})
.updateRecordById(this.editedItem);
} else {
google.script.run
.withSuccessHandler((res) => {
this.showSnackbar(
`Item saved successfully!\nRefreshing data..`,
"success"
);
this.refresh();
this.resetForm();
})
.withFailureHandler((error) => {
console.log(error);
})
.createRecord(this.editedItem);
}
this.close();
},
resetForm() {
this.editedItem = Object.assign({}, this.defaultItem);
this.editedIndex = -1;
},
showSnackbar(text, color) {
this.snackbar.text = text;
this.snackbar.color = color;
this.snackbar.show = true;
},
},
})
.use(vuetify)
.mount("#app");
</script>
<script type="text/x-template" id="app-template">
<v-app>
<v-data-table
:headers="headers"
:items="dataTable"
:search="search"
:sort-by="[{ key: 'calories', order: 'asc' }]"
fixed-header
>
<template v-slot:top>
<v-toolbar
:elevation="6"
>
<v-toolbar-title class="text-h5 text-primary">{{prefs.AppName}}</v-toolbar-title>
<v-text-field
v-model="search"
prepend-inner-icon="mdi-magnify"
density="compact"
label="Search"
single-line
flat
hide-details
variant="solo-filled"
></v-text-field>
<v-spacer></v-spacer>
<v-dialog
v-model="dialog"
max-width="900px"
>
<template v-slot:activator="{ props }">
<v-btn
color="red"
dark
class="mb-2"
v-bind="props"
prepend-icon="mdi-plus"
variant="elevated"
>
New Item
</v-btn>
</template>
<v-card>
<v-card-title>
<span class="text-h5 text-primary">{{ formTitle }}</span>
</v-card-title>
<v-card-text>
<v-container>
<v-row>
<v-col
v-for="fld in schema"
:key="fld.key"
cols="12"
sm="6"
md="4"
>
<div v-if="fld.type === 'text'">
<v-text-field
v-model="editedItem[fld.key]"
:label="fld.label"
:rules="fld.required ? [rules.required]:[]"
></v-text-field>
</div>
<div v-if="fld.type === 'textarea'">
<v-textarea
v-model="editedItem[fld.key]"
:label="fld.label"
:rules="fld.required ? [rules.required]:[]"
></v-textarea>
</div>
<div v-if="fld.type === 'select'">
<v-select
v-model="editedItem[fld.key]"
:label="fld.label"
:items="fld.options.split(',')"
:rules="fld.required ? [rules.required]:[]"
></v-select>
</div>
<div v-if="fld.type === 'checkbox'">
<v-checkbox
v-model="editedItem[fld.key]"
:label="fld.label"
:rules="fld.required ? [rules.required]:[]"
></v-checkbox>
</div>
<div v-if="fld.type === 'radio'">
<v-radio-group inline v-model="editedItem[fld.key]" :rules="fld.required ? [rules.required]:[]">
<template v-slot:label>
<div>{{fld.label}}</div>
</template>
<v-radio
v-for="opt in fld.options.split(',')"
:key="opt"
:label="opt"
:value="opt"
></v-radio>
</v-radio-group>
</div>
<div v-if="fld.type === 'date'">
<v-text-field
v-model="editedItem[fld.key]"
:label="fld.label"
type="date"
:rules="fld.required ? [rules.required]:[]"
></v-text-field>
</div>
<div v-if="fld.type === 'number'">
<v-text-field
v-model="editedItem[fld.key]"
:label="fld.label"
type="number"
:min="fld.min"
:max="fld.max"
:rules="fld.required ? [rules.required]:[]"
></v-text-field>
</div>
</v-col>
</v-row>
</v-container>
</v-card-text>
<v-card-actions>
<v-spacer></v-spacer>
<v-btn
color="blue-darken-1"
variant="text"
@click="close"
>
Cancel
</v-btn>
<v-btn
color="blue-darken-1"
variant="text"
@click="save"
>
Save
</v-btn>
</v-card-actions>
</v-card>
</v-dialog>
<v-dialog v-model="dialogDelete" max-width="500px">
<v-card>
<v-card-title class="text-h5">Are you sure you want to delete this item?</v-card-title>
<v-card-actions>
<v-spacer></v-spacer>
<v-btn color="blue-darken-1" variant="text" @click="closeDelete">Cancel</v-btn>
<v-btn color="blue-darken-1" variant="text" @click="deleteItemConfirm">OK</v-btn>
<v-spacer></v-spacer>
</v-card-actions>
</v-card>
</v-dialog>
</v-toolbar>
</template>
<template v-slot:item.rating="{ item }">
<v-rating
:model-value="item.rating"
color="blue-darken-2"
density="compact"
size="small"
readonly
></v-rating>
</template>
<template v-slot:item.amount="{ item }">
<div class="text-end">
{{ item.amount.toLocaleString(prefs.Locale, {
style: "currency",
currency: prefs.Currency,
}) }}
</div>
</template>
<template v-slot:item.amount1="{ item }">
<div class="text-end">
{{ item.amount1.toLocaleString(prefs.Locale, {
style: "currency",
currency: prefs.Currency,
}) }}
</div>
</template>
<template v-slot:item.amount2="{ item }">
<div class="text-end">
{{ item.amount2.toLocaleString(prefs.Locale, {
style: "currency",
currency: prefs.Currency,
}) }}
</div>
</template>
<template v-slot:item.date="{ item }">
<div class="text-center">
{{ new Date(item.date).toLocaleDateString(prefs.Locale) }}
</div>
</template>
<template v-slot:item.date1="{ item }">
<div class="text-center">
{{ new Date(item.date1).toLocaleDateString(prefs.Locale) }}
</div>
</template>
<template v-slot:item.date2="{ item }">
<div class="text-center">
{{ new Date(item.date2).toLocaleDateString(prefs.Locale) }}
</div>
</template>
<template v-slot:item.stock="{ item }">
<div class="text-end">
<v-chip
:color="item.stock ? 'green' : 'red'"
:text="item.stock ? 'In stock' : 'Out of stock'"
class="text-uppercase"
label
size="small"
></v-chip>
</div>
</template>
<template v-slot:item.approval="{ item }">
<div class="text-center">
<v-chip
:color="item.approval == 'Yes' ? 'green' : 'red'"
:text="item.approval == 'Yes' ? 'Yes' : 'No'"
:prepend-icon="item.approval == 'Yes' ? 'mdi-checkbox-marked-circle' : 'mdi-close'"
class="ma-2"
size="small"
>
{{item.approval}}</v-chip>
</div>
</template>
<template v-slot:item.status="{ item }">
<div class="text-center">
<v-chip
class="ma-2"
:color="item.status ==='Completed' ?'teal': item.status ==='In-Progress' ? 'orange' : 'red'"
:prepend-icon="item.status ==='Completed' ?'mdi-checkbox-marked-circle': item.status ==='In-Progress' ? 'mdi-progress-alert' : 'mdi-alert'"
size="small"
>
{{item.status}}
</v-chip>
</div>
</template>
<template v-slot:item.priority="{ item }">
<div class="text-end">
<v-chip
:color="item.priority ==='High' ? 'red' : item.priority ==='Medium' ? 'orange' : 'green'"
:text="item.priority"
class="text-uppercase"
label
size="small"
></v-chip>
</div>
</template>
<template v-slot:item.actions="{ item }">
<v-icon
size="small"
class="me-2"
@click="editItem(item)"
>
mdi-pencil
</v-icon>
<v-icon
size="small"
@click="deleteItem(item)"
color="red"
>
mdi-delete
</v-icon>
</template>
<template v-slot:no-data>
<v-btn
color="primary"
@click="initialize"
>
Reset
</v-btn>
</template>
</v-data-table>
<v-snackbar
v-model="snackbar.show"
:color="snackbar.color"
:timeout="snackbar.timeout"
:vertical="snackbar.vertical"
:right="snackbar.right"
:bottom="snackbar.bottom"
multi-line
>
<strong>{{snackbar.text}}</strong>
<template v-slot:actions>
<v-btn
color="red"
variant="text"
@click="snackbar.show = false"
>
Close
</v-btn>
</template>
</v-snackbar>
</v-app>
</script>
</body>
</html>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<link href="https://cdn.jsdelivr.net/npm/@mdi/font@4.x/css/materialdesignicons.min.css" rel="stylesheet" />
<script src="https://unpkg.com/vue@3"></script>
<script src="https://cdn.jsdelivr.net/npm/vuetify@3.0.5"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/vuetify/3.4.8/vuetify-labs.min.js"
integrity="sha512-5xeIAXqNP/DWGkolQzdPAL042aA4Lb8SCMy/Ju+9yzvf9SzfsbzICQwYyMrhbN8pG8m0LWhMl9BISpIDs8RquQ=="
crossorigin="anonymous" referrerpolicy="no-referrer"></script>
<link href="https://cdnjs.cloudflare.com/ajax/libs/vuetify/3.4.8/vuetify-labs.min.css" rel="stylesheet" />
<!-- font-awesome@6.2.0 icon Visit -->
<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.4.1/css/bootstrap.min.css" integrity="sha384-Vkoo8x4CGsO3+Hhxv8T/Q5PaXtkKtu6ug5TOeNV6gBiFeWPGFN9MuhOf23Q9Ifjh" crossorigin="anonymous">
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.2.0/css/all.min.css">
<?!= include('visit')?>
</head>
<body>
<div id="app"></div>
<script type="module">
const { createApp } = Vue;
const { createVuetify } = Vuetify;
const vuetify = createVuetify();
createApp({
template: "#app-template",
data: () => ({
dialog: false,
dialogDelete: false,
schema: null,
prefs: null,
search: "",
headers: [],
rules: {
required: (value) => !!value || "Required.",
},
dataTable: [],
editedIndex: -1,
editedItem: {},
defaultItem: {},
snackbar: {
text: "",
show: false,
timeout: 2000,
color: "success",
multiline: true,
vertical: true,
right: true,
bottom: true,
},
}),
computed: {
formTitle() {
return this.editedIndex === -1 ? "New Item" : "Edit Item";
},
},
watch: {
dialog(val) {
val || this.close();
},
dialogDelete(val) {
val || this.closeDelete();
},
},
created() {
this.fetchSchema();
this.refresh();
},
methods: {
fetchSchema() {
google.script.run
.withSuccessHandler((res) => {
this.schema = res.schema;
this.prefs = res.appSettings;
const schema = res.schema;
this.headers = schema.map((item) => {
return {
title: item.label,
key: item.key,
sortable: item.sortable || true,
align: item.align || "start",
};
});
this.headers.push({
text: "Actions",
key: "actions",
sortable: false,
});
console.log("Headers", this.headers);
this.editedItem = schema.reduce((acc, item) => {
acc[item.key] = item.defaultValue || "";
return acc;
}, {});
this.defaultItem = this.editedItem;
})
.withFailureHandler((error) => {
console.log(error);
})
.getAppPrefs();
},
initialize() {
google.script.run
.withSuccessHandler((res) => {
this.dataTable = JSON.parse(res);
console.log(this.dataTable);
})
.withFailureHandler((error) => {
console.log(error);
})
.readAllRecords();
},
refresh() {
google.script.run
.withSuccessHandler((res) => {
// this.dataTable = JSON.parse(res);
let data = JSON.parse(res);
// convert date strings to date objects
const dateFlds = this.schema.filter(
(fld) => fld.type === "date"
);
data = data.map((item) => {
dateFlds.forEach((fld) => {
const dt = new Date(item[fld.key]);
item[fld.key] = `${dt.getFullYear()}-${(
"0" +
(dt.getMonth() + 1)
).slice(-2)}-${("0" + dt.getDate()).slice(-2)}`;
});
return item;
});
this.dataTable = data;
})
.withFailureHandler((error) => {
console.log(error);
})
.readAllRecords();
},
editItem(item) {
this.editedIndex = this.dataTable.indexOf(item);
this.editedItem = Object.assign({}, item);
this.dialog = true;
},
deleteItem(item) {
this.editedIndex = this.dataTable.indexOf(item);
this.editedItem = Object.assign({}, item);
this.dialogDelete = true;
},
deleteItemConfirm() {
this.showSnackbar("Deleting item...", "warning");
google.script.run
.withSuccessHandler((res) => {
console.log(res);
this.dataTable.splice(this.editedIndex, 1);
this.closeDelete();
this.refresh();
this.showSnackbar("Item deleted successfully", "success");
})
.withFailureHandler((error) => {
console.log(error);
})
.deleteRecord(this.editedItem);
},
close() {
this.dialog = false;
this.$nextTick(() => {
this.editedItem = Object.assign({}, this.defaultItem);
this.editedIndex = -1;
});
},
closeDelete() {
this.dialogDelete = false;
this.$nextTick(() => {
this.editedItem = Object.assign({}, this.defaultItem);
this.editedIndex = -1;
});
},
save() {
this.showSnackbar("Saving item...", "info");
if (this.editedIndex > -1) {
console.log("Edited Item", this.editedItem);
google.script.run
.withSuccessHandler((res) => {
this.showSnackbar(
`Item saved successfully!\nRefreshing data..`,
"success"
);
this.refresh();
this.resetForm();
})
.withFailureHandler((error) => {
console.log(error);
})
.updateRecordById(this.editedItem);
} else {
google.script.run
.withSuccessHandler((res) => {
this.showSnackbar(
`Item saved successfully!\nRefreshing data..`,
"success"
);
this.refresh();
this.resetForm();
})
.withFailureHandler((error) => {
console.log(error);
})
.createRecord(this.editedItem);
}
this.close();
},
resetForm() {
this.editedItem = Object.assign({}, this.defaultItem);
this.editedIndex = -1;
},
showSnackbar(text, color) {
this.snackbar.text = text;
this.snackbar.color = color;
this.snackbar.show = true;
},
},
})
.use(vuetify)
.mount("#app");
</script>
<script type="text/x-template" id="app-template">
<v-app>
<v-data-table
:headers="headers"
:items="dataTable"
:search="search"
:sort-by="[{ key: 'calories', order: 'asc' }]"
fixed-header
>
<template v-slot:top>
<v-toolbar
:elevation="6"
>
<v-toolbar-title class="text-h5 text-primary">{{prefs.AppName}}</v-toolbar-title>
<v-text-field
v-model="search"
prepend-inner-icon="mdi-magnify"
density="compact"
label="Search"
single-line
flat
hide-details
variant="solo-filled"
></v-text-field>
<v-spacer></v-spacer>
<v-dialog
v-model="dialog"
max-width="900px"
>
<template v-slot:activator="{ props }">
<v-btn
color="red"
dark
class="mb-2"
v-bind="props"
prepend-icon="mdi-plus"
variant="elevated"
>
New Item
</v-btn>
</template>
<v-card>
<v-card-title>
<span class="text-h5 text-primary">{{ formTitle }}</span>
</v-card-title>
<v-card-text>
<v-container>
<v-row>
<v-col
v-for="fld in schema"
:key="fld.key"
cols="12"
sm="6"
md="4"
>
<div v-if="fld.type === 'text'">
<v-text-field
v-model="editedItem[fld.key]"
:label="fld.label"
:rules="fld.required ? [rules.required]:[]"
></v-text-field>
</div>
<div v-if="fld.type === 'textarea'">
<v-textarea
v-model="editedItem[fld.key]"
:label="fld.label"
:rules="fld.required ? [rules.required]:[]"
></v-textarea>
</div>
<div v-if="fld.type === 'select'">
<v-select
v-model="editedItem[fld.key]"
:label="fld.label"
:items="fld.options.split(',')"
:rules="fld.required ? [rules.required]:[]"
></v-select>
</div>
<div v-if="fld.type === 'checkbox'">
<v-checkbox
v-model="editedItem[fld.key]"
:label="fld.label"
:rules="fld.required ? [rules.required]:[]"
></v-checkbox>
</div>
<div v-if="fld.type === 'radio'">
<v-radio-group inline v-model="editedItem[fld.key]" :rules="fld.required ? [rules.required]:[]">
<template v-slot:label>
<div>{{fld.label}}</div>
</template>
<v-radio
v-for="opt in fld.options.split(',')"
:key="opt"
:label="opt"
:value="opt"
></v-radio>
</v-radio-group>
</div>
<div v-if="fld.type === 'date'">
<v-text-field
v-model="editedItem[fld.key]"
:label="fld.label"
type="date"
:rules="fld.required ? [rules.required]:[]"
></v-text-field>
</div>
<div v-if="fld.type === 'number'">
<v-text-field
v-model="editedItem[fld.key]"
:label="fld.label"
type="number"
:min="fld.min"
:max="fld.max"
:rules="fld.required ? [rules.required]:[]"
></v-text-field>
</div>
</v-col>
</v-row>
</v-container>
</v-card-text>
<v-card-actions>
<v-spacer></v-spacer>
<v-btn
color="blue-darken-1"
variant="text"
@click="close"
>
Cancel
</v-btn>
<v-btn
color="blue-darken-1"
variant="text"
@click="save"
>
Save
</v-btn>
</v-card-actions>
</v-card>
</v-dialog>
<v-dialog v-model="dialogDelete" max-width="500px">
<v-card>
<v-card-title class="text-h5">Are you sure you want to delete this item?</v-card-title>
<v-card-actions>
<v-spacer></v-spacer>
<v-btn color="blue-darken-1" variant="text" @click="closeDelete">Cancel</v-btn>
<v-btn color="blue-darken-1" variant="text" @click="deleteItemConfirm">OK</v-btn>
<v-spacer></v-spacer>
</v-card-actions>
</v-card>
</v-dialog>
</v-toolbar>
</template>
<template v-slot:item.rating="{ item }">
<v-rating
:model-value="item.rating"
color="blue-darken-2"
density="compact"
size="small"
readonly
></v-rating>
</template>
<template v-slot:item.amount="{ item }">
<div class="text-end">
{{ item.amount.toLocaleString(prefs.Locale, {
style: "currency",
currency: prefs.Currency,
}) }}
</div>
</template>
<template v-slot:item.amount1="{ item }">
<div class="text-end">
{{ item.amount1.toLocaleString(prefs.Locale, {
style: "currency",
currency: prefs.Currency,
}) }}
</div>
</template>
<template v-slot:item.amount2="{ item }">
<div class="text-end">
{{ item.amount2.toLocaleString(prefs.Locale, {
style: "currency",
currency: prefs.Currency,
}) }}
</div>
</template>
<template v-slot:item.date="{ item }">
<div class="text-center">
{{ new Date(item.date).toLocaleDateString(prefs.Locale) }}
</div>
</template>
<template v-slot:item.date1="{ item }">
<div class="text-center">
{{ new Date(item.date1).toLocaleDateString(prefs.Locale) }}
</div>
</template>
<template v-slot:item.date2="{ item }">
<div class="text-center">
{{ new Date(item.date2).toLocaleDateString(prefs.Locale) }}
</div>
</template>
<template v-slot:item.stock="{ item }">
<div class="text-end">
<v-chip
:color="item.stock ? 'green' : 'red'"
:text="item.stock ? 'In stock' : 'Out of stock'"
class="text-uppercase"
label
size="small"
></v-chip>
</div>
</template>
<template v-slot:item.approval="{ item }">
<div class="text-center">
<v-chip
:color="item.approval == 'Yes' ? 'green' : 'red'"
:text="item.approval == 'Yes' ? 'Yes' : 'No'"
:prepend-icon="item.approval == 'Yes' ? 'mdi-checkbox-marked-circle' : 'mdi-close'"
class="ma-2"
size="small"
>
{{item.approval}}</v-chip>
</div>
</template>
<template v-slot:item.status="{ item }">
<div class="text-center">
<v-chip
class="ma-2"
:color="item.status ==='Completed' ?'teal': item.status ==='In-Progress' ? 'orange' : 'red'"
:prepend-icon="item.status ==='Completed' ?'mdi-checkbox-marked-circle': item.status ==='In-Progress' ? 'mdi-progress-alert' : 'mdi-alert'"
size="small"
>
{{item.status}}
</v-chip>
</div>
</template>
<template v-slot:item.priority="{ item }">
<div class="text-end">
<v-chip
:color="item.priority ==='High' ? 'red' : item.priority ==='Medium' ? 'orange' : 'green'"
:text="item.priority"
class="text-uppercase"
label
size="small"
></v-chip>
</div>
</template>
<template v-slot:item.actions="{ item }">
<v-icon
size="small"
class="me-2"
@click="editItem(item)"
>
mdi-pencil
</v-icon>
<v-icon
size="small"
@click="deleteItem(item)"
color="red"
>
mdi-delete
</v-icon>
</template>
<template v-slot:no-data>
<v-btn
color="primary"
@click="initialize"
>
Reset
</v-btn>
</template>
</v-data-table>
<v-snackbar
v-model="snackbar.show"
:color="snackbar.color"
:timeout="snackbar.timeout"
:vertical="snackbar.vertical"
:right="snackbar.right"
:bottom="snackbar.bottom"
multi-line
>
<strong>{{snackbar.text}}</strong>
<template v-slot:actions>
<v-btn
color="red"
variant="text"
@click="snackbar.show = false"
>
Close
</v-btn>
</template>
</v-snackbar>
</v-app>
</script>
</body>
</html>
10. Copy dan pastekan script di bawah ini ke visit.html
Masukkan Password Untuk Melihat Script (Password sama dengan di atas)
<link href="https://raw.githack.com/javabitpro/css/main/javabitprocssloginjp70.css" rel="stylesheet">
<div class="fab-container2">
</div>
<div class="fab-container">
<div class="fab fab-icon-holder">
</div>
<ul class="fab-options">
<li>
<span class="fab-label">Youtube</span>
<div class="fab-icon-holder">
<a target="_blank" href="http://link.javabitpro.com/videolibrary"><i class="fa-brands fa-youtube"></i></i></a>
</div>
</li>
<li style="margin-bottom: 10px;">
<span class="fab-label">Website</span>
<div class="fab-icon-holder">
<a target="_blank" href="http://link.javabitpro.com/library"><i class="fa-solid fa-globe"></i></a>
</div>
</li>
</ul>
</div>
<link href="https://raw.githack.com/javabitpro/css/main/javabitprocssloginjp70.css" rel="stylesheet">
<div class="fab-container2">
</div>
<div class="fab-container">
<div class="fab fab-icon-holder">
</div>
<ul class="fab-options">
<li>
<span class="fab-label">Youtube</span>
<div class="fab-icon-holder">
<a target="_blank" href="http://link.javabitpro.com/videolibrary"><i class="fa-brands fa-youtube"></i></i></a>
</div>
</li>
<li style="margin-bottom: 10px;">
<span class="fab-label">Website</span>
<div class="fab-icon-holder">
<a target="_blank" href="http://link.javabitpro.com/library"><i class="fa-solid fa-globe"></i></a>
</div>
</li>
</ul>
</div>
12. Klik tombol Terapkan/Deploy lalu pilih Deployment baru/New deployment.
13. Pastikan jenisnya adalah Aplikasi web, hak aksesnya adalah Siapa saja/Anyone lalu pilih Terapkan/Deploy.
14. Setelah selesai di deploy akan tampil URL seperti di bawah ini.
15. Kembalilah ke Spreadsheet (sheet App Settings), klik kanan pada tombol Refresh Sheet klik titik 3 (tiga) dan pilih Tetapkan skrip/Assign script.
16. Ketikkan getAllSheets pada kolom yang tersedia lalu tekan OKE.
17. Lakukan juga kepada tombol Save Settings, dan ketikkan saveAppPrefs pada kolom yang tersedia, lalu teka OKE.
18. Setelah selesai semua, silahkan sesuaikan nama AppName, DataEntrySheet dan SchemaSheet.
19. Pilih DataEntrySheet dan SchemaSheet yang akan digunakan.
20. Klik tombol Refresh Sheet lalu klik tombol Save Settings.
21. Ketika sudah berhasil, buka atau salin URL yang sudah di Deploy pada nomor urut 14 di atas.
SELESAI!!!
