Google Spreadsheet^{(Google Sheets)} adalah alat spreadsheet berbasis cloud yang kuat yang memungkinkan Anda melakukan hampir semua hal yang dapat Anda lakukan di Microsoft Excel . Tetapi kekuatan sebenarnya dari Google Sheets adalah fitur Google Scripting yang menyertainya.

Skrip Google Apps^{(Google Apps)} adalah alat skrip latar belakang yang berfungsi tidak hanya di Google Spreadsheet^{(in Google Sheets)} tetapi juga Google Documents, Gmail, Google Analytics , dan hampir semua layanan cloud Google lainnya. ^(Google)Ini memungkinkan Anda mengotomatiskan aplikasi individual tersebut, dan mengintegrasikan masing-masing aplikasi tersebut satu sama lain.

Dalam artikel ini Anda akan mempelajari cara memulai skrip Google Apps , membuat skrip dasar di Google Spreadsheet^{(Google Sheets)} untuk membaca dan menulis data sel, dan fungsi skrip Google Spreadsheet^{(Google Sheets)} lanjutan yang paling efektif .

Cara Membuat Skrip Google Apps^{(How to Create a Google Apps Script)}

Anda dapat mulai sekarang membuat skrip Google Apps pertama Anda dari dalam ^{(Google Apps)}Google Spreadsheet^{(Google Sheets)} . 

Untuk melakukannya, pilih Alat^(Tools) dari menu, lalu Editor Skrip^{(Script Editor)} .

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

Untuk mencobanya, coba buat fungsi skrip Google Sheets yang akan membaca data dari satu sel, melakukan perhitungan padanya, 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 sel itu dalam variabel data . Anda dapat melakukan penghitungan pada data, lalu menulis data tersebut ke sel lain. Jadi bagian terakhir dari fungsi ini adalah:

   var results = data * 100;
   sheet.getRange(row, col+1).setValue(results);
}

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

Saat pertama kali menjalankan fungsi skrip Google Spreadsheet^{(Google Sheets)} baru seperti ini (dengan memilih ikon jalankan), Anda harus memberikan Otorisasi^{(Authorization)} agar skrip dapat dijalankan di Akun Google^{(Google Account)} Anda .

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

Sekarang setelah Anda mengetahui cara menulis fungsi skrip Google Apps dasar , mari kita lihat beberapa fungsi lanjutan.

Gunakan getValues ​​Untuk Memuat Array^{(Use getValues To Load Arrays)}

Anda dapat mengambil konsep melakukan perhitungan pada data di spreadsheet Anda dengan skrip ke tingkat yang baru dengan menggunakan array. Jika Anda memuat variabel dalam skrip Google Apps menggunakan getValues, variabel tersebut akan berupa larik yang dapat memuat beberapa nilai dari sheet.

function myFunction() {
   var sheet = SpreadsheetApp.getActiveSheet();
   var data = sheet.getDataRange().getValues();

Variabel data adalah array multidimensi yang menampung semua data dari sheet. Untuk melakukan perhitungan pada data, Anda menggunakan for loop. Penghitung untuk loop 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.

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

Simpan^(Save) 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 mereferensikan sel dan baris dalam variabel array berbeda 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 tempat hasil disimpan.

Gunakan appendRow Untuk Menulis Hasil^{(Use appendRow To Write Results)}

Bagaimana jika Anda memiliki spreadsheet tempat Anda ingin menulis data ke baris baru, bukan 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 lembar yang ada.

Sebagai contoh, buatlah sebuah fungsi yang akan menghitung dari 1 hingga 10 dan menampilkan penghitung dengan kelipatan 2 pada kolom Penghitung .^(Counter)

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

Berikut adalah hasil ketika Anda menjalankan fungsi ini.

Proses Umpan RSS Dengan URLFetchApp^{(Process RSS Feeds With URLFetchApp)}

Anda dapat menggabungkan fungsi skrip Google Sheets sebelumnya dan ^{(Google Sheets)}URLFetchApp untuk menarik umpan RSS dari situs web mana 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 umpan RSS Anda sendiri!^(RSS)

Script 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.parse menarik setiap item dari RSS feed dan memisahkan setiap baris ke dalam judul, link, tanggal dan deskripsi. 

Menggunakan fungsi appendRow , Anda dapat menempatkan item ini ke dalam kolom yang sesuai untuk setiap item dalam umpan RSS .

Output di lembar Anda akan terlihat seperti ini:

Alih-alih menyematkan 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.

Menggabungkan String^{(Concatenate Strings)} dan Menambahkan^(Add) Pengembalian Kereta^{(Carriage Return)}

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

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 akan ingin membuat baris subjek dan badan teks email dengan mem-parsing semua informasi dari larik "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 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 menggabungkan keempat item bersama-sama diikuti oleh “ ” untuk carriage return setelah setiap baris. Di akhir setiap blok data judul, Anda akan menginginkan dua carriage return 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^{(How To Send Email In Google Apps Script)}

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

var emailAddress = [email protected];
MailApp.sendEmail(emailAddress, subject, message);

MailApp adalah kelas^(MailApp) 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 mengirim email apa pun^{(send any email)} hanya dengan alamat email, baris subjek, dan teks isi.

Seperti inilah tampilan email yang dihasilkan. 

Menggabungkan kemampuan untuk mengekstrak umpan RSS situs web , menyimpannya di Google Sheet , dan mengirimkannya ke diri 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 mengotomatiskan tindakan dan mengintegrasikan beberapa layanan cloud.

5 Google Sheets Script Functions You Need to Know

Google Sheets is a powerful cloud-bаsed spreadsheet tool that lets you do nearly everything you could do in Microsоft Excel. Βut the real power of Google Sheets is the Google Scriрting feature that comes with it.

Google Apps scripting is a background scripting tool that works not only in Google Sheets but also Google Docs, Gmail, Google Analytics, and nearly every other Google cloud service. It lets you automate those individual apps, and integrate each of those apps with each other.

In this article you’ll learn how to get started with Google Apps scripting, creating a basic script in Google Sheets to read and write cell data, and the most effective advanced Google Sheets script functions.

How to Create a Google Apps Script

You can get started right now creating your first Google Apps script from inside Google Sheets. 

To do this, select Tools from the menu, then Script Editor.

This opens the script editor window and defaults to a function called myfunction(). This is where you can create and test your Google Script.

To give it a shot, try creating a Google Sheets script function that will read data from one cell, perform a calculation on it, and output the data amount to another cell.

The function to get data from a cell is the getRange() and getValue() functions. You can identify the cell by row and column. So if you have a value in row 2 and column 1 (the A column), the first part of your script will look like this:

function myFunction() {
   var sheet = SpreadsheetApp.getActiveSheet();
   var row = 2;
   var col = 1;
   var data = sheet.getRange(row, col).getValue();
}

This stores the value from that cell in the data variable. You can perform a calculation on the data, and then write that data to another cell. So the last part of this function will be:

   var results = data * 100;
   sheet.getRange(row, col+1).setValue(results);
}

When you’re done writing your function, select the disk icon to save. 

The first time you run a new Google Sheets script function like this (by selecting the run icon), you’ll need to provide Authorization for the script to run on your Google Account.

Allow permissions to continue. Once your script runs, you’ll see that the script wrote the calculation results to the target cell.

Now that you know how to write a basic Google Apps script function, let’s take a look at some more advanced functions.

Use getValues To Load Arrays

You can take the concept of doing calculations on data in your spreadsheet with scripting to a new level by using arrays. If you load a variable in Google Apps script using getValues, the variable will be an array that can load multiple values from the sheet.

function myFunction() {
   var sheet = SpreadsheetApp.getActiveSheet();
   var data = sheet.getDataRange().getValues();

The data variable is a multi-dimensional array that holds all of the data from the sheet. To perform a calculation on the data, you use a for loop. The counter of the for loop will work through each row, and the column remains constant, based on the column where you want to pull the data.

In our example spreadsheet, you can perform calculations on the three rows of data as follows.

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

Save and run this script just as you did above. You’ll see that all of the results are filled into column 2 in your spreadsheet.

You’ll notice that referencing a cell and row in an array variable is different than with a getRange function. 

data[i][0] refers to the array dimensions where the first dimension is the row and the second is the column. Both of these start at zero.

getRange(i+1, 2) refers to the second row when i=1 (since row 1 is the header), and 2 is the second column where the results are stored.

Use appendRow To Write Results

What if you have a spreadsheet where you want to write data into a new row instead of a new column?

This is easy to do with the appendRow function. This function won’t bother any existing data in the sheet. It’ll just append a new row to the existing sheet.

As an example, make a function that will count from 1 to 10 and show a counter with multiples of 2 in a Counter column.

This function would look like this:

function myFunction() {
   var sheet = SpreadsheetApp.getActiveSheet();

   for (var i = 1; i<11; i++) {
      var result = i * 2;
     sheet.appendRow([i,result]);
   }
}

Here are the results when you run this function.

Process RSS Feeds With URLFetchApp

You could combine the previous Google Sheets script function and the URLFetchApp to pull the RSS feed from any website, and write a row to a spreadsheet for every article recently published to that website.

This is basically a DIY method to create your own RSS feed reader spreadsheet!

The script to do this isn’t too complicated either.

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

As you can see, Xml.parse pulls each item out of the RSS feed and separates each line into the title, link, date and description. 

Using the appendRow function, you can put these items into appropriate columns for every single item in the RSS feed.

The output in your sheet will look something like this:

Instead of embedding the RSS feed URL into the script, you could have a field in your sheet with the URL, and then have multiple sheets – one for every website you want to monitor.

Concatenate Strings and Add a Carriage Return

You could take the RSS spreadsheet a step further by adding some text manipulation functions, and then use email functions to send yourself an email with a summary of all new posts in the site’s RSS feed.

To do this, under the script you created in the previous section, you’ll want to add some scripting that will extract all of the information in the spreadsheet. 

You’ll want to build the subject line and the email text body by parsing together all of the information from the same “items” array that you used to write the RSS data to the spreadsheet. 

To do this, initialize the subject and message by placing the following lines before the “items” For loop.

var subject = ‘Latest 10 articles published at mysite.com’
var message = ‘’

Then, at the end of the “items” for loop (right after the appendRow function), add the following line.

message = message + title + '\n' + link + '\n' + date + '\n' + desc + '\n' + '\n \n';

The “+” symbol  will concatenate all four items together followed by “\n” for a carriage return after each line. At the end of each title data block, you’ll want two carriage returns for a nicely formatted email body.

Once all rows are processed, the “body” variable holds the entire email message string. Now you’re ready to send the email!

How To Send Email In Google Apps Script

The next section of your Google Script will be to send the “subject” and the “body” via email. Doing this in Google Script is very easy.

var emailAddress = [email protected];
MailApp.sendEmail(emailAddress, subject, message);

The MailApp is a very convenient class inside of Google Apps scripts that gives you access to your Google Account’s email service to send or receive emails. Thanks to this, the single line with the sendEmail function lets you send any email with just the email address, subject line, and body text.

This is what the resulting email will look like. 

Combining the ability to extract a website’s RSS feed, store it in a Google Sheet, and send it to yourself with URL links included, makes it very convenient to follow the latest content for any website.

This is just one example of the power that’s available in Google Apps scripts to automate actions and integrate multiple cloud services.

Related posts

• 4 Cara Mengonversi Excel ke Google Spreadsheet

• Google Spreadsheet vs Microsoft Excel – Apa Perbedaannya?

• Cara Menghapus Batas Tabel di Google Documents

• Bagaimana Obrolan Google Documents Membantu Anda Berkolaborasi di Dokumen

• Cara Menyisipkan Tanda Air di Word dan Google Documents

• Cara Memperbaiki Outlook Terjebak Saat Memuat Profil

• Google Documents vs Microsoft Word – Apa Perbedaannya?

• Cara Membuat Dokumen PDF di Microsoft Office

• Cara Memperbaiki Kesalahan Bookmark Tidak Didefinisikan di Word

• Cara Menggunakan Fitur Sel Bicara Excel

• Cara Menggunakan Analisis Bagaimana-Jika Excel

• Microsoft Outlook Tidak Bisa Dibuka? 10 Cara untuk Memperbaiki

• Bagaimana Menambahkan Nomor Slide ke Presentasi PowerPoint

• Cara Memperbaiki Baris Di Excel

• Cara Menemukan dan Mengganti Kata di MS Word dan Google Docs

• Cara Memperbaiki Kesalahan #N/A dalam Rumus Excel seperti VLOOKUP

• Ubah ke Orientasi Lanskap di Google Documents

• Sisipkan Bullet di Tengah Kalimat di Word

• 7 Alternatif untuk PowerPoint yang Dapat Anda Gunakan Secara Online

• Perbaiki Pesan Kesalahan "Tidak Dapat Memverifikasi Lisensi" MS Office