Fungsi Kustom di Google Spreadsheet

Google Spreadsheet menawarkan ratusan fungsi bawaan seperti AVERAGE, SUM, dan VLOOKUP. Jika hal ini tidak cukup untuk kebutuhan Anda, gunakan Google Apps Script untuk menulis fungsi kustom—misalnya, untuk mengonversi meter ke mil atau mengambil konten langsung dari internet—lalu menggunakannya di Google Spreadsheet, sama seperti fungsi bawaan.

Memulai

Fungsi kustom dibuat menggunakan JavaScript standar. Jika Anda baru mengenal JavaScript, Codecademy menawarkan kursus yang bagus untuk pemula. (Catatan: kursus ini tidak dikembangkan oleh dan tidak terkait dengan Google.)

Berikut adalah fungsi kustom sederhana, bernama 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 tidak tahu cara menulis JavaScript dan tidak punya waktu untuk mempelajarinya, periksa penyimpanan add-on 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 Google Spreadsheet.
  2. Pilih item menu Ekstensi > Apps Script..
  3. Hapus semua kode di editor skrip. Untuk fungsi DOUBLE di atas, cukup salin dan tempel kode ke editor skrip.
  4. Di bagian atas, klik Simpan .

Sekarang Anda dapat menggunakan fungsi kustom.

Mendapatkan fungsi kustom dari Google Workspace Marketplace

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

  1. Buat atau buka spreadsheet di Google Spreadsheet.
  2. Di bagian atas, klik add-on > Dapatkan add-on.
  3. Setelah Google Workspace Marketplace terbuka, klik kotak penelusuran di sudut kanan atas.
  4. Ketik "custom function", lalu tekan Enter.
  5. Jika Anda menemukan add-on fungsi kustom yang diminati, klik Install untuk menginstalnya.
  6. Akan muncul kotak dialog yang memberitahukan 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 lainnya, lalu di bagian atas, klik add-on > Kelola add-on. Temukan add-on yang ingin Anda gunakan, lalu klik Opsi > Gunakan dalam dokumen ini.

Menggunakan fungsi kustom

Setelah Anda menulis fungsi kustom atau menginstal fungsi dariGoogle Workspace Marketplace, akan dapat digunakan semudah fungsi bawaan:

  1. Klik sel tempat Anda ingin menggunakan fungsi.
  2. Ketik tanda sama dengan (=) diikuti 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, ada beberapa panduan yang perlu diketahui.

Penamaan

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

  • Nama fungsi kustom harus berbeda dari 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().
  • Kapitalisasi tidak menjadi masalah, meskipun nama fungsi spreadsheet biasanya menggunakan huruf besar.

Argumen

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

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

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


  • Argumen fungsi kustom harus deterministik. Artinya, fungsi spreadsheet bawaan yang menampilkan hasil berbeda setiap kali melakukan penghitungan — 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, Loading... akan ditampilkan tanpa batas.

Nilai yang ditampilkan

Setiap fungsi kustom harus menampilkan nilai yang akan ditampilkan, sehingga:

  • Jika fungsi kustom menampilkan nilai, nilai tersebut akan ditampilkan dalam sel tempat fungsi dipanggil.
  • Jika fungsi kustom menampilkan array nilai dua dimensi, nilai tersebut akan meluber ke sel yang bersebelahan selama sel-sel tersebut kosong. Jika ini akan menyebabkan array menimpa konten sel yang ada, fungsi kustom akan menampilkan error. Misalnya, lihat bagian mengoptimalkan fungsi kustom.
  • Fungsi khusus tidak dapat memengaruhi sel selain sel yang menghasilkan nilai. Dengan kata lain, fungsi kustom tidak dapat mengedit sel arbitrer, hanya sel yang dipanggil dan sel yang bersebelahan. Untuk mengedit sel arbitrer, gunakan menu kustom untuk menjalankan fungsi.
  • Panggilan fungsi kustom harus ditampilkan dalam 30 detik. Jika tidak, sel akan menampilkan error: Internal error executing the custom function.

Jenis data

Google Spreadsheet menyimpan data dalam format yang berbeda bergantung pada sifat data. Saat nilai tersebut digunakan dalam fungsi kustom, Apps Script akan memperlakukannya sebagai jenis data yang sesuai di JavaScript. Berikut adalah hal-hal yang paling membingungkan:

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

Pelengkapan Otomatis

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

Fungsi kustom akan muncul dalam daftar ini jika skripnya menyertakan tag @customfunction JsDoc, seperti pada contoh DOUBLE() di bawah ini.

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

Lanjutan

Menggunakan layanan Google Apps Script

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

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

Layanan yang didukung Catatan
Cache Berhasil, tetapi tidak terlalu berguna dalam fungsi kustom
HTML Dapat menghasilkan HTML, tetapi tidak dapat menampilkannya (jarang berguna)
JDBC
Bahasa
Kunci Berhasil, tetapi tidak terlalu berguna dalam fungsi kustom
Maps Dapat menghitung rute, tetapi tidak menampilkan peta
Properti getUserProperties() hanya mendapatkan properti pemilik spreadsheet. Editor spreadsheet tidak dapat menetapkan properti pengguna dalam fungsi kustom.
Spreadsheet Hanya baca (dapat menggunakan sebagian besar metode get*(), tetapi tidak set*()).
Tidak dapat membuka spreadsheet lainnya (SpreadsheetApp.openById() atau SpreadsheetApp.openByUrl()).
Pengambilan URL
Utilitas
XML

Jika fungsi kustom Anda menampilkan pesan error You do not have permission to call X service., layanan memerlukan otorisasi pengguna, sehingga tidak dapat digunakan dalam fungsi kustom.

Untuk menggunakan layanan selain yang tercantum di atas, buat menu kustom yang menjalankan fungsi Apps Script, bukan menulis fungsi kustom. Fungsi yang dipicu dari menu akan meminta otorisasi dari pengguna jika diperlukan, sehingga dapat menggunakan semua layanan Apps Script.

Berbagi

Fungsi kustom dimulai terikat ke spreadsheet tempatnya 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 tempelkan ke editor skrip spreadsheet lain.
  • Buat salinan spreadsheet yang berisi fungsi kustom dengan mengklik File > Buat salinan. Ketika {i>spreadsheet<i} disalin, semua skrip yang dilampirkan ke {i>spreadsheet<i} juga akan disalin. Siapa pun yang memiliki akses ke {i>spreadsheet <i}dapat menyalin skrip. (Kolaborator yang hanya memiliki akses lihat tidak dapat membuka editor skrip dalam spreadsheet asli. Namun, jika membuat salinan, mereka menjadi pemilik salinan tersebut dan dapat melihat skrip.)
  • Publikasikan skrip sebagai Add-on Editor Google Spreadsheet.

Pengoptimalan

Setiap kali fungsi kustom digunakan di spreadsheet, Google Spreadsheet akan melakukan panggilan terpisah ke server Apps Script. Jika spreadsheet berisi lusinan (atau ratusan, atau ribuan) panggilan fungsi kustom, proses ini bisa berjalan lambat.

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

Misalnya, fungsi DOUBLE() yang ditampilkan di atas 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 di atas menggunakan metode peta dari objek Array JavaScript untuk memanggil DOUBLE secara berulang pada setiap nilai dalam array sel dua dimensi. Metode ini menampilkan array dua dimensi yang berisi hasil. Dengan cara ini, Anda dapat memanggil DOUBLE sekali saja, tetapi membuatnya menghitung sejumlah besar sel sekaligus, seperti yang ditunjukkan pada screenshot di bawah. (Anda dapat melakukan hal yang sama dengan pernyataan if bertingkat, bukan panggilan map.)

Demikian pula, fungsi kustom di bawah 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 fungsinya sendiri, operasi tersebut akan memerlukan waktu yang jauh lebih lama, karena server Apps Script harus mendownload dan mengurai feed XML setiap kali diperlukan.

/**
 * 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 akan bervariasi, bergantung pada perilaku fungsi.