Fungsi Kustom di Google Spreadsheet

Google Sheets menawarkan ratusan fungsi bawaan seperti AVERAGE, SUM, dan VLOOKUP. Jika pertanyaannya tidak sesuai sesuai kebutuhan, Anda dapat menggunakan Google Apps Script untuk menulis fungsi khusus — misalnya, untuk mengonversi meter ke mil atau mengambil konten live dari internet — lalu gunakan di {i>Google Sheets<i} seperti fungsi {i>built-in<i}.

Memulai

Fungsi kustom dibuat menggunakan JavaScript standar. Jika Anda baru saja 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 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 belajar, periksa toko add-on untuk melihat apakah orang lain telah membuat fungsi kustom yang Anda butuhkan.

Membuat fungsi kustom

Untuk menulis fungsi kustom:

  1. Buat atau membuka {i>spreadsheet<i} di Google Sheets.
  2. Pilih item menu Ekstensi &gt; 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 opsi berfungsi sebagai add-on untuk Google Spreadsheet. Untuk menggunakan atau menjelajahi add-on ini:

  1. Buat atau membuka {i>spreadsheet<i} di Google Sheets.
  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" (fungsi kustom) 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 {i>spreadsheet<i} yang berbeda, buka {i>spreadsheet <i}lain dan di bagian atas, klik add-on > Kelola add-on. Temukan add-on yang ingin Anda gunakan, lalu klik Opsi > Gunakan di dokumen.

Menggunakan fungsi kustom

Setelah Anda menulis fungsi khusus atau menginstal fungsi dari Google Workspace Marketplace, penggunaannya semudah 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, 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 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 masalah, meskipun nama-nama {i>function<i} pada {i>spreadsheet<i} biasanya ditulis dengan 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 berupa nilai sel.

  • Jika Anda memanggil {i>function<i} dengan referensi ke rentang sel sebagai (seperti =DOUBLE(A1:B10)), argumennya akan berupa dua dimensi himpunan sel masing-masing. Misalnya, dalam screenshot di bawah ini, argumen di =DOUBLE(A1:B2) ditafsirkan oleh Apps Script sebagai double([[1,3],[2,4]]). Perhatikan bahwa kode contoh untuk DOUBLE dari atas harus diubah untuk menerima array sebagai input.


  • Argumen fungsi kustom harus determenistik. Bahwa adalah, fungsi {i>spreadsheet<i} bawaan yang menampilkan hasil berbeda setiap kali yang dihitung — seperti NOW() atau RAND() — tidak diizinkan sebagai argumen ke suatu fungsi kustom. Jika fungsi kustom mencoba menampilkan nilai berdasarkan salah satu fungsi bawaan yang tidak stabil ini akan menampilkan Loading... tanpa batas waktu.

Nilai yang ditampilkan

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

  • Jika fungsi khusus menampilkan nilai, nilai tersebut akan ditampilkan dalam sel fungsi tersebut dipanggil.
  • Jika fungsi kustom menampilkan array nilai dua dimensi, nilainya {i>meluap<i} ke sel yang bersebelahan selama sel tersebut kosong. Jika ini akan menyebabkan {i>array<i} menimpa isi sel yang ada, maka fungsi khusus akan memunculkan pesan {i>error<i}. Sebagai contoh, lihat bagian tentang mengoptimalkan fungsi kustom.
  • Fungsi khusus tidak dapat memengaruhi sel selain sel yang menghasilkan nilai. Dengan kata lain, fungsi khusus tidak dapat mengedit sel arbitrer, hanya saja dari sel-sel itu dan sel-sel yang berdekatan. Untuk mengedit sel arbitrer, gunakan menu kustom untuk menjalankan fungsi.
  • Panggilan fungsi kustom harus ditampilkan dalam 30 detik. Jika tidak, sel menampilkan #ERROR! dan catatan sel menampilkan Exceeded maximum execution time (line 0).

Jenis data

{i>Google Sheets <i}menyimpan data di format yang berbeda tergantung sifat dari data. Saat nilai tersebut digunakan dalam fungsi kustom, Apps Skrip memperlakukan nilai tersebut sebagai jenis data yang sesuai di JavaScript. Berikut adalah hal-hal yang paling membingungkan:

  • Waktu dan tanggal di Spreadsheet menjadi Date di Apps Script. Jika {i>spreadsheet<i} dan skrip menggunakan zona waktu yang berbeda (masalah yang jarang terjadi), fungsi khusus akan perlu mengimbangi.
  • Nilai durasi di Spreadsheet juga menjadi objek Date, tetapi bekerja dengan mereka bisa jadi rumit.
  • Nilai persentase di Spreadsheet menjadi angka desimal di Apps Script. Sebagai misalnya, sel dengan nilai 10% menjadi 0.1 di Apps Script.

Pelengkapan Otomatis

Google Sheets mendukung pelengkapan otomatis untuk fungsi khusus seperti pada fungsi bawaan. Saat Anda mengetik nama fungsi dalam sel, Anda akan melihat daftar fungsi bawaan dan {i>custom<i} fungsi yang sesuai dengan yang Anda masukkan.

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

/**
 * 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 khusus dapat memanggil Layanan Google Apps Script untuk melakukan operasi yang lebih kompleks tugas klasifikasi. Misalnya, fungsi kustom dapat memanggil Layanan Bahasa untuk menerjemahkan bahasa Inggris frase yang sama ke dalam bahasa Spanyol.

Tidak seperti kebanyakan jenis Apps Script lainnya, fungsi khusus tidak pernah meminta pengguna untuk mengizinkan akses ke data pribadi. Akibatnya, mereka hanya dapat memanggil layanan yang tidak memiliki akses ke data pribadi, khususnya hal 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 {i>spreadsheet<i}. Editor spreadsheet tidak dapat menetapkan properti pengguna di fungsi kustom.
Spreadsheet Hanya baca (dapat menggunakan sebagian besar metode get*(), tetapi tidak set*()).
Tidak dapat membuka spreadsheet lain (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 yang digunakan dalam fungsi kustom.

Untuk menggunakan layanan selain yang tercantum di atas, buat menu kustom yang menjalankan fungsi Apps Script alih-alih menulis fungsi khusus. Sebuah fungsi yang dipicu dari menu akan meminta otorisasi kepada pengguna jika diperlukan dan akibatnya dapat menggunakan semua Layanan Apps Script.

Berbagi

Fungsi kustom dimulai terikat pada {i>spreadsheet<i} tempat mereka dibuat. Ini berarti bahwa fungsi khusus yang ditulis dalam satu {i>spreadsheet <i}tidak dapat digunakan di {i>spreadsheet <i}lain kecuali Anda menggunakan metode berikut:

  • Klik Ekstensi &gt; Apps Script untuk buka editor skrip, lalu salin teks skrip dari spreadsheet asli dan tempelkan ke editor skrip dari {i>spreadsheet <i}lain.
  • Buat salinan {i>spreadsheet<i} yang berisi fungsi khusus dengan mengklik File > Buat salinan. Saat {i>spreadsheet<i} disalin, semua skrip yang dilampirkan data itu disalin juga. Siapa saja yang memiliki akses ke {i>spreadsheet<i} dapat menyalin {i>script<i}. (Kolaborator yang hanya memiliki akses lihat tidak dapat membuka editor skrip dalam {i>spreadsheet <i}asli. Namun, ketika mereka membuat salinan, mereka menjadi pemilik salinan dan dapat melihat skripnya.)
  • Publikasikan skrip sebagai Add-on Editor Google Spreadsheet.

Pengoptimalan

Setiap kali fungsi khusus digunakan dalam {i>spreadsheet<i}, Google Sheets membuat panggilan terpisah ke server Apps Script. Jika {i>spreadsheet <i}Anda berisi lusinan (atau ratusan, atau ribuan!) panggilan fungsi khusus, proses ini bisa sangat lambat.

Akibatnya, jika Anda berencana untuk menggunakan fungsi khusus beberapa kali pada {i>range<i} data, pertimbangkan untuk memodifikasi fungsi sehingga dapat menerima rentang sebagai dalam bentuk larik dua dimensi, kemudian mengembalikan yang bisa meluap ke sel yang sesuai.

Misalnya, fungsi DOUBLE() yang ditampilkan di atas dapat ditulis ulang untuk menerima sel tunggal 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 map dari objek Array JavaScript untuk secara rekursif memanggil DOUBLE di setiap nilai dalam array sel dua dimensi. Metode ini mengembalikan array dua dimensi yang berisi hasil. Dengan cara ini, Anda dapat memanggil DOUBLE sekali saja tetapi membuatnya menghitung sejumlah besar sel sekaligus, seperti ditunjukkan dalam screenshot di bawah. (Anda dapat mencapai hal yang sama dengan if bertingkat alih-alih panggilan map.)

Demikian juga, fungsi khusus di bawah ini mengambil konten live secara efisien dari Internet dan menggunakan array dua dimensi untuk menampilkan dua kolom hasil dengan hanya dengan satu panggilan fungsi. Jika setiap sel memerlukan panggilan fungsinya sendiri, akan memakan waktu lebih lama, karena server Apps Script akan harus mendownload dan mengurai feed XML setiap saat.

/**
 * 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 {i>spreadsheet<i}, meskipun detail implementasinya akan bervariasi tergantung pada perilaku {i>function<i}.