Cara menggunakan baris google sheet yang hilang

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

1. Buat atau buka spreadsheet di Google Sheets
2. Pilih item menu Extensions > Apps Script.
3. Hapus kode apa pun di editor skrip. Untuk fungsi DOUBLE di atas, cukup salin dan tempel kode ke editor skrip
4. 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

1. Buat atau buka spreadsheet di Google Sheets
2. Di bagian atas, klik Add-on > Dapatkan add-on
3. Setelah Google Workspace Marketplace terbuka, klik kotak telusur di pojok kanan atas
4. Ketik "fungsi khusus" dan tekan Enter
5. Jika Anda menemukan add-on fungsi kustom yang Anda minati, klik Instal untuk menginstalnya
6. Kotak dialog mungkin memberi tahu Anda bahwa add-on memerlukan otorisasi. Jika ya, baca pemberitahuan tersebut dengan saksama, lalu klik Izinkan
7. 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

1. Klik sel tempat Anda ingin menggunakan fungsi
2. 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
3. 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;
}

/**
 * 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;
}

/**
 * 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

/**
 * 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;
}

/**
 * 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;
}

/**
 * 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;
}

/**
 * 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;
}

/**
 * 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;
}

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 = '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;
}

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;
}

/**
 * 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 = '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;
}

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 = '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 penerapannya akan bervariasi bergantung pada perilaku fungsi.

Bagaimana Cara Melindungi 1 sheet agar tidak bisa diedit di Google Sheets?

Apa fungsi rumus countif() di Google Sheets?

Bagaimana cara mengurutkan lembar kerja?

Di mana rumus Google Sheet?