Website Surat Masuk, Surat Keluar, Approve, Tracking Surat Menggunakan Google Apps Script

1. Copy Spreadsheet (Klik disini)

2. Pada Spreadsheet yang telah di copy di atas memilik beberapa sheet diantaranya :

sheet login
sheet Form
sheet suratkeluar
sheet suratmasuk
sheet tracking

3. Buatlah folder baru pada Google Drive untuk menyimpan berkas.

(Pastikan status folder sharing view)

4. Buatlah lembar kerja Apps Script dengan cara klik menu Ekstensi/Extensions lalu pilih Apps Script.

5. Pada lembar kerja Apps Script terdapat beberapa file dan layanan API (free) default yaitu :

File

Code.gs
header.html
login.html
register.html
main.html
suratkeluar.html
suratmasuk.html
trackingsurat.html
visit.html
csstable.html
cssmain.html
cssloading.html

Layanan

Drive API (free)
Sheet API (free)

6. Copy dan pastekan script di bawah ini ke Code.gs

*Untuk Mendapatkan Full Script tanpa password silahkan Klik Disini*

Masukkan Password Untuk Melihat Script (Password ada di dalam video)

//Source Code by www.javabitpro.com

let MySheets  = SpreadsheetApp.getActiveSpreadsheet();
let LoginSheet  = MySheets.getSheetByName("login");  
let FormSheet = MySheets.getSheetByName("Form");  
let EmpSheet  = MySheets.getSheetByName("suratkeluar");     

function doGet(e) {
  var output = HtmlService.createTemplateFromFile('login');
  
  var sess = getSession();
  if (sess.loggedIn) {
    let page = e.parameter.page;         
    if (page == null) page = "main";     
    var output = HtmlService.createTemplateFromFile(page); 
  }
  return output.evaluate();
}

function includeHeader() {
  return HtmlService.createTemplateFromFile("header.html").evaluate().getContent();
}

function include(filename) {
  return HtmlService.createHtmlOutputFromFile(filename)
      .getContent();
}

function myURL() {
  return ScriptApp.getService().getUrl();
}

function setSession(session) {
  var sId   = Session.getTemporaryActiveUserKey();
  var uProp = PropertiesService.getUserProperties();
  uProp.setProperty(sId, JSON.stringify(session));
}

function getSession() {
  var sId   = Session.getTemporaryActiveUserKey();
  var uProp = PropertiesService.getUserProperties();
  var sData = uProp.getProperty(sId);
  return sData ? JSON.parse(sData) : { loggedIn: false };
}

function loginUser(pUID, pPassword) {
  if (loginCheck(pUID, pPassword)) {
    var sess = getSession();
    sess.loggedIn = true;
    sess.userId = pUID;  // Simpan ID pengguna ke sesi
    setSession(sess);
    return 'success';
  } else {
    return 'failure';
  }
}

function getUserId() {
  var sess = getSession();
  return sess.userId ? sess.userId : '';
}

function logoutUser() {
  var sess = getSession();
  sess.loggedIn = false;
  setSession(sess);
}

function loginCheck(pUID, pPassword) {
  let LoginPass = false;
  let ReturnData = LoginSheet.getRange("A:A").createTextFinder(pUID).matchEntireCell(true).findAll();
  
  ReturnData.forEach(function (range) {
    let StartRow = range.getRow();
    let TmpPass = LoginSheet.getRange(StartRow, 2).getValue();
    if (TmpPass == pPassword) {
      LoginPass = true;
    }
  });

  return LoginPass;
}
//Source Code by www.javabitpro.com
function getInputsList() {
  let table = FormSheet.getRange("A2:C").getValues().filter(r => r.every(Boolean));
  
  // Ambil username yang sedang login
  let sess = getSession();
  let loggedInUser = sess.userId;

  // Ambil daftar username dari sheet login
  let allUsers = LoginSheet.getRange("A2:A").getValues().flat().filter(Boolean);

  // Filter untuk mengecualikan username yang sama dengan username yang sedang login
  let otherUsers = allUsers.filter(user => user !== loggedInUser);

  return { table, loggedInUser, otherUsers };
}

//Source Code by www.javabitpro.com
function getSubmit(jsonData, index) {
  let data = JSON.parse(jsonData);
  let fileUrl = '';

  // Handle file upload if present
  if (data.file) {
    let blob = Utilities.newBlob(Utilities.base64Decode(data.file), 'application/octet-stream', 'uploaded_file');
    let folder = DriveApp.getFolderById("ID_FOLDER"); // Replace with your folder ID
    let uploadedFile = folder.createFile(blob);
    fileUrl = uploadedFile.getUrl();
  }

  // Prepare data for sheet
  let rowData = [];
  for (let key in data) {
    if (data.hasOwnProperty(key) && key !== 'file') {
      rowData.push(data[key]);
    }
  }
  rowData.push(fileUrl);

  if (index === 0) {
    // Add new data
    EmpSheet.appendRow(rowData);
  } else {
    // Update existing data
    let cols = EmpSheet.getLastColumn();
    EmpSheet.getRange(index, 1, 1, cols).setValues([rowData]);
  }
}

function getASOpenById(id) {
  let cols = EmpSheet.getLastColumn();
  let colnm = FormSheet.getRange("A2:C").getValues().filter(r => r.every(Boolean)); // Mendapatkan nama kolom dan tipe field

  // Cari baris berdasarkan ID
  let rowToUpdate = EmpSheet.getRange(2, 1, EmpSheet.getLastRow() - 1, 1)
                            .createTextFinder(id)
                            .matchEntireCell(true)
                            .findNext();

  if (rowToUpdate) {
    let rowIndex = rowToUpdate.getRow();  // Ambil indeks baris yang akan di-update
    let data = EmpSheet.getRange(rowIndex, 1, 1, cols).getValues();

    // Loop melalui data dan cek tipe file atau tanggal
    data = data.map(function(row) {
      return row.map(function(cell, idx) {
        let colType = colnm[idx][2];  // Ambil tipe field (misal: 'file')

        // Jika field bertipe file, kembalikan URL file
        if (colType === 'file' && cell) {
          return `<a href="${cell}" target="_blank">Lihat File</a>`;  // Tampilkan link ke file
        }

        // Jika field berupa tanggal, konversi ke format 'yyyy-MM-dd'
        if (cell instanceof Date) {
          return Utilities.formatDate(cell, Session.getScriptTimeZone(), 'yyyy-MM-dd');
        }

        return cell;  // Kembalikan nilai lain apa adanya
      });
    });

    return [colnm, data, rowIndex];  // Kembalikan nama kolom, data, dan indeks baris
  }
  return [colnm, [], 0];  // Jika ID tidak ditemukan, kembalikan data kosong
}

function deleteRecord(id) {
  // Cari baris berdasarkan ID (asumsi ID ada di kolom pertama)
  let rowToDelete = EmpSheet.getRange(2, 1, EmpSheet.getLastRow()-1, 1)
                            .createTextFinder(id)
                            .matchEntireCell(true)
                            .findNext();

  if (rowToDelete) {
    let rowIndex = rowToDelete.getRow();  // Ambil indeks baris
    EmpSheet.deleteRow(rowIndex);  // Hapus baris
    return "success";
  }
  return "failure";
}

function getFilteredSuratKeluar() {
  var sess = getSession();
  var userId = sess.userId; // Ambil ID pengguna dari sesi
  
  if (!userId) {
    return [[], []]; // Jika tidak ada ID pengguna, kembalikan array kosong
  }

  let cols = EmpSheet.getLastColumn();
  let rows = EmpSheet.getLastRow() - 1;
  let tHead = EmpSheet.getRange(1, 1, 1, cols).getValues();
  let allData = EmpSheet.getRange(2, 1, rows, cols).getValues();
  
  // Filter data berdasarkan ID pengguna
  let filteredData = allData.filter(row => row[1] === userId);
  
  // Konversi setiap nilai tanggal menjadi string
  filteredData = filteredData.map(function(row) {
    return row.map(function(cell) {
      // Jika cell adalah URL, biarkan seperti itu
      if (typeof cell === 'string' && (cell.startsWith('http://') || cell.startsWith('https://'))) {
        return cell;  // URL akan ditangani di sisi klien
      }
      // Jika cell adalah tanggal, konversi ke format string (misalnya: dd/MM/yyyy)
      if (cell instanceof Date) {
        return Utilities.formatDate(cell, Session.getScriptTimeZone(), 'dd/MM/yyyy');
      }

      return cell;  // Kembalikan nilai lain apa adanya
    });
  });

  return [tHead, filteredData];
}


function getASOpen(i) {
  let cols = EmpSheet.getLastColumn();
  let colnm = FormSheet.getRange("A2:C").getValues().filter(r => r.every(Boolean)); 
  let data = EmpSheet.getRange(i, 1, 1, cols).getValues();

  // Loop melalui data dan cek jika ada nilai yang merupakan objek Date
  data = data.map(function(row) {
    return row.map(function(cell) {
      // Jika cell adalah objek Date, konversi ke format string 'yyyy-MM-dd'
      if (cell instanceof Date) {
        return Utilities.formatDate(cell, Session.getScriptTimeZone(), 'yyyy-MM-dd');
      }
      return cell;
    });
  });

  return [colnm, data];
}

function OpenPage(PageName) {
  return HtmlService.createHtmlOutputFromFile(PageName).getContent();
}

function UserRegister(pUID, pPassword, pName) {
  let RetMsg = '';
  let ReturnData = LoginSheet.getRange("A:A").createTextFinder(pUID).matchEntireCell(true).findAll();
  let StartRow = 0;
  ReturnData.forEach(function (range) {
    StartRow = range.getRow();
  });

  if (StartRow > 0) {
    RetMsg = 'danger, User Already Exists';
  } else {
    LoginSheet.appendRow([pUID, pPassword, pName]);  
    RetMsg = 'success, User Successfully Registered'; 
  }

  return RetMsg;
}

// SURAT MASUK

function getFilteredSuratMasuk() {
  var sess = getSession();
  var userId = sess.userId; // Ambil ID pengguna dari sesi
  
  if (!userId) {
    return [[], []]; // Jika tidak ada ID pengguna, kembalikan array kosong
  }

  let cols = EmpSheet.getLastColumn();
  let rows = EmpSheet.getLastRow() - 1;
  let tHead = EmpSheet.getRange(1, 1, 1, cols).getValues();
  let allData = EmpSheet.getRange(2, 1, rows, cols).getValues();
  
  // Filter data berdasarkan kolom 3 (misalnya kolom yang berisi ID pengguna)
  let filteredData = allData.filter(row => row[2] === userId);
  
  // Ubah URL menjadi hyperlink dan format data lainnya jika perlu
  filteredData = filteredData.map(function(row) {
    return row.map(function(cell, index) {
      if (index === 5 && typeof cell === "string" && cell.startsWith("http")) { // Misalkan kolom 6 berisi URL
        return `<a href="${cell}" target="_blank">Lihat File</a>`;
      }
      if (cell instanceof Date) {
        return Utilities.formatDate(cell, Session.getScriptTimeZone(), 'dd/MM/yyyy');
      }
      return cell;
    });
  });
  
  return [tHead, filteredData];
}


function submitApproval(id, approvalDate, status, rejectionReason) {
  let sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("suratmasuk");
  let rowToUpdate = sheet.getRange(2, 1, sheet.getLastRow() - 1, 1)
                        .createTextFinder(id)
                        .matchEntireCell(true)
                        .findNext();

  if (rowToUpdate) {
    let rowIndex = rowToUpdate.getRow();
    let currentData = sheet.getRange(rowIndex, 1, 1, sheet.getLastColumn()).getValues()[0];

    let newData = [...currentData];
    newData[4] = approvalDate; // Assuming date goes into the 5th column
    newData[5] = status;       // Assuming status goes into the 6th column
    newData[6] = rejectionReason; // Assuming rejection reason goes into the 7th column

    sheet.getRange(rowIndex, 1, 1, sheet.getLastColumn()).setValues([newData]);
    return "success";
  }
  return "failure";
}
function getFilteredSuratMasuk() {
  var sess = getSession();
  var userId = sess.userId;
  
  if (!userId) {
    return [[], []];
  }

  let cols = EmpSheet.getLastColumn();
  let rows = EmpSheet.getLastRow() - 1;
  let tHead = EmpSheet.getRange(1, 1, 1, cols).getValues();
  let allData = EmpSheet.getRange(2, 1, rows, cols).getValues();
  
  // Filter data berdasarkan kolom yang berisi ID pengguna
  let filteredData = allData.filter(row => row[2] === userId);
  
  // Ubah URL menjadi hyperlink dan format data lainnya jika perlu
  filteredData = filteredData.map(function(row) {
    return row.map(function(cell, index) {
      if (index === 5 && typeof cell === "string" && cell.startsWith("http")) {
        return `<a href="${cell}" target="_blank">Lihat File</a>`;
      }
      if (cell instanceof Date) {
        return Utilities.formatDate(cell, Session.getScriptTimeZone(), 'dd/MM/yyyy');
      }
      return cell;
    });
  });
  
  return [tHead, filteredData];
}

function getSuratKeluarById(id) {
  let rowToUpdate = EmpSheet.getRange(2, 1, EmpSheet.getLastRow() - 1, 1)
                            .createTextFinder(id)
                            .matchEntireCell(true)
                            .findNext();

  if (rowToUpdate) {
    let rowIndex = rowToUpdate.getRow();
    let data = EmpSheet.getRange(rowIndex, 1, 1, EmpSheet.getLastColumn()).getValues();
    return data[0];
  }
  return [];
}

function approveData(id, approvalStatus, date, note) {
  var EmpSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('suratkeluar');
  var MasukSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('suratmasuk');
  
  // Cari baris di sheet suratkeluar berdasarkan ID
  var rowToApprove = EmpSheet.getRange(2, 1, EmpSheet.getLastRow() - 1, 1)
                            .createTextFinder(id)
                            .matchEntireCell(true)
                            .findNext();

  if (rowToApprove) {
    var rowIndex = rowToApprove.getRow();
    var rowData = EmpSheet.getRange(rowIndex, 1, 1, EmpSheet.getLastColumn()).getValues()[0];
    
    // Tambahkan tanggal, status, dan keterangan jika ditolak
    rowData.push(date); // Tambahkan tanggal
    rowData.push(approvalStatus); // Tambahkan status approve/reject
    rowData.push(approvalStatus === 'rejected' ? note : ''); // Tambahkan keterangan jika reject
    
    // Salin data ke sheet suratmasuk
    MasukSheet.appendRow(rowData);
    
    // Hapus baris dari suratkeluar
    EmpSheet.deleteRow(rowIndex);

    return "success";
  }
  
  return "failure";
}

//Tracking
function getFilteredTracking() {
  var sess = getSession();
  var userId = sess.userId;

  if (!userId) {
    return [[], []]; // Jika tidak ada ID pengguna, kembalikan array kosong
  }

  let TrackingSheet = MySheets.getSheetByName("tracking");
  let cols = TrackingSheet.getLastColumn();
  let rows = TrackingSheet.getLastRow() - 1;
  let tHead = TrackingSheet.getRange(1, 1, 1, cols).getValues();
  let allData = TrackingSheet.getRange(2, 1, rows, cols).getValues();
  
  // Filter data berdasarkan salah satu dari dua kondisi
  let filteredData = allData.filter(row => row[1] === userId || row[2] === userId);
  
  // Konversi setiap nilai menjadi string dan jika URL, buat hyperlink
  filteredData = filteredData.map(function(row) {
    return row.map(function(cell, index) {
      if (cell instanceof Date) {
        return Utilities.formatDate(cell, Session.getScriptTimeZone(), 'dd/MM/yyyy');
      } else if (typeof cell === 'string' && cell.startsWith('http')) {
        return `<a href="${cell}" target="_blank">Lihat File</a>`;
      } else if (index === 3) { // Misalnya, kolom ke-4 berisi status
        switch (cell) {
          case 'Approved':
            return `<span style="color: green;"><i class="fa fa-check"></i> Approved</span>`;
          case 'Reject':
            return `<span style="color: red;"><i class="fa fa-times"></i> Reject</span>`;
          default:
            return cell;
        }
      }
      return cell;
    });
  });
  
  return [tHead, filteredData];
}

//Main
function getSuratKeluarCount() {
  var sess = getSession();
  var userId = sess.userId; // Get the user ID from session

  if (!userId) {
    return { totalSuratKeluar: 0 }; // Return zero if no user ID is found
  }

  var SuratKeluarSheet = MySheets.getSheetByName("tracking");
  if (!SuratKeluarSheet) {
    return { totalSuratKeluar: 0 }; // Return zero if the sheet is missing
  }

  let cols = SuratKeluarSheet.getLastColumn();
  let rows = SuratKeluarSheet.getLastRow() - 1;
  
  if (rows <= 0) {
    return { totalSuratKeluar: 0 }; // Return zero if the sheet has no data
  }

  let allData = SuratKeluarSheet.getRange(2, 1, rows, cols).getValues();

  // Filter data based on user ID and column 2
  let filteredData = allData.filter(row => row[1] === userId);
  
  // Return the count of filtered data
  return { totalSuratKeluar: filteredData.length };
}

function getSuratKeluarPendingCount() {
  var sess = getSession();
  var userId = sess.userId; // Get the user ID from session

  if (!userId) {
    return { totalSuratKeluarPending: 0 }; // Return zero if no user ID is found
  }

  var SuratKeluarPendingSheet = MySheets.getSheetByName("suratkeluar");
  if (!SuratKeluarPendingSheet) {
    return { totalSuratKeluarPending: 0 }; // Return zero if the sheet is missing
  }

  let cols = SuratKeluarPendingSheet.getLastColumn();
  let rows = SuratKeluarPendingSheet.getLastRow() - 1;
  
  if (rows <= 0) {
    return { totalSuratKeluarPending: 0 }; // Return zero if the sheet has no data
  }

  let allData = SuratKeluarPendingSheet.getRange(2, 1, rows, cols).getValues();

  // Filter data based on user ID and column 2
  let filteredData = allData.filter(row => row[1] === userId);
  
  // Return the count of filtered data
  return { totalSuratKeluarPending: filteredData.length };
}

function getSuratKeluarSuksesCount() {
  var sess = getSession();
  var userId = sess.userId; // Get the user ID from session

  if (!userId) {
    return { totalSuratKeluarSukses: 0 }; // Return zero if no user ID is found
  }

  var SuratKeluarSuksesSheet = MySheets.getSheetByName("suratmasuk");
  if (!SuratKeluarSuksesSheet) {
    return { totalSuratKeluarSukses: 0 }; // Return zero if the sheet is missing
  }

  let cols = SuratKeluarSuksesSheet.getLastColumn();
  let rows = SuratKeluarSuksesSheet.getLastRow() - 1;
  
  if (rows <= 0) {
    return { totalSuratKeluarSukses: 0 }; // Return zero if the sheet has no data
  }

  let allData = SuratKeluarSuksesSheet.getRange(2, 1, rows, cols).getValues();

  // Filter data based on user ID and column 2
  let filteredData = allData.filter(row => row[1] === userId);
  
  // Return the count of filtered data
  return { totalSuratKeluarSukses: filteredData.length };
}

function getSuratMasukBelumApproveCount() {
  var sess = getSession();
  var userId = sess.userId; // Get the user ID from session

  if (!userId) {
    return { totalSuratMasukBelumApprove: 0 }; // Return zero if no user ID is found
  }

  var SuratMasukBelumApproveSheet = MySheets.getSheetByName("suratkeluar");
  if (!SuratMasukBelumApproveSheet) {
    return { totalSuratMasukBelumApprove: 0 }; // Return zero if the sheet is missing
  }

  let cols = SuratMasukBelumApproveSheet.getLastColumn();
  let rows = SuratMasukBelumApproveSheet.getLastRow() - 1;
  
  if (rows <= 0) {
    return { totalSuratMasukBelumApprove: 0 }; // Return zero if the sheet has no data
  }

  let allData = SuratMasukBelumApproveSheet.getRange(2, 1, rows, cols).getValues();

  // Filter data based on user ID and column 2
  let filteredData = allData.filter(row => row[2] === userId);
  
  // Return the count of filtered data
  return { totalSuratMasukBelumApprove: filteredData.length };
}

function getSuratMasukSudahApproveCount() {
  var sess = getSession();
  var userId = sess.userId; // Get the user ID from session

  if (!userId) {
    return { totalSuratMasukSudahApprove: 0 }; // Return zero if no user ID is found
  }

  var SuratMasukSudahApproveSheet = MySheets.getSheetByName("suratmasuk");
  if (!SuratMasukSudahApproveSheet) {
    return { totalSuratMasukSudahApprove: 0 }; // Return zero if the sheet is missing
  }

  let cols = SuratMasukSudahApproveSheet.getLastColumn();
  let rows = SuratMasukSudahApproveSheet.getLastRow() - 1;
  
  if (rows <= 0) {
    return { totalSuratMasukSudahApprove: 0 }; // Return zero if the sheet has no data
  }

  let allData = SuratMasukSudahApproveSheet.getRange(2, 1, rows, cols).getValues();

  // Filter data based on user ID and column 2
  let filteredData = allData.filter(row => row[2] === userId);
  
  // Return the count of filtered data
  return { totalSuratMasukSudahApprove: filteredData.length };
}

function getSuratMasukCount() {
  var sess = getSession();
  var userId = sess.userId; // Get the user ID from session

  if (!userId) {
    return { totalSuratMasuk: 0 }; // Return zero if no user ID is found
  }

  var SuratMasukSheet = MySheets.getSheetByName("tracking");
  if (!SuratMasukSheet) {
    return { totalSuratMasuk: 0 }; // Return zero if the sheet is missing
  }

  let cols = SuratMasukSheet.getLastColumn();
  let rows = SuratMasukSheet.getLastRow() - 1;
  
  if (rows <= 0) {
    return { totalSuratMasuk: 0 }; // Return zero if the sheet has no data
  }

  let allData = SuratMasukSheet.getRange(2, 1, rows, cols).getValues();

  // Filter data based on user ID and column 2
  let filteredData = allData.filter(row => row[2] === userId);
  
  // Return the count of filtered data
  return { totalSuratMasuk: filteredData.length };
}

function getTrackingCounts() {
  var sess = getSession();
  var userId = sess.userId; // Get the user ID from session

  if (!userId) {
    return { totalSurat: 0 }; // Return zero if no user ID is found
  }

  var trackingSheet = MySheets.getSheetByName("tracking");
  if (!trackingSheet) {
    return { totalSurat: 0 }; // Return zero if the sheet is missing
  }

  let cols = trackingSheet.getLastColumn();
  let rows = trackingSheet.getLastRow() - 1;

  if (rows <= 0) {
    return { totalSurat: 0 }; // Return zero if the sheet has no data
  }

  let allData = trackingSheet.getRange(2, 1, rows, cols).getValues();

  // Filter data based on user ID and column 2
  let filteredData = allData.filter(row => row[1] === userId || row[2] === userId);

  // Return the count of filtered data
  return { totalSurat: filteredData.length };
}


function getDashboardCounts() {
  var trackingCount = getTrackingCounts(); // Call the function to get the tracking count
  var suratKeluarCount = getSuratKeluarCount(); // Call the function to get the suratkeluar count
  var suratKeluarPendingCount = getSuratKeluarPendingCount();
  var suratKeluarSuksesCount = getSuratKeluarSuksesCount();
  var suratMasukBelumApproveCount = getSuratMasukBelumApproveCount();
  var suratMasukSudahApproveCount = getSuratMasukSudahApproveCount();
  var suratMasukCount = getSuratMasukCount();
  
  return {
    totalSurat: trackingCount.totalSurat,
    totalSuratKeluar: suratKeluarCount.totalSuratKeluar,
    totalSuratKeluarPending: suratKeluarPendingCount.totalSuratKeluarPending,
    totalSuratKeluarSukses: suratKeluarSuksesCount.totalSuratKeluarSukses,
    totalSuratMasukBelumApprove: suratMasukBelumApproveCount.totalSuratMasukBelumApprove,
    totalSuratMasukSudahApprove: suratMasukSudahApproveCount.totalSuratMasukSudahApprove,
    totalSuratMasuk: suratMasukCount.totalSuratMasuk
  };
}

Pada point 108 gantilah ID Folder dengan ID Folder yang telah dibuat di atas.