Google Sheets adalah alat spreadsheet berbasis cloud yang kuat yang memungkinkan Anda melakukan hampir semua yang dapat Anda lakukan di Microsoft Excel. Tetapi kekuatan sebenarnya dari Google Sheets adalah fitur Google Scripting 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 cloud Google lainnya. Ini memungkinkan Anda mengotomatiskan masing-masing aplikasi, dan mengintegrasikan masing-masing aplikasi itu satu sama lain.
Pada artikel ini Anda akan belajar cara memulai dengan skrip Google Apps, membuat skrip dasar di Google Sheets untuk membaca dan menulis data sel, dan Google Sheets canggih yang paling efektif fungsi skrip.
Cara Membuat Skrip Google Apps
Anda dapat memulai sekarang membuat skrip Google Apps pertama dari dalam Google Sheets.
Untuk melakukan ini, pilih Alatdari menu, lalu Editor Skrip.
Ini membuka jendela editor skrip dan default ke fungsi yang disebut fungsi saya ()kuat. Di sinilah Anda dapat membuat dan menguji Google Script Anda.
In_content_1 all: [300x250] / dfp: [640x360]->Untuk mencobanya, coba buat fungsi skrip Google Sheets yang akan membaca data dari satu sel, melakukan perhitungan di dalamnya, dan menampilkan jumlah data ke sel lain.
Fungsi untuk mendapatkan data dari sel adalah fungsi getRange ()dan getValue (). Anda dapat mengidentifikasi sel dengan baris dan kolom. Jadi jika Anda memiliki nilai di baris 2 dan kolom 1 (kolom A), bagian pertama skrip Anda akan terlihat seperti ini:
function myFunction() {
var sheet = SpreadsheetApp.getActiveSheet();
var row = 2;
var col = 1;
var data = sheet.getRange(row, col).getValue();
}
Ini menyimpan nilai dari itu sel dalam variabel data. Anda dapat melakukan perhitungan pada data, dan kemudian menulis data itu ke sel lain. Jadi bagian terakhir dari fungsi ini adalah:
var results = data * 100;sheet.getRange(row, col+1).setValue(results); }
Saat Anda selesai menulis fungsi Anda, pilih ikon disk untuk menyimpan.
Pertama kali Anda menjalankan fungsi skrip Google Sheets baru seperti ini (dengan memilih ikon jalankan), Anda harus memberikan Otorisasi agar skrip berjalan di Akun Google Anda.
Izinkan izin untuk melanjutkan. Setelah skrip Anda berjalan, Anda akan melihat bahwa skrip menulis hasil perhitungan ke sel target.
Sekarang setelah Anda tahu cara menulis fungsi skrip Google Apps dasar, mari kita lihat beberapa fungsi yang lebih maju.
Gunakan getValues Untuk Memuat Array
Anda dapat mengambil konsep melakukan perhitungan pada data dalam spreadsheet Anda dengan skrip ke tingkat baru dengan menggunakan array. Jika Anda memuat variabel dalam skrip Google Apps menggunakan getValues, variabel tersebut akan berupa array yang dapat memuat beberapa nilai dari sheet.
function myFunction() {
var sheet = SpreadsheetApp.getActiveSheet();
var data = sheet.getDataRange().getValues();
Variabel data adalah multi-dimensi array yang menampung semua data dari sheet. Untuk melakukan perhitungan pada data, Anda menggunakan loop untuk. Penghitung for for akan bekerja melalui setiap baris, dan kolom tetap konstan, berdasarkan kolom tempat Anda ingin menarik data.
Dalam contoh lembar bentang kami, Anda dapat melakukan perhitungan pada tiga baris data sebagai berikut.
for (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 hasil diisi ke dalam kolom 2 di spreadsheet Anda.
Anda akan melihat bahwa referensi sel dan baris dalam variabel array berbeda dari dengan fungsi getRange.
data [i] [0]merujuk pada dimensi array di mana dimensi pertama adalah baris dan yang kedua adalah kolom. Keduanya dimulai dari nol.
getRange (i + 1, 2)merujuk ke baris kedua ketika saya = 1 (karena baris 1 adalah tajuk), dan 2 adalah kolom kedua tempat hasil disimpan.
Gunakan appendRow Untuk Menulis Hasil
Bagaimana jika Anda memiliki spreadsheet tempat Anda ingin menulis data ke yang baru baris alih-alih kolom baru?
Ini mudah dilakukan dengan fungsi appendRow. Fungsi ini tidak akan mengganggu data yang ada di sheet. Itu hanya akan menambahkan baris baru ke sheet yang ada.
Sebagai contoh, buat fungsi yang akan dihitung dari 1 hingga 10 dan tampilkan penghitung dengan kelipatan 2 dalam Penghitung.
Fungsi ini akan terlihat seperti ini:
function myFunction() {
var sheet = SpreadsheetApp.getActiveSheet();
for (var i = 1; i<11; i++) {
var result = i * 2;
sheet.appendRow([i,result]);
}
}
Inilah hasil ketika Anda menjalankan fungsi ini.
Memproses Umpan RSS Dengan URLFetchApp
Anda dapat menggabungkan fungsi skrip Google Sheets sebelumnya dan URLFetchAppuntuk menarik umpan RSS dari situs web apa pun, dan menulis baris ke spreadsheet untuk setiap artikel yang baru-baru ini diterbitkan ke situs web itu .
Ini pada dasarnya adalah metode DIY untuk membuat spreadsheet pembaca RSS feed Anda sendiri!
Skrip untuk melakukan ini juga tidak terlalu rumit.
function myFunction() {
var sheet = SpreadsheetApp.getActiveSheet();
var item, date, title, link, desc;
var txt = UrlFetchApp.fetch("https://www.topsecretwriters.com/rss").getContentText();
var doc = Xml.parse(txt, false);
title = doc.getElement().getElement("channel").getElement("title").getText();
var items = doc.getElement().getElement("channel").getElements("item");
// Parsing single items in the RSS Feed
for (var i in items) {
item = items[i];
title = item.getElement("title").getText();
link = item.getElement("link").getText();
date = item.getElement("pubDate").getText();
desc = item.getElement("description").getText();
sheet.appendRow([title,link,date,desc]);
}
}
Seperti yang Anda lihat, Xml.parsemenarik setiap item dari umpan RSS dan memisahkan setiap baris ke dalam judul, tautan, tanggal dan deskripsi.
Menggunakan fungsi appendRow, Anda dapat menempatkan item ini ke dalam kolom yang sesuai untuk setiap item di umpan RSS.
Output di sheet Anda akan terlihat sesuatu seperti ini:
Sebagai gantinya menanamkan URL umpan RSS ke dalam skrip, Anda dapat memiliki bidang di sheet Anda dengan URL, dan kemudian memiliki beberapa sheet - satu untuk setiap situs web yang ingin Anda pantau.
String Gabungan dan Tambahkan Pengembalian Gerbong
Anda bisa melangkah lebih jauh dengan spreadsheet RSS dengan menambahkan beberapa fungsi manipulasi teks, dan kemudian menggunakan fungsi email untuk mengirim sendiri email dengan ringkasan semua posting baru kepada Anda di umpan RSS situs.
Untuk melakukan ini, di bawah skrip yang Anda buat di bagian sebelumnya, Anda ingin menambahkan beberapa skrip yang akan mengekstraksi semua informasi dalam spreadsheet.
Anda ingin membuat baris subjek dan badan teks email dengan menguraikan semua informasi dari array "item" yang sama dengan 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 subject = ‘Latest 10 articles published at mysite.com’
var message = ‘’
Kemudian, di akhir "item" untuk loop (tepat setelah fungsi appendRow), tambahkan baris berikut.
message = message + title + '\n' + link + '\n' + date + '\n' + desc + '\n' + '\n \n';
Simbol "+" akan menyatukan keempat item bersama-sama diikuti oleh "\ n ”Untuk carriage return setelah setiap baris. Di akhir setiap blok data judul, Anda ingin dua pengembalian carriage untuk badan email yang diformat dengan baik.
Setelah semua baris diproses, variabel "body" menyimpan seluruh string pesan email. Sekarang Anda siap mengirim email!
Cara Mengirim Email di Google Apps Script
Bagian selanjutnya dari Google Script Anda adalah mengirim "subjek" dan "tubuh" melalui email. Melakukan hal ini di Google Script sangat mudah.
var emailAddress = [email protected];
MailApp.sendEmail(emailAddress, subject, message);
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, satu baris dengan fungsi sendEmail memungkinkan Anda kirim email apa saja hanya dengan alamat email, baris subjek, dan teks isi.
Beginilah tampilan email yang dihasilkan. .
Menggabungkan kemampuan untuk mengekstrak umpan RSS sebuah situs web, simpan di Google Sheet, dan kirimkan kepada Anda sendiri dengan menyertakan tautan URL, 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.