Cara Ambil Data dari API ke Google Spreadsheet Otomatis dengan UrlFetchApp

Tiap pagi buka dashboard, sorot tabel, copy, balik ke spreadsheet, paste. Kurs, harga produk, stok gudang, data cuaca, apa pun itu. Lima menit terbuang, dan besok diulang lagi. Padahal datanya sudah rapi tersedia lewat API.

Google Apps Script bisa narik data itu sendiri. Satu fungsi UrlFetchApp, satu trigger terjadwal, dan sheet-mu terisi tanpa kamu buka tab mana pun. Artikel ini bahas alurnya dari nol: panggil API, cek status, parse JSON, ratakan ke baris, tulis sekali pakai setValues, lalu jadwalkan. Kalau kamu masih baru di Apps Script, mampir dulu ke panduan dasar Apps Script biar nyaman baca kodenya.

UrlFetchApp vs ImportJSON: kenapa pilih yang lebih terkontrol

ImportJSON populer karena tinggal tempel formula di sel dan selesai. Masalahnya kamu numpang skrip orang lain, susah nambah header auth, dan begitu API-nya minta token atau struktur JSON-nya agak nyeleneh, kamu mentok. UrlFetchApp kasih kontrol penuh. Kamu yang tentukan header, cara parsing, kolom mana yang masuk, dan kapan refresh jalan.

• Header dan Bearer token bebas kamu atur, hal yang nyaris mustahil di ImportJSON.
• Error handling di tangan kamu: lihat kode status, lalu tangani sendiri.
• JSON bersarang atau dibungkus payload? Kamu bongkar sesuai kebutuhan.
• Performa: tulis batch sekali, bukan formula yang re-fetch tiap sheet dibuka.

Panggil API dan cek statusnya dulu

Fungsi inti cuma satu: UrlFetchApp.fetch(url, opsi). Sebelum sentuh datanya, cek kode status. Jangan pernah langsung JSON.parse respons yang belum kamu pastikan sukses, nanti skripnya error di tempat yang bikin bingung.

function ambilData() {
  var url = 'https://api.contoh.com/v1/produk';

  var res = UrlFetchApp.fetch(url, {
    method: 'get',
    muteHttpExceptions: true
  });

  var code = res.getResponseCode();
  if (code !== 200) {
    Logger.log('Gagal: HTTP ' + code + ' - ' + res.getContentText());
    return;
  }

  var data = JSON.parse(res.getContentText());
  Logger.log('Dapat ' + data.length + ' item');
}

Ratakan JSON ke baris, tulis sekali pakai setValues

API biasa balikin array of objects. Sheet butuh array dua dimensi: baris kali kolom. Jadi kerjanya meratakan tiap objek jadi satu baris array, dengan baris pertama sebagai header. Tentukan kolom mana yang mau diambil, urut, lalu isi.

function tulisKeSheet() {
  var url = 'https://api.contoh.com/v1/produk';
  var res = UrlFetchApp.fetch(url, { method: 'get', muteHttpExceptions: true });

  if (res.getResponseCode() !== 200) {
    Logger.log('Gagal: ' + res.getResponseCode());
    return;
  }

  var data = JSON.parse(res.getContentText());
  if (!data.length) return;

  // Kolom yang diambil, urut sesuai header
  var kolom = ['id', 'nama', 'harga', 'stok'];

  // Baris pertama = header
  var rows = [kolom];

  // Ratakan tiap objek jadi satu baris
  for (var i = 0; i < data.length; i++) {
    var item = data[i];
    var baris = [];
    for (var j = 0; j < kolom.length; j++) {
      var nilai = item[kolom[j]];
      baris.push(nilai == null ? '' : nilai);
    }
    rows.push(baris);
  }

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Data');
  sheet.clearContents();
  sheet.getRange(1, 1, rows.length, kolom.length).setValues(rows);
}

Kuncinya di baris terakhir: dimensi getRange harus persis cocok dengan array. rows.length baris, kolom.length kolom, tidak boleh meleset satu sel pun. Tulis sekali pakai setValues jauh lebih ngebut dan hemat kuota dibanding appendRow di dalam loop, yang manggil layanan Sheets tiap item. Detail soal pola batch ini ada di panduan baca-tulis data sheet.

API key jangan di-hardcode: simpan di PropertiesService

Kalau key kamu tempel langsung di kode, siapa pun yang buka skripnya lihat key-mu, termasuk kalau file-nya kamu bagikan atau salin. Simpan di Script Properties. Sekali set, dipanggil dari kode tanpa pernah muncul di sumber.

// Jalankan SEKALI dari editor, lalu hapus lagi barisnya
function simpanKey() {
  PropertiesService.getScriptProperties()
    .setProperty('API_KEY', 'tempel-key-di-sini');
}

function ambilDenganAuth() {
  var key = PropertiesService.getScriptProperties().getProperty('API_KEY');
  var url = 'https://api.contoh.com/v1/produk';

  var res = UrlFetchApp.fetch(url, {
    method: 'get',
    muteHttpExceptions: true,
    headers: {
      Authorization: 'Bearer ' + key
    }
  });

  if (res.getResponseCode() !== 200) {
    Logger.log('Gagal: ' + res.getResponseCode());
    return;
  }

  var data = JSON.parse(res.getContentText());
  Logger.log(data.length + ' item diterima');
}

Jalankan simpanKey sekali, lalu hapus barisnya biar key-nya tidak nyangkut di kode. Alternatifnya set manual lewat Project Settings, bagian Script Properties, tanpa nulis fungsi sama sekali. Sebagian API tidak pakai header, malah minta key di query string. Untuk itu susun URL-nya dengan encodeURIComponent(key) supaya karakter aneh tidak merusak alamatnya.

Bongkar data bersarang (nested JSON)

API jarang ngasih array datar yang manis. Sering hasilnya dibungkus, misal payload.data, dan field-nya bersarang seperti kategori.nama atau harga.jual. Dua pola ini yang paling bikin ImportJSON menyerah, dan di UrlFetchApp cukup beberapa baris.

// Banyak API membungkus hasil: { "data": [ ... ] }
var payload = JSON.parse(res.getContentText());
var data = payload.data || [];

// Ambil field bersarang lewat jalur 'kategori.nama'
function ambilJalur(obj, jalur) {
  var bagian = jalur.split('.');
  var nilai = obj;
  for (var k = 0; k < bagian.length; k++) {
    if (nilai == null) return '';
    nilai = nilai[bagian[k]];
  }
  return nilai == null ? '' : nilai;
}

// Contoh: ambilJalur(item, 'kategori.nama')

Pakai ambilJalur di dalam loop flatten tadi buat kolom yang bersarang. Definisikan kolom sebagai jalur, misal ['id', 'nama', 'kategori.nama'], lalu panggil ambilJalur(item, kolom[j]) menggantikan akses item[kolom[j]] yang polos. Guard nilai null penting: satu field kosong tidak boleh bikin seluruh refresh gagal.

Auto-refresh dengan time-driven trigger

Jalan manual sekali sih oke buat tes, tapi tujuannya otomatis. Pasang time-driven trigger yang manggil tulisKeSheet secara berkala. Cukup jalankan pasangTrigger sekali dari editor.

// Jalankan SEKALI untuk memasang refresh tiap 6 jam
function pasangTrigger() {
  ScriptApp.newTrigger('tulisKeSheet')
    .timeBased()
    .everyHours(6)
    .create();
}

Bisa juga dari menu Triggers (ikon jam) di editor kalau lebih suka klik-klik. Yang penting jangan pasang dua trigger untuk fungsi sama, nanti sheet ke-refresh dobel. Opsi jadwal lain, plus cara hindari eksekusi tumpang tindih, ada di panduan menjadwalkan script dengan trigger.

Kuota dan rate limit yang perlu kamu jaga

UrlFetchApp bukan tanpa batas. Akun gratis dapat sekitar 20.000 panggilan per hari, akun Workspace lebih longgar. Kedengarannya banyak, tapi trigger tiap menit itu 1.440 panggilan sehari cuma dari satu skrip, dan itu sebelum hitung rate limit dari API sumbernya sendiri.

• Set interval sesuai seberapa cepat datanya benar-benar berubah.
• Hormati rate limit API sumber, banyak yang membatasi per menit.
• Kalau satu eksekusi manggil banyak endpoint, hitung total panggilannya.
• Pantau tab Executions buat lihat pola error dan durasi tiap run.

Rincian angka kuota UrlFetchApp, runtime, dan batas lain yang gampang kelewat ada di panduan batas kuota Google Apps Script. Baca sebelum bikin sesuatu yang jalan 24 jam.

Rangkai jadi satu, dari fetch ke dashboard

Begitu data masuk otomatis, sheet-mu jadi fondasi buat hal lain. Bangun dashboard realtime dari Google Sheets yang baca sheet ini, atau pasang notifikasi Telegram yang nyala kalau stok nyentuh ambang tertentu. Butuh contoh struktur data yang matang buat mulai? Lihat template kasir toko sebagai kerangka.

Kalau kamu mau rapiin idenya dulu sebelum ngoding, kolom apa yang masuk, API mana yang dipanggil, seberapa sering refresh, tulis dulu jadi spesifikasi. Skematik punya mode Web GAS yang bikin PRD Apps Script kamu terstruktur, lengkap sampai skema sheet dan jadwal trigger, tinggal serahkan ke agent buat dikodein.

Datanya sudah nunggu di API. Tinggal disuruh jalan sendiri.