Otomatisasi Pelacakan Pengeluaran Freelancer di Sheets

Pisahkan alur data Anda ke dalam 3 tab: Import, Expenses, dan Summary. Ini terdengar sederhana, tetapi sebagian besar pelacak pengeluaran yang gagal disebabkan oleh seseorang yang menempelkan ekspor CSV langsung di atas formula aktif. Itu menghapus formula, data menjadi mentah, dan tidak ada yang teragregasi dengan benar.

Ekspor bank, pembayaran Stripe, dan unduhan alat akuntansi tidak selalu hadir dalam urutan kolom yang sama setiap bulan. Mercury menempatkan tanggal di kolom A; perangkat lunak akuntansi Anda mungkin menaruhnya di kolom D. Urutan itu juga bisa bergeser saat Anda mengubah pengaturan ekspor atau menambahkan kolom data baru.

Solusinya adalah pencarian baris header yang menemukan kolom yang tepat berdasarkan nama, bukan posisi. Di tab Expenses, definisikan 3 sel pembantu yang menyimpan indeks kolom untuk setiap kolom yang Anda butuhkan:

=MATCH("Date", Import!$1:$1, 0)
=MATCH("Description", Import!$1:$1, 0)
=MATCH("Amount", Import!$1:$1, 0)

Beri nama sel-sel ini col_date, col_desc, col_amount menggunakan Name Box. Kemudian di baris Expenses, tarik data secara dinamis:

=INDEX(Import!$A:$Z, ROW(A2), col_date)
=INDEX(Import!$A:$Z, ROW(A2), col_desc)
=INDEX(Import!$A:$Z, ROW(A2), col_amount)

Sekarang ketika ekspor Anda hadir dengan Description di kolom C alih-alih kolom B bulan depan, tidak ada yang rusak. Pencarian baris header menemukannya dan setiap formula hilir menyesuaikan secara otomatis.

String vendor mentah dari ekspor bank sering bermasalah: spasi di akhir string, huruf campuran, spasi berlebih dari kolom OCR, dan artefak enkode. Dua transaksi dari vendor yang sama bisa terlihat sangat berbeda bagi formula SEARCH jika salah satunya memiliki spasi terselip.

Di tab Expenses, tambahkan kolom Vendor_Clean yang menerapkan tiga transformasi secara berurutan:

=TRIM(UPPER(SUBSTITUTE(C2, "  ", " ")))

TRIM menghapus spasi di awal dan akhir. UPPER menghilangkan perbedaan huruf besar/kecil. SUBSTITUTE menyatukan spasi ganda yang terkadang muncul di kolom memo yang diekspor. Kolom tunggal ini adalah yang akan direferensikan oleh setiap formula hilir - deskripsi impor mentah tetap tidak tersentuh di kolomnya sendiri sebagai jejak audit.

Untuk normalisasi tanggal, tambahkan kolom Date_Clean. Format tanggal campuran - MM/DD/YYYY vs YYYY-MM-DD vs "16 Apr 2026" - umum terjadi saat menarik data dari berbagai sumber. Paksa ke nilai standar:

=IF(ISNUMBER(D2), D2, DATEVALUE(D2))

Tab Rules memuat tabel 2 kolom: Keyword di kolom A, Category di kolom B. Inilah satu-satunya tempat yang Anda ubah ketika vendor baru muncul atau Anda ingin mengklasifikasi ulang suatu kategori.

Ada beberapa aturan desain yang perlu diperhatikan. Keyword sebaiknya cocok dengan bagian paling khas dari string vendor - bukan "GOOGLE" (terlalu luas, menangkap Google Drive, Google Ads, dan Google Cloud sebagai satu hal) melainkan "GOOGLE CLOUD" atau "GOOGLE ADS" secara spesifik. Tidak ada batasan jumlah aturan per kategori; akurasi adalah prioritasnya. Payment processor yang menghasilkan 7 varian string berbeda untuk vendor yang sama membutuhkan 7 baris keyword.

Urutkan Keyword berdasarkan spesifisitas dalam setiap kategori - pola yang lebih spesifik lebih dahulu. Pengkategorisasi mengembalikan kecocokan pertama, jadi "FACEBOOK ADS" harus muncul sebelum "FACEBOOK" jika keduanya ada di tabel Anda.

Formula kategori memindai tabel Rules Anda untuk menemukan keyword apa pun yang muncul di dalam string vendor yang sudah dibersihkan. Di tab Expenses, tambahkan kolom Category:

=IFERROR(INDEX(Rules!$B$2:$B$100, MATCH(TRUE, ISNUMBER(SEARCH(Rules!$A$2:$A$100, E2)), 0)), "Uncategorized")

Masukkan ini dengan Ctrl+Shift+Enter untuk menjadikannya formula array (di Sheets versi terbaru, Anda juga bisa membungkus ekspresi dalam ARRAYFORMULA). Formula ini mengembalikan kategori pertama yang keyword-nya muncul di mana saja dalam string vendor yang dibersihkan - pencocokan parsial berfungsi, sehingga "GOOGLE CLOUD STORAGE" dengan tepat mengenai aturan "GOOGLE CLOUD" meskipun tidak identik.

Fallback "Uncategorized" lebih penting dari yang terlihat. Jika Anda mengembalikan "" untuk baris yang tidak cocok, transaksi tersebut diam-diam menghilang dari total SUMIFS - SUMIFS melewati sel kosong. Menggunakan string eksplisit berarti pengeluaran yang tidak cocok muncul sebagai kategori tersendiri di ringkasan Anda, di mana Anda bisa melihat dengan tepat apa yang belum dipetakan. Visibilitas itulah yang memberi tahu Anda kapan tabel Rules perlu diperbarui.

Tab Summary mengagregasi total berdasarkan kategori dan bulan. Siapkan grid dengan kategori di kolom A dan bulan di baris 1 (diformat sebagai hari pertama setiap bulan, misalnya 2026-01-01). Kemudian untuk setiap sel dalam grid:

=SUMIFS(Expenses!$F:$F, Expenses!$G:$G, $A2, Expenses!$H:$H, ">="&B$1, Expenses!$H:$H, "<"&EDATE(B$1, 1))

Di mana kolom F adalah Amount, kolom G adalah Category, dan kolom H adalah Date_Clean. EDATE(B$1, 1) membatasi rentang tanggal ke bulan kalender tanpa perlu hardcode tanggal akhir.

Tambahkan baris Total di bagian bawah (SUM setiap kolom bulan) dan baris Budget tempat Anda hardcode rencana pengeluaran per kategori. Varians adalah Aktual dikurangi Budget:

=B3 - B2

Terapkan conditional formatting pada sel varians: merah untuk melebihi anggaran, hijau untuk di bawah. Ini adalah pemeriksaan yang berguna, tetapi bukan output akhir - ini adalah lapisan input.

Ini adalah langkah yang paling sering dilewatkan oleh panduan pelacak pengeluaran lainnya. Grid Summary dari Langkah 6 berguna untuk pembukuan internal, tetapi bukan model keuangan - ini hanya tabel datar. Agar berguna sebagai input untuk burn model atau P&L yang bisa ditunjukkan kepada investor, Anda membutuhkan tab P&L terpisah yang memperlakukan Summary sebagai lapisan Aktual.

Siapkan tab P&L dengan struktur lini item lengkap di kolom A, selaras dengan cara biaya muncul dalam laporan laba rugi nyata:

Setiap sel Aktual di P&L menarik dari Summary menggunakan referensi langsung atau SUMIFS yang memetakan lini item P&L ke kategori Summary:

=SUMIFS(Summary!$B:$B, Summary!$A:$A, "Software")

Untuk kategori yang digabung ke satu lini P&L (misalnya, "Hosting & Infrastruktur" mungkin menggabungkan baris Summary untuk AWS, Google Cloud, dan Cloudflare), gunakan beberapa SUMIFS:

=SUMIFS(Summary!B:B,Summary!A:A,"Software") + SUMIFS(Summary!B:B,Summary!A:A,"Hosting")

Inilah arsitekturnya: pengkategorisasi mengisi Summary, Summary adalah lapisan input Aktual, dan tab P&L adalah modelnya. Pendapatan masuk ke P&L secara langsung atau dari sumber datanya sendiri. Dari sini, aktual bulanan Anda mengalir secara alami ke perhitungan burn rate dan runway - untuk struktur tersebut, lihat cara menghitung runway startup dan dashboard burn rate startup.

Formula array di Langkah 5 menjalankan perbandingan N × M setiap kali kalkulasi ulang - setiap baris di Expenses dipindai terhadap setiap baris di tabel Rules. Pada 500 baris pengeluaran terhadap tabel rules 50 baris, itu 25.000 pencarian per kalkulasi ulang. Pada 10.000 baris, menjadi 500.000, dan Anda akan memperhatikan sheet membeku selama beberapa detik setiap kali ada sel yang berubah.

Untuk dataset lebih dari 2.000 baris, pindahkan kategorisasi ke Apps Script. Logikanya identik, tetapi berjalan sekali dan menulis hasilnya dalam satu batch:

function categorizeExpenses() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const expenses = ss.getSheetByName('Expenses');
  const rules = ss.getSheetByName('Rules');

  const expData = expenses.getDataRange().getValues();
  const ruleData = rules.getRange(2, 1, rules.getLastRow() - 1, 2).getValues();

  const categories = [];

  for (let i = 1; i < expData.length; i++) {
    const vendor = String(expData[i][4]).toUpperCase(); // Vendor_Clean column
    let matched = 'Uncategorized';

    for (const [keyword, category] of ruleData) {
      if (vendor.includes(String(keyword).toUpperCase())) {
        matched = category;
        break;
      }
    }
    categories.push([matched]);
  }

  // Single batch write - never write inside the loop
  expenses.getRange(2, 6, categories.length, 1).setValues(categories);
}

Aturan kritis: jangan pernah memanggil setValue() di dalam loop. Setiap penulisan individual memicu round-trip ke Sheets API. Pada 10.000 baris, menulis di dalam loop bisa memakan waktu lebih dari 15 menit. Batch setValues() di akhir menjalankan operasi yang sama dalam waktu kurang dari 10 detik.

Hubungkan ini ke trigger melalui Extensions → Apps Script → Triggers agar berjalan otomatis saat diedit, atau atur agar aktif ketika tab Import berubah. Per April 2026, batas waktu eksekusi Apps Script adalah 6 menit untuk akun konsumen dan 30 menit untuk akun Workspace - pola batch write membuat Anda tetap nyaman dalam kedua batas tersebut bahkan pada batas maksimum Google Sheets sebesar 10 juta sel.