*Untuk Mendapatkan Full Script tanpa password silahkan Klik Disini*
Menampilkan data ke form dari sheet lain otomatis (Autofill)
1. Silahkan copy Spreadsheet (Klik di sini)
Pada spreadsheet terdapat 2 sheet yaitu :
- Sheet Input : Sheet untuk hasil input dari form
- Sheet Data : Sheet data yang akan tampil pada form
2. Buatlah folder baru pada google drive.
3. Pada Spreadsheet yang sudah di copy tadi, klik menu Ekstensi - pilih Apps Script.
4. Pada lembar kerja Apps Script terdapat file default yaitu :
- Code.gs
- utility.gs
- form.html
- page-js.html
5. Copy dan pastekan script di bawah ini ke Code.gs.
Masukkan Password Untuk Melihat Script (Password ada di dalam video)
function doGet(e) {
return HtmlService.createTemplateFromFile("form").evaluate()
.setTitle("Input Data")
.addMetaTag('viewport', 'width=device-width, initial-scale=1')
.setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL);
}
const spreadsheetId = '11c-iAQxxrGIPrKWKCaId4kVTb1ZNEC968LOKqcyWlPU'; //ID sheet data
const ss = SpreadsheetApp.openById(spreadsheetId);
var url = "https://docs.google.com/spreadsheets/d/11c-iAQxxrGIPrKWKCaId4kVTb1ZNEC968LOKqcyWlPU/edit#gid=0"; //url sheet input
var folderId = "14vGJlp8RMBGJRC_t5FU7mFKZxHK8sP3Z";
//https://drive.google.com/drive/folders/14vGJlp8RMBGJRC_t5FU7mFKZxHK8sP3Z?usp=sharing
function include(filename){
return HtmlService.createHtmlOutputFromFile(filename).getContent();
}
function uploadFiles(data)
{
var file = data.myFile;
var folder = DriveApp.getFolderById(folderId);
var createFile = folder.createFile(file);
return createFile.getUrl();
}
function addNewRow2(rowData2) {
const currentDate = new Date();
var files = DriveApp.getFilesByName(rowData2.unggahan);
while (files.hasNext()) {
var file = files.next();
//var keteranganFile = file.getName()+' - '+ file.getUrl();
var linkFile = file.getUrl();
var sizeFileTemp = file.getSize();
var sizeFile = (sizeFileTemp/1000).toString() + " KB";
}
var ss = SpreadsheetApp.openByUrl(url);
ws = ss.getSheetByName("Input"); //sheet input
ws.appendRow([currentDate, rowData2.namadepan, rowData2.namabelakang, rowData2.kelas, rowData2.alamat,
rowData2.keterangan, rowData2.unggahan, sizeFile, linkFile,])
return true;
}
//Pencarian
function getCustomerById(id){
const ws = ss.getSheetByName("Data");
const dataIDs = ws.getRange(2, 1, ws.getLastRow()-1,1).getValues().map(r => r[0].toString().toLowerCase());
const posIndex = dataIDs.indexOf(id.toString().toLowerCase());
const rowNumber = posIndex === -1 ? 0 : posIndex+2;
const customerInfo = ws.getRange(rowNumber, 1,1 ,7).getValues()[0];
//Logger.log(customerInfo[0])
return {
dataID:customerInfo[0],
custID:customerInfo[1],
namadepan:customerInfo[2],
namabelakang:customerInfo[3],
kelas:customerInfo[4],
alamat:customerInfo[5],
keterangan:customerInfo[6]
}
}
//Nama Depan
function searchNamadepanByCustomerId(formObject){
const ws = ss.getSheetByName("Data").getDataRange();
const data = ws.getValues();
const firstElement = data.shift()
let ar = [];
data.forEach(function(f) {
if (f[1] == formObject.custID) {
ar.push(f[2]);
}
});
let uniqueNamadepans = [];
ar.forEach(function(d){
if(uniqueNamadepans.indexOf(d) === -1){
uniqueNamadepans.push(d);
}
});
//Logger.log(data);
return uniqueNamadepans;
}
function notNamadepanByCustomerId(){ //for name autocomplete
const ws = ss.getSheetByName("Data").getDataRange();
const data = ws.getValues();
const firstElement = data.shift()
let ar = [];
data.forEach(function(f) {
if (f[1] == "ex_ttmd"){
ar.push(f[2]);
}
});
let uniqueNamadepans = [];
ar.forEach(function(d){
if(uniqueNamadepans.indexOf(d) === -1){
uniqueNamadepans.push(d);
}
});
//Logger.log(uniqueNames);
return uniqueNamadepans;
}
//Nama Belakang
function searchNamabelakangByCustomerId(formObject){
const ws = ss.getSheetByName("Data").getDataRange();
const data = ws.getValues();
const firstElement = data.shift()
let ar = [];
data.forEach(function(f) {
if (f[1] == formObject.custID) {
ar.push(f[3]);
}
});
let uniqueNamabelakangs = [];
ar.forEach(function(d){
if(uniqueNamabelakangs.indexOf(d) === -1){
uniqueNamabelakangs.push(d);
}
});
//Logger.log(data);
return uniqueNamabelakangs;
}
function notNamabelakangByCustomerId(){ //for name autocomplete
const ws = ss.getSheetByName("Data").getDataRange();
const data = ws.getValues();
const firstElement = data.shift()
let ar = [];
data.forEach(function(f) {
if (f[1] == "ex_ttmd"){
ar.push(f[3]);
}
});
let uniqueNamabelakangs = [];
ar.forEach(function(d){
if(uniqueNamabelakangs.indexOf(d) === -1){
uniqueNamabelakangs.push(d);
}
});
//Logger.log(uniqueNames);
return uniqueNamabelakangs;
}
//Kelas
function searchKelasByCustomerId(formObject){
const ws = ss.getSheetByName("Data").getDataRange();
const data = ws.getValues();
const firstElement = data.shift()
let ar = [];
data.forEach(function(f) {
if (f[1] == formObject.custID) {
ar.push(f[4]);
}
});
let uniqueKelass = [];
ar.forEach(function(d){
if(uniqueKelass.indexOf(d) === -1){
uniqueKelass.push(d);
}
});
//Logger.log(data);
return uniqueKelass;
}
function notKelasByCustomerId(){ //for name autocomplete
const ws = ss.getSheetByName("Data").getDataRange();
const data = ws.getValues();
const firstElement = data.shift()
let ar = [];
data.forEach(function(f) {
if (f[1] == "ex_ttmd"){
ar.push(f[4]);
}
});
let uniqueKelass = [];
ar.forEach(function(d){
if(uniqueKelass.indexOf(d) === -1){
uniqueKelass.push(d);
}
});
//Logger.log(uniqueNames);
return uniqueKelass;
}
//Alamat
function searchAlamatByCustomerId(formObject){
const ws = ss.getSheetByName("Data").getDataRange();
const data = ws.getValues();
const firstElement = data.shift()
let ar = [];
data.forEach(function(f) {
if (f[1] == formObject.custID) {
ar.push(f[5]);
}
});
let uniqueAlamats = [];
ar.forEach(function(d){
if(uniqueAlamats.indexOf(d) === -1){
uniqueAlamats.push(d);
}
});
//Logger.log(data);
return uniqueAlamats;
}
function notAlamatByCustomerId(){ //for name autocomplete
const ws = ss.getSheetByName("Data").getDataRange();
const data = ws.getValues();
const firstElement = data.shift()
let ar = [];
data.forEach(function(f) {
if (f[1] == "ex_ttmd"){
ar.push(f[5]);
}
});
let uniqueAlamats = [];
ar.forEach(function(d){
if(uniqueAlamats.indexOf(d) === -1){
uniqueAlamats.push(d);
}
});
//Logger.log(uniqueNames);
return uniqueAlamats;
}
//Keterangan
function searchKeteranganByCustomerId(formObject){
const ws = ss.getSheetByName("Data").getDataRange();
const data = ws.getValues();
const firstElement = data.shift()
let ar = [];
data.forEach(function(f) {
if (f[1] == formObject.custID) {
ar.push(f[6]);
}
});
let uniqueKeterangans = [];
ar.forEach(function(d){
if(uniqueKeterangans.indexOf(d) === -1){
uniqueKeterangans.push(d);
}
});
//Logger.log(data);
return uniqueKeterangans;
}
function notKeteranganByCustomerId(){ //for name autocomplete
const ws = ss.getSheetByName("Data").getDataRange();
const data = ws.getValues();
const firstElement = data.shift()
let ar = [];
data.forEach(function(f) {
if (f[1] == "ex_ttmd"){
ar.push(f[6]);
}
});
let uniqueKeterangans = [];
ar.forEach(function(d){
if(uniqueKeterangans.indexOf(d) === -1){
uniqueKeterangans.push(d);
}
});
//Logger.log(uniqueNames);
return uniqueKeterangans;
}
function doGet(e) {
return HtmlService.createTemplateFromFile("form").evaluate()
.setTitle("Input Data")
.addMetaTag('viewport', 'width=device-width, initial-scale=1')
.setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL);
}
const spreadsheetId = '11c-iAQxxrGIPrKWKCaId4kVTb1ZNEC968LOKqcyWlPU'; //ID sheet data
const ss = SpreadsheetApp.openById(spreadsheetId);
var url = "https://docs.google.com/spreadsheets/d/11c-iAQxxrGIPrKWKCaId4kVTb1ZNEC968LOKqcyWlPU/edit#gid=0"; //url sheet input
var folderId = "14vGJlp8RMBGJRC_t5FU7mFKZxHK8sP3Z";
//https://drive.google.com/drive/folders/14vGJlp8RMBGJRC_t5FU7mFKZxHK8sP3Z?usp=sharing
function include(filename){
return HtmlService.createHtmlOutputFromFile(filename).getContent();
}
function uploadFiles(data)
{
var file = data.myFile;
var folder = DriveApp.getFolderById(folderId);
var createFile = folder.createFile(file);
return createFile.getUrl();
}
function addNewRow2(rowData2) {
const currentDate = new Date();
var files = DriveApp.getFilesByName(rowData2.unggahan);
while (files.hasNext()) {
var file = files.next();
//var keteranganFile = file.getName()+' - '+ file.getUrl();
var linkFile = file.getUrl();
var sizeFileTemp = file.getSize();
var sizeFile = (sizeFileTemp/1000).toString() + " KB";
}
var ss = SpreadsheetApp.openByUrl(url);
ws = ss.getSheetByName("Input"); //sheet input
ws.appendRow([currentDate, rowData2.namadepan, rowData2.namabelakang, rowData2.kelas, rowData2.alamat,
rowData2.keterangan, rowData2.unggahan, sizeFile, linkFile,])
return true;
}
//Pencarian
function getCustomerById(id){
const ws = ss.getSheetByName("Data");
const dataIDs = ws.getRange(2, 1, ws.getLastRow()-1,1).getValues().map(r => r[0].toString().toLowerCase());
const posIndex = dataIDs.indexOf(id.toString().toLowerCase());
const rowNumber = posIndex === -1 ? 0 : posIndex+2;
const customerInfo = ws.getRange(rowNumber, 1,1 ,7).getValues()[0];
//Logger.log(customerInfo[0])
return {
dataID:customerInfo[0],
custID:customerInfo[1],
namadepan:customerInfo[2],
namabelakang:customerInfo[3],
kelas:customerInfo[4],
alamat:customerInfo[5],
keterangan:customerInfo[6]
}
}
//Nama Depan
function searchNamadepanByCustomerId(formObject){
const ws = ss.getSheetByName("Data").getDataRange();
const data = ws.getValues();
const firstElement = data.shift()
let ar = [];
data.forEach(function(f) {
if (f[1] == formObject.custID) {
ar.push(f[2]);
}
});
let uniqueNamadepans = [];
ar.forEach(function(d){
if(uniqueNamadepans.indexOf(d) === -1){
uniqueNamadepans.push(d);
}
});
//Logger.log(data);
return uniqueNamadepans;
}
function notNamadepanByCustomerId(){ //for name autocomplete
const ws = ss.getSheetByName("Data").getDataRange();
const data = ws.getValues();
const firstElement = data.shift()
let ar = [];
data.forEach(function(f) {
if (f[1] == "ex_ttmd"){
ar.push(f[2]);
}
});
let uniqueNamadepans = [];
ar.forEach(function(d){
if(uniqueNamadepans.indexOf(d) === -1){
uniqueNamadepans.push(d);
}
});
//Logger.log(uniqueNames);
return uniqueNamadepans;
}
//Nama Belakang
function searchNamabelakangByCustomerId(formObject){
const ws = ss.getSheetByName("Data").getDataRange();
const data = ws.getValues();
const firstElement = data.shift()
let ar = [];
data.forEach(function(f) {
if (f[1] == formObject.custID) {
ar.push(f[3]);
}
});
let uniqueNamabelakangs = [];
ar.forEach(function(d){
if(uniqueNamabelakangs.indexOf(d) === -1){
uniqueNamabelakangs.push(d);
}
});
//Logger.log(data);
return uniqueNamabelakangs;
}
function notNamabelakangByCustomerId(){ //for name autocomplete
const ws = ss.getSheetByName("Data").getDataRange();
const data = ws.getValues();
const firstElement = data.shift()
let ar = [];
data.forEach(function(f) {
if (f[1] == "ex_ttmd"){
ar.push(f[3]);
}
});
let uniqueNamabelakangs = [];
ar.forEach(function(d){
if(uniqueNamabelakangs.indexOf(d) === -1){
uniqueNamabelakangs.push(d);
}
});
//Logger.log(uniqueNames);
return uniqueNamabelakangs;
}
//Kelas
function searchKelasByCustomerId(formObject){
const ws = ss.getSheetByName("Data").getDataRange();
const data = ws.getValues();
const firstElement = data.shift()
let ar = [];
data.forEach(function(f) {
if (f[1] == formObject.custID) {
ar.push(f[4]);
}
});
let uniqueKelass = [];
ar.forEach(function(d){
if(uniqueKelass.indexOf(d) === -1){
uniqueKelass.push(d);
}
});
//Logger.log(data);
return uniqueKelass;
}
function notKelasByCustomerId(){ //for name autocomplete
const ws = ss.getSheetByName("Data").getDataRange();
const data = ws.getValues();
const firstElement = data.shift()
let ar = [];
data.forEach(function(f) {
if (f[1] == "ex_ttmd"){
ar.push(f[4]);
}
});
let uniqueKelass = [];
ar.forEach(function(d){
if(uniqueKelass.indexOf(d) === -1){
uniqueKelass.push(d);
}
});
//Logger.log(uniqueNames);
return uniqueKelass;
}
//Alamat
function searchAlamatByCustomerId(formObject){
const ws = ss.getSheetByName("Data").getDataRange();
const data = ws.getValues();
const firstElement = data.shift()
let ar = [];
data.forEach(function(f) {
if (f[1] == formObject.custID) {
ar.push(f[5]);
}
});
let uniqueAlamats = [];
ar.forEach(function(d){
if(uniqueAlamats.indexOf(d) === -1){
uniqueAlamats.push(d);
}
});
//Logger.log(data);
return uniqueAlamats;
}
function notAlamatByCustomerId(){ //for name autocomplete
const ws = ss.getSheetByName("Data").getDataRange();
const data = ws.getValues();
const firstElement = data.shift()
let ar = [];
data.forEach(function(f) {
if (f[1] == "ex_ttmd"){
ar.push(f[5]);
}
});
let uniqueAlamats = [];
ar.forEach(function(d){
if(uniqueAlamats.indexOf(d) === -1){
uniqueAlamats.push(d);
}
});
//Logger.log(uniqueNames);
return uniqueAlamats;
}
//Keterangan
function searchKeteranganByCustomerId(formObject){
const ws = ss.getSheetByName("Data").getDataRange();
const data = ws.getValues();
const firstElement = data.shift()
let ar = [];
data.forEach(function(f) {
if (f[1] == formObject.custID) {
ar.push(f[6]);
}
});
let uniqueKeterangans = [];
ar.forEach(function(d){
if(uniqueKeterangans.indexOf(d) === -1){
uniqueKeterangans.push(d);
}
});
//Logger.log(data);
return uniqueKeterangans;
}
function notKeteranganByCustomerId(){ //for name autocomplete
const ws = ss.getSheetByName("Data").getDataRange();
const data = ws.getValues();
const firstElement = data.shift()
let ar = [];
data.forEach(function(f) {
if (f[1] == "ex_ttmd"){
ar.push(f[6]);
}
});
let uniqueKeterangans = [];
ar.forEach(function(d){
if(uniqueKeterangans.indexOf(d) === -1){
uniqueKeterangans.push(d);
}
});
//Logger.log(uniqueNames);
return uniqueKeterangans;
}
Line 9 : Silahkan ganti dengan ID Spreadseheet kalian.
Line 11 : Silahkan ganti dengan link Spreadsheet kalian.
Line 12 : Silahkan ganti dengan ID folder kalian (yang sudah dibuat pada langkah nomer 2).
6. Copy dan pastekan script di bawah ini ke utility.gs.
Masukkan Password Untuk Melihat Script (Password sama dengan di atas)
function countIF(array,index1,condition1){ //count for 1 condition
var countVal = 0;
array.forEach(function(e){
if(e[index1] == condition1){
countVal ++
}
});
return countVal
}
function countIFS2(array,index1,condition1,index2,condition2){ //count for 2 condition
var countVal = 0;
array.forEach(function(e){
if(e[index1] == condition1 && e[index2] == condition2){
countVal ++
}
});
return countVal
}
////////end count function//////////////////
function uniqueId(){
const firstItem = {
value: "0"
};
/*length can be increased for lists with more items.*/
let counter = "123456789".split('')
.reduce((acc, curValue, curIndex, arr) => {
const curObj = {};
curObj.value = curValue;
curObj.prev = acc;
return curObj;
}, firstItem);
firstItem.prev = counter;
return function () {
let now = Date.now();
if (typeof performance === "object" && typeof performance.now === "function") {
now = performance.now().toString().replace('.', '');
}
counter = counter.prev;
return `${now}${Math.random().toString(16).substr(2)}${counter.value}`;
}
}
//utility.gs
function countIF(array,index1,condition1){ //count for 1 condition
var countVal = 0;
array.forEach(function(e){
if(e[index1] == condition1){
countVal ++
}
});
return countVal
}
function countIFS2(array,index1,condition1,index2,condition2){ //count for 2 condition
var countVal = 0;
array.forEach(function(e){
if(e[index1] == condition1 && e[index2] == condition2){
countVal ++
}
});
return countVal
}
////////end count function//////////////////
function uniqueId(){
const firstItem = {
value: "0"
};
/*length can be increased for lists with more items.*/
let counter = "123456789".split('')
.reduce((acc, curValue, curIndex, arr) => {
const curObj = {};
curObj.value = curValue;
curObj.prev = acc;
return curObj;
}, firstItem);
firstItem.prev = counter;
return function () {
let now = Date.now();
if (typeof performance === "object" && typeof performance.now === "function") {
now = performance.now().toString().replace('.', '');
}
counter = counter.prev;
return `${now}${Math.random().toString(16).substr(2)}${counter.value}`;
}
}
//utility.gs
Masukkan Password Untuk Melihat Script (Password sama dengan di atas)
<!doctype html>
<html lang="en">
<head>
<base target="_self">
<meta name="viewport" content="width=device-width, initial-scale=1.0"/>
<link href="https://fonts.googleapis.com/icon?family=Material+Icons" rel="stylesheet">
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/materialize/1.0.0/css/materialize.min.css">
<link href='https://fonts.googleapis.com/css?family=Assistant' rel='stylesheet'>
<link href='https://fonts.googleapis.com/css?family=Open Sans' rel='stylesheet'>
<!-- Bootstrap CSS -->
<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.5.0/css/bootstrap.min.css" integrity="sha384-9aIt2nRpC12Uk9gS9baDl411NQApFmC26EwAOH8WgZl5MYYxFfc+NcPb1dKGj7Sk" crossorigin="anonymous">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/css/bootstrap.min.css" integrity="sha384-Gn5384xqQ1aoWXA+058RXPxPg6fy4IWvTNh0E263XmFcJlSAwiGgFAW/dAiS6JXm" crossorigin="anonymous">
<script>
function harusHuruf(evt){
var charCode = (evt.which) ? evt.which : event.keyCode
if ((charCode < 65 || charCode > 90)&&(charCode < 97 || charCode > 122)&&charCode>32)
return false;
return true;
}
function hanyaAngka(evt) {
var charCode = (evt.which) ? evt.which : event.keyCode
if (charCode > 31 && (charCode < 48 || charCode > 57))
return false;
return true;
}
</script>
</head>
<body>
<div class="container" id="app">
<!--- Frame kontak -->
<div class="z-depth-4" novalidate="novalidate" style="max-width: 6000px;margin: 40px auto;height:850px;" id="kontak">
<!-- <span class="black-text"></span> -->
<center><br>
<h6 style="font-size:18px;"><b>INPUT DATA SISWA</b></h6>
</center>
<div class="container">
<div class="input-field col s12">
<input type="number" id="custID" name="custID" class="form-control" placeholder="Masukkan Nomer Absen" onchange="sendCustIDforAutocomplete()" required>
</div>
</div>
<!-- Nama Depan -->
<div class="container">
<div class="input-field col s12">
<h6>Nama Depan</h6>
<input style="color: black" type="text" id="namadepan" name="namadepan" placeholder="Otomatis" readonly>
<div class = "invalid-feedback">required</div>
<p style="color:blue;" id = "search_namadepan">Silahkan periksa nama depan</p>
</div>
<!-- Jenis - tel -->
<div class="input-field col s12">
<h6>Nama Belakang</h6>
<input style="color: black" type="text" id="namabelakang" name="namabelakang" placeholder="Otomatis" readonly>
<div class = "invalid-feedback">required</div>
<p style="color:blue;" id = "search_namabelakang">Silahkan nama belakang</p>
</div>
<div class="input-field col s12">
<h6>Kelas</h6>
<input style="color: black" type="text" id="kelas" name="kelas" placeholder="Otomatis" readonly>
<div class = "invalid-feedback">required</div>
<p style="color:blue;" id = "search_kelas">Silahkan periksa kelas</p>
</div>
<div class="input-field col s12">
<h6>Alamat</h6>
<input style="color: black" type="text" id="alamat" name="alamat" placeholder="Otomatis" readonly>
<div class = "invalid-feedback">required</div>
<p style="color:blue;" id= "search_alamat">Silahkan periksa alamat</p>
</div>
<div class="input-field col s12">
<h6>Keterangan</h6>
<input style="color: black" type="text" id="keterangan" name="keterangan" placeholder="Otomatis" readonly>
<div class = "invalid-feedback">required</div>
<p style="color:blue;" id= "search_keterangan">Silahkan periksa keterangan</p>
</div>
<div class="input-field col s8">
<span style="font-size:15px; color:black;margin-left: 14px;">Upload Foto Ketua (4x6)</span>
<form>
<input style="color:black;margin-left: 14px;" type="file" name="myFile" multiple>
<span id="resp-spinnerA" class="spinner-border spinner-grow-sm text-danger d-none" role="status" aria-hidden="true"></span>
<input style="color:white; background-color:#990099; display: none" class="multi-lainnya" type="button" id="submitBtn" value="Upload" >
</form>
</div>
<div id ="divMainButton2" style="display: none">
<center> <button style="background-color: #4db6ac; color: white;" class="btn waves-effect waves-light" type="submit" name="mainButton2"><b>Kirim</b><i class="material-icons right">send</i> </button>
<span id="resp-spinner5" class="spinner-grow spinner-grow-sm text-danger d-none" role="status" aria-hidden="true"></span>
<span id="resp-spinner6" class="spinner-grow spinner-grow-sm text-warning d-none" role="status" aria-hidden="true"></span>
<span id="resp-spinner7" class="spinner-grow spinner-grow-sm text-info d-none" role="status" aria-hidden="true"></span>
</center>
</div>
<!-- Frame-->
</div>
<script>
function sendCustIDforAutocomplete() {
var formObject = {};
formObject.custID = document.getElementById('custID').value;
if(formObject.custID !=""){
document.getElementById('namadepan').disabled = true;
document.getElementById("search_namadepan").innerHTML = "mencari nama depan...";
document.getElementById("search_namadepan").style = "color:red";
//document.getElementById("search_name").style.fontSize = "initial";
google.script.run.withSuccessHandler(populatePtNamadepansID).searchNamadepanByCustomerId(formObject);
}
//Nama Belakang
var formObject = {};
formObject.custID = document.getElementById('custID').value;
if(formObject.custID !=""){
document.getElementById('namabelakang').disabled = true;
document.getElementById("search_namabelakang").innerHTML = "mencari nama belakang...";
document.getElementById("search_namabelakang").style = "color:red";
//document.getElementById("search_name").style.fontSize = "initial";
google.script.run.withSuccessHandler(populatePtNamabelakangsID).searchNamabelakangByCustomerId(formObject);
}
//Kelas
var formObject = {};
formObject.custID = document.getElementById('custID').value;
if(formObject.custID !=""){
document.getElementById('kelas').disabled = true;
document.getElementById("search_kelas").innerHTML = "mencari kelas...";
document.getElementById("search_kelas").style = "color:red";
//document.getElementById("search_name").style.fontSize = "initial";
google.script.run.withSuccessHandler(populatePtKelassID).searchKelasByCustomerId(formObject);
}
//Alamat
var formObject = {};
formObject.custID = document.getElementById('custID').value;
if(formObject.custID !=""){
document.getElementById('alamat').disabled = true;
document.getElementById("search_alamat").innerHTML = "mencari alamat ...";
document.getElementById("search_alamat").style = "color:red";
//document.getElementById("search_name").style.fontSize = "initial";
google.script.run.withSuccessHandler(populatePtAlamatsID).searchAlamatByCustomerId(formObject);
}
//Keterangan
var formObject = {};
formObject.custID = document.getElementById('custID').value;
if(formObject.custID !=""){
document.getElementById('keterangan').disabled = true;
document.getElementById("search_keterangan").innerHTML = "mencari jumlah anggota...";
document.getElementById("search_keterangan").style = "color:red";
//document.getElementById("search_name").style.fontSize = "initial";
google.script.run.withSuccessHandler(populatePtKeterangansID).searchKeteranganByCustomerId(formObject);
}
};
//Nama Depan
function populatePtNamadepansID(namadepans){
if(namadepans && namadepans !== undefined && namadepans.length != 0){ //alert(names[0]);
$( function(){
var availableTags2 = namadepans;
$("#namadepan").autocomplete({
source: availableTags2
});
});
document.getElementById('namadepan').value = namadepans[namadepans.length-1];
}
else{
google.script.run.withSuccessHandler(populatePtNamadepans).notNamadepanByCustomerId();
document.getElementById('namadepans').value = "";
}
document.getElementById("search_namadepan").innerHTML = "Silahkan periksa nama depan";
document.getElementById("search_namadepan").style = "color:blue";
document.getElementById('namadepan').disabled = false;
document.getElementById('save-btn').disabled = false;
}
//Nama Belakang
function populatePtNamabelakangsID(namabelakangs){
if(namabelakangs && namabelakangs !== undefined && namabelakangs.length != 0){ //alert(names[0]);
$( function(){
var availableTags3 = namabelakangs;
$("#namabelakang").autocomplete({
source: availableTags3
});
});
document.getElementById('namabelakang').value = namabelakangs[namabelakangs.length-1];
}
else{
google.script.run.withSuccessHandler(populatePtNamabelakangs).notNamabelakangByCustomerId();
document.getElementById('namabelakang').value = "";
}
document.getElementById("search_namabelakang").innerHTML = "Silahkan periksa nama belakang";
document.getElementById("search_namabelakang").style = "color:blue";
document.getElementById('namabelakang').disabled = false;
document.getElementById('save-btn').disabled = false;
}
//Kelas
function populatePtKelassID(kelass){
if(kelass && kelass !== undefined && kelass.length != 0){ //alert(names[0]);
$( function(){
var availableTags4 = kelass;
$("#kelas").autocomplete({
source: availableTags4
});
});
document.getElementById('kelas').value = kelass[kelass.length-1];
}
else{
google.script.run.withSuccessHandler(populatePtKelass).notKelasByCustomerId();
document.getElementById('kelas').value = "";
}
document.getElementById("search_kelas").innerHTML = "Silahkan periksa kelas";
document.getElementById("search_kelas").style = "color:blue";
document.getElementById('kelas').disabled = false;
document.getElementById('save-btn').disabled = false;
}
//Alamat
function populatePtAlamatsID(alamats){
if(alamats && alamats !== undefined && alamats.length != 0){ //alert(names[0]);
$( function(){
var availableTags5 = alamats;
$("#alamat").autocomplete({
source: availableTags5
});
});
document.getElementById('alamat').value = alamats[alamats.length-1];
}
else{
google.script.run.withSuccessHandler(populatePtAlamats).notAlamatByCustomerId();
document.getElementById('alamat').value = "";
}
document.getElementById("search_alamat").innerHTML = "Silahkan periksa alamat";
document.getElementById("search_alamat").style = "color:blue";
document.getElementById('alamat').disabled = false;
document.getElementById('save-btn').disabled = false;
}
//Keterangan
function populatePtKeterangansID(keterangans){
if(keterangans && keterangans !== undefined && keterangans.length != 0){ //alert(names[0]);
$( function(){
var availableTags6 = keterangans;
$("#keterangan").autocomplete({
source: availableTags6
});
});
document.getElementById('keterangan').value = keterangans[keterangans.length-1];
}
else{
google.script.run.withSuccessHandler(populatePtKeterangans).notKeteranganByCustomerId();
document.getElementById('keterangan').value = "";
}
document.getElementById("search_keterangan").innerHTML = "Silahkan periksa keterangan";
document.getElementById("search_keterangan").style = "color:blue";
document.getElementById('keterangan').disabled = false;
document.getElementById('save-btn').disabled = false;
}
</script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/materialize/1.0.0/js/materialize.min.js"></script>
<script src="https://unpkg.com/sweetalert/dist/sweetalert.min.js"></script>
<script src="https://code.jquery.com/jquery-3.2.1.slim.min.js" integrity="sha384-KJ3o2DKtIkvYIK3UENzmM7KCkRr/rE9/Qpg6aAZGJwFDMVNA/GpGFF93hXpG5KkN" crossorigin="anonymous"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.12.9/umd/popper.min.js" integrity="sha384-ApNbgh9B+Y1QKtv3Rn7W3mgPxhU9K/ScQsAP7hUibX39j7fakFPskvXusvfa0b4Q" crossorigin="anonymous"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/js/bootstrap.min.js" integrity="sha384-JZR6Spejh4U02d8jOt6vLEHfe/JQGiRRSQQxSfFWpi1MquVdAyjUar5+76PVCmYl" crossorigin="anonymous"></script>
<?!= include("page-js"); ?>
</body>
</html>
<!doctype html>
<html lang="en">
<head>
<base target="_self">
<meta name="viewport" content="width=device-width, initial-scale=1.0"/>
<link href="https://fonts.googleapis.com/icon?family=Material+Icons" rel="stylesheet">
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/materialize/1.0.0/css/materialize.min.css">
<link href='https://fonts.googleapis.com/css?family=Assistant' rel='stylesheet'>
<link href='https://fonts.googleapis.com/css?family=Open Sans' rel='stylesheet'>
<!-- Bootstrap CSS -->
<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.5.0/css/bootstrap.min.css" integrity="sha384-9aIt2nRpC12Uk9gS9baDl411NQApFmC26EwAOH8WgZl5MYYxFfc+NcPb1dKGj7Sk" crossorigin="anonymous">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/css/bootstrap.min.css" integrity="sha384-Gn5384xqQ1aoWXA+058RXPxPg6fy4IWvTNh0E263XmFcJlSAwiGgFAW/dAiS6JXm" crossorigin="anonymous">
<script>
function harusHuruf(evt){
var charCode = (evt.which) ? evt.which : event.keyCode
if ((charCode < 65 || charCode > 90)&&(charCode < 97 || charCode > 122)&&charCode>32)
return false;
return true;
}
function hanyaAngka(evt) {
var charCode = (evt.which) ? evt.which : event.keyCode
if (charCode > 31 && (charCode < 48 || charCode > 57))
return false;
return true;
}
</script>
</head>
<body>
<div class="container" id="app">
<!--- Frame kontak -->
<div class="z-depth-4" novalidate="novalidate" style="max-width: 6000px;margin: 40px auto;height:850px;" id="kontak">
<!-- <span class="black-text"></span> -->
<center><br>
<h6 style="font-size:18px;"><b>INPUT DATA SISWA</b></h6>
</center>
<div class="container">
<div class="input-field col s12">
<input type="number" id="custID" name="custID" class="form-control" placeholder="Masukkan Nomer Absen" onchange="sendCustIDforAutocomplete()" required>
</div>
</div>
<!-- Nama Depan -->
<div class="container">
<div class="input-field col s12">
<h6>Nama Depan</h6>
<input style="color: black" type="text" id="namadepan" name="namadepan" placeholder="Otomatis" readonly>
<div class = "invalid-feedback">required</div>
<p style="color:blue;" id = "search_namadepan">Silahkan periksa nama depan</p>
</div>
<!-- Jenis - tel -->
<div class="input-field col s12">
<h6>Nama Belakang</h6>
<input style="color: black" type="text" id="namabelakang" name="namabelakang" placeholder="Otomatis" readonly>
<div class = "invalid-feedback">required</div>
<p style="color:blue;" id = "search_namabelakang">Silahkan nama belakang</p>
</div>
<div class="input-field col s12">
<h6>Kelas</h6>
<input style="color: black" type="text" id="kelas" name="kelas" placeholder="Otomatis" readonly>
<div class = "invalid-feedback">required</div>
<p style="color:blue;" id = "search_kelas">Silahkan periksa kelas</p>
</div>
<div class="input-field col s12">
<h6>Alamat</h6>
<input style="color: black" type="text" id="alamat" name="alamat" placeholder="Otomatis" readonly>
<div class = "invalid-feedback">required</div>
<p style="color:blue;" id= "search_alamat">Silahkan periksa alamat</p>
</div>
<div class="input-field col s12">
<h6>Keterangan</h6>
<input style="color: black" type="text" id="keterangan" name="keterangan" placeholder="Otomatis" readonly>
<div class = "invalid-feedback">required</div>
<p style="color:blue;" id= "search_keterangan">Silahkan periksa keterangan</p>
</div>
<div class="input-field col s8">
<span style="font-size:15px; color:black;margin-left: 14px;">Upload Foto Ketua (4x6)</span>
<form>
<input style="color:black;margin-left: 14px;" type="file" name="myFile" multiple>
<span id="resp-spinnerA" class="spinner-border spinner-grow-sm text-danger d-none" role="status" aria-hidden="true"></span>
<input style="color:white; background-color:#990099; display: none" class="multi-lainnya" type="button" id="submitBtn" value="Upload" >
</form>
</div>
<div id ="divMainButton2" style="display: none">
<center> <button style="background-color: #4db6ac; color: white;" class="btn waves-effect waves-light" type="submit" name="mainButton2"><b>Kirim</b><i class="material-icons right">send</i> </button>
<span id="resp-spinner5" class="spinner-grow spinner-grow-sm text-danger d-none" role="status" aria-hidden="true"></span>
<span id="resp-spinner6" class="spinner-grow spinner-grow-sm text-warning d-none" role="status" aria-hidden="true"></span>
<span id="resp-spinner7" class="spinner-grow spinner-grow-sm text-info d-none" role="status" aria-hidden="true"></span>
</center>
</div>
<!-- Frame-->
</div>
<script>
function sendCustIDforAutocomplete() {
var formObject = {};
formObject.custID = document.getElementById('custID').value;
if(formObject.custID !=""){
document.getElementById('namadepan').disabled = true;
document.getElementById("search_namadepan").innerHTML = "mencari nama depan...";
document.getElementById("search_namadepan").style = "color:red";
//document.getElementById("search_name").style.fontSize = "initial";
google.script.run.withSuccessHandler(populatePtNamadepansID).searchNamadepanByCustomerId(formObject);
}
//Nama Belakang
var formObject = {};
formObject.custID = document.getElementById('custID').value;
if(formObject.custID !=""){
document.getElementById('namabelakang').disabled = true;
document.getElementById("search_namabelakang").innerHTML = "mencari nama belakang...";
document.getElementById("search_namabelakang").style = "color:red";
//document.getElementById("search_name").style.fontSize = "initial";
google.script.run.withSuccessHandler(populatePtNamabelakangsID).searchNamabelakangByCustomerId(formObject);
}
//Kelas
var formObject = {};
formObject.custID = document.getElementById('custID').value;
if(formObject.custID !=""){
document.getElementById('kelas').disabled = true;
document.getElementById("search_kelas").innerHTML = "mencari kelas...";
document.getElementById("search_kelas").style = "color:red";
//document.getElementById("search_name").style.fontSize = "initial";
google.script.run.withSuccessHandler(populatePtKelassID).searchKelasByCustomerId(formObject);
}
//Alamat
var formObject = {};
formObject.custID = document.getElementById('custID').value;
if(formObject.custID !=""){
document.getElementById('alamat').disabled = true;
document.getElementById("search_alamat").innerHTML = "mencari alamat ...";
document.getElementById("search_alamat").style = "color:red";
//document.getElementById("search_name").style.fontSize = "initial";
google.script.run.withSuccessHandler(populatePtAlamatsID).searchAlamatByCustomerId(formObject);
}
//Keterangan
var formObject = {};
formObject.custID = document.getElementById('custID').value;
if(formObject.custID !=""){
document.getElementById('keterangan').disabled = true;
document.getElementById("search_keterangan").innerHTML = "mencari jumlah anggota...";
document.getElementById("search_keterangan").style = "color:red";
//document.getElementById("search_name").style.fontSize = "initial";
google.script.run.withSuccessHandler(populatePtKeterangansID).searchKeteranganByCustomerId(formObject);
}
};
//Nama Depan
function populatePtNamadepansID(namadepans){
if(namadepans && namadepans !== undefined && namadepans.length != 0){ //alert(names[0]);
$( function(){
var availableTags2 = namadepans;
$("#namadepan").autocomplete({
source: availableTags2
});
});
document.getElementById('namadepan').value = namadepans[namadepans.length-1];
}
else{
google.script.run.withSuccessHandler(populatePtNamadepans).notNamadepanByCustomerId();
document.getElementById('namadepans').value = "";
}
document.getElementById("search_namadepan").innerHTML = "Silahkan periksa nama depan";
document.getElementById("search_namadepan").style = "color:blue";
document.getElementById('namadepan').disabled = false;
document.getElementById('save-btn').disabled = false;
}
//Nama Belakang
function populatePtNamabelakangsID(namabelakangs){
if(namabelakangs && namabelakangs !== undefined && namabelakangs.length != 0){ //alert(names[0]);
$( function(){
var availableTags3 = namabelakangs;
$("#namabelakang").autocomplete({
source: availableTags3
});
});
document.getElementById('namabelakang').value = namabelakangs[namabelakangs.length-1];
}
else{
google.script.run.withSuccessHandler(populatePtNamabelakangs).notNamabelakangByCustomerId();
document.getElementById('namabelakang').value = "";
}
document.getElementById("search_namabelakang").innerHTML = "Silahkan periksa nama belakang";
document.getElementById("search_namabelakang").style = "color:blue";
document.getElementById('namabelakang').disabled = false;
document.getElementById('save-btn').disabled = false;
}
//Kelas
function populatePtKelassID(kelass){
if(kelass && kelass !== undefined && kelass.length != 0){ //alert(names[0]);
$( function(){
var availableTags4 = kelass;
$("#kelas").autocomplete({
source: availableTags4
});
});
document.getElementById('kelas').value = kelass[kelass.length-1];
}
else{
google.script.run.withSuccessHandler(populatePtKelass).notKelasByCustomerId();
document.getElementById('kelas').value = "";
}
document.getElementById("search_kelas").innerHTML = "Silahkan periksa kelas";
document.getElementById("search_kelas").style = "color:blue";
document.getElementById('kelas').disabled = false;
document.getElementById('save-btn').disabled = false;
}
//Alamat
function populatePtAlamatsID(alamats){
if(alamats && alamats !== undefined && alamats.length != 0){ //alert(names[0]);
$( function(){
var availableTags5 = alamats;
$("#alamat").autocomplete({
source: availableTags5
});
});
document.getElementById('alamat').value = alamats[alamats.length-1];
}
else{
google.script.run.withSuccessHandler(populatePtAlamats).notAlamatByCustomerId();
document.getElementById('alamat').value = "";
}
document.getElementById("search_alamat").innerHTML = "Silahkan periksa alamat";
document.getElementById("search_alamat").style = "color:blue";
document.getElementById('alamat').disabled = false;
document.getElementById('save-btn').disabled = false;
}
//Keterangan
function populatePtKeterangansID(keterangans){
if(keterangans && keterangans !== undefined && keterangans.length != 0){ //alert(names[0]);
$( function(){
var availableTags6 = keterangans;
$("#keterangan").autocomplete({
source: availableTags6
});
});
document.getElementById('keterangan').value = keterangans[keterangans.length-1];
}
else{
google.script.run.withSuccessHandler(populatePtKeterangans).notKeteranganByCustomerId();
document.getElementById('keterangan').value = "";
}
document.getElementById("search_keterangan").innerHTML = "Silahkan periksa keterangan";
document.getElementById("search_keterangan").style = "color:blue";
document.getElementById('keterangan').disabled = false;
document.getElementById('save-btn').disabled = false;
}
</script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/materialize/1.0.0/js/materialize.min.js"></script>
<script src="https://unpkg.com/sweetalert/dist/sweetalert.min.js"></script>
<script src="https://code.jquery.com/jquery-3.2.1.slim.min.js" integrity="sha384-KJ3o2DKtIkvYIK3UENzmM7KCkRr/rE9/Qpg6aAZGJwFDMVNA/GpGFF93hXpG5KkN" crossorigin="anonymous"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.12.9/umd/popper.min.js" integrity="sha384-ApNbgh9B+Y1QKtv3Rn7W3mgPxhU9K/ScQsAP7hUibX39j7fakFPskvXusvfa0b4Q" crossorigin="anonymous"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/js/bootstrap.min.js" integrity="sha384-JZR6Spejh4U02d8jOt6vLEHfe/JQGiRRSQQxSfFWpi1MquVdAyjUar5+76PVCmYl" crossorigin="anonymous"></script>
<?!= include("page-js"); ?>
</body>
</html>
Masukkan Password Untuk Melihat Script (Password sama dengan di atas)
<script>
//script upload file
document.getElementsByName('myFile')[0].addEventListener('change',unggahFiles)
function unggahFiles(e){
if(document.getElementsByName('myFile')[0].value !== ""){
document.getElementById('resp-spinnerA').classList.remove("d-none");
google.script.run.withSuccessHandler(onSuccess).uploadFiles(this.parentNode)
} else {
sweetAlert({text: "Silahkan pilih file", icon:"warning", timer:2000})
}
}
function onSuccess(data){
document.getElementById('resp-spinnerA').classList.add("d-none");
document.getElementById('submitBtn').value = "Uploaded";
sweetAlert({text: "File berhasil divalidasi.", icon:"success", timer:2000})
document.getElementById('divMainButton2').style.display = "block";
}
//batas script upload file
document.getElementsByName("mainButton2")[0].addEventListener("click", afterButttonClicked2);
function afterButttonClicked2(){
var namadepan = document.getElementById("namadepan");
var namabelakang = document.getElementById("namabelakang");
var kelas = document.getElementById("kelas");
var alamat = document.getElementById("alamat");
var keterangan = document.getElementById("keterangan");
var input = document.getElementsByName('myFile')[0];
var file = input.value.split("\\");
var unggahan = file[file.length-1];
var submit2 = document.getElementById('submitBtn');
var rowData2 = {
namadepan: namadepan.value,
namabelakang: namabelakang.value,
kelas: kelas.value,
alamat: alamat.value,
keterangan: keterangan.value,
unggahan: unggahan,
}
if(namadepan.value == "" || namabelakang.value == "" || kelas.value == "" || alamat.value == "" || keterangan.value == "" || input.value == "" || submit2.value == ""){
sweetAlert({text: "Warning!!!"+"\n\n"+"Semua field wajib diisi.", icon:"error", timer:2000})
}else{
document.getElementById('resp-spinner5').classList.remove("d-none");
document.getElementById('resp-spinner6').classList.remove("d-none");
document.getElementById('resp-spinner7').classList.remove("d-none");
google.script.run.withSuccessHandler(afterSubmit2).addNewRow2(rowData2);
}
}
function afterSubmit2(){
document.getElementById('resp-spinner5').classList.add("d-none");
document.getElementById('resp-spinner6').classList.add("d-none");
document.getElementById('resp-spinner7').classList.add("d-none");
sweetAlert({text: "Sukses"+"\n\n"+"Data Terkirim", icon:"success", timer:2000})
//Apabila di iframe website, ganti url di bawah ini dengan url webiste kalian untuk auto reload
window.open("https://www.google.com/", "_top");
}
</script>
<script>
//script upload file
document.getElementsByName('myFile')[0].addEventListener('change',unggahFiles)
function unggahFiles(e){
if(document.getElementsByName('myFile')[0].value !== ""){
document.getElementById('resp-spinnerA').classList.remove("d-none");
google.script.run.withSuccessHandler(onSuccess).uploadFiles(this.parentNode)
} else {
sweetAlert({text: "Silahkan pilih file", icon:"warning", timer:2000})
}
}
function onSuccess(data){
document.getElementById('resp-spinnerA').classList.add("d-none");
document.getElementById('submitBtn').value = "Uploaded";
sweetAlert({text: "File berhasil divalidasi.", icon:"success", timer:2000})
document.getElementById('divMainButton2').style.display = "block";
}
//batas script upload file
document.getElementsByName("mainButton2")[0].addEventListener("click", afterButttonClicked2);
function afterButttonClicked2(){
var namadepan = document.getElementById("namadepan");
var namabelakang = document.getElementById("namabelakang");
var kelas = document.getElementById("kelas");
var alamat = document.getElementById("alamat");
var keterangan = document.getElementById("keterangan");
var input = document.getElementsByName('myFile')[0];
var file = input.value.split("\\");
var unggahan = file[file.length-1];
var submit2 = document.getElementById('submitBtn');
var rowData2 = {
namadepan: namadepan.value,
namabelakang: namabelakang.value,
kelas: kelas.value,
alamat: alamat.value,
keterangan: keterangan.value,
unggahan: unggahan,
}
if(namadepan.value == "" || namabelakang.value == "" || kelas.value == "" || alamat.value == "" || keterangan.value == "" || input.value == "" || submit2.value == ""){
sweetAlert({text: "Warning!!!"+"\n\n"+"Semua field wajib diisi.", icon:"error", timer:2000})
}else{
document.getElementById('resp-spinner5').classList.remove("d-none");
document.getElementById('resp-spinner6').classList.remove("d-none");
document.getElementById('resp-spinner7').classList.remove("d-none");
google.script.run.withSuccessHandler(afterSubmit2).addNewRow2(rowData2);
}
}
function afterSubmit2(){
document.getElementById('resp-spinner5').classList.add("d-none");
document.getElementById('resp-spinner6').classList.add("d-none");
document.getElementById('resp-spinner7').classList.add("d-none");
sweetAlert({text: "Sukses"+"\n\n"+"Data Terkirim", icon:"success", timer:2000})
//Apabila di iframe website, ganti url di bawah ini dengan url webiste kalian untuk auto reload
window.open("https://www.google.com/", "_top");
}
</script>
PENJELASANA!!
Line 71 : Silahkan ganti dengan url website kalian untuk auto reload, jika tidak memiliki website silahkan non aktifkan script dengan menambahkan tanda "//" di baris depan line 71.
10. Klik klik tombol Terapkan/Deploy lalu pilih Deployment baru/New Deployment.
11. Pastikan jenisnya adalah Aplikasi web dan hak akses Siapa saja/Anyone.
12. Klik link atau copy link yang sudah di Deploy.
