Google Spreadsheet menawarkan ratusan fungsi bawaan seperti AVERAGE, SUM, dan VLOOKUP. Ketika ini tidak cukup untuk kebutuhan Anda, Anda dapat menggunakan Google Apps Script untuk menulis fungsi khusus — katakanlah, untuk mengonversi meter menjadi mil atau — lalu gunakan di Google Sheets seperti fungsi bawaan
Mulai
Fungsi khusus 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. )
Inilah 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 Anda tidak tahu cara menulis JavaScript dan tidak punya waktu untuk mempelajarinya, untuk melihat apakah orang lain telah membuat fungsi khusus yang Anda perlukan
Membuat fungsi kustom
Untuk menulis fungsi kustom
- Buat atau buka spreadsheet di Google Sheets
- Pilih item menu Extensions > Apps Script.
- Hapus kode apa pun di editor skrip. Untuk fungsi DOUBLE di atas, cukup salin dan tempel kode ke editor skrip
- Di bagian atas, klik Simpan simpan .
Sekarang kamu bisa
Mendapatkan fungsi kustom dari Google Workspace Marketplace
Google Workspace Marketplace menawarkan beberapa fungsi kustom sebagai add-on untuk Google Spreadsheet. Untuk menggunakan atau menjelajahi pengaya ini
- Buat atau buka spreadsheet di Google Sheets
- Di bagian atas, klik Add-on > Dapatkan add-on
- Setelah Google Workspace Marketplace terbuka, klik kotak telusur di pojok kanan atas
- Ketik "fungsi khusus" dan tekan Enter
- Jika Anda menemukan add-on fungsi kustom yang Anda minati, klik Instal untuk menginstalnya
- Kotak dialog mungkin memberi tahu Anda bahwa add-on memerlukan otorisasi. Jika ya, baca pemberitahuan tersebut dengan saksama, lalu klik Izinkan
- Pengaya akan tersedia di spreadsheet. Untuk menggunakan add-on di spreadsheet lain, buka spreadsheet lain dan di bagian atas, klik Add-on > Kelola add-on. Temukan add-on yang ingin Anda gunakan dan klik Opsi more_vert > Gunakan dalam dokumen ini.
Menggunakan fungsi kustom
Setelah Anda menulis fungsi khusus atau menginstalnya dari Google Workspace Marketplace, penggunaannya semudah fungsi bawaan
- Klik sel tempat Anda ingin menggunakan fungsi
- Ketik tanda sama dengan (=_) diikuti dengan nama fungsi dan nilai input apa pun — misalnya, /** * 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; } 0 — dan tekan Enter
- Sel akan menampilkan /** * 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; } _1 sebentar, lalu mengembalikan hasilnya
Panduan untuk fungsi kustom
Sebelum menulis fungsi kustom Anda sendiri, ada beberapa pedoman yang perlu diketahui
Yaitu
Selain konvensi standar untuk penamaan fungsi JavaScript, perhatikan hal berikut
- Nama fungsi kustom harus berbeda dari nama fungsi bawaan seperti /** * 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; } 2
- Nama fungsi khusus tidak boleh diakhiri dengan garis 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; } 3), yang menunjukkan fungsi pribadi di Apps Script
- Nama fungsi khusus harus dideklarasikan dengan sintaks /** * 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; } 4, bukan /** * 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; } 5
- Kapitalisasi tidak menjadi masalah, meskipun nama fungsi spreadsheet biasanya menggunakan huruf besar
Argumen
Seperti fungsi bawaan, fungsi kustom dapat menggunakan argumen sebagai nilai masukan
- Jika Anda memanggil fungsi Anda dengan referensi ke satu sel sebagai argumen (seperti /** * 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; } 0), argumen tersebut akan menjadi nilai sel
Jika Anda memanggil fungsi Anda dengan referensi ke rentang sel sebagai argumen (seperti /** * 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; } 7), argumen tersebut akan menjadi larik dua dimensi dari nilai sel. Misalnya, pada tangkapan layar di bawah, argumen di /** * 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; } 8 ditafsirkan oleh Apps Script sebagai /** * 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; } 9. Perhatikan bahwa kode contoh untuk DOUBLE harus
Argumen fungsi kustom harus deterministik. Artinya, fungsi spreadsheet bawaan yang mengembalikan hasil yang berbeda setiap kali dihitung — seperti /** * 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; } 1 atau /** * 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; } 2 — tidak diizinkan sebagai argumen untuk fungsi kustom. Jika fungsi kustom mencoba mengembalikan nilai berdasarkan salah satu fungsi bawaan yang mudah menguap ini, fungsi tersebut akan menampilkan /** * 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; } 1 tanpa batas
Kembalikan nilai
Setiap fungsi kustom harus mengembalikan nilai untuk ditampilkan, seperti itu
- Jika fungsi kustom mengembalikan nilai, nilai akan ditampilkan di sel tempat fungsi dipanggil
- Jika fungsi kustom mengembalikan larik nilai dua dimensi, nilai akan meluap ke sel yang berdekatan selama sel tersebut kosong. Jika ini akan menyebabkan larik menimpa konten sel yang ada, fungsi kustom akan menimbulkan kesalahan. Sebagai contoh, lihat bagian tentang
- Fungsi kustom tidak dapat memengaruhi sel selain yang dikembalikan nilainya. Dengan kata lain, fungsi kustom tidak dapat mengedit sel arbitrer, hanya sel asalnya dan sel yang berdekatan. Untuk mengedit sel arbitrer, gunakan menu khusus untuk menjalankan fungsi
- Panggilan fungsi kustom harus kembali dalam 30 detik. Jika tidak, sel akan menampilkan kesalahan. /** * 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; } _4
Tipe data
Google Spreadsheet menyimpan data dalam berbagai format bergantung pada sifat datanya. Saat nilai ini digunakan dalam fungsi kustom, Apps Script memperlakukannya sebagai tipe data yang sesuai di JavaScript. Ini adalah area kebingungan yang paling umum
- Waktu dan tanggal di Spreadsheet menjadi objek Tanggal di Apps Script. Jika spreadsheet dan skrip menggunakan zona waktu yang berbeda (masalah yang jarang terjadi), fungsi kustom harus memberikan kompensasi
- Nilai durasi di Lembar juga menjadi /** * 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; } 5 objek, tetapi bekerja dengannya bisa jadi rumit
- Nilai persentase di Spreadsheet menjadi angka desimal di Apps Script. Misalnya, sel dengan nilai /** * 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; } 6 menjadi /** * 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; } 7 di Apps Script
Pelengkapan otomatis
Google Spreadsheet mendukung pelengkapan otomatis untuk fungsi khusus seperti fungsi bawaan. Saat Anda mengetik nama fungsi di sel, Anda akan melihat daftar fungsi bawaan dan kustom yang cocok dengan apa yang Anda masukkan
Fungsi khusus akan muncul dalam daftar ini jika skripnya menyertakan tag JsDoc /** * 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; } 8, seperti pada contoh /** * 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; } 9 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; } _Canggih
Menggunakan layanan Apps Script
Fungsi kustom dapat memanggil layanan 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 Skrip Aplikasi lainnya, fungsi khusus tidak pernah meminta pengguna untuk mengotorisasi akses ke data pribadi. Akibatnya, mereka hanya dapat memanggil layanan yang tidak memiliki akses ke data pribadi, khususnya berikut ini
Layanan yang didukungCatatanCacheWorks, tetapi tidak terlalu berguna dalam fungsi khususHTMLDapat menghasilkan HTML, tetapi tidak dapat menampilkannya (jarang berguna)JDBCLanguageLockWorks, tetapi tidak terlalu berguna dalam fungsi khususPetaDapat menghitung arah, tetapi tidak menampilkan petaProperti/** * 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 = '//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('//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; } 0 hanya mendapatkan properti dari pemilik spreadsheet. Editor spreadsheet tidak dapat menyetel properti pengguna dalam fungsi kustom. SpreadsheetRead only (dapat menggunakan sebagian besar metode /** * 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 = '//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('//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; } 1, tetapi tidak /** * 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 = '//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('//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; } 2)Tidak dapat membuka spreadsheet lain (/** * 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 = '//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('//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; } _3 atau /** * 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 = '//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('//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; } 4). URL FetchUtilitiesXML
Jika fungsi kustom Anda melontarkan pesan kesalahan /** * 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 = '//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('//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; } 5, layanan memerlukan otorisasi pengguna dan karenanya tidak dapat digunakan dalam fungsi kustom
Untuk menggunakan layanan selain yang tercantum di atas, buat menu kustom yang menjalankan fungsi Apps Script alih-alih menulis fungsi kustom. Fungsi yang dipicu dari menu akan meminta otorisasi pengguna jika perlu dan akibatnya dapat menggunakan semua layanan Apps Script
Membagikan
Fungsi khusus mulai terikat ke spreadsheet tempat mereka dibuat. Artinya, fungsi kustom yang ditulis di satu spreadsheet tidak dapat digunakan di spreadsheet lain kecuali 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 dari spreadsheet lain.
- Buat salinan spreadsheet yang berisi fungsi khusus dengan mengklik File > Buat salinan. Saat spreadsheet disalin, semua skrip yang menyertainya juga akan disalin. Siapa pun yang memiliki akses ke spreadsheet dapat menyalin skrip. (Kolaborator yang hanya memiliki akses melihat tidak dapat membuka editor skrip di spreadsheet asli. Namun, ketika mereka membuat salinan, mereka menjadi pemilik salinan tersebut dan dapat melihat naskahnya. )
- Publikasikan skrip sebagai add-on Google Sheets
Optimasi
Setiap kali fungsi khusus digunakan dalam spreadsheet, Google Sheets melakukan panggilan terpisah ke server Apps Script. Jika spreadsheet Anda berisi lusinan (atau ratusan, atau ribuan. ) panggilan fungsi kustom, proses ini bisa sangat lambat
Akibatnya, jika Anda berencana untuk menggunakan fungsi kustom berkali-kali pada rentang data yang besar, pertimbangkan untuk memodifikasi fungsi agar menerima rentang sebagai masukan dalam bentuk larik dua dimensi, lalu mengembalikan larik dua dimensi yang dapat meluap ke sel yang sesuai
Misalnya, fungsi /** * 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; } _9 yang ditunjukkan 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 map dari objek /** * 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 = '//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('//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; } 7 JavaScript untuk memanggil DOUBLE secara rekursif pada setiap nilai dalam array sel dua dimensi. Ini mengembalikan array dua dimensi yang berisi hasilnya. Dengan cara ini, Anda dapat memanggil DOUBLE sekali saja tetapi memintanya menghitung sejumlah besar sel sekaligus, seperti yang ditunjukkan pada tangkapan layar di bawah. (Anda dapat melakukan hal yang sama dengan pernyataan AVERAGE0 bersarang alih-alih panggilan AVERAGE1. )
Demikian pula, fungsi kustom di bawah secara efisien mengambil konten langsung dari Internet dan menggunakan larik dua dimensi untuk menampilkan dua kolom hasil hanya dengan satu panggilan fungsi. Jika setiap sel memerlukan pemanggilan fungsinya sendiri, operasi akan memakan waktu lebih lama, karena server Apps Script harus mengunduh dan mengurai umpan 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 = '//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('//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 penerapannya akan bervariasi bergantung pada perilaku fungsi.