Luaskan Google Spreadsheet

Menggunakan Google Apps Script untuk memperluas Spreadsheet. Tambahkan menu kustom, dialog, dan sidebar ke Spreadsheet. Tulis fungsi kustom untuk Spreadsheet, dan integrasikan dengan layanan Google lainnya seperti Google Kalender, Google Drive, dan Gmail.

Sebagian besar skrip yang dirancang untuk Spreadsheet memanipulasi array untuk berinteraksi dengan sel, baris, dan kolom dalam spreadsheet. Jika Anda belum terbiasa dengan array di JavaScript, Codecademy menawarkan modul pelatihan yang bagus untuk array. Kursus ini tidak dikembangkan oleh dan tidak terkait dengan Google.

Untuk pengantar singkat tentang penggunaan Apps Script dengan Spreadsheet, lihat panduan memulai cepat 5 menit untuk Makro, Menu, dan Fungsi Kustom.

Mulai

Apps Script menyertakan API khusus untuk membuat, membaca, dan mengedit Spreadsheet secara terprogram. Apps Script berinteraksi dengan Spreadsheet dalam dua cara: skrip apa pun dapat membuat atau mengubah spreadsheet jika pengguna skrip memiliki izin yang sesuai untuk melakukannya, dan skrip juga dapat terikat ke spreadsheet. Skrip terikat memiliki kemampuan khusus untuk mengubah antarmuka pengguna atau merespons saat spreadsheet dibuka. Untuk membuat skrip terikat, pilih Ekstensi > Apps Script dari dalam Spreadsheet.

Spreadsheet service memperlakukan Spreadsheet sebagai petak, yang beroperasi dengan array dua dimensi. Untuk mengambil data dari spreadsheet, dapatkan akses ke spreadsheet tempat data disimpan, dapatkan rentang yang menyimpan data, lalu dapatkan nilai sel. Apps Script memfasilitasi akses data dengan membaca data terstruktur di spreadsheet dan membuat objek JavaScript untuk data tersebut.

Membaca data

Misalnya, Anda memiliki daftar nama produk dan nomor produk yang disimpan dalam spreadsheet, seperti yang ditunjukkan pada gambar berikut.

Contoh berikut menunjukkan cara mengambil dan mencatat nama produk dan nomor produk.

function logProductInfo() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const data = sheet.getDataRange().getValues();
  for (let i = 0; i < data.length; i++) {
    Logger.log('Product name: ' + data[i][0]);
    Logger.log('Product number: ' + data[i][1]);
  }
}

Melihat log

Untuk melihat data yang telah dicatat, di bagian atas editor skrip, klik Log eksekusi.

Menulis data

Untuk menyimpan data, seperti nama dan nomor produk baru ke spreadsheet, tambahkan kode berikut di akhir skrip.

function addProduct() {
  const sheet = SpreadsheetApp.getActiveSheet();
  sheet.appendRow(['Cotton Sweatshirt XL', 'css004']);
}

Kode sebelumnya menambahkan baris baru di bagian bawah spreadsheet, dengan nilai yang ditentukan. Jika Anda menjalankan fungsi ini, baris baru akan ditambahkan ke spreadsheet.

Menu kustom dan antarmuka pengguna

Sesuaikan Spreadsheet dengan menambahkan menu, dialog, dan sidebar kustom. Untuk mempelajari dasar-dasar pembuatan menu, lihat panduan menu. Untuk mempelajari cara menyesuaikan konten dialog, lihat panduan layanan HTML.

Lampirkan fungsi skrip ke gambar atau diagram dalam spreadsheet; fungsi dijalankan saat pengguna mengklik gambar atau diagram. Untuk mempelajari lebih lanjut, lihat Gambar dan Sketsa di Spreadsheet.

Jika Anda berencana memublikasikan antarmuka kustom sebagai bagian dari add-on, ikuti panduan gaya untuk konsistensi dengan gaya dan tata letak editor Spreadsheet.

Menghubungkan ke Google Formulir

Hubungkan Google Formulir dengan Spreadsheet melalui layanan Forms dan Spreadsheet. Fitur ini otomatis membuat Google Formulir berdasarkan data dalam spreadsheet. Apps Script juga memungkinkan Anda menggunakan pemicu, seperti onFormSubmit untuk melakukan tindakan tertentu setelah pengguna merespons formulir. Untuk mempelajari lebih lanjut cara menghubungkan Spreadsheet ke Formulir, coba Membuat dan Mengelola Respons untuk Formulir mulai cepat 5 menit.

Memformat data

Class Range memiliki metode seperti setBackground untuk mengakses dan mengubah format sel atau rentang sel. Contoh berikut menetapkan gaya font rentang:

function formatMySpreadsheet() {
  // Set the font style of the cells in the range of B2:C2 to be italic.
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheets()[0];
  const cell = sheet.getRange('B2:C2');
  cell.setFontStyle('italic');
}

Validasi data

Akses aturan validasi data yang ada di Spreadsheet atau buat aturan baru. Misalnya, contoh berikut menunjukkan cara menetapkan aturan validasi data yang hanya mengizinkan angka antara 1 dan 100 pada sel.

function validateMySpreadsheet() {
  // Set a rule for the cell B4 to be a number between 1 and 100.
  const cell = SpreadsheetApp.getActive().getRange('B4');
  const rule = SpreadsheetApp.newDataValidation()
     .requireNumberBetween(1, 100)
     .setAllowInvalid(false)
     .setHelpText('Number must be between 1 and 100.')
     .build();
  cell.setDataValidation(rule);
}

Untuk mengetahui detail selengkapnya tentang cara menggunakan aturan validasi data, lihat SpreadsheetApp.newDataValidation, DataValidationBuilder, dan Range.setDataValidation

Diagram

Menyematkan diagram dalam spreadsheet yang merepresentasikan data dalam rentang tertentu. Contoh berikut menghasilkan diagram batang sematan, dengan asumsi Anda memiliki data yang dapat dibuat diagram di sel A1:B15:

function newChart() {
  // Generate a chart representing the data in the range of A1:B15.
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheets()[0];

  const chart = sheet.newChart()
     .setChartType(Charts.ChartType.BAR)
     .addRange(sheet.getRange('A1:B15'))
     .setPosition(5, 5, 0, 0)
     .build();

  sheet.insertChart(chart);
}

Untuk mempelajari lebih lanjut cara menyematkan diagram ke spreadsheet, lihat EmbeddedChart dan alat pembuat diagram tertentu, seperti EmbeddedPieChartBuilder.

Fungsi kustom di Google Spreadsheet

Fungsi kustom mirip dengan fungsi spreadsheet bawaan seperti =SUM(A1:A5), kecuali Anda menentukan perilaku fungsi dengan Apps Script. Misalnya, Anda dapat membuat fungsi kustom, in2mm(), yang mengonversi nilai dari inci ke milimeter, lalu menggunakan formula di spreadsheet dengan mengetik =in2mm(A1) atau =in2mm(10) ke dalam sel.

Untuk mempelajari fungsi kustom lebih lanjut, coba Memulai Cepat Menu dan Fungsi Kustom selama 5 menit, atau lihat panduan fungsi kustom yang lebih mendalam.

Makro

Makro adalah cara lain untuk mengeksekusi kode Apps Script dari UI Spreadsheet. Tidak seperti fungsi kustom, Anda mengaktifkannya dengan pintasan keyboard atau melalui menu Spreadsheet. Untuk mengetahui informasi selengkapnya, lihat Makro Spreadsheet.

Add-on untuk Google Spreadsheet

Add-on adalah project Apps Script yang dikemas secara khusus yang berjalan di dalam Spreadsheet dan dapat diinstal dari toko add-on Spreadsheet. Jika Anda telah mengembangkan skrip untuk Spreadsheet dan ingin membagikannya, Apps Script memungkinkan Anda memublikasikan skrip sebagai add-on agar pengguna lain dapat menginstalnya.

Performa dan penskalaan

Seiring bertambahnya ukuran set data, Anda mungkin mengalami masalah performa. Untuk mengoptimalkan spreadsheet dan skrip Anda:

Ikuti praktik terbaik: Baca Panduan Praktik Terbaik untuk mendapatkan tips tentang meminimalkan panggilan layanan dan menggunakan operasi batch.
Mengoptimalkan formula: Jika spreadsheet Anda lambat karena formula yang rumit (seperti VLOOKUP, ARRAYFORMULA, atau IMPORTRANGE), pertimbangkan untuk menggunakan Apps Script guna melakukan penghitungan ini dalam memori dan menulis kembali hasilnya dalam batch.
Pertimbangkan alternatif database: Untuk set data yang sangat besar (mendekati 10 juta sel) atau entri data frekuensi tinggi (misalnya, banyak formulir yang terhubung), pertimbangkan untuk menggunakan Google Cloud SQL menggunakan JDBC atau BigQuery.

Pemicu

Skrip yang terikat ke file Spreadsheet dapat menggunakan pemicu sederhana seperti fungsi onOpen() dan onEdit() untuk merespons secara otomatis saat pengguna yang memiliki akses edit ke spreadsheet membuka atau mengedit spreadsheet. Seperti pemicu sederhana, pemicu yang dapat diinstal memungkinkan Spreadsheet menjalankan fungsi secara otomatis saat peristiwa tertentu terjadi. Namun, pemicu yang dapat diinstal menawarkan fleksibilitas yang lebih besar daripada pemicu sederhana dan mendukung peristiwa berikut: buka, edit, ubah, kirim formulir, dan berbasis waktu (jam).

Luaskan Google Spreadsheet Tetap teratur dengan koleksi Simpan dan kategorikan konten berdasarkan preferensi Anda.