5 Google Sheets Script Functions yang perlu Anda ketahui

Google Sheets adalah alat spreadsheet berbasis cloud yang kuat yang memungkinkan Anda melakukan hampir semua yang dapat Anda lakukan di Microsoft Excel. Tapi kekuatan sebenarnya dari Google Sheets adalah fitur skrip Google yang menyertainya.

Google Apps Scripting adalah alat skrip latar belakang yang berfungsi tidak hanya di Google Sheets tetapi juga Google Documents, Gmail, Google Analytics, dan hampir setiap layanan Google Cloud lainnya. Ini memungkinkan Anda mengotomatiskan aplikasi individual tersebut, dan mengintegrasikan masing -masing aplikasi tersebut satu sama lain.

Ini membuka jendela editor skrip dan default ke fungsi yang disebut myfunction (). Di sinilah Anda dapat membuat dan menguji skrip google Anda.

Untuk mencobanya, coba buat fungsi skrip Google Sheets yang akan membaca data dari satu sel, melakukan perhitungan di atasnya, dan mengeluarkan jumlah data ke sel lain.

Fungsi untuk mendapatkan data dari sel adalah getRange () Dan getValue () fungsi. Anda dapat mengidentifikasi sel demi baris dan kolom. Jadi jika Anda memiliki nilai di baris 2 dan kolom 1 (kolom A), bagian pertama dari skrip Anda akan terlihat seperti ini:

fungsi myfunction () var sheet = spreadsheetapp.getActivesheet (); var row = 2; var col = 1; var data = sheet.GetRange (Row, Col).getValue (); 

Ini menyimpan nilai dari sel itu di data variabel. Anda dapat melakukan perhitungan pada data, dan kemudian menulis data itu ke sel lain. Jadi bagian terakhir dari fungsi ini adalah:

 Hasil var = data * 100; lembaran.getRange (baris, col+1).setValue (hasil); 

Setelah selesai menulis fungsi Anda, pilih ikon disk untuk menyimpan. 

Pertama kali Anda menjalankan fungsi skrip Google Sheets baru seperti ini (dengan memilih ikon run), Anda harus memberikan otorisasi untuk skrip untuk dijalankan di akun Google Anda.

Izinkan izin untuk melanjutkan. Setelah skrip Anda berjalan, Anda akan melihat bahwa skrip menulis hasil perhitungan ke sel target.

Sekarang Anda tahu cara menulis fungsi skrip Google Apps dasar, mari kita lihat beberapa fungsi yang lebih canggih.

Gunakan GetValues ​​untuk memuat array

Anda dapat mengambil konsep melakukan perhitungan data dalam spreadsheet Anda dengan skrip ke tingkat baru dengan menggunakan array. Jika Anda memuat variabel dalam skrip Google Apps menggunakan GetValues, variabel akan menjadi array yang dapat memuat beberapa nilai dari lembar.

fungsi myfunction () var sheet = spreadsheetapp.getActivesheet (); var data = sheet.getDataRange ().getValues ​​();

Variabel data adalah array multi-dimensi yang menyimpan semua data dari lembar. Untuk melakukan perhitungan pada data, Anda menggunakan a untuk lingkaran. Penghitung loop for akan bekerja melalui setiap baris, dan kolom tetap konstan, berdasarkan kolom tempat Anda ingin menarik data.

Dalam contoh spreadsheet kami, Anda dapat melakukan perhitungan pada tiga baris data sebagai berikut.

untuk (var i = 1; i < data.length; i++)  var result = data[i][0] * 100; sheet.getRange(i+1, 2).setValue(result);   

Simpan dan jalankan skrip ini seperti yang Anda lakukan di atas. Anda akan melihat bahwa semua hasilnya diisi ke kolom 2 di spreadsheet Anda.

Anda akan melihat bahwa merujuk sel dan baris dalam variabel array berbeda dari dengan fungsi getRange. 

Data [i] [0] mengacu pada dimensi array di mana dimensi pertama adalah baris dan yang kedua adalah kolom. Keduanya dimulai dari nol.

getRange (i+1, 2) mengacu pada baris kedua ketika i = 1 (karena baris 1 adalah header), dan 2 adalah kolom kedua di mana hasilnya disimpan.

Gunakan Appendrow untuk menulis hasil

Bagaimana jika Anda memiliki spreadsheet di mana Anda ingin menulis data ke baris baru alih -alih kolom baru?

Ini mudah dilakukan dengan Appendrow fungsi. Fungsi ini tidak akan mengganggu data yang ada di lembar. Itu hanya akan menambahkan baris baru ke lembar yang ada.

Sebagai contoh, buat fungsi yang akan dihitung dari 1 hingga 10 dan menunjukkan penghitung dengan kelipatan 2 di a Menangkal kolom.

Fungsi ini akan terlihat seperti ini:

fungsi myfunction () var sheet = spreadsheetapp.getActivesheet (); untuk (var i = 1; i<11; i++)  var result = i * 2; sheet.appendRow([i,result]);  

Berikut adalah hasilnya saat Anda menjalankan fungsi ini.

Proses RSS Feed dengan UrlfetchApp

Anda dapat menggabungkan fungsi skrip Google Sheets sebelumnya dan UrlfetchApp Untuk menarik umpan RSS dari situs web mana pun, dan menulis barisan ke spreadsheet untuk setiap artikel yang baru -baru ini diterbitkan ke situs web itu.

Ini pada dasarnya adalah metode DIY untuk membuat spreadsheet pembaca umpan RSS Anda sendiri!

Skrip untuk melakukan ini juga tidak terlalu rumit.

fungsi myfunction () var sheet = spreadsheetapp.getActivesheet (); var item, tanggal, judul, tautan, desc; var txt = urlfetchapp.fetch ("https: // www.TopsecretWriters.com/rss ").getContentText (); var doc = xml.parse (txt, false); judul = DOC.GetElement ().GetElement ("Channel").GetElement ("Judul").getText (); var item = doc.GetElement ().GetElement ("Channel").pembebasan ("item"); // parsing item tunggal di feed RSS untuk (var i dalam item) item = item [i]; title = item.GetElement ("Judul").getText (); tautan = item.GetElement ("Link").getText (); Tanggal = Item.GetElement ("pubdate").getText (); desc = item.GetElement ("Deskripsi").getText (); lembaran.AppendRow ([judul, tautan, tanggal, desc]); 

Seperti yang Anda lihat, Xml.parse menarik setiap item dari umpan RSS dan memisahkan setiap baris ke dalam judul, tautan, tanggal dan deskripsi. 

Menggunakan Appendrow Fungsi, Anda dapat memasukkan item -item ini ke kolom yang sesuai untuk setiap item tunggal di Feed RSS.

Output di lembar Anda akan terlihat seperti ini:

Alih -alih menanamkan URL umpan RSS ke dalam skrip, Anda dapat memiliki bidang di lembar Anda dengan URL, dan kemudian memiliki beberapa lembar - satu untuk setiap situs web yang ingin Anda pantau.

Gabungan string dan tambahkan pengembalian kereta

Anda dapat mengambil spreadsheet RSS selangkah lebih maju dengan menambahkan beberapa fungsi manipulasi teks, dan kemudian menggunakan fungsi email untuk mengirimkan email dengan ringkasan semua posting baru di umpan RSS situs tersebut.

Untuk melakukan ini, di bawah skrip yang Anda buat di bagian sebelumnya, Anda ingin menambahkan beberapa skrip yang akan mengekstrak semua informasi dalam spreadsheet. 

Anda ingin membangun baris subjek dan badan teks email dengan menguraikan semua informasi dari array "item" yang sama yang Anda gunakan untuk menulis data RSS ke spreadsheet. 

Untuk melakukan ini, inisialisasi subjek dan pesan dengan menempatkan baris berikut sebelum "item" untuk loop.

var subjek = '10 artikel terbaru yang diterbitkan di MySite.com '
var pesan = "

Kemudian, di akhir "item" untuk loop (tepat setelah fungsi AppendRow), tambahkan baris berikut.

pesan = pesan + judul + '\ n' + tautan + '\ n' + tanggal + '\ n' + desc + '\ n' + '\ n \ n';

Simbol "+" akan menggabungkan keempat item bersama diikuti oleh "\ n" untuk pengembalian kereta setelah setiap baris. Di akhir setiap blok data judul, Anda ingin dua pengembalian kereta untuk badan email yang diformat dengan baik.

Setelah semua baris diproses, variabel "tubuh" memegang seluruh string pesan email. Sekarang Anda siap mengirim email!

Cara mengirim email di skrip Google Apps

Bagian selanjutnya dari skrip Google Anda adalah mengirim "subjek" dan "tubuh" melalui email. Melakukan ini di Google Script sangat mudah.

var emailAddress = [email protected];
Mailapp.sendeMail (emailAddress, subjek, pesan);

Mailapp adalah kelas yang sangat nyaman di dalam skrip Google Apps yang memberi Anda akses ke layanan email akun Google Anda untuk mengirim atau menerima email. Berkat ini, baris tunggal dengan fungsi sendeMail memungkinkan Anda mengirim email apa pun hanya dengan alamat email, baris subjek, dan teks tubuh.

Seperti inilah email yang dihasilkan. 

Menggabungkan kemampuan untuk mengekstrak umpan RSS situs web, menyimpannya di lembar Google, dan mengirimkannya kepada diri Anda sendiri dengan tautan URL yang disertakan, membuatnya sangat nyaman untuk mengikuti konten terbaru untuk situs web mana pun.

Ini hanyalah salah satu contoh kekuatan yang tersedia di skrip Google Apps untuk mengotomatisasi tindakan dan mengintegrasikan beberapa layanan cloud.