Baca dan Tulis Data Google Sheets dengan Apps Script: Panduan CRUD Fundamental

Skrip kamu jalan 40 detik untuk 500 baris, lalu kena timeout di 800 baris. Buka kodenya, hampir pasti ada getValue() atau setValue() di dalam for loop. Tiap panggilan itu satu percakapan bolak-balik ke server Google. Panggil 500 kali, kamu bayar ongkos jaringan 500 kali.

Cara benarnya: baca semua sekaligus jadi satu array, olah di memori, tulis balik sekaligus. Dua panggilan server, bukan seribu. Itu inti dari seluruh artikel ini. Sisanya cuma detail cara ambil sheet, bentuk arraynya, dan pola cari, update, serta hapus baris yang aman. Semua kode di sini jalan di Apps Script runtime V8.

Ambil objek sheet dulu

Sebelum baca apa pun, kamu butuh objek Sheet. Ada dua jalan masuk. Kalau skrip terikat ke spreadsheet (dibuat dari menu Extensions di dalam file Sheets), pakai getActiveSpreadsheet(). Kalau skrip berdiri sendiri atau perlu buka file lain, pakai openById() dengan ID dari URL.

function ambilSheet() {
  // Skrip terikat: spreadsheet yang sedang dibuka
  const ss = SpreadsheetApp.getActiveSpreadsheet();

  // Atau buka file spesifik lewat ID dari URL:
  // https://docs.google.com/spreadsheets/d/ID_DI_SINI/edit
  // const ss = SpreadsheetApp.openById('1AbC...xyz');

  // Ambil tab berdasarkan nama, bukan urutan
  const sheet = ss.getSheetByName('Data');
  if (!sheet) {
    throw new Error('Tab "Data" tidak ditemukan');
  }

  Logger.log('Baris terisi: ' + sheet.getLastRow());
  Logger.log('Kolom terisi: ' + sheet.getLastColumn());
  return sheet;
}

Selalu pakai getSheetByName(), jangan getSheets()[0]. Orang suka geser urutan tab, dan skrip yang mengandalkan posisi bakal diam-diam baca tab yang salah. Kalau baru mulai dari nol, cek dulu google apps script untuk pemula buat dasar editor dan izin.

Baca data: getRange, getValues, getDataRange

Baca data itu dua langkah: tentukan range, lalu tarik nilainya. getRange(row, col, numRows, numCols) menandai kotak sel yang kamu mau, keempat argumennya 1-based (baris 1 = header, kolom 1 = A). getValues() mengembalikan isinya sebagai array 2 dimensi.

function bacaData() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Data');

  // getDataRange = seluruh blok terisi, dari A1 sampai sel terakhir
  const range = sheet.getDataRange();
  const values = range.getValues();

  // values adalah array 2D: [ [baris], [baris], ... ]
  // values[0] = baris pertama (biasanya header)
  // values[0][0] = sel A1, values[0][1] = sel B1
  Logger.log('Total baris terbaca: ' + values.length);
  Logger.log('Header: ' + values[0].join(', '));

  // Lewati header (index 0), loop sisanya DI MEMORI
  for (let i = 1; i < values.length; i++) {
    const nama = values[i][0];   // kolom A
    const email = values[i][1];  // kolom B
    Logger.log(nama + ' -> ' + email);
  }
}

Perhatikan pergeseran indeks yang selalu bikin pusing. Di getRange() semua 1-based. Begitu datanya jadi array JavaScript, indeksnya 0-based. Jadi baris ke-3 di spreadsheet adalah values[2], dan kolom C adalah values[2][2]. Kalau cuma butuh sebagian, pakai getRange() terbatas biar tidak menarik ribuan baris kosong.

function bacaSebagian() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Data');

  // Mulai baris 2, kolom 1, ambil 10 baris, 3 kolom (A:C)
  const data = sheet.getRange(2, 1, 10, 3).getValues();

  // Satu sel saja? getValue() (tunggal) boleh, ini murah kalau sekali
  const judul = sheet.getRange('A1').getValue();
  Logger.log('Judul: ' + judul);
  Logger.log('Baris pertama data: ' + data[0]);
}

Tulis data: setValues untuk batch

Aturan tulis batch cuma satu, tapi ketat: array yang kamu kasih ke setValues() harus punya dimensi PERSIS sama dengan range tujuan. Range 10 baris kali 3 kolom butuh array berisi 10 elemen, tiap elemen array berisi 3 nilai. Meleset satu, kamu dapat error incorrect range.

function tulisBatch() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Data');

  // Siapkan array 2D: 3 baris, 2 kolom
  const barisBaru = [
    ['Andi', 'andi@mail.com'],
    ['Bunga', 'bunga@mail.com'],
    ['Citra', 'citra@mail.com']
  ];

  // Tujuan harus cocok: mulai baris 2 kolom 1, ukuran 3 x 2
  const numRows = barisBaru.length;        // 3
  const numCols = barisBaru[0].length;     // 2
  sheet.getRange(2, 1, numRows, numCols).setValues(barisBaru);

  Logger.log('Ditulis ' + numRows + ' baris dalam satu panggilan');
}

Ambil numRows dan numCols dari arraynya sendiri, jangan hardcode angka. Kalau isi array berubah, range ikut menyesuaikan otomatis dan kamu tidak akan lupa update salah satunya. Untuk kolom yang panjangnya beda-beda per baris, ratakan dulu jadi persegi (isi sel kosong dengan string kosong) sebelum setValues.

Tambah baris di bawah dengan appendRow

Kalau cuma nambah satu baris di ujung bawah data, tidak perlu hitung getLastRow() manual. appendRow() cari baris kosong pertama dan isi di situ. Argumennya satu array 1 dimensi, satu nilai per kolom.

function tambahBaris() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Data');

  // Satu baris, kolom kiri ke kanan
  sheet.appendRow(['Dewi', 'dewi@mail.com', new Date()]);

  // appendRow ambil baris kosong terkini, aman untuk satu submit.
  // Tapi JANGAN panggil dalam loop besar (lambat, satu per satu):
  //   for (const r of ribuanBaris) sheet.appendRow(r);  // <- lemot
  //
  // Untuk banyak baris sekaligus, hitung posisi lalu setValues:
  const banyak = [
    ['Eka', 'eka@mail.com', new Date()],
    ['Fajar', 'fajar@mail.com', new Date()]
  ];
  const mulai = sheet.getLastRow() + 1;
  sheet.getRange(mulai, 1, banyak.length, banyak[0].length).setValues(banyak);
}

appendRow() enak untuk satu baris, misalnya menyimpan satu submission form. Kalau baris datang dari form input data ke Google Spreadsheet dari HP, satu appendRow per submit sudah cukup. Tapi begitu kamu punya puluhan baris hasil olahan, kumpulkan dulu ke array lalu satu setValues.

Cari baris berdasarkan nilai, lalu update

Pola paling sering: cari baris yang kolom tertentu cocok, ubah satu nilainya. Godaannya adalah loop sambil panggil getRange(i, ...).getValue() tiap iterasi. Jangan. Baca semua sekali, cari di memori, baru tulis balik hanya sel yang berubah.

function updateStatusByEmail(emailTarget, statusBaru) {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Data');
  const values = sheet.getDataRange().getValues();

  // Anggap kolom: A=nama(0), B=email(1), C=status(2)
  const KOL_EMAIL = 1;
  const KOL_STATUS = 2;

  // Cari di memori, mulai index 1 untuk lewati header
  for (let i = 1; i < values.length; i++) {
    if (values[i][KOL_EMAIL] === emailTarget) {
      // i = index array (0-based). Baris sheet = i + 1 (1-based).
      const barisSheet = i + 1;
      const kolomSheet = KOL_STATUS + 1; // 0-based -> 1-based
      sheet.getRange(barisSheet, kolomSheet).setValue(statusBaru);
      Logger.log('Update baris ' + barisSheet + ' -> ' + statusBaru);
      return barisSheet;
    }
  }

  Logger.log('Email tidak ditemukan: ' + emailTarget);
  return -1;
}

Untuk satu baris ketemu, setValue() tunggal sudah oke. Tapi kalau kamu update banyak baris (misalnya tandai semua yang statusnya kosong), jangan setValue satu-satu. Ubah nilainya di dalam array values, lalu tulis balik seluruh kolom sekali jalan.

function tandaiKosongMassal() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Data');
  const values = sheet.getDataRange().getValues();
  const KOL_STATUS = 2; // kolom C

  // Ubah di memori
  for (let i = 1; i < values.length; i++) {
    if (values[i][KOL_STATUS] === '') {
      values[i][KOL_STATUS] = 'Belum diproses';
    }
  }

  // Tulis balik SELURUH data sekali. Range harus sama persis:
  // mulai A1, sebanyak values.length baris, values[0].length kolom.
  sheet.getRange(1, 1, values.length, values[0].length).setValues(values);
  Logger.log('Selesai update massal dalam satu tulis');
}

Hapus baris tanpa merusak indeks

deleteRow(rowPosition) menghapus satu baris, argumennya 1-based. Masalahnya muncul saat menghapus banyak baris dalam loop: begitu baris 5 dihapus, baris 6 turun jadi baris 5, dan semua indeks di bawahnya geser. Loop dari atas ke bawah akan melewati baris atau menghapus yang salah.

Solusinya: kumpulkan dulu nomor baris yang mau dihapus, lalu hapus dari bawah ke atas. Menghapus baris besar tidak mempengaruhi nomor baris di atasnya, jadi indeks tetap valid.

function hapusBarisKadaluarsa() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Data');
  const values = sheet.getDataRange().getValues();
  const KOL_STATUS = 2; // kolom C

  // Tahap 1: kumpulkan nomor baris (1-based) yang mau dihapus
  const barisHapus = [];
  for (let i = 1; i < values.length; i++) {
    if (values[i][KOL_STATUS] === 'kadaluarsa') {
      barisHapus.push(i + 1); // index 0-based -> baris 1-based
    }
  }

  // Tahap 2: hapus dari BAWAH ke ATAS biar indeks tidak geser
  for (let j = barisHapus.length - 1; j >= 0; j--) {
    sheet.deleteRow(barisHapus[j]);
  }

  Logger.log('Dihapus ' + barisHapus.length + ' baris');
}

Kalau baris yang dihapus banyak dan berurutan, deleteRows(startRow, howMany) (bentuk jamak) lebih cepat daripada deleteRow berkali-kali. Untuk hapus tersebar, pola dari-bawah-ke-atas di atas adalah yang paling aman diingat.

flush dan kapan kamu butuh

Apps Script menunda operasi tulis dan menggabungkannya biar efisien. Biasanya kamu tidak perlu memikirkan ini. Tapi kalau kamu perlu memastikan perubahan benar-benar tertulis di titik tertentu, misalnya sebelum operasi lain membacanya balik, atau sebelum menampilkan progress ke pengguna, panggil SpreadsheetApp.flush().

function tulisLaluPastikan() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Data');

  sheet.getRange('A1').setValue('Diproses ' + new Date());
  SpreadsheetApp.flush(); // paksa tulisan ke sheet sekarang juga

  // Setelah flush, perubahan dijamin sudah ada di spreadsheet
}

flush() opsional. Jangan taruh di dalam loop panjang, itu justru mematikan keuntungan batching dan bikin skrip lambat lagi. Panggil sekali di titik yang benar-benar perlu.

Merangkai jadi satu alur CRUD

Empat operasi dasar tadi (create, read, update, delete) adalah tulang punggung tiap otomasi Sheets. Ambil sheet sekali, baca sekali jadi array, olah semua logika di memori, tulis balik sedikit mungkin. Pola ini yang membuat skrip tetap di bawah batas kuota Google Apps Script sekalipun datanya membesar.

• Read: getDataRange().getValues() sekali, jangan getValue per sel.
• Create: appendRow untuk satu baris, setValues untuk banyak.
• Update: cari di memori, setValue kalau satu, setValues kalau massal.
• Delete: kumpulkan nomor baris, hapus dari bawah ke atas.

Dari sini kamu bisa lanjut ke hal yang lebih ramai: menjadwalkan skrip lewat trigger otomatis biar jalan sendiri tiap jam, menarik data eksternal dengan impor data API ke Google Spreadsheet, atau menyajikan datanya sebagai dashboard realtime dari Google Sheets web app. Semua bertumpu pada baca-tulis yang barusan kamu kuasai.

Punya ide otomasi Sheets tapi masih berupa gambaran di kepala? Tuang jadi PRD terstruktur dulu di mode Web GAS Skematik biar alur baca-tulis, struktur sheet, dan trigger-nya jelas sebelum kamu ketik baris pertama. Atau contek langsung struktur datanya dari template kasir toko Google Spreadsheet. Kuasai empat operasi ini, sisanya cuma variasi.