Google Apps Script Membaca Google Sheets
Google Apps Script dapat diaplikasikan sebagai pengaya pada GSuite, semisal Google Sheets, terutama saat fitur standar yang disediakan tidak lagi memadai untuk kondisi kita. Pada tulisan ini kita akan belajar membaca data pada Google Sheets dan kemudian menuliskannya lagi pada file Google Sheets baru dan, sebagai bonus, menuliskannya pada file Google Docs.
Kode Lima Detik
var datumMonth = SpreadsheetApp.getActiveSheet().getName();
var timeDict = {};
var dateDict = {};
var data = {};
var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getDataRange();
var allData = range.getValues();
var ui = SpreadsheetApp.getUi();
function readDate() {
for (let c=1; c<allData[1].length; c++) {
dateDict[c] = allData[1][c];
}
}
function readTime() {
for (let r=2; r<allData.length; r++) {
timeDict[r] = allData[r][0];
}
}
function readData() {
for (const [dKey, dValue] of Object.entries(dateDict)) {
let datum = {};
for (const[tKey, tValue] of Object.entries(timeDict)) {
datum[tValue] = sheet.getRange(parseInt(tKey) + 1, parseInt(dKey) + 1).getValue();
}
data[dKey] = datum;
}
}
function createSheets() {
let newFile = SpreadsheetApp.create(datumMonth + '_new');
// create sheets
for (const [dKey, dValue] of Object.entries(data)) {
let newSheet = newFile.insertSheet(dKey);
newSheet.activate();
newSheet.getRange('A1').setValue('Waktu');
newSheet.getRange('B1').setValue('Kegiatan');
let r = 2;
for (const [tKey, tValue] of Object.entries(dValue)) {
newSheet.getRange('A' + r).setValue(tKey);
newSheet.getRange('B' + r).setValue(tValue);
r++;
}
}
// remove unnecessary sheet
let sheet1 = newFile.getSheetByName('Sheet1');
newFile.deleteSheet(sheet1);
}
function createDocs() {
let newFile = DocumentApp.create(datumMonth + '_new');
let body = newFile.getBody();
for (const [dKey, dValue] of Object.entries(data)) {
let par = 'Kegiatan pada tanggal ' + dKey;
body.appendParagraph(par);
// table
let table = [];
table.push(['Waktu', 'Kegiatan']);
for (const [tKey, tValue] of Object.entries(dValue)) {
table.push([tKey, tValue]);
}
body.appendTable(table);
// new page
body.appendPageBreak();
}
}
function myFunction() {
readDate();
readTime();
readData();
createSheets();
createDocs();
}
Latar Belakang
Kebutuhan manusia yang teramat beragam menyulitkan pembuat program generik, seperti spreadsheets menyediakan fitur yang dapat menjadi obat bagi semua permasalahan pengguna. Karena itu pembuat program menyediakan fasilitas untuk pengguna menambahkan sendiri fungsi/fitur/menu yang sesuai dengan kebutuhan.
Saat kita menggunakan Google Sheets, fasilitas tersebut bernama Google Apps Script (GAS).
Kita sudah berkenalan dengan GAS, melalui kegiatan membuat sebuah file Google Sheets baru, dari tidak ada menjadi ada. Pada tulisan ini kita akan melangkah lebih jauh, membaca file Sheets kemudian menuliskannya kembali pada file Sheets lain dan juga menulis pada file Google Docs.
Studi kasus yang kita lakukan adalah mengonversikan daftar kegiatan harian dari semula seperti ini.
Menjadi sebuah file Google Sheets, dengan masing-masing tanggal memiliki sheet sendiri, seperti ini.
Juga menjadi file Google Docs seperti ini, dengan masing-masing tanggal menjadi sebuah halaman tersendiri, seperti ini.
Google Sheets
Google Sheets adalah sebuah aplikasi spreadsheet online gratis. Teknologi ini adalah substitusi dari Microsoft Excel. Jika membandingkan keduanya, terdapat beberapa keunggulan bawaan Google Sheets, misal gratis, dapat diakses di mana saja dan mudah saat berkolaborasi.
Tentu ada bagian dimana Google Sheets lebih sulit ditangani ketimbang saat menggunakan Ms. Excel.
Langkah Kerja
Script editor
Untuk mengakses GAS pada Google Sheets adalah melalui menu Tools > Script editor.
Kita akan mendapatkan sebuah halaman baru berisi IDE seperti ini.
Jangan lupa untuk mengubah nama project di pojok kiri atas, yang semula bernama Untitled project.
Kode
Kode yang kita gunakan terdiri dari 6 fungsi dengan masing-masing penjelasan di bawah ini.
Ambil Data per Tanggal
var datumMonth = SpreadsheetApp.getActiveSheet().getName();
var timeDict = {};
var dateDict = {};
var data = {};
var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getDataRange();
var allData = range.getValues();
var ui = SpreadsheetApp.getUi();
function readDate() {
for (let c=1; c<allData[1].length; c++) {
dateDict[c] = allData[1][c];
}
}
Tujuan fungsi readDate adalah menyimpan daftar tanggal yang ada dalam sebuah sheet. Seperti pengetahuan bersama dalam tiap bulan mungkin terdapat 28 hari sampai 31 hari. Karena ketidakpastian tersebut, fungsi readDate perlu ada.
Cara mendapatkan daftar tanggal adalah dengan mengambil sebagian data dari variabel allData. Variabel allData sendiri adalah nilai pada semua cell yang ada pada sheet aktif.
Isi dari variabel allData adalah sebagai berikut (Jika belum mengetahui cara melihat isi dari suatu variabel pada GAS, silahkan mengikuti 3 alternatif pada halaman kedua tulisan ini).
Daftar tanggal ada pada Elemen ke-1, variabel allData.
allData[1]
Variabel dateDict menampung elemen ke-1 sampai terakhir dari variabel allData[1]. Karena elemen ke-0 adalah teks “Waktu/Tanggal” yang tidak kita perlukan.
Ambil Data per Waktu (dalam Satu Hari)
var datumMonth = SpreadsheetApp.getActiveSheet().getName();
var timeDict = {};
var dateDict = {};
var data = {};
var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getDataRange();
var allData = range.getValues();
var ui = SpreadsheetApp.getUi();
function readDate() {/* code */}
function readTime() {
for (let r=2; r<allData.length; r++) {
timeDict[r] = allData[r][0];
}
}
Fungsi readTime bertujuan mendapatkan daftar waktu tiap hari, yang ada di kolom A, baris ke-3 sampai selesai.
Gabungkan Data Tanggal dan Waktu
var datumMonth = SpreadsheetApp.getActiveSheet().getName();
var timeDict = {};
var dateDict = {};
var data = {};
var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getDataRange();
var allData = range.getValues();
var ui = SpreadsheetApp.getUi();
function readDate() {/* code */}
function readTime() {/* code */}
function readData() {
for (const [dKey, dValue] of Object.entries(dateDict)) {
let datum = {};
for (const[tKey, tValue] of Object.entries(timeDict)) {
datum[tValue] = sheet.getRange(parseInt(tKey) + 1, parseInt(dKey) + 1).getValue();
}
data[dKey] = datum;
}
}
Dua fungsi sebelumnya mengisi variabel dateDict dan timeDict sebagai sebuah dictionary, yang tidak dimulai dengan 0. Di bagian selanjutnya kita akan tahu mengapa begitu.
Fungsi readData menggunakan fungsi getRange untuk mendapatkan data mulai baris 3, kolom B hingga selesai.
Fungsi getRange dapat diisi dengan parameter index baris (row) dan index kolom (column).
Index baris atau kolom pada fungsi getRange tersebut dimulai dari 1.
Sehingga untuk mendapatkan isi dari cell B1, gunakan fungsi getRange(1, 2).
Variabel tKey adalah key dari dictionary timeDict, yang dimulai dari 2.
Karena data mulai pada baris ke-3 maka tKey perlu ditambah 1.
Begitu pula variabel dKey adalah key dari dictionary dateDict, yang dimulai dari 1.
Karena kolom yang digunakan adalah kolom B maka dKey perlu ditambah 1.
Sehingga jika menggunakan Bahasa Indonesia, fungsi readData mengisi variabel data dengan nilai dari cell pada baris 3, kolom B hingga cell pada baris 4 (atau berapapun baris terakhir), kolom AF (atau kolom apapun yang ada paling kanan).
Sampai di sini, variabel data telah menampung semua yang kita perlukan. Sekarang tinggal menuliskannya pada media lain.
Buat File Google Sheets
var datumMonth = SpreadsheetApp.getActiveSheet().getName();
var timeDict = {};
var dateDict = {};
var data = {};
var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getDataRange();
var allData = range.getValues();
var ui = SpreadsheetApp.getUi();
function readDate() {/* code */}
function readTime() {/* code */}
function readData() {/* code */}
function createSheets() {
let newFile = SpreadsheetApp.create(datumMonth + '_new');
// create sheets
for (const [dKey, dValue] of Object.entries(data)) {
let newSheet = newFile.insertSheet(dKey);
newSheet.activate();
newSheet.getRange('A1').setValue('Waktu');
newSheet.getRange('B1').setValue('Kegiatan');
let r = 2;
for (const [tKey, tValue] of Object.entries(dValue)) {
newSheet.getRange('A' + r).setValue(tKey);
newSheet.getRange('B' + r).setValue(tValue);
r++;
}
}
// remove unnecessary sheet
let sheet1 = newFile.getSheetByName('Sheet1');
newFile.deleteSheet(sheet1);
}
Fungsi createSheets, seperti namanya, digunakan untuk membuat sebuah file Google Sheets.
Karena file tersebut akan disimpan dalam Google Drive, kita perlu memberikan otorisasi kepada project ini agar dapat mengakses Google Drive.
Tidak banyak yang menarik dari kode di atas, variabel newFile menggunakan fungsi create dengan nama file sesuai variabel datumMonth (dengan tambahan kata “_new“).
Kemudian dictionary data diurai satu per satu, menjadi satu sheet per elemen. Sheet tersebut ditampung dalam variabel newSheet.
Cell A1 pada newSheet diisi dengan teks “Waktu“, sedang cell B1 dengan teks “Kegiatan“.
Cell A2 dan seterusnya diisi dengan key dari dictionary data, sedang cell sebelahnya (B2 dan seterusnya) dengan value dari dictionary data.
Dua baris terakhir digunakan untuk menghapus sheet bernama “Sheet1” yang tidak berisi data apapun. Secara default saat pembuatan sebuah file Google Sheets telah disertakan sebuah sheet bernama “Sheet1”, namun karena kita tidak membutuhkannya maka dihapus. Untuk mengurangi kebingungan pengguna file.
Buat File Google Docs
var datumMonth = SpreadsheetApp.getActiveSheet().getName();
var timeDict = {};
var dateDict = {};
var data = {};
var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getDataRange();
var allData = range.getValues();
var ui = SpreadsheetApp.getUi();
function readDate() {/* code */}
function readTime() {/* code */}
function readData() {/* code */}
function createSheets() {/* code */}
function createDocs() {
let newFile = DocumentApp.create(datumMonth + '_new');
let body = newFile.getBody();
for (const [dKey, dValue] of Object.entries(data)) {
let par = 'Kegiatan pada tanggal ' + dKey;
body.appendParagraph(par);
// table
let table = [];
table.push(['Waktu', 'Kegiatan']);
for (const [tKey, tValue] of Object.entries(dValue)) {
table.push([tKey, tValue]);
}
body.appendTable(table);
// new page
body.appendPageBreak();
}
}
Fungsi createDocs digunakan untuk membuat sebuah file Google Docs berdasar data yang ada pada variabel data. Untuk membuat file tersebut, kita menggunakan fungsi create dari kelas DocumentApp.
Kelas DocumentApp tersebut disediakan GAS untuk melakukan manipulasi pada Google Docs.
Karena kita membuat sebuah file Google Docs, yang akan otomatis tersimpan dalam Google Drive, lagi-lagi kita akan dimintai otorisasi.
Variabel body berisi kelas Body, yang merepresentasikan tubuh dokumen. Seluruh operasi (kode) di bawahnya akan diaplikasikan pada variabel body ini.
Dictionary data kemudian diurai menjadi satu elemen per halaman. Sebagai pemisah antar halaman digunakan fungsi appendPageBreak.
Fungsi appendParagraph digunakan untuk menambahkan paragraf pada body.
Lalu sebuah tabel dibuat, yang isinya adalah key dan value dari elemen dictionary data. Tabel tersebut kemudian ditempelkan ke body menggunakan fungsi appendTable.
Fungsi yang Dipanggil oleh Tombol
var datumMonth = SpreadsheetApp.getActiveSheet().getName();
var timeDict = {};
var dateDict = {};
var data = {};
var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getDataRange();
var allData = range.getValues();
var ui = SpreadsheetApp.getUi();
function readDate() {/* code */}
function readTime() {/* code */}
function readData() {/* code */}
function createSheets() {/* code */}
function createDocs() {/* code */}
function myFunction() {
readTime();
readDate();
readData();
createSheets();
createDocs();
}
Fungsi myFunction memanggil lima fungsi yang sebelumnya dibuat. Fungsi myFunction inilah yang dipanggil saat tombol Konversi diklik.
Jika belum mengetahui cara membuat tombol untuk memanggil kode, silahkan mengikuti halaman 3 tulisan ini.
Jika tombol diklik maka akan dibuat dua buah file pada Google Drive, yaitu file Google Sheets dan Google Docs bernama Agustus_new.
Simpulan
Meski jarang namun dengan segera kita akan bertemu dengan kebutuhan yang tidak tersedia solusinya secara default pada program yang kita gunakan.
Umumnya pembuat program telah memberikan fasilitas yang memungkinkan pengguna menambah fungsi/fitur/menu sesuai kebutuhan. Penambahan tersebut, biasanya, melibatkan penulisan kode.
Kode yang ditulis bergantung dari kompleksitas tujuan yang ingin dicapai. Dalam kasus di atas, hanya butuh 82 baris kode untuk mengubah sebuah file Sheets menjadi dua file lain.
Dokumentasi API telah cukup lengkap sehingga memudahkan kita menulis kode pada Google Apps Script.
Tulisan ini memberikan contoh mengenai mudahnya membuat solusi menggunakan Google Apps Script, semoga dapat menjadi pencetus ide agar pembaca dapat membuat sendiri solusi permasalahannya.
Referensi
Cover Photo by Thought Catalog on Unsplash
terima kasih ilmunya , mantaab