Membuat Aplikasi Surat Masuk dan Keluar dengan Google Sheet App Script

Aplikasi Google Sheet memberikan banyak fitur canggih yang bisa kita gunakan untuk memudahkan kita dalam mengelola data dengan Google Sheet.

Salah satu fitur yang bisa kalian gunakan adalah menambahkan script untuk custom Google Sheet seperti menambahkan rumus, bahkan juga bisa membuat aplikasi seperti yang kita  inginkan.

Sebenarnya tidak jauh berbeda dengan fitur di Excel, jika di Excel ada VBA Excel maka di Google Sheet kalian bisa menggunakan Google Script

Postingan kali ini kita akan memberikan Tutorial Membuat Aplikasi Google Sheet App Script dengan studi kasus membuat Aplikasi untuk Mengelola Surat Masuk dan Surat Keluar,  tidak jauh beda sih dengan Sistem Arsip Surat namun dari yang akan kita sampaikan mungkin masih sederhana saja, dan nantinya kalian bisa kembangkan sendiri sesuai kebutuhan, dan berikut adalah langkah-langkahnya'

1. Buat File Spreadsheet baru Google Sheet

2. Buat Rename sheet dengan nama Data Surat Keluar dan data Surat masuk  yang berisi yang fungsinya untuk menampung data dari hasil input

3. Buat sheet baru dengan nama Form input Surat masuk dan Surat keluar yang berfungsi untuk memasukkan data surat masuk dan keluar

4. Buat sheet baru kembali kemudian beri nama dengan laporan

5. Buat sheet baru dengan nama home

6. Selanjutnya membuat script App script dengan klik menu App script seperti gambar di bawah

7. Membuat beberapa file .gs dan .html yaitu

Code.gs script ini digunakan untuk proses input surat masuk ke data surat keluar di google sheet

function Save() {

var Sheet = SpreadsheetApp.getActiveSpreadsheet();

var shtinput = Sheet.getSheetByName('form surat keluar');

var shtdb = Sheet.getSheetByName('data surat keluar');

var ui = SpreadsheetApp.getUi();

// Display a dialog box with a title, message, and "Yes" and "No" buttons. The user can also

// close the dialog by clicking the close button in its title bar.

var response = ui.alert("Submit", 'Apakah anda yakin akan menyimpan data ini?',ui.ButtonSet.YES_NO);

// Checking the user response and proceed with clearing the form if user selects Yes

if (response == ui.Button.NO)

{return;//exit from this function

}

var nomor_surat = shtinput.getRange('E6').getValue();

var perihal_surat = shtinput.getRange('E7').getValue();

var tujuan = shtinput.getRange('E8').getValue();

var alamat_tujuan = shtinput.getRange('E9').getValue();

var tanggal_surat = shtinput.getRange('E10').getValue();

var deskripsi = shtinput.getRange('E11').getValue();

var tanggal_kirim = shtinput.getRange('E12').getValue();

var tanggal_diterima = shtinput.getRange('E13').getValue();

var penerima = shtinput.getRange('E14').getValue();

var file_surat = shtinput.getRange('E15').getValue();

var baris = shtdb.getRange('K1').getValue();

baris += 1;

var rangeisi = shtdb.getRange('A' + baris + ':J'+ baris);

rangeisi.setValues([[nomor_surat, perihal_surat, tujuan, alamat_tujuan, tanggal_surat, deskripsi, tanggal_kirim, tanggal_diterima, penerima, file_surat]]);

Reset();

}

function Reset() {

var Sheet = SpreadsheetApp.getActiveSpreadsheet();

var shtinput = Sheet.getSheetByName('form surat keluar');

shtinput.getRange('E6').clearContent();

shtinput.getRange('E7').clearContent();

shtinput.getRange('E8').clearContent();

shtinput.getRange('E9').clearContent();

shtinput.getRange('E10').setValue(new Date()).setNumberFormat('dd-mm-yyyy')

shtinput.getRange('E11').clearContent();

shtinput.getRange('E12').setValue(new Date()).setNumberFormat('dd-mm-yyyy')

shtinput.getRange('E13').setValue(new Date()).setNumberFormat('dd-mm-yyyy')

shtinput.getRange('E14').clearContent();

shtinput.getRange('E15').setValue('klik upload file Button');

}

function searchRecord() {

var myGooglSheet= SpreadsheetApp.getActiveSpreadsheet(); //declare a variable and set with active google sheet

var shUserForm= myGooglSheet.getSheetByName("form surat keluar"); //delcare a variable and set with the User Form worksheet

var datasheet = myGooglSheet.getSheetByName("data surat keluar"); ////delcare a variable and set with the Database worksheet

var str = shUserForm.getRange("E6").getValue();

var values = datasheet.getDataRange().getValues(); //getting the entire values from the used range and assigning it to values variable

var valuesFound=false; //variable to store boolean value

for (var i = 0; i < values.length; i++)

{

var rowValue = values[i]; //declaraing a variable and storing the value

//checking the first value of the record is equal to search item

if (rowValue[0] == str) {

shUserForm.getRange("E7").setValue(rowValue[1]) ;

shUserForm.getRange("E8").setValue(rowValue[2]);

shUserForm.getRange("E9").setValue(rowValue[3]);

shUserForm.getRange("E10").setValue(rowValue[4]);

shUserForm.getRange("E11").setValue(rowValue[5]);

shUserForm.getRange("E12").setValue(rowValue[6]);

shUserForm.getRange("E13").setValue(rowValue[7]);

shUserForm.getRange("E14").setValue(rowValue[8]);

shUserForm.getRange("E15").setValue(rowValue[9]);

return; //come out from the search function

}

}

if(valuesFound==false){

//to create the instance of the user-interface environment to use the messagebox features

var ui = SpreadsheetApp.getUi();

ui.alert("No record found!");

}

}

//Function to edit the record

function editRecord() {

var myGooglSheet= SpreadsheetApp.getActiveSpreadsheet(); //declare a variable and set with active google sheet

var shUserForm= myGooglSheet.getSheetByName("form surat keluar"); //delcare a variable and set with the User Form worksheet

var datasheet = myGooglSheet.getSheetByName("data surat keluar"); ////delcare a variable and set with the Database worksheet

//to create the instance of the user-interface environment to use the messagebox features

var ui = SpreadsheetApp.getUi();

// Display a dialog box with a title, message, and "Yes" and "No" buttons. The user can also

// close the dialog by clicking the close button in its title bar.

var response = ui.alert("Submit", 'Apakah anda yakin akan merubah data ini?',ui.ButtonSet.YES_NO);

// Checking the user response and proceed with clearing the form if user selects Yes

if (response == ui.Button.NO)

{return;//exit from this function

}

var str = shUserForm.getRange("E6").getValue();

var values = datasheet.getDataRange().getValues(); //getting the entire values from the used range and assigning it to values variable

var valuesFound=false; //variable to store boolean value to validate whether values found or not

for (var i = 0; i < values.length; i++)

{

var rowValue = values[i]; //declaraing a variable and storing the value

//checking the first value of the record is equal to search item

if (rowValue[0] == str) {

var iRow = i+1; //identify the row number

datasheet.getRange(iRow, 1).setValue(shUserForm.getRange("E6").getValue()); //Employee ID

datasheet.getRange(iRow, 2).setValue(shUserForm.getRange("E7").getValue()); //Employee Name

datasheet.getRange(iRow, 3).setValue(shUserForm.getRange("E8").getValue()); //Gender

datasheet.getRange(iRow, 4).setValue(shUserForm.getRange("E9").getValue()); // Email ID

datasheet.getRange(iRow, 5).setValue(shUserForm.getRange("E10").getValue()); //Department

datasheet.getRange(iRow, 6).setValue(shUserForm.getRange("E11").getValue());// Address

datasheet.getRange(iRow, 7).setValue(shUserForm.getRange("E12").getValue());// Address

datasheet.getRange(iRow, 8).setValue(shUserForm.getRange("E13").getValue());// Address

datasheet.getRange(iRow, 9).setValue(shUserForm.getRange("E14").getValue());// Address

datasheet.getRange(iRow, 10).setValue(shUserForm.getRange("E15").getValue());// Address

// date function to update the current date and time as submittted on

// datasheet.getRange(iRow, 7).setValue(new Date()).setNumberFormat('yyyy-mm-dd h:mm'); //Submitted On

//get the email address of the person running the script and update as Submitted By

// datasheet.getRange(iRow, 8).setValue(Session.getActiveUser().getEmail()); //Submitted By

//ui.alert(' "Data updated for - Emp #' + shUserForm.getRange("C7").getValue() +' "');

//Clearnign the data from the Data Entry Form

shUserForm.getRange('E6').clearContent();

shUserForm.getRange('E7').clearContent();

shUserForm.getRange('E8').clearContent();

shUserForm.getRange('E9').clearContent();

shUserForm.getRange('E10').setValue(new Date()).setNumberFormat('dd-mm-yyyy')

shUserForm.getRange('E11').clearContent();

shUserForm.getRange('E12').setValue(new Date()).setNumberFormat('dd-mm-yyyy')

shUserForm.getRange('E13').setValue(new Date()).setNumberFormat('dd-mm-yyyy')

shUserForm.getRange('E14').clearContent();

shUserForm.getRange('E15').setValue('klik upload file Button');

valuesFound=true;

return; //come out from the search function

}

}

if(valuesFound==false){

//to create the instance of the user-interface environment to use the messagebox features

var ui = SpreadsheetApp.getUi();

ui.alert("No record found!");

}

}

//Function to delete the record

function deleteRow() {

var myGooglSheet= SpreadsheetApp.getActiveSpreadsheet(); //declare a variable and set with active google sheet

var shUserForm= myGooglSheet.getSheetByName("form surat keluar"); //delcare a variable and set with the User Form worksheet

var datasheet = myGooglSheet.getSheetByName("data surat keluar"); ////delcare a variable and set with the Database worksheet

//to create the instance of the user-interface environment to use the messagebox features

var ui = SpreadsheetApp.getUi();

// Display a dialog box with a title, message, and "Yes" and "No" buttons. The user can also

// close the dialog by clicking the close button in its title bar.

var response = ui.alert("Submit", 'Apakah anda akan menghapus data ini?',ui.ButtonSet.YES_NO);

// Checking the user response and proceed with clearing the form if user selects Yes

if (response == ui.Button.NO)

{return;//exit from this function

}

var str = shUserForm.getRange("E6").getValue();

var values = datasheet.getDataRange().getValues(); //getting the entire values from the used range and assigning it to values variable

var valuesFound=false; //variable to store boolean value to validate whether values found or not

for (var i = 0; i < values.length; i++)

{

var rowValue = values[i]; //declaraing a variable and storing the value

//checking the first value of the record is equal to search item

if (rowValue[0] == str) {

var iRow = i+1; //identify the row number

datasheet.deleteRow(iRow) ; //deleting the row

//message to confirm the action

ui.alert(' "Record deleted for Emp #' + shUserForm.getRange("E6").getValue() +' "');

//Clearing the user form

shUserForm.getRange('E6').clearContent();

shUserForm.getRange('E7').clearContent();

shUserForm.getRange('E8').clearContent();

shUserForm.getRange('E9').clearContent();

shUserForm.getRange('E10').setValue(new Date()).setNumberFormat('dd-mm-yyyy')

shUserForm.getRange('E11').clearContent();

shUserForm.getRange('E12').setValue(new Date()).setNumberFormat('dd-mm-yyyy')

shUserForm.getRange('E13').setValue(new Date()).setNumberFormat('dd-mm-yyyy')

shUserForm.getRange('E14').clearContent();

shUserForm.getRange('E15').setValue('klik upload file Button');

valuesFound=true;

return; //come out from the search function

}

}

if(valuesFound==false){

//to create the instance of the user-interface environment to use the messagebox features

var ui = SpreadsheetApp.getUi();

ui.alert("No record found!");

}

}

upload.gs script ini berfungsi untuk memproses upload dokumen surat

// Use this code for Google Docs, Slides, Forms, or Sheets.

function onOpen() {

SpreadsheetApp.getUi() // Or DocumentApp or SlidesApp or FormApp.

.createMenu('Upload File Tutorial')

.addItem('Attachment', 'openAttachmentDialog')

.addToUi();

}

function openAttachmentDialog() {

var html = HtmlService.createHtmlOutputFromFile('UploadFile');

SpreadsheetApp.getUi() // Or DocumentApp or SlidesApp or FormApp.

.showModalDialog(html, 'Upload File');

}

function saveFile(obj) {

var blob = Utilities.newBlob(Utilities.base64Decode(obj.data), obj.mimeType, obj.fileName);

var file = DriveApp.getFolderById("11aWs4OWaxgss2V9lydj3RL42uMqWhpVB").createFile(blob);

//var cellFormula = 'hyperlink("' + file.getUrl() + '";"' + file.getName() + '")';

var cellFormula = 'hyperlink("' + file.getUrl() + '")';

var activeSheet = SpreadsheetApp.getActiveSheet();

// var selection = activeSheet.getSelection();

var cell = activeSheet.getRange('E15');

cell.setFormula(cellFormula);

return file.getId();

}

uploadmasuk.gs script ini berfungsi untuk memproses upload dokumen surat masuk

// Use this code for Google Docs, Slides, Forms, or Sheets.

function onOpen() {

SpreadsheetApp.getUi() // Or DocumentApp or SlidesApp or FormApp.

.createMenu('Upload File Tutorial')

.addItem('Attachment', 'openAttachmentDialog')

.addToUi();

}

function openAttachmentDialog1() {

var html = HtmlService.createHtmlOutputFromFile('UploadFile');

SpreadsheetApp.getUi() // Or DocumentApp or SlidesApp or FormApp.

.showModalDialog(html, 'Upload File');

}

function saveFile(obj) {

var blob = Utilities.newBlob(Utilities.base64Decode(obj.data), obj.mimeType, obj.fileName);

var file = DriveApp.getFolderById("1ouTdhqOlzYEGjkJVBbqktod9XcclqN9h").createFile(blob);

//var cellFormula = 'hyperlink("' + file.getUrl() + '";"' + file.getName() + '")';

var cellFormula = 'hyperlink("' + file.getUrl() + '")';

var activeSheet = SpreadsheetApp.getActiveSheet();

// var selection = activeSheet.getSelection();

var cell = activeSheet.getRange('E15');

cell.setFormula(cellFormula);

return file.getId();

}

CodeMasuk.gs script ini digunakan untuk proses input surat masuk ke data surat masuk di google sheet

function SaveMasuk() {

var Sheet = SpreadsheetApp.getActiveSpreadsheet();

var shtinput = Sheet.getSheetByName('form surat masuk');

var shtdb = Sheet.getSheetByName('data surat masuk');

var ui = SpreadsheetApp.getUi();

// Display a dialog box with a title, message, and "Yes" and "No" buttons. The user can also

// close the dialog by clicking the close button in its title bar.

var response = ui.alert("Submit", 'Apakah anda yakin akan menyimpan data ini?',ui.ButtonSet.YES_NO);

// Checking the user response and proceed with clearing the form if user selects Yes

if (response == ui.Button.NO)

{return;//exit from this function

}

var nomor_surat_keluar = shtinput.getRange('E6').getValue();

var perihal_surat = shtinput.getRange('E7').getValue();

var asal_surat = shtinput.getRange('E8').getValue();

var alamat_asal = shtinput.getRange('E9').getValue();

var ditujukan_kepada = shtinput.getRange('E10').getValue();

var tanggal_surat = shtinput.getRange('E11').getValue();

var deskripsi = shtinput.getRange('E12').getValue();

var tanggal_diterima = shtinput.getRange('E13').getValue();

var penerima = shtinput.getRange('E14').getValue();

var file_surat = shtinput.getRange('E15').getValue();

var baris = shtdb.getRange('K1').getValue();

baris += 1;

var rangeisi = shtdb.getRange('A' + baris + ':J'+ baris);

rangeisi.setValues([[nomor_surat_keluar, perihal_surat, asal_surat, alamat_asal, ditujukan_kepada, tanggal_surat, deskripsi, tanggal_diterima, penerima, file_surat]]);

Reset();

}

function ResetMasuk() {

var Sheet = SpreadsheetApp.getActiveSpreadsheet();

var shtinput = Sheet.getSheetByName('form surat masuk');

shtinput.getRange('E6').clearContent();

shtinput.getRange('E7').clearContent();

shtinput.getRange('E8').clearContent();

shtinput.getRange('E9').clearContent();

shtinput.getRange('E10').clearContent();

shtinput.getRange('E11').setValue(new Date()).setNumberFormat('dd-mm-yyyy');

shtinput.getRange('E12').clearContent();

shtinput.getRange('E13').setValue(new Date()).setNumberFormat('dd-mm-yyyy');

shtinput.getRange('E14').clearContent();

shtinput.getRange('E15').setValue('klik upload file Button');

}

function searchRecordMasuk() {

var myGooglSheet= SpreadsheetApp.getActiveSpreadsheet(); //declare a variable and set with active google sheet

var shUserForm= myGooglSheet.getSheetByName("form surat masuk"); //delcare a variable and set with the User Form worksheet

var datasheet = myGooglSheet.getSheetByName("data surat masuk"); ////delcare a variable and set with the Database worksheet

var str = shUserForm.getRange("E6").getValue();

var values = datasheet.getDataRange().getValues(); //getting the entire values from the used range and assigning it to values variable

var valuesFound=false; //variable to store boolean value

for (var i = 0; i < values.length; i++)

{

var rowValue = values[i]; //declaraing a variable and storing the value

//checking the first value of the record is equal to search item

if (rowValue[0] == str) {

shUserForm.getRange("E7").setValue(rowValue[1]) ;

shUserForm.getRange("E8").setValue(rowValue[2]);

shUserForm.getRange("E9").setValue(rowValue[3]);

shUserForm.getRange("E10").setValue(rowValue[4]);

shUserForm.getRange("E11").setValue(rowValue[5]);

shUserForm.getRange("E12").setValue(rowValue[6]);

shUserForm.getRange("E13").setValue(rowValue[7]);

shUserForm.getRange("E14").setValue(rowValue[8]);

shUserForm.getRange("E15").setValue(rowValue[9]);

return; //come out from the search function

}

}

if(valuesFound==false){

//to create the instance of the user-interface environment to use the messagebox features

var ui = SpreadsheetApp.getUi();

ui.alert("No record found!");

}

}

//Function to edit the record

function editRecordMasuk() {

var myGooglSheet= SpreadsheetApp.getActiveSpreadsheet(); //declare a variable and set with active google sheet

var shUserForm= myGooglSheet.getSheetByName("form surat masuk"); //delcare a variable and set with the User Form worksheet

var datasheet = myGooglSheet.getSheetByName("data surat masuk"); ////delcare a variable and set with the Database worksheet

//to create the instance of the user-interface environment to use the messagebox features

var ui = SpreadsheetApp.getUi();

// Display a dialog box with a title, message, and "Yes" and "No" buttons. The user can also

// close the dialog by clicking the close button in its title bar.

var response = ui.alert("Submit", 'Apakah anda yakin akan merubah data ini?',ui.ButtonSet.YES_NO);

// Checking the user response and proceed with clearing the form if user selects Yes

if (response == ui.Button.NO)

{return;//exit from this function

}

var str = shUserForm.getRange("E6").getValue();

var values = datasheet.getDataRange().getValues(); //getting the entire values from the used range and assigning it to values variable

var valuesFound=false; //variable to store boolean value to validate whether values found or not

for (var i = 0; i < values.length; i++)

{

var rowValue = values[i]; //declaraing a variable and storing the value

//checking the first value of the record is equal to search item

if (rowValue[0] == str) {

var iRow = i+1; //identify the row number

datasheet.getRange(iRow, 1).setValue(shUserForm.getRange("E6").getValue()); //Employee ID

datasheet.getRange(iRow, 2).setValue(shUserForm.getRange("E7").getValue()); //Employee Name

datasheet.getRange(iRow, 3).setValue(shUserForm.getRange("E8").getValue()); //Gender

datasheet.getRange(iRow, 4).setValue(shUserForm.getRange("E9").getValue()); // Email ID

datasheet.getRange(iRow, 5).setValue(shUserForm.getRange("E10").getValue()); //Department

datasheet.getRange(iRow, 6).setValue(shUserForm.getRange("E11").getValue());// Address

datasheet.getRange(iRow, 7).setValue(shUserForm.getRange("E12").getValue());// Address

datasheet.getRange(iRow, 8).setValue(shUserForm.getRange("E13").getValue());// Address

datasheet.getRange(iRow, 9).setValue(shUserForm.getRange("E14").getValue());// Address

datasheet.getRange(iRow, 10).setValue(shUserForm.getRange("E15").getValue());// Address

// date function to update the current date and time as submittted on

// datasheet.getRange(iRow, 7).setValue(new Date()).setNumberFormat('yyyy-mm-dd h:mm'); //Submitted On

//get the email address of the person running the script and update as Submitted By

// datasheet.getRange(iRow, 8).setValue(Session.getActiveUser().getEmail()); //Submitted By

//ui.alert(' "Data updated for - Emp #' + shUserForm.getRange("C7").getValue() +' "');

//Clearnign the data from the Data Entry Form

shUserForm.getRange('E6').clearContent();

shUserForm.getRange('E7').clearContent();

shUserForm.getRange('E8').clearContent();

shUserForm.getRange('E9').clearContent();

shUserForm.getRange('E10').clearContent();

shUserForm.getRange('E11').setValue(new Date()).setNumberFormat('dd-mm-yyyy');

shUserForm.getRange('E12').clearContent();

shUserForm.getRange('E13').setValue(new Date()).setNumberFormat('dd-mm-yyyy');

shUserForm.getRange('E14').clearContent();

shUserForm.getRange('E15').setValue('klik upload file Button');

valuesFound=true;

return; //come out from the search function

}

}

if(valuesFound==false){

//to create the instance of the user-interface environment to use the messagebox features

var ui = SpreadsheetApp.getUi();

ui.alert("No record found!");

}

}

//Function to delete the record

function deleteRowMasuk() {

var myGooglSheet= SpreadsheetApp.getActiveSpreadsheet(); //declare a variable and set with active google sheet

var shUserForm= myGooglSheet.getSheetByName("form surat masuk"); //delcare a variable and set with the User Form worksheet

var datasheet = myGooglSheet.getSheetByName("data surat masuk"); ////delcare a variable and set with the Database worksheet

//to create the instance of the user-interface environment to use the messagebox features

var ui = SpreadsheetApp.getUi();

// Display a dialog box with a title, message, and "Yes" and "No" buttons. The user can also

// close the dialog by clicking the close button in its title bar.

var response = ui.alert("Submit", 'Apakah anda akan menghapus data ini?',ui.ButtonSet.YES_NO);

// Checking the user response and proceed with clearing the form if user selects Yes

if (response == ui.Button.NO)

{return;//exit from this function

}

var str = shUserForm.getRange("E6").getValue();

var values = datasheet.getDataRange().getValues(); //getting the entire values from the used range and assigning it to values variable

var valuesFound=false; //variable to store boolean value to validate whether values found or not

for (var i = 0; i < values.length; i++)

{

var rowValue = values[i]; //declaraing a variable and storing the value

//checking the first value of the record is equal to search item

if (rowValue[0] == str) {

var iRow = i+1; //identify the row number

datasheet.deleteRow(iRow) ; //deleting the row

//message to confirm the action

ui.alert(' "Record deleted for Emp #' + shUserForm.getRange("E6").getValue() +' "');

//Clearing the user form

shUserForm.getRange('E6').clearContent();

shUserForm.getRange('E7').clearContent();

shUserForm.getRange('E8').clearContent();

shUserForm.getRange('E9').clearContent();

shUserForm.getRange('E10').clearContent();

shUserForm.getRange('E11').setValue(new Date()).setNumberFormat('dd-mm-yyyy');

shUserForm.getRange('E12').clearContent();

shUserForm.getRange('E13').setValue(new Date()).setNumberFormat('dd-mm-yyyy');

shUserForm.getRange('E14').clearContent();

shUserForm.getRange('E15').setValue('klik upload file Button');

valuesFound=true;

return; //come out from the search function

}

}

if(valuesFound==false){

//to create the instance of the user-interface environment to use the messagebox features

var ui = SpreadsheetApp.getUi();

ui.alert("No record found!");

}

}

UploadFile.html berfungsi untuk menampilkan menampilkan form upload dokumen surat keluar

<!DOCTYPE html>

<html>

<head>

<base target="_top">

<link rel="stylesheet" href="https://ssl.gstatic.com/docs/script/css/add-ons1.css">

</head>

<script>

function getFiles() {

document.getElementById("uploadButton").disabled = true;

const progressText = document.getElementById("progress");

const f = document.getElementById('files');

var uploadCompletedCount = 0;

progressText.innerHTML = "Uploading file " + (uploadCompletedCount + 1) + "/" + [...f.files].length + "";

[...f.files].forEach((file, i) => {

const fr = new FileReader();

fr.onload = (e) => {

const data = e.target.result.split(",");

const obj = {fileName: f.files[i].name, mimeType: data[0].match(/:(\w.+);/)[1], data: data[1]};

google.script.run.withSuccessHandler((id) => {

uploadCompletedCount++;

progressText.innerHTML = "Upload completed";

if (uploadCompletedCount >= [...f.files].length){

google.script.host.close();

}

else{

progressText.innerHTML = "Uploading file " + (uploadCompletedCount + 1) + "/" + [...f.files].length + "";

}

}).saveFile(obj);

}

fr.readAsDataURL(file);

});

}

</script>

<body>

<!-- <input type="file" name="upload" id="files" multiple/> -->

<input type="file" name="upload" id="files"/> <!-- I expect to up load only one file -->

<input type='button' id="uploadButton" value='Upload' onclick='getFiles()' class="action">

<br><br>

<div id="progress"> </div>

</body>

</html>

UploadFileMasuk.html berfungsi untuk menampilkan menampilkan form upload dokumen surat masuk

<!DOCTYPE html>

<html>

<head>

<base target="_top">

<link rel="stylesheet" href="https://ssl.gstatic.com/docs/script/css/add-ons1.css">

</head>

<script>

function getFiles() {

document.getElementById("uploadButton").disabled = true;

const progressText = document.getElementById("progress");

const f = document.getElementById('files');

var uploadCompletedCount = 0;

progressText.innerHTML = "Uploading file " + (uploadCompletedCount + 1) + "/" + [...f.files].length + "";

[...f.files].forEach((file, i) => {

const fr = new FileReader();

fr.onload = (e) => {

const data = e.target.result.split(",");

const obj = {fileName: f.files[i].name, mimeType: data[0].match(/:(\w.+);/)[1], data: data[1]};

google.script.run.withSuccessHandler((id) => {

uploadCompletedCount++;

progressText.innerHTML = "Upload completed";

if (uploadCompletedCount >= [...f.files].length){

google.script.host.close();

}

else{

progressText.innerHTML = "Uploading file " + (uploadCompletedCount + 1) + "/" + [...f.files].length + "";

}

}).saveFile(obj);

}

fr.readAsDataURL(file);

});

}

</script>

<body>

<!-- <input type="file" name="upload" id="files" multiple/> -->

<input type="file" name="upload" id="files"/> <!-- I expect to up load only one file -->

<input type='button' id="uploadButton" value='Upload' onclick='getFiles()' class="action">

<br><br>

<div id="progress"> </div>

</body>

</html>

8. Setelah semua selesai di buat langkah selanjutnya adalah tes apakah data yang dimasukkan berhasil

Template Spreadsheet : https://docs.google.com/spreadsheets/d/1bGPAe47aAllnDXG9batbtVz1CZlr6aCpV_VcL7_B35Q/copy

Referensi :

1. https://github.com/ba5tz/vbaTutorial/blob/master/Googe%20AppScript/Form%20Input%20Sederhana/code.gs