Cara Ampuh Mengoptimasi Backend API Menggunakan Google Apps Script dan Google Sheets

Menggunakan Google Apps Script (GAS) dan Google Sheets sebagai backend API adalah salah satu trik favorit di kalangan developer untuk melakukan prototyping cepat atau membangun aplikasi skala kecil. Tanpa perlu repot memikirkan hosting atau konfigurasi database yang rumit, Anda sudah bisa memiliki API yang fungsional.

Namun, ada satu masalah besar yang sering muncul: performa.

Seiring bertambahnya baris data dan lonjakan traffic request dari pengguna, API yang tadinya cepat bisa melambat secara drastis, mengalami timeout, atau bahkan terkena limit quota dari Google. Ingat, pada dasarnya Google Sheets bukanlah sebuah database relasional.

Jika Anda mulai merasakan masalah performa tersebut, jangan buru-buru membuang kode Anda. Berikut adalah 6 praktik terbaik (best practices) untuk mengoptimasi backend API Google Apps Script Anda agar berjalan jauh lebih cepat dan efisien.

1. Gunakan Batch Operations (Hindari Looping Read/Write)

Kesalahan paling umum yang menjadi penyebab utama lambatnya GAS adalah membaca atau menulis data sel-per-sel di dalam sebuah loop. Setiap kali Anda memanggil layanan Sheets (seperti .getValue() atau .setValue()), sistem membutuhkan waktu komunikasi ke server sekitar 100-300 milidetik. Bayangkan jika Anda me-looping 1.000 baris!

Cara yang Salah (Sangat Lambat):

JavaScript

// Jangan lakukan ini! Memanggil API berulang kali di dalam loop.
for (let i = 1; i <= 1000; i++) {
  let nama = sheet.getRange(i, 1).getValue(); 
}

Cara yang Benar (Batching):

Solusinya adalah mengambil semua data sekaligus ke dalam memori server (sebagai array 2D JavaScript). Pemrosesan data di dalam memori JavaScript berjalan kilat.

JavaScript

// 1. Ambil semua data sekaligus (Hanya 1 panggilan API)
let data = sheet.getDataRange().getValues(); 

// 2. Proses data di memori menggunakan JavaScript murni
let hasil = data.map(row => {
  return { nama: row[0], email: row[1] };
});

2. Implementasikan Caching dengan CacheService

Jika API Anda sering menerima request (doGet) untuk menyajikan data yang sama (misalnya: daftar katalog produk, daftar provinsi, dll), jangan membaca data dari Sheets setiap kali ada request masuk. Gunakan CacheService bawaan GAS untuk menyimpan respons secara sementara.

Dengan caching, jika ada request kedua dan seterusnya dalam rentang waktu tertentu, GAS akan langsung mengembalikan data dari cache dalam hitungan milidetik.

JavaScript

function doGet(e) {
  let cache = CacheService.getScriptCache();
  let cachedData = cache.get(\"api_data_produk\");
  
  if (cachedData != null) {
    // Kembalikan dari cache (sangat cepat!)
    return ContentService.createTextOutput(cachedData).setMimeType(ContentService.MimeType.JSON);
  }
  
  // Jika cache kosong, baru baca dari Sheets
  let sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(\"Produk\");
  let data = sheet.getDataRange().getValues();
  let jsonString = JSON.stringify(data);
  
  // Simpan data di cache selama 15 menit (900 detik)
  cache.put(\"api_data_produk\", jsonString, 900);
  
  return ContentService.createTextOutput(jsonString).setMimeType(ContentService.MimeType.JSON);
}

3. Gunakan TextFinder untuk Fitur Pencarian

Ketika Anda membuat endpoint untuk mencari data spesifik (misal: mencari profil user berdasarkan email), me-looping ribuan baris array bisa menghabiskan memori dan waktu. Sebagai gantinya, gunakan TextFinder. Fitur ini dieksekusi langsung di sisi server Google dan jauh lebih cepat daripada me-looping array secara manual.

JavaScript

function cariUserByEmail(email) {
  let sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(\"Users\");
  
  // Mencari eksak string di dalam sheet dengan cepat
  let tf = sheet.createTextFinder(email).matchEntireCell(true).findNext();
  
  if (tf) {
    let row = tf.getRow();
    let userData = sheet.getRange(row, 1, 1, 5).getValues()[0]; // Ambil data di baris tersebut
    return userData;
  }
  return null; // Tidak ditemukan
}

4. Amankan Endpoint doPost dengan LockService

Jika aplikasi Anda sering menerima banyak data masuk (insert data) di saat yang bersamaan—misalnya saat sistem absensi dipakai banyak karyawan di jam yang sama—ada risiko data bertumpuk (race condition) atau gagal ditulis ke spreadsheet.

LockService berfungsi layaknya pintu antrean yang memastikan hanya ada satu proses penulisan yang berjalan pada satu waktu.

JavaScript

function doPost(e) {
  let lock = LockService.getScriptLock();
  
  try {
    // Tunggu maksimal 10 detik jika ada proses lain yang sedang menulis
    lock.waitLock(10000); 
    
    let sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(\"Logs\");
    let payload = JSON.parse(e.postData.contents);
    
    sheet.appendRow([new Date(), payload.nama, payload.aktivitas]);
    
    return ContentService.createTextOutput(JSON.stringify({status: \"success\"}))
                         .setMimeType(ContentService.MimeType.JSON);
                         
  } catch (err) {
    return ContentService.createTextOutput(JSON.stringify({status: \"error\", message: err.message}))
                         .setMimeType(ContentService.MimeType.JSON);
  } finally {
    // Wajib: selalu lepaskan kunci setelah proses selesai!
    lock.releaseLock();
  }
}

5. Pindahkan Logika Perhitungan dari Sheets ke Backend

GAS akan berjalan lambat jika file Google Sheets Anda sedang sibuk mengkalkulasi ratusan formula.

• Hindari fungsi volatile seperti =NOW(), =TODAY(), =RAND(), atau =INDIRECT() jika memungkinkan, karena fungsi ini memaksa seluruh dokumen untuk dihitung ulang pada setiap perubahan terkecil.
• Pindahkan beban komputasi: Daripada menggunakan formula rumit seperti VLOOKUP atau QUERY berlapis di dalam sel Sheets, lebih baik tarik data mentahnya menggunakan GAS, lakukan kalkulasi atau filtering menggunakan JavaScript, dan kirimkan hasilnya via API.

6. Minimalkan Ukuran Payload JSON

Semakin besar ukuran string JSON yang Anda kembalikan dari endpoint, semakin lama waktu transfer jaringannya. Jangan mengirim seluruh kolom jika aplikasi klien di frontend hanya membutuhkan 2 kolom (misal: ID dan Nama). Selalu petakan (map) array Anda untuk membuang properti yang tidak diperlukan sebelum melakukan JSON.stringify().

Kesimpulan

Google Sheets dan Apps Script adalah kombinasi mematikan untuk membangun purwarupa aplikasi dengan biaya nol. Meskipun memiliki keterbatasan bawaan, dengan menerapkan batching, memanfaatkan cache, mengamankan jalur write dengan LockService, dan memindahkan beban komputasi ke dalam kode, Anda dapat memperpanjang \”usia pakai\” backend ini secara signifikan sebelum akhirnya Anda benar-benar harus bermigrasi ke layanan database profesional seperti Firebase, PostgreSQL, atau MySQL.