Membuat Aplikasi Pengelolaan Inventaris Kantor dengan Google Spreadsheet & Apps Script
Pada kesempatan kali ini kita akan membuat aplikasi sederhana untuk mengelola inventaris aset kantor dengan Google Spreadsheet sebagai backend dan Apps Script sebagai front end.
Apps Script akan kita manfaatkan untuk membuat QR Code reader dalam bentuk aplikasi web (mobile).
Lihat demo
Scan QR di samping untuk membuka WebApp melalui ponsel, kemudian Klik di sini untuk contoh target QR
Untuk mengakses artikel ini dianjurkan menggunakan komputer desktop π
1. Langkah Pertama, Buat Database dengan Spreadsheet.
Buat dokumen Spreadsheet seperti contoh di atas. Beberapa kolom penting yang perlu diperhatikan yaitu:
- Kolom No. ID berisi kode inventaris, wajib ada dan akan digunakan sebagai kode QR
- Kolom File berisi link file, Anda dapat mengarahkan ke Google Drive. Nama kolom ini tidak boleh diubah
- Kolom Image berfungsi untuk menampilkan gambar, wajib berisi direct link ke URL gambar. Nama kolom ini tidak boleh diubah
- Gunakan format angka pada kolom harga
Selain kolom penting tersebut, Anda bebas mengubah atau menambah kolom sesuai dengan kebutuhan.
Untuk File, Anda dapat mengupload ke Google Drive sehingga lebih aman sedangkan untuk Image, Anda dapat menggunakan layanan gratis seperti Cloudinary atau PostImage
2. Langkah Kedua, Siapkan Apps Script
Untuk yang belum tahu atau familiar dengan Apps Script, Google Apps Script adalah platform pengembangan aplikasi cepat yang mempercepat dan mempermudah pembuatan aplikasi bisnis yang terintegrasi dengan Google Workspace.
Berikut langkah demi langkah mempersiapkan Apps Script melalui Spreadsheet
- Klik menu Extensions, kemudian pilih Apps Script, Browser akan membuka tab editor kode.
- Pada menu Files di sidebar, tambahkan file Html dengan nama Index, Script, dan Style.
- Lanjut pada menu Services, tambahkan Google Sheets API.
- Jangan lupa memberi judul Apps Script.
Apps Script Anda telah tersedia, dan siap untuk digunakan.
3. Langkah Ketiga, Koding Tipis-tipis π₯
Buka file Code.gs, kemudian copy & paste kode berikut 
//CONSTANTS
const SPREADSHEETID = "1PaNgeXUHqpA_tW9_RvD_Mx43iplaYmTvxLVZ_4iofw4";
const DATASHEET = "Data";
const LASTCOLUMN = "Z";
const DATARANGE = DATASHEET + "!A2:" + LASTCOLUMN;
//DISPLAY HTML PAGE
function doGet() {
return HtmlService.createTemplateFromFile('Index').evaluate()
.addMetaTag('viewport', 'width=device-width, initial-scale=1')
.setTitle('Inventaris Aset Kantor')
.setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL);
}
function include(filename) {
return HtmlService.createHtmlOutputFromFile(filename)
.getContent();
}
// READ RECORDS
function readRecord(range) {
try {
let result = Sheets.Spreadsheets.Values.get(SPREADSHEETID, range);
return result.values;
} catch (err) {
console.log('Failed with error %s', err.message);
}
}
//GET FIRST ROW
function getFirstRow() {
let range = DATASHEET + "!A1:1";
let firstRow = readRecord(range);
return firstRow;
}
//SEARCH RECORDS
function searchRecords(formObject) {
let result = [];
try {
if (formObject.searchText) {
const data = readRecord(DATARANGE);
const searchText = formObject.searchText;
for (let i = 0; i < data.length; i++) {
for (let j = 0; j < data[i].length; j++) {
const cellValue = data[i][j];
if (cellValue.toLowerCase().includes(searchText.toLowerCase())) {
result.push(data[i]);
break;
}
}
}
}
} catch (err) {
console.log('Failed with error %s', err.message);
}
return result;
}Perhatikan bagian ini:
const SPREADSHEETID = "1PaNgeXUHqpA_tW9_RvD_Mx43iplaYmTvxLVZ_4iofw4";
const DATASHEET = "Data";Kode 3.2. Sesuaikan Spreadsheet ID
Masukkan Spreadsheet ID dan Sheet Name yang sesuai, seperti petunjuk di bawah ini. Kemudian simpan Code.gs dengan klik CTRL+S atau klik icon disket.
Selanjutnya buka file Index.html kemudian copy & paste kode berikut
<!DOCTYPE html>
<html>
<head>
<base target="_top">
<?!= include('Style.html'); ?>
</head>
<body>
<main>
<div id="dataTable">
<div style="text-align:center;font-size:14px;color:#777;padding:40px 0">
<div style="font-size:24px;line-height:1.125">Inventaris Aset Kantor</div>
<div style="margin-top:1em;">Masukkan No. ID <br>atau Scan QR</div>
</div>
</div>
<div style="position:relative;height:92px"></div>
<div class="input-form">
<div style="flex:1;overflow:auto;-webkit-overflow-scrolling:touch;">
<form id="search-form" class="form-inline" onsubmit="handleSearchForm(this)">
<div class="input-wrapper">
<input type="text" id="searchText" name="searchText" spellcheck="false" autocapitalize="off" autocomplete="off" placeholder="No. ID">
<button id="codeInputBtn" type="submit">
<svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 24 24" width="24" height="24" color="#000000" fill="none" style="display:block;width:32px;height:32px;fill: white;stroke:#999">
<path d="M17.5 17.5L22 22" stroke="currentColor" stroke-width="1.5" stroke-linecap="round" stroke-linejoin="round" />
<path d="M20 11C20 6.02944 15.9706 2 11 2C6.02944 2 2 6.02944 2 11C2 15.9706 6.02944 20 11 20C15.9706 20 20 15.9706 20 11Z" stroke="currentColor" stroke-width="1.5" stroke-linejoin="round" />
</svg>
</button>
</div>
</form>
</div>
<div style="padding-left:12px">
<a id="btn-scan-qr" class="btn-scan-qr">
<svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 24 24" width="24" height="24" color="#000000"
fill="currentColor">
<path
d="M7.00018 6.00055C5.77954 6.00421 5.10401 6.03341 4.54891 6.2664C3.77138 6.59275 3.13819 7.19558 2.76829 7.96165C2.46636 8.58693 2.41696 9.38805 2.31814 10.9903L2.1633 13.501C1.91757 17.4854 1.7947 19.4776 2.96387 20.7388C4.13303 22 6.10271 22 10.0421 22H13.9583C17.8977 22 19.8673 22 21.0365 20.7388C22.2057 19.4776 22.0828 17.4854 21.8371 13.501L21.6822 10.9903C21.5834 9.38805 21.534 8.58693 21.2321 7.96165C20.8622 7.19558 20.229 6.59275 19.4515 6.2664C18.8964 6.03341 18.2208 6.00421 17.0002 6.00055"
stroke="currentColor" stroke-width="1.5" stroke-linecap="round" />
<path
d="M17 7L16.1142 4.78543C15.732 3.82996 15.3994 2.7461 14.4166 2.25955C13.8924 2 13.2616 2 12 2C10.7384 2 10.1076 2 9.58335 2.25955C8.6006 2.7461 8.26801 3.82996 7.88583 4.78543L7 7"
stroke="currentColor" stroke-width="1.5" stroke-linecap="round" stroke-linejoin="round" />
<path
d="M15.5 14C15.5 15.933 13.933 17.5 12 17.5C10.067 17.5 8.5 15.933 8.5 14C8.5 12.067 10.067 10.5 12 10.5C13.933 10.5 15.5 12.067 15.5 14Z"
stroke="currentColor" stroke-width="1.5" />
<path d="M11.9998 6H12.0088" stroke="currentColor" stroke-width="2" stroke-linecap="round"
stroke-linejoin="round" />
</svg>
</a>
</div>
</div>
</main>
<!-- QR SCANNER -->
<div id="qr-canvasWrapper" hidden="" style="position:fixed;z-index:9999;top:0;right:0;bottom:0;left:0;">
<div style="" class="canvas-frame">
<div style="width:100%;padding-bottom:100%;position:relative;overflow:hidden;">
<div style="position: absolute;top:0;right:0;bottom:0;left:0;"><canvas id="qr-canvas"></canvas></div>
</div>
<div style="position:absolute;top:0;left:0;width:24px;height:8px;background:#fde047"></div>
<div style="position:absolute;top:0;left:0;width:8px;height:24px;background:#fde047"></div>
<div style="position:absolute;top:0;right:0;width:24px;height:8px;background:#fde047"></div>
<div style="position:absolute;top:0;right:0;width:8px;height:24px;background:#fde047"></div>
<div style="position:absolute;bottom:0;left:0;width:24px;height:8px;background:#fde047"></div>
<div style="position:absolute;bottom:0;left:0;width:8px;height:24px;background:#fde047"></div>
<div style="position:absolute;bottom:0;right:0;width:24px;height:8px;background:#fde047"></div>
<div style="position:absolute;bottom:0;right:0;width:8px;height:24px;background:#fde047"></div>
</div>
<div id="closeCam"></div>
</div>
<!-- SPINNER -->
<div class="spinner">
<svg xmlns="http://www.w3.org/2000/svg" width="1em" height="1em" viewBox="0 0 24 24">
<rect width="10" height="10" x="1" y="1" fill="currentColor" rx="1">
<animate id="svgSpinnersBlocksShuffle30" fill="freeze" attributeName="x"
begin="0;svgSpinnersBlocksShuffle3b.end" dur="0.2s" values="1;13" />
<animate id="svgSpinnersBlocksShuffle31" fill="freeze" attributeName="y" begin="svgSpinnersBlocksShuffle38.end"
dur="0.2s" values="1;13" />
<animate id="svgSpinnersBlocksShuffle32" fill="freeze" attributeName="x" begin="svgSpinnersBlocksShuffle39.end"
dur="0.2s" values="13;1" />
<animate id="svgSpinnersBlocksShuffle33" fill="freeze" attributeName="y" begin="svgSpinnersBlocksShuffle3a.end"
dur="0.2s" values="13;1" />
</rect>
<rect width="10" height="10" x="1" y="13" fill="currentColor" rx="1">
<animate id="svgSpinnersBlocksShuffle34" fill="freeze" attributeName="y" begin="svgSpinnersBlocksShuffle30.end"
dur="0.2s" values="13;1" />
<animate id="svgSpinnersBlocksShuffle35" fill="freeze" attributeName="x" begin="svgSpinnersBlocksShuffle31.end"
dur="0.2s" values="1;13" />
<animate id="svgSpinnersBlocksShuffle36" fill="freeze" attributeName="y" begin="svgSpinnersBlocksShuffle32.end"
dur="0.2s" values="1;13" />
<animate id="svgSpinnersBlocksShuffle37" fill="freeze" attributeName="x" begin="svgSpinnersBlocksShuffle33.end"
dur="0.2s" values="13;1" />
</rect>
<rect width="10" height="10" x="13" y="13" fill="currentColor" rx="1">
<animate id="svgSpinnersBlocksShuffle38" fill="freeze" attributeName="x" begin="svgSpinnersBlocksShuffle34.end"
dur="0.2s" values="13;1" />
<animate id="svgSpinnersBlocksShuffle39" fill="freeze" attributeName="y" begin="svgSpinnersBlocksShuffle35.end"
dur="0.2s" values="13;1" />
<animate id="svgSpinnersBlocksShuffle3a" fill="freeze" attributeName="x" begin="svgSpinnersBlocksShuffle36.end"
dur="0.2s" values="1;13" />
<animate id="svgSpinnersBlocksShuffle3b" fill="freeze" attributeName="y" begin="svgSpinnersBlocksShuffle37.end"
dur="0.2s" values="1;13" />
</rect>
</svg>
</div>
<!-- SCRIPT -->
<script src="https://cdn.jsdelivr.net/npm/[email protected]/dist/jquery.min.js"></script>
<script src="https://cdn.jsdelivr.net/gh/ardianzzz/qr-scanner/qr-scanner.js"></script>
<?!= include('Script.html'); ?>
</body>
</html>Kode 3.3. File Index.html
Selanjutnya buka file Script.html kemudian copy & paste kode berikut
<script>
window.addEventListener("load", functionInit, true);
// PREVENT FORM SUBMITTING
function preventFormSubmit() {
var forms = document.querySelectorAll('form');
for (var i = 0; i < forms.length; i++) {
forms[i].addEventListener('submit', function(event) {
event.preventDefault();
});
}
}
//INITIALIZE FUNCTIONS ONLOAD
function functionInit(){
$('.spinner').hide();
preventFormSubmit();
};
function handleFormSubmit(formObject) {
$('#dataTable').empty();
$('.spinner').show();
google.script.run.withSuccessHandler(createTable).processForm(formObject);
}
//CREATE THE DATA TABLE
function createTable(dataArray) {
if (dataArray && dataArray.length) {
google.script.run.withSuccessHandler(showData).getFirstRow();
function showData(nestedArray) {
var keys = nestedArray.flat();
var values = dataArray;
var arrayOfObjects = [];
for(var i=0; i<values.length; i++){
var obj = {};
for(var j=0; j<values[i].length; j++){
obj[keys[j]] = values[i][j];
}
arrayOfObjects.push(obj);
}
var result = "<div>";
$.each(arrayOfObjects, function (i) {
result += '<div class="result-card">';
$.each(arrayOfObjects[i], function (key, val) {
if(key == 'File') {
result += `<div class="${key.toLowerCase()}"><div>${key}</div><div><a href="${val}" target="_blank">Lihat berkas</a></div></div>`;
}
else if (key == 'Harga') {
var price = new Intl.NumberFormat('en-DE').format(val);
result += `<div class="${key.toLowerCase()}"><div>${key}</div><div>Rp ${price}</div></div>`;
}
else {
result += `<div class="${key.toLowerCase()}"><div>${key.replace("Timestamp", "Tanggal")}</div><div>${val}</div></div>`;
}
});
result += '</div>';
});
$('#dataTable').empty().append(result);
$('.spinner').hide();
}
} else {
$('#dataTable').empty().append(`
<div style="text-align:center;font-size:14px;color:#777;padding:40px 0">
<div style="font-size:40px">( ΛοΈΉΛ )</div>
<div style="margin-top:1em;">Data tidak ditemukan</div>
</div>
`);
$('.spinner').hide();
}
}
//SEARCH RECORDS
function handleSearchForm(formObject) {
$('#dataTable').empty();
$('.spinner').show();
google.script.run.withSuccessHandler(createTable).searchRecords(formObject);
$('#searchText').addClass('searchActive');
}
// QR SCANNER
var qrcode = window.qrcode;
var video = document.createElement("video");
var canvasWrapper = document.getElementById("qr-canvasWrapper");
var canvasElement = document.getElementById("qr-canvas");
var canvas = canvasElement.getContext("2d");
var qrResult = document.getElementById("qr-result");
var outputData = document.getElementById("outputData");
var btnScanQR = document.getElementById("btn-scan-qr");
var scanning = false;
qrcode.callback = function(res) {
if (res) {
var txtOutput = res;
scanning = false;
video.srcObject.getTracks().forEach(function(track) {
track.stop();
});
canvasWrapper.hidden = true;
btnScanQR.hidden = false;
$('#searchText').val(txtOutput).trigger( "submit" )
}
};
btnScanQR.onclick = function() {
navigator.mediaDevices.getUserMedia({
video: {
facingMode: "environment"
}
}).then(function(stream) {
scanning = true;
btnScanQR.hidden = true;
canvasWrapper.hidden = false;
video.setAttribute("playsinline", true);
video.srcObject = stream;
video.play();
tick();
scan();
}).catch((err) => {
alert('Device not supported')
});
};
function tick() {
canvasElement.height = video.videoHeight;
canvasElement.width = video.videoWidth;
canvas.drawImage(video, 0, 0, canvasElement.width, canvasElement.height);
scanning && requestAnimationFrame(tick);
}
function scan() {
try {
qrcode.decode();
} catch (e) {
setTimeout(scan, 300);
}
}
$('body').on('click', '#closeCam', function() {
canvasWrapper.hidden = true;
btnScanQR.hidden = false;
});
$('body').on('click', '.searchActive', function(){
$(this).removeClass('searchActive').val('');
})
</script>Kode 3.4. File Script.html
Buka file Style.html kemudian copy & paste kode berikut
<style>
* {
box-sizing: border-box;
}
html {
height: 100%;
-webkit-text-size-adjust: 100%;
}
body {
background: #f1f5f9;
-webkit-tap-highlight-color: rgba(0, 0, 0, 0);
margin: 0;
font-family: system-ui, sans-serif;
height: 100%;
font-size: 16px;
line-height: 1.5;
color: #0f172a;
}
main {
padding: 16px;
max-width: 480px;
margin: auto;
height: 100%;
}
.qr-scan-input {
border: solid 2px;
position: relative;
background: white;
display: flex;
align-items: center;
padding: 2px;
}
.qr-scan-input:focus-within {
border-color: blue;
}
.qr-scan-input:focus-within #btn-scan-qr {
display: none !important;
}
a {
color: #1e40af;
}
#qr-canvasWrapper {
overflow: hidden;
width: 100%;
height: 100%;
}
#qr-canvas {
display: block;
width: 100%;
height: auto;
}
.list {
display: none;
}
.input-wrapper {
position: relative;
background: white;
border: solid 2px #333;
border-radius: 10em;
}
.input-wrapper:focus-within {
border-color: #1d4ed8;
}
#searchText {
-webkit-appearance: none;
appearance: none;
display: block;
border: 0;
font-family: inherit;
font-size: 18px;
font-weight: 600;
background: none;
box-shadow: none;
line-height: 52px;
padding: 0 52px 0 16px;
outline: none;
display: block;
width: 100%;
}
#codeInputBtn {
-webkit-appearance: none;
appearance: none;
position: absolute;
border: 0;
box-shadow: none;
background: transparent;
padding: 6px;
top: 4px;
right: 4px;
border-radius: 50%;
}
.result-card {
padding: 8px 16px;
background: white;
border-radius: 4px;
box-shadow: 0 1.6px 3.6px 0 rgba(0, 0, 0, 0.132),
0 0.3px 0.9px 0 rgba(0, 0, 0, 0.108);
margin: 0 0 16px;
display: flex;
flex-direction: column;
}
.result-card>div {
display: flex;
align-items: center;
padding: 12px 0;
border-bottom: dotted 1px #e9e9e9;
}
.result-card>div>div {
flex: 1;
}
.result-card>div>div:nth-child(1) {
flex: 0 0 124px;
}
.result-card>div:last-of-type {
border: 0 !important;
}
.timestamp {
order: 9;
border-bottom: 0 !important;
display: none !important;
}
.id {
order: -1;
font-weight: 600;
}
.spinner {
position: fixed;
top: 0;
right: 0;
bottom: 92px;
left: 0;
background: rgba(241, 245, 249, 0.64);
z-index: 99999999;
}
.spinner svg {
display: block;
width: 24px;
height: 24px;
opacity: 0.64;
position: absolute;
top: 50%;
left: 50%;
margin: -20px 0 0 -20px;
}
.input-form {
padding: 16px;
z-index: 99;
display: flex;
align-items: center;
position: fixed;
bottom: 0;
left: 0;
right: 0;
z-index: 99;
background: #f3f6f9;
}
.btn-scan-qr {
display: block;
padding: 12px;
border-radius: 50%;
text-align: center;
background: crimson;
color: white;
text-decoration: none;
border: solid 2px #333;
}
.btn-scan-qr svg {
display: block;
width: 32px;
height: 32px;
fill: white;
}
#closeCam {
position: absolute;
top: 0;
right: 0;
bottom: 0;
left: 0;
background: rgba(0, 0, 0, .64);
-webkit-backdrop-filter: saturate(180%) blur(20px);
backdrop-filter: saturate(180%) blur(20px);
}
.canvas-frame {
width: 300px;
height: 300px;
top: 50%;
left: 50%;
margin: -196px 0 0 -150px;
position: absolute;
z-index: 99999;
border-radius: 8px;
overflow: hidden;
border: solid 3px;
box-shadow: 0 25.6px 57.6px 0 rgba(0, 0, 0, 0.22), 0 4.8px 14.4px 0 rgba(0, 0, 0, 0.18);
}
</style>Kode 3.5. File Style.html
Udah cuma gitu doang kodingnya… πΏ
4. Langkah Keempat, Waktunya Deploy!
- Klik tombol Deploy, kemudian pilih New deployment
- Pada menu Select type, pilih Web App
- Masukkan deskripsi aplikasi,
- Pada bagian Execute as pilih Me, kemudian pada bagian Who can access pilih Anyone
- Klik tombol Deploy, anda akan diarahkan untuk Authorize App
Lakukan otorisasi, Anda akan menghadapi halaman Warning, abaikan dengan klik Advanced, kemudian lanjutkan proses otorisasi
Jika proses Deploy berhasil, Anda akan mendapatkan link WebApp, Copy URL kemudian buka di ponsel.
Gunakan ShortURL seperti bit.ly atau manfaatkan fitur Add to Home Screen di ponsel untuk memudahkan mengakses aplikasi.
5. Langkah kelima, Testing & Rilis!
Masukkan data ke dalam Spreadsheet, selanjutnya buat QR Code berisi No. ID dengan tools di bawah ini.
QR Code Generator
Kemudian buka WebApp, lalu scan QR code tersebut dengan klik tombol kamera.
Selamat, Anda telah membuat produk! ππ₯³
Jika ada bug, pertanyaan, kritik, saran, atau penawaran, silakan mengirim email ke [email protected].
Terimakasih telah membaca, semoga artikel ini bermanfaat. π
Anda bisa buat salinan dokumen, sudah termasuk Apps Script! π