Fungsi Kustom di Google Spreadsheet

Google Spreadsheet menawarkan ratusan fungsi bawaan seperti AVERAGE, SUM, dan VLOOKUP. Jika fungsi bawaan tersebut tidak cukup untuk memenuhi kebutuhan Anda, Anda dapat menggunakan Apps Script untuk menulis fungsi kustom, lalu menggunakannya di Spreadsheet seperti fungsi bawaan.

Untuk contoh fungsi kustom, lihat tutorial berikut:

• Menghitung harga jual item yang didiskon (panduan memulai)
• Menghitung diskon harga bertingkat
• Menghitung jarak tempuh &mengonversi meter ke mil
• Meringkas data dari beberapa sheet
• Memeriksa kebenaran pernyataan dengan agen AI ADK dan model Gemini

Memulai

Fungsi kustom dibuat menggunakan JavaScript standar. Jika Anda baru menggunakan JavaScript, Codecademy menawarkan kursus untuk pemula. Kursus ini tidak dikembangkan oleh dan tidak terkait dengan Google.

Berikut adalah fungsi kustom, yang diberi nama DOUBLE, yang mengalikan nilai input dengan 2:

/**
 * Multiplies an input value by 2.
 * @param {number} input The number to double.
 * @return The input multiplied by 2.
 * @customfunction
*/
function DOUBLE(input) {
  return input * 2;
}

Jika Anda tidak tahu cara menulis JavaScript dan tidak punya waktu untuk mempelajarinya, periksa Google Workspace Marketplace untuk melihat apakah orang lain telah membuat fungsi kustom yang Anda butuhkan.

Membuat fungsi kustom

Untuk menulis fungsi kustom:

1. Buat atau buka spreadsheet di Spreadsheet.
2. Pilih item menu Ekstensi > Apps Script.
3. Hapus semua kode di editor skrip. Untuk fungsi DOUBLE yang ditampilkan sebelumnya, salin dan tempel kode ke editor skrip.
4. Di bagian atas, klik Simpan save.

Sekarang Anda dapat menggunakan fungsi kustom.

Mendapatkan fungsi kustom dari Google Workspace Marketplace

Google Workspace Marketplace menawarkan beberapa fungsi kustom sebagai add-on Google Workspace untuk Spreadsheet. Untuk menggunakan atau menjelajahi add-on ini:

1. Buat atau buka spreadsheet di Spreadsheet.
2. Di bagian atas, klik Add-on > Dapatkan add-on.
3. Setelah Google Workspace Marketplace terbuka, klik kotak penelusuran di pojok kanan atas.
4. Ketik "fungsi kustom" dan tekan Enter.
5. Jika Anda menemukan add-on fungsi kustom yang menarik, klik Instal untuk menginstalnya.
6. Dialog mungkin memberi tahu Anda bahwa add-on memerlukan otorisasi. Jika demikian, baca pemberitahuan dengan cermat, lalu klik Izinkan.
7. Add-on akan tersedia di spreadsheet. Untuk menggunakan add-on di spreadsheet lain, buka spreadsheet lain, lalu di bagian atas, klik Add-on > Kelola add-on. Temukan add-on yang ingin Anda gunakan, lalu klik Opsi more_vert > Gunakan di dokumen ini.

Menggunakan fungsi kustom

Setelah Anda menulis fungsi kustom atau menginstal fungsi kustom dari Google Workspace Marketplace, fungsi tersebut akan digunakan seperti fungsi bawaan:

1. Klik sel tempat Anda ingin menggunakan fungsi.
2. Ketik tanda sama dengan (=) diikuti dengan nama fungsi dan nilai input apa pun — misalnya, =DOUBLE(A1) — lalu tekan Enter.
3. Sel akan menampilkan Loading... sesaat, lalu menampilkan hasilnya.

Panduan untuk fungsi kustom

Sebelum menulis fungsi kustom Anda sendiri, ada beberapa panduan yang perlu diketahui.

Penamaan fungsi

Selain konvensi standar untuk penamaan fungsi JavaScript, perhatikan hal berikut:

• Nama fungsi kustom harus berbeda dengan nama fungsi bawaan seperti SUM().
• Nama fungsi kustom tidak boleh diakhiri dengan garis bawah (_), yang menunjukkan fungsi pribadi di Apps Script.
• Nama fungsi kustom harus dideklarasikan dengan sintaksis function myFunction(), bukan var myFunction = new Function().
• Penggunaan huruf kapital tidak masalah, meskipun nama fungsi spreadsheet biasanya menggunakan huruf kapital.

Argumen

Seperti fungsi bawaan, fungsi kustom dapat menggunakan argumen sebagai nilai input:

• Jika Anda memanggil fungsi dengan referensi ke satu sel sebagai argumen (seperti =DOUBLE(A1)), argumennya adalah nilai sel.

• Jika Anda memanggil fungsi dengan referensi ke rentang sel sebagai argumen (seperti =DOUBLE(A1:B10)), argumennya adalah array dua dimensi dari nilai sel. Misalnya, dalam screenshot berikut, argumen dalam =DOUBLE(A1:B2) ditafsirkan oleh Apps Script sebagai double([[1,3],[2,4]]). Perhatikan bahwa kode contoh untuk DOUBLE yang dijelaskan sebelumnya harus di ubah agar dapat menerima array sebagai input.

  
  

• Argumen fungsi kustom harus deterministik. Artinya, fungsi spreadsheet bawaan yang menampilkan hasil berbeda setiap kali menghitung — seperti NOW() atau RAND() — tidak diizinkan sebagai argumen untuk fungsi kustom. Jika fungsi kustom mencoba menampilkan nilai berdasarkan salah satu fungsi bawaan yang tidak stabil ini, fungsi tersebut akan menampilkan Loading... tanpa batas waktu.

• Untuk memicu penghitungan ulang, Anda harus meneruskan sel atau rentang sel yang direferensikan secara langsung sebagai argumen ke fungsi kustom. Jika tidak, fungsi kustom tidak akan dihitung ulang hingga Anda mengedit fungsi, atau Anda mengubah nilai sel yang direferensikan. Jika Anda menggunakan metode getValue dalam fungsi kustom, perhatikan bahwa rentang yang direferensikan tidak diteruskan secara langsung sebagai argumen ke fungsi kustom.

Nilai yang ditampilkan

Setiap fungsi kustom harus menampilkan nilai untuk ditampilkan, sehingga:

• Jika fungsi kustom menampilkan nilai, nilai tersebut akan ditampilkan di sel tempat fungsi dipanggil.
• Jika fungsi kustom menampilkan array dua dimensi dari nilai, nilai tersebut akan meluap ke sel yang berdekatan selama sel tersebut kosong. Jika hal ini menyebabkan array menimpa konten sel yang ada, fungsi kustom akan menampilkan error. Untuk contohnya, lihat bagian tentang mengoptimalkan fungsi kustom.
• Fungsi kustom tidak dapat memengaruhi sel selain sel yang menampilkan nilai. Dengan kata lain, fungsi kustom tidak dapat mengedit sel arbitrer, hanya sel tempat fungsi dipanggil dan sel yang berdekatan. Untuk mengedit sel arbitrer, gunakan menu kustom untuk menjalankan fungsi.
• Panggilan fungsi kustom harus ditampilkan dalam waktu 30 detik. Jika tidak, sel akan menampilkan #ERROR! dan catatan sel adalah Exceeded maximum execution time (line 0).

Jenis data

Spreadsheet menyimpan data dalam format yang berbeda-beda, bergantung pada sifat data. Saat nilai ini digunakan dalam fungsi kustom, Apps Script memperlakukannya sebagai jenis data yang sesuai di JavaScript. Berikut adalah area yang paling sering menimbulkan kebingungan:

• Waktu dan tanggal di Spreadsheet menjadi Tanggal objek di Apps Script. Jika spreadsheet dan skrip menggunakan zona waktu yang berbeda (masalah yang jarang terjadi), fungsi kustom harus mengimbanginya.
• Nilai durasi di Spreadsheet juga menjadi objek Date, tetapi penggunaannya bisa rumit.
• Nilai persentase di Spreadsheet menjadi angka desimal di Apps Script. Misalnya, sel dengan nilai 10% menjadi 0.1 di Apps Script.

Pelengkapan otomatis

Spreadsheet mendukung pelengkapan otomatis untuk fungsi kustom seperti untuk fungsi bawaan. Saat Anda mengetik nama fungsi di sel, Anda akan melihat daftar fungsi bawaan dan kustom yang cocok dengan yang Anda masukkan.

Fungsi kustom muncul dalam daftar ini jika skripnya menyertakan JSDoc @customfunction tag, seperti dalam DOUBLE() contoh.

/**
 * Multiplies the input value by 2.
 *
 * @param {number} input The value to multiply.
 * @return {number} The input multiplied by 2.
 * @customfunction
 */
function DOUBLE(input) {
  return input * 2;
}

Lanjutan

Bagian ini membahas topik fungsi kustom lanjutan.

Menggunakan layanan Google Apps Script

Fungsi kustom dapat memanggil layanan Apps Script tertentu untuk melakukan tugas yang lebih kompleks. Misalnya, fungsi kustom dapat memanggil layanan Bahasa untuk menerjemahkan frasa bahasa Inggris ke bahasa Spanyol.

Tidak seperti sebagian besar jenis Apps Script lainnya, fungsi kustom tidak pernah meminta pengguna untuk mengotorisasi akses ke data pribadi. Oleh karena itu, fungsi kustom hanya dapat memanggil layanan yang tidak memiliki akses ke data pribadi, khususnya yang berikut:

Jika fungsi kustom Anda menampilkan pesan error You do not have permission to call X service., layanan tersebut memerlukan otorisasi pengguna dan oleh karena itu tidak dapat di gunakan dalam fungsi kustom.

Untuk menggunakan layanan selain yang ada dalam daftar sebelumnya, buat menu kustom yang menjalankan fungsi Apps Script bukan menulis fungsi kustom. Fungsi yang dipicu dari menu akan meminta otorisasi pengguna jika diperlukan dan oleh karena itu dapat menggunakan semua layanan Apps Script.

Membagikan fungsi kustom

Fungsi kustom awalnya terikat ke spreadsheet tempat fungsi tersebut dibuat. Artinya, fungsi kustom yang ditulis dalam satu spreadsheet tidak dapat digunakan di spreadsheet lain kecuali jika Anda menggunakan salah satu metode berikut:

• Klik Ekstensi > Apps Script untuk membuka editor skrip, lalu salin teks skrip dari spreadsheet asli dan tempel ke editor skrip spreadsheet lain.
• Buat salinan spreadsheet yang berisi fungsi kustom dengan mengklik File > Buat salinan. Saat spreadsheet disalin, skrip apa pun yang dilampirkan ke spreadsheet tersebut juga akan disalin. Siapa pun yang memiliki akses ke spreadsheet dapat menyalin skrip. (Kolaborator yang hanya memiliki akses lihat tidak dapat membuka editor skrip di spreadsheet asli. Namun, saat membuat salinan, mereka akan menjadi pemilik salinan dan dapat melihat skrip.)
• Publikasikan skrip sebagai add-on Editor Spreadsheet .

Semua skrip terikat penampung berbagi daftar akses yang sama dengan penampungnya. Artinya, siapa pun yang memiliki izin untuk mengedit spreadsheet juga dapat mengedit kode Apps Script apa pun yang dilampirkan ke spreadsheet tersebut. Untuk mengetahui informasi selengkapnya, lihat akses ke skrip terikat.

Pengoptimalan

Setiap kali fungsi kustom digunakan dalam spreadsheet, Spreadsheet akan membuat panggilan terpisah ke server Apps Script. Jika spreadsheet Anda berisi puluhan (atau ratusan, atau ribuan!) panggilan fungsi kustom, proses ini dapat berjalan lambat. Beberapa project dengan banyak atau fungsi kustom yang kompleks mungkin mengalami penundaan sementara dalam eksekusi.

Oleh karena itu, jika Anda berencana menggunakan fungsi kustom beberapa kali pada rentang data yang besar, pertimbangkan untuk mengubah fungsi agar menerima rentang sebagai input dalam bentuk array dua dimensi, lalu menampilkan array dua dimensi yang dapat meluap ke sel yang sesuai.

Misalnya, fungsi DOUBLE() yang ditampilkan sebelumnya dapat ditulis ulang untuk menerima satu sel atau rentang sel sebagai berikut:

/**
 * Multiplies the input value by 2.
 *
 * @param {number|Array<Array<number>>} input The value or range of cells
 *     to multiply.
 * @return The input multiplied by 2.
 * @customfunction
 */
function DOUBLE(input) {
  return Array.isArray(input) ?
      input.map(row => row.map(cell => cell * 2)) :
      input * 2;
}

Pendekatan ini menggunakan metode peta objek Array JavaScript pada array dua dimensi sel untuk mendapatkan setiap baris, lalu untuk setiap baris, metode ini menggunakan map lagi untuk menampilkan nilai setiap sel dua kali lipat. Metode ini menampilkan array dua dimensi yang berisi hasil. Dengan cara ini, Anda dapat memanggil DOUBLE hanya sekali, tetapi fungsi tersebut dapat menghitung untuk sejumlah besar sel sekaligus, seperti yang ditunjukkan dalam screenshot berikut. Anda dapat mencapai hal yang sama dengan pernyataan if bertingkat, bukan panggilan map.

Demikian pula, fungsi kustom berikut mengambil konten live dari Internet secara efisien dan menggunakan array dua dimensi untuk menampilkan dua kolom hasil hanya dengan satu panggilan fungsi. Jika setiap sel memerlukan panggilan fungsi sendiri, operasi akan memerlukan waktu yang jauh lebih lama, karena server Apps Script harus mendownload dan mengurai feed XML setiap kali.

/**
 * Show the title and date for the first page of posts on the
 * Developer blog.
 *
 * @return Two columns of data representing posts on the
 *     Developer blog.
 * @customfunction
 */
function getBlogPosts() {
  var array = [];
  var url = 'https://gsuite-developers.googleblog.com/atom.xml';
  var xml = UrlFetchApp.fetch(url).getContentText();
  var document = XmlService.parse(xml);
  var root = document.getRootElement();
  var atom = XmlService.getNamespace('http://www.w3.org/2005/Atom');
  var entries = document.getRootElement().getChildren('entry', atom);
  for (var i = 0; i < entries.length; i++) {
    var title = entries[i].getChild('title', atom).getText();
    var date = entries[i].getChild('published', atom).getValue();
    array.push([title, date]);
  }
  return array;
}

Teknik ini dapat diterapkan ke hampir semua fungsi kustom yang digunakan berulang kali di seluruh spreadsheet, meskipun detail implementasinya bervariasi bergantung pada perilaku fungsi.