Membuat Aplikasi Payroll dengan Parameter PPH21, Allowance, Overtime dengan Google Apps Script

Membuat aplikasi payroll dengan Google Apps Script menjadi solusi efisien bagi HR dan finance untuk menghitung gaji karyawan secara otomatis. Dengan integrasi Google Sheets, perhitungan parameter penting seperti PPH21, allowance, dan overtime dapat dilakukan secara real-time tanpa software tambahan. Penggunaan Google Apps Script sangat mendukung kebutuhan perusahaan kecil hingga menengah karena bersifat cloud-based, mudah diakses, dan dapat dihubungkan dengan berbagai layanan Google Workspace. Kata kunci seperti Google Apps Script payroll dan cara hitung gaji otomatis sering dicari HR untuk optimasi penggajian.

Selain mempermudah proses, aplikasi payroll dengan Google Apps Script juga memberikan fleksibilitas tinggi. Anda bisa menambahkan fungsi kustom, misalnya logika perhitungan PPH21 sesuai aturan pajak terbaru, tunjangan tetap maupun tidak tetap, hingga kalkulasi lembur berdasarkan jam kerja di luar jadwal. Intent utama pengguna yang mencari topik ini adalah menemukan cara membuat sistem penggajian otomatis di Google Sheets dengan script sederhana namun akurat. Dengan memanfaatkan tools gratis dari Google, perusahaan bisa mengurangi human error, menghemat waktu, sekaligus mendapatkan laporan gaji yang rapi dan terstruktur.

Fitur Aplikasi Payroll:

Update Karyawa
Update Karyawa
Slip Gaji
PPH21
Allowance
Overtime
Benefit Level
Gross Salary
Net Pay

Struktur Kode Google Apps Script

Adapun struktur kode Google Apps Script terdiri dari beberapa file seperti code.gs, Admin.html,Laporan.html, dan Slipgaji.html sehingga memudahkan para UMKM dalam kelola gaji karyawannya. Berikut kode yang bisa digunakan untuk usaha Anda dan pastikan semua file sama.

Code.gs

// Code.gs - Payroll Web App (uses Google Sheets)
// Spreadsheet ID (sesuaikan jika perlu)
var SPREADSHEET_ID = '1-JIbgUwB5ALB2mjy5AMtdqlX-WqQhkU-RDwVBMTPnbQ';
var SHEET_KARYAWAN = 'karyawan';
var SHEET_PAYROOL = 'payrool'; // gunakan persis seperti input user

// Simple static users (ganti atau simpan di sheet jika mau)
var USERS = [
  {username: 'admin', password: 'admin123', role: 'admin'},
  {username: 'hrd', password: 'payroll', role: 'hr'}
];

/* -----------------------
   Main Router
   ----------------------- */
function doGet(e) {
  var userProps = PropertiesService.getUserProperties();
  var isLoggedIn = userProps.getProperty("isLoggedIn");
  var scriptUrl = ScriptApp.getService().getUrl();

  // jika belum login → ke form login
  if (isLoggedIn !== "true") {
    return HtmlService.createHtmlOutputFromFile("Index")
      .setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL)
      .setTitle('Payroll App Login');
  }

  // kalau sudah login
  var page = (e && e.parameter && e.parameter.page) ? e.parameter.page.toLowerCase() : 'admin';
  var template;

  switch (page) {
    case 'admin':
      template = HtmlService.createTemplateFromFile('Admin');
      break;
    case 'laporan':
      template = HtmlService.createTemplateFromFile('Laporan');
      break;
    case 'slipgaji':
      template = HtmlService.createTemplateFromFile('Slipgaji');
      template.empId = (e.parameter.empId || '');
      template.periode = (e.parameter.periode || '');
      break;
    default:
      template = HtmlService.createTemplateFromFile('Admin'); // fallback
  }

  template.scriptUrl = scriptUrl;

  return template.evaluate()
    .setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL)
    .setTitle('Payroll App');
}

/* -----------------------
   Authentication
   ----------------------- */
function doLogin(username, password) {
  var result = checkLogin(username, password);
  if (result.success) {
    var userProps = PropertiesService.getUserProperties();
    userProps.setProperty("isLoggedIn", "true");
    userProps.setProperty("role", result.role);
    userProps.setProperty("username", result.username);
  }
  return result;
}

function doLogout() {
  var userProps = PropertiesService.getUserProperties();
  userProps.deleteProperty("isLoggedIn");
  userProps.deleteProperty("role");
  userProps.deleteProperty("username");
  return {success: true};
}

function checkLogin(username, password) {
  for (var i = 0; i < USERS.length; i++) {
    var u = USERS[i];
    if (u.username === username && u.password === password) {
      return {success: true, role: u.role, username: u.username};
    }
  }
  return {success: false};
}

/* -----------------------
   Helper: open spreadsheet & sheet
   ----------------------- */
function getSheet(name) {
  var ss = SpreadsheetApp.openById(SPREADSHEET_ID);
  var sheet = ss.getSheetByName(name);
  if (!sheet) throw new Error('Sheet "' + name + '" tidak ditemukan.');
  return sheet;
}

/* -----------------------
   Karyawan CRUD (sheet: karyawan)
   Columns expected (in sheet karyawan):
   KaryawanID | Nama | Gaji Pokok | Tunjangan Jabatan | Allowance | Insentif | Lembur Rate | pph type
   ----------------------- */
function getKaryawanAll() {
  var sh = getSheet(SHEET_KARYAWAN);
  var data = sh.getDataRange().getValues();
  if (data.length <= 1) return []; // empty
  console.log("Data ada "+data.length+" record");

  var headers = data[0];
  var rows = data.slice(1);
  
  let results = rows.map(function(r, idx){
    return {
      rowIndex: idx + 2, // baris asli di sheet
      karyawanId: r[0],
      nama: r[1],
      gajiPokok: r[2] || 0,
      tunjanganJabatan: r[3] || 0,
      allowance: r[4] || 0,
      insentif: r[5] || 0,
      lemburRate: r[6] || 0,
      pphType: r[7] || ''
    };
  });

  console.log("Results: " + JSON.stringify(results)); // debug
  return results;
}

function addKaryawan(obj) {
  // obj: {karyawanId, nama, gajiPokok, tunjanganJabatan, allowance, insentif, lemburRate, pphType}
  var sh = getSheet(SHEET_KARYAWAN);
  var values = [
    obj.karyawanId || '',
    obj.nama || '',
    Number(obj.gajiPokok) || 0,
    Number(obj.tunjanganJabatan) || 0,
    Number(obj.allowance) || 0,
    Number(obj.insentif) || 0,
    Number(obj.lemburRate) || 0,
    obj.pphType || ''
  ];
  sh.appendRow(values);
  return {success: true};
}

function editKaryawanById(karyawanId, obj) {
  var sh = getSheet(SHEET_KARYAWAN);
  var data = sh.getDataRange().getValues();
  for (var r = 1; r < data.length; r++) {
    if (String(data[r][0]) === String(karyawanId)) {
      // update row r+1
      var rowIndex = r+1;
      var rowValues = [
        obj.karyawanId || data[r][0],
        obj.nama || data[r][1],
        Number(obj.gajiPokok || data[r][2]) || 0,
        Number(obj.tunjanganJabatan || data[r][3]) || 0,
        Number(obj.allowance || data[r][4]) || 0,
        Number(obj.insentif || data[r][5]) || 0,
        Number(obj.lemburRate || data[r][6]) || 0,
        obj.pphType || data[r][7] || ''
      ];
      sh.getRange(rowIndex, 1, 1, rowValues.length).setValues([rowValues]);
      return {success: true};
    }
  }
  return {success: false, message: 'KaryawanID tidak ditemukan'};
}

function deleteKaryawanById(karyawanId) {
  var sh = getSheet(SHEET_KARYAWAN);
  var data = sh.getDataRange().getValues();
  for (var r = 1; r < data.length; r++) {
    if (String(data[r][0]) === String(karyawanId)) {
      sh.deleteRow(r+1);
      return {success: true};
    }
  }
  return {success: false};
}

/* -----------------------
   Payroll calculation & report (sheet: payrool)
   Columns expected in payrool:
   KaryawanID | Nama | tanggal periode | Gaji Pokok | Tunjangan Jabatan | Allowance | Insentif | Lembur Rate | Lembur Jam | Total Lembur | Gross Monthly | PPh21 Monthly | Net Pay
   ----------------------- */

function calculatePayrollForPeriod(periode) {
  // periode: string (contoh "2025-09" atau "September 2025" - disimpan apa adanya)
  // Will compute payroll from 'karyawan' sheet and append rows to 'payrool' sheet.
  var karyawans = getKaryawanAll();
  var sh = getSheet(SHEET_PAYROOL);
  // Ensure header exists
  var header = ['KaryawanID','Nama','tanggal periode','Gaji Pokok','Tunjangan Jabatan','Allowance','Insentif','Lembur Rate','Lembur Jam','Total Lembur','Gross Monthly','PPh21 Monthly','Net Pay'];
  if (sh.getLastRow() === 0) {
    sh.appendRow(header);
  } else {
    var firstRow = sh.getRange(1,1,1,header.length).getValues()[0];
    // if header not matching, we don't override; assume it's OK
  }

  // PPh21 kalkulator sederhana (annualisasi)
  var PTKP = 54000000;
  var taxBrackets = [50000000, 250000000, 500000000];
  var taxRates = [0.05, 0.15, 0.25, 0.30];

  var results = [];

  karyawans.forEach(function(k){
    var lemburJam = 0; // default, user can update payrool later per employee if needed
    var totalLembur = Number(k.lemburRate || 0) * lemburJam;
    var grossMonthly = Number(k.gajiPokok || 0) + Number(k.tunjanganJabatan || 0) + Number(k.allowance || 0) + Number(k.insentif || 0) + Number(totalLembur || 0);

    var annualGross = grossMonthly * 12;
    var pkp = annualGross - PTKP;
    if (pkp < 0) pkp = 0;
    var annualTax = calculateProgressiveTax(pkp, taxBrackets, taxRates);
    var monthlyPph = annualTax / 12;
    var netPay = grossMonthly - monthlyPph;

    var row = [
      k.karyawanId,
      k.nama,
      periode || Utilities.formatDate(new Date(), Session.getScriptTimeZone(), 'yyyy-MM'),
      Number(k.gajiPokok) || 0,
      Number(k.tunjanganJabatan) || 0,
      Number(k.allowance) || 0,
      Number(k.insentif) || 0,
      Number(k.lemburRate) || 0,
      Number(lemburJam) || 0,
      Number(totalLembur) || 0,
      Number(grossMonthly) || 0,
      Number(monthlyPph) || 0,
      Number(netPay) || 0
    ];
    sh.appendRow(row);
    results.push(row);
  });

  return {success: true, rowsAppended: results.length};
}

function calculateProgressiveTax(pkp, brackets, rates) {
  var remaining = pkp;
  var tax = 0;
  var lower = 0;
  for (var i = 0; i < brackets.length; i++) {
    var upper = brackets[i];
    var taxable = Math.max(0, Math.min(remaining, upper - lower));
    if (taxable > 0) {
      tax += taxable * rates[i];
      remaining -= taxable;
    }
    lower = upper;
    if (remaining <= 0) break;
  }
  if (remaining > 0) {
    tax += remaining * rates[rates.length - 1];
  }
  return tax;
}

function normalizePeriod(p) {
  if (!p) return '';
  if (typeof p !== 'string') p = String(p);

  // Jika sudah format YYYY-MM, langsung return
  if (/^\d{4}-\d{2}$/.test(p)) return p;

  // Jika formatnya "Sep-2025"
  var parts = p.split('-');
  if (parts.length === 2) {
    var monthNames = {
      Jan:"01",Feb:"02",Mar:"03",Apr:"04",May:"05",Jun:"06",
      Jul:"07",Aug:"08",Sep:"09",Oct:"10",Nov:"11",Dec:"12"
    };
    var mm = monthNames[parts[0]];
    if (mm) return parts[1] + '-' + mm;  // contoh: "2025-09"
  }

  return p; // fallback
}

function getPayrollReport(periodFilter) {
  var sh = getSheet(SHEET_PAYROOL);
  var data = sh.getDataRange().getValues();
  if (data.length <= 1) return [];
  var rows = data.slice(1);

  var mapped = rows.map(function(r){
    return {
      karyawanId: r[0],
      nama: r[1],
      periode: normalizePeriod(r[2]),
      gajiPokok: r[3],
      tunjanganJabatan: r[4],
      allowance: r[5],
      insentif: r[6],
      lemburRate: r[7],
      lemburJam: r[8],
      totalLembur: r[9],
      grossMonthly: r[10],
      pph21Monthly: r[11],
      netPay: r[12]
    };
  });

  if (periodFilter && periodFilter !== '') {
    periodFilter = normalizePeriod(periodFilter);
    return mapped.filter(function(m){ return m.periode === periodFilter; });
  }
  return mapped;
}

function getPayrollRowByEmployeeAndPeriod(karyawanId, periode) {
  var report = getPayrollReport(periode);
  for (var i = 0; i < report.length; i++) {
    if (String(report[i].karyawanId) === String(karyawanId)) return report[i];
  }
  return null;
}

/* -----------------------
   Utilities to include HTML files
   ----------------------- */
function include(filename) {
  return HtmlService.createHtmlOutputFromFile(filename).getContent();
}

/* -----------------------
   Optional: small test helpers for front-end
   ----------------------- */
function serverTime() {
  return new Date();
}

$ads={1}

Html

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
    <meta name="viewport" content="width=device-width,initial-scale=1">
    <style>
      body { font-family: Arial, sans-serif; background:#f6f7fb; display:flex; align-items:center; justify-content:center; height:100vh; margin:0; }
      .box { background:#fff; padding:28px; border-radius:8px; box-shadow:0 4px 14px rgba(0,0,0,0.08); width:320px; }
      input { width:100%; padding:10px; margin:8px 0; border-radius:6px; border:1px solid #ddd; box-sizing:border-box; }
      button { width:100%; padding:10px; margin-top:10px; border:0; border-radius:6px; background:#1976d2; color:white; font-weight:600; cursor:pointer; }
      .error { color:#b00020; text-align:center; margin-top:8px; }
      .link { text-align:center; margin-top:10px; }
    </style>
    <script>
      function doLogin(){
        var user = document.getElementById('user').value.trim();
        var pass = document.getElementById('pass').value;
        document.getElementById('err').innerText = '';

        google.script.run.withSuccessHandler(function(res){
          if (res && res.success) {
            // redirect ke halaman admin
            var base = window.location.origin + window.location.pathname;
            window.location.href = base + '?page=admin';
          } else {
            document.getElementById('err').innerText = 'Login gagal — periksa username/password.';
          }
        }).doLogin(user, pass);  // ✅ pakai doLogin, bukan checkLogin
      }

      document.addEventListener('keydown', function(e){
        if (e.key === 'Enter') doLogin();
      });
    </script>
  </head>
  <body>
    <div class="box">
      <h3 style="margin:0 0 10px 0; text-align:center;">Payroll Login</h3>
      <input id="user" placeholder="Username" autocomplete="username">
      <input id="pass" placeholder="Password" type="password" autocomplete="current-password">
      <button onclick="doLogin()">Login</button>
      <div id="err" class="error"></div>
      <div class="link">
        <a href="?page=laporan" target="_self">Lihat Laporan (tanpa login)</a><br>
        <p style="text-align:center;">
          Supported by <a href="https://www.mampirklik.com">www.mampirklik.com</a> || 
          <a href="https://cmsgue.id">cmsgue.id</a>
        </p>
      </div>
    </div>
  </body>
</html>

#Admin.html

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
    <meta name="viewport" content="width=device-width,initial-scale=1">
    <style>
      body { font-family: Arial, sans-serif; padding:18px; background:#fafafa; }
      h2 { margin-top:0; }
      table { width:100%; border-collapse:collapse; margin-top:10px; background:#fff; }
      th, td { border:1px solid #ddd; padding:8px; text-align:left; }
      th { background:#f2f2f2; }
      input, select { padding:6px; margin:4px; }
      button { padding:8px 12px; margin:4px; cursor:pointer; border:0; border-radius:4px; }
      button:hover { opacity:0.9; }
      #btnAdd { background:#1976d2; color:#fff; }
      #btnUpdate { background:#ffa000; color:#fff; }
      #btnCancel { background:#9e9e9e; color:#fff; }
      .logout { float:right; background:#d32f2f; color:#fff; }
    </style>
    <script>
      const SCRIPT_URL = "<?= scriptUrl ?>";

      function loadKaryawan() {
        google.script.run.withSuccessHandler(renderTable).getKaryawanAll();
      }

      function renderTable(data) {
        var html = '<table><tr><th>ID</th><th>Nama</th><th>Gaji Pokok</th><th>Tunjangan</th><th>Allowance</th><th>Insentif</th><th>Lembur Rate</th><th>PPh Type</th><th>Aksi</th></tr>';
        data.forEach(function(r){
          html += '<tr>' +
            '<td>' + escapeHtml(r.karyawanId) + '</td>' +
            '<td>' + escapeHtml(r.nama) + '</td>' +
            '<td>' + formatNumber(r.gajiPokok) + '</td>' +
            '<td>' + formatNumber(r.tunjanganJabatan) + '</td>' +
            '<td>' + formatNumber(r.allowance) + '</td>' +
            '<td>' + formatNumber(r.insentif) + '</td>' +
            '<td>' + formatNumber(r.lemburRate) + '</td>' +
            '<td>' + escapeHtml(r.pphType) + '</td>' +
            '<td>' +
              '<button onclick="openEdit(\'' + r.karyawanId + '\')">Edit</button>' +
              '<button onclick="hapus(\'' + r.karyawanId + '\')">Hapus</button>' +
            '</td>' +
          '</tr>';
        });
        html += '</table>';
        document.getElementById('list').innerHTML = html;
      }

      function escapeHtml(s){ if(s===null||s===undefined) return ''; return String(s).replace(/&/g,'&amp;').replace(/</g,'&lt;').replace(/>/g,'&gt;'); }
      function formatNumber(n){ if(!n && n !== 0) return ''; return Number(n).toLocaleString('id-ID'); }

      function tambah() {
        var emp = {
          karyawanId: document.getElementById('id').value.trim(),
          nama: document.getElementById('nama').value.trim(),
          gajiPokok: Number(document.getElementById('gaji').value) || 0,
          tunjanganJabatan: Number(document.getElementById('tunjangan').value) || 0,
          allowance: Number(document.getElementById('allowance').value) || 0,
          insentif: Number(document.getElementById('insentif').value) || 0,
          lemburRate: Number(document.getElementById('lembur').value) || 0,
          pphType: document.getElementById('pphType').value
        };
        if (!emp.karyawanId || !emp.nama) { alert('Isi ID dan Nama'); return; }
        google.script.run.withSuccessHandler(function(){ clearForm(); loadKaryawan(); }).addKaryawan(emp);
      }

      function openEdit(karyawanId) {
        google.script.run.withSuccessHandler(function(rows){
          var found = rows.find(function(r){ return String(r.karyawanId) === String(karyawanId); });
          if (!found) { alert('Data tidak ditemukan'); return; }
          document.getElementById('id').value = found.karyawanId;
          document.getElementById('nama').value = found.nama;
          document.getElementById('gaji').value = found.gajiPokok;
          document.getElementById('tunjangan').value = found.tunjanganJabatan;
          document.getElementById('allowance').value = found.allowance;
          document.getElementById('insentif').value = found.insentif;
          document.getElementById('lembur').value = found.lemburRate;
          document.getElementById('pphType').value = found.pphType || '';
          document.getElementById('btnAdd').style.display = 'none';
          document.getElementById('btnUpdate').style.display = 'inline-block';
          document.getElementById('btnCancel').style.display = 'inline-block';
        }).getKaryawanAll();
      }

      function update() {
        var karyawanId = document.getElementById('id').value.trim();
        if (!karyawanId) { alert('ID kosong'); return; }
        var obj = {
          karyawanId: document.getElementById('id').value.trim(),
          nama: document.getElementById('nama').value.trim(),
          gajiPokok: Number(document.getElementById('gaji').value) || 0,
          tunjanganJabatan: Number(document.getElementById('tunjangan').value) || 0,
          allowance: Number(document.getElementById('allowance').value) || 0,
          insentif: Number(document.getElementById('insentif').value) || 0,
          lemburRate: Number(document.getElementById('lembur').value) || 0,
          pphType: document.getElementById('pphType').value || ''
        };
        google.script.run.withSuccessHandler(function(res){
          if (res && res.success) {
            clearForm(); loadKaryawan();
            document.getElementById('btnAdd').style.display = 'inline-block';
            document.getElementById('btnUpdate').style.display = 'none';
            document.getElementById('btnCancel').style.display = 'none';
          } else {
            alert('Gagal update: ' + (res.message||'')); 
          }
        }).editKaryawanById(karyawanId, obj);
      }

      function hapus(karyawanId) {
        if (!confirm('Hapus karyawan ' + karyawanId + '?')) return;
        google.script.run.withSuccessHandler(function(res){
          if (res && res.success) loadKaryawan();
          else alert('Gagal menghapus');
        }).deleteKaryawanById(karyawanId);
      }

      function clearForm() {
        document.getElementById('id').value = '';
        document.getElementById('nama').value = '';
        document.getElementById('gaji').value = '';
        document.getElementById('tunjangan').value = '';
        document.getElementById('allowance').value = '';
        document.getElementById('insentif').value = '';
        document.getElementById('lembur').value = '';
        document.getElementById('pphType').value = '';
        document.getElementById('btnAdd').style.display = 'inline-block';
        document.getElementById('btnUpdate').style.display = 'none';
        document.getElementById('btnCancel').style.display = 'none';
      }

      function hitungPayroll() {
        var periode = document.getElementById('periode').value.trim();
        if (!periode) { alert('Isi periode (misal 2025-09)'); return; }
        if (!confirm('Generate payroll untuk periode ' + periode + ' ?')) return;
        google.script.run.withSuccessHandler(function(res){
          if (res && res.success) {
            alert('Payroll dihitung untuk ' + res.rowsAppended + ' karyawan.');
          } else {
            alert('Terjadi error saat kalkulasi');
          }
        }).calculatePayrollForPeriod(periode);
      }

      function gotoPage(page) {
        window.location.href = SCRIPT_URL + "?page=" + encodeURIComponent(page.toLowerCase());
      }
      function logout(){
        google.script.run.withSuccessHandler(function(){
          var base = window.location.origin + window.location.pathname;
          window.location.href = base; // kembali ke login
        }).doLogout();
      }

      window.onload = function(){
        loadKaryawan();
        document.getElementById('btnUpdate').style.display = 'none';
        document.getElementById('btnCancel').style.display = 'none';
      };
    </script>
  </head>
  <body>
    <h2>Admin — Manajemen Karyawan 
      <button class="logout" onclick="logout()">Logout</button>
    </h2>

    <div>
      <label>ID:</label>
      <input id="id" placeholder="KAR001">
      <label>Nama:</label>
      <input id="nama" placeholder="Nama Karyawan"><br>
      <label>Gaji Pokok:</label>
      <input id="gaji" type="number" placeholder="Gaji Pokok">
      <label>Tunjangan Jabatan:</label>
      <input id="tunjangan" type="number" placeholder="Tunjangan"><br>
      <label>Allowance:</label>
      <input id="allowance" type="number" placeholder="Allowance">
      <label>Insentif:</label>
      <input id="insentif" type="number" placeholder="Insentif"><br>
      <label>Lembur Rate (per jam):</label>
      <input id="lembur" type="number" placeholder="50000">
      <label>PPh Type:</label>
      <input id="pphType" placeholder="default"><br>
      <button id="btnAdd" onclick="tambah()">Tambah</button>
      <button id="btnUpdate" onclick="update()">Update</button>
      <button id="btnCancel" onclick="clearForm()">Batal</button>
    </div>

    <div style="margin-top:18px;">
      <label>Periode untuk generate payroll (format bebas, contoh: 2025-09):</label>
      <input id="periode" placeholder="2025-09">
      <button onclick="hitungPayroll()">Generate Payroll</button>
      <button onclick="gotoPage('laporan')">Lihat Laporan</button>
    </div>

    <div id="list" style="margin-top:18px;"></div>
  </body>
</html>

Semoga bermanfaat dan berguna untuk penggunaan Aplikasi Payroll yang bisa di download secara gratis dan mudah.

Membuat Aplikasi Payroll dengan Parameter PPH21, Allowance, Overtime dengan Google Apps Script