Tutorial Skrip Google Apps untuk Menguasai Macro

Para eksekutif yang efektif tahu bahwa waktu adalah faktor pembatas… Mungkin tidak ada hal lain yang membedakan eksekutif yang efektif selain perhatian mereka yang lembut terhadap waktu.

Peter Drucker

Waktu adalah sumber daya kita yang paling berharga. Kami ingin membelanjakannya untuk aktivitas yang berdampak tertinggi dan paling bernilai-akretif yang kami bisa, bukan hanya karena aktivitas tersebut biasanya menghasilkan nilai uang tertinggi, tetapi juga untuk terus menantang diri kami sendiri dan memaksimalkan kepuasan kerja kami.

Ada banyak cara untuk meningkatkan efisiensi dan produktivitas Anda agar dapat memanfaatkan waktu Anda dengan lebih baik. Dalam artikel sebelumnya tentang Google Spreadsheet, saya menjelaskan bagaimana kekuatan kolaborasi online adalah salah satu kunci untuk meningkatkan produktivitas.

Di artikel lain, saya menunjukkan bagaimana bahasa pemrograman Python dapat menjadi alat analisis dan otomatisasi tugas yang kuat bagi para profesional keuangan.

Mengambil inspirasi dari ini, sekarang saya ingin menyajikan tutorial Google Apps Script. Google Apps Script memungkinkan Anda menulis skrip dan program dalam JavaScript untuk mengotomatisasi, menghubungkan, dan memperluas produk di Google G Suite, termasuk Spreadsheet, Dokumen, Slide, Gmail, Drive, dan beberapa lainnya. Mempelajarinya membutuhkan investasi waktu, seperti halnya menulis skrip, tetapi produktivitas meningkat dan peluang tambahan yang terbuka membuatnya sangat berharga.

Sebagai langkah pertama, mari kita mulai dengan melihat konsep yang sudah dikenal: makro.

Merekam dan Menggunakan Makro di Google Spreadsheet

Jika Anda telah menghabiskan banyak waktu untuk bekerja dengan Excel, maka Anda pasti telah bersentuhan dengan antarmuka makro VBA (Visual Basic for Applications) Excel di beberapa titik. Baik melalui merekam atau menulisnya sendiri atau membonceng yang dibuat oleh orang lain.

Makro adalah cara yang bagus untuk mengotomatiskan alur kerja yang berulang dan membosankan. VBA mungkin bukan bahasa yang Anda dedikasikan banyak waktu untuk dipelajari, tetapi keindahannya adalah bahwa Anda tidak benar-benar perlu untuk menjadi produktif dan membuat makro Anda sendiri. Anda cukup merekam alur kerja yang ingin Anda otomatisasi, lalu masuk ke kode dan buat perubahan kecil apa pun yang diperlukan untuk membuat makro lebih umum.

Dalam beberapa hal, VBA adalah pelajaran yang bagus dan terlupakan tentang cara memperkenalkan orang non-teknis ke pengkodean . Cara Anda dapat merekam tindakan dan kemudian memasukkan kode untuk ditinjau nanti memang merupakan cara belajar yang jauh lebih pragmatis daripada membaca buku teks dan menonton tutorial secara pasif.

Fungsi perekaman VBA yang sama tersedia di Google Spreadsheet. Berikut adalah contoh sederhana cara menggunakannya:

Mari kita mulai dengan beberapa contoh data, menggunakan kueri IMPORTHTML untuk mengimpor tabel. Dalam contoh ini, saya telah mengunduh daftar dari Wikipedia 15 hedge fund terbesar di dunia. Tak perlu dikatakan, tetapi, ini adalah contoh yang sewenang-wenang; tujuannya adalah agar Anda lebih fokus pada aplikasi, di atas subjek.

Proses perekaman makro dimulai melalui jalur menu berikut: Alat > Makro > Rekam makro.

Kami kemudian berjalan melalui tindakan (format PC) yang ingin kami rekam:

1. Pilih baris pertama
2. Tekan Shift + Ctrl + Panah Bawah untuk memilih semuanya
3. Ctrl + C untuk menyalin
4. Shift + F11 untuk membuat lembar baru
5. Beri lembar nama baru
6. Tekan Shift + Control + V untuk menempelkan nilai

Setelah selesai, tekan tombol Simpan pada jendela makro di bagian bawah, beri nama dan pintasan keyboard opsional.

Untuk tindakan sederhana yang dapat direplikasi persis melalui langkah-langkah yang sama ini, prosesnya akan berakhir di sini dan Anda dapat langsung mulai menggunakan makro Anda. Namun, dalam kasus ini, kita perlu membuat beberapa perubahan sebelum kode dapat digunakan. Misalnya, lembar tempat kita menyalin harus memiliki nama yang berbeda setiap kali. Mari kita lihat bagaimana melakukan ini.

Menulis Skrip Google Apps Secara Manual

Sekarang kita akan melihat tulang-tulang Google Apps Script untuk pertama kalinya; platform pemrograman yang berjalan di server Google. Ini memperkuat makro kami dan memungkinkan Anda membuat alur kerja yang sangat kompleks, dan bahkan add-on, untuk aplikasi itu sendiri. Ini dapat digunakan untuk mengotomatisasi tidak hanya pekerjaan spreadsheet tetapi sebenarnya hampir semua hal yang saling berhubungan dalam Google G Suite.

Bahasa pemrograman Apps Script adalah JavaScript , salah satu bahasa pemrograman paling populer, yang berarti ada banyak sumber daya di luar sana untuk siapa saja yang ingin belajar secara ekstensif. Namun, seperti halnya VBA, Anda tidak benar-benar perlu melakukannya: Anda dapat menggunakan fungsi Rekam yang sama dan cukup lakukan langkah-langkah yang Anda inginkan agar dapat diulang secara otomatis. Keluaran dari rekaman mungkin terlihat kasar dan kemungkinan besar tidak akan cocok dengan sempurna dengan apa yang ingin Anda selesaikan, tetapi ini akan memberikan titik awal yang cukup solid. Sekarang mari kita lakukan untuk skrip yang baru saja kita rekam.

Saat merekam, masuk akal untuk berhati-hati untuk tidak merekam langkah tambahan apa pun secara tidak sengaja yang tidak ingin Anda tangkap dalam rekaman akhir, tetapi terkadang sulit untuk menghindari: sesuatu yang sederhana seperti memilih sel yang berbeda sebelum menekan tombol Stop Tombol perekaman akan ditangkap dan selanjutnya diulang setiap kali Anda menjalankan skrip. Langkah pertama saat mengedit skrip kami adalah membersihkannya dan menghapus langkah-langkah tersebut. Mari selami dengan masuk ke Tools > Script editor di menu file.

Jika Anda mengetahui JavaScript, Anda akan langsung mengenalinya, dan Anda mungkin juga terkejut melihat kata kunci “var” alih-alih “let” atau “const” seperti yang Anda lihat di JavaScript modern. Ini mencerminkan fakta bahwa versi JavaScript di Apps Script cukup lama dan tidak mendukung banyak fitur bahasa yang lebih baru. Menjelang akhir, saya akan memperkenalkan solusi namun bagi mereka yang ingin menggunakan fitur bahasa terbaru.

Saat Anda menjalankan skrip untuk pertama kalinya, skrip akan meminta otorisasi, yang masuk akal, karena skrip dapat memodifikasi (dan berpotensi menghapus) semua data Anda. Anda kemungkinan besar akan mengenali proses otorisasi dari produk Google lainnya.

Sekarang kita bisa mulai memodifikasi kodenya. Perubahan yang perlu kita lakukan kecil, tetapi jika Anda melakukan ini untuk pertama kalinya, mungkin masih memerlukan beberapa pencarian cepat melalui dokumentasi Skrip Aplikasi Spreadsheet dan/atau pencarian cepat konsep JavaScript, seperti bekerja dengan tanggal. Di sini fakta bahwa JavaScript adalah bahasa yang tersebar luas sangat berguna: Solusi untuk masalah apa pun yang Anda hadapi atau fungsionalitas yang muncul di pikiran biasanya dapat ditemukan dengan cepat jika Anda merumuskan istilah pencarian dengan cara yang lugas.

Perubahan yang dibuat dalam versi skrip ini dari versi rekaman asli adalah bahwa alih-alih nama hardcode untuk lembar baru yang kami buat, kami sekarang menamainya dengan tanggal hari ini sebagai gantinya. Selain itu, kami juga mengubah jalur penyalinan di bagian akhir untuk merujuk ke lembar baru ini. Empat baris terakhir juga menunjukkan cara melakukan beberapa operasi pemformatan, seperti mengubah nilai sel, mengubah ukuran kolom, dan menyembunyikan garis kisi.

 function createSnapshot() { var spreadsheet = SpreadsheetApp.getActive(); var date = new Date().toISOString().slice(0,10); var destination = spreadsheet.insertSheet(date); spreadsheet.getRange('HTML!A1:F1').activate(); spreadsheet.getSelection() .getNextDataRange(SpreadsheetApp.Direction.DOWN) .activate(); spreadsheet.getActiveRange() .copyTo(SpreadsheetApp.setActiveSheet(destination) .getRange(1,1), SpreadsheetApp.CopyPasteType.PASTE_VALUES,false); var sheet = spreadsheet.setActiveSheet(destination) sheet.getRange("D1").setValue("AUM $bn") sheet.setHiddenGridlines(true); sheet.getRange("A1:D1").setFontWeight("bold"); sheet.autoResizeColumns(1, 4); };

Menjalankan skrip sekarang akan menunjukkan bahwa lembar baru memang dinamai dengan tanggal hari ini dan berisi informasi yang disalin sebagai nilai (bukan rumus) dari lembar utama.

Visualisasi charting sekarang dapat ditambahkan dengan menggunakan proses record yang sama. Saya menggunakan ini untuk membuat tiga grafik sederhana.

Membersihkan kode untuk masing-masing akan terlihat seperti ini:

 function createColumnChart() { var spreadsheet = SpreadsheetApp.getActive(); spreadsheet.getRange('C1:D16').activate(); var sheet = spreadsheet.getActiveSheet(); chart = sheet.newChart() .asColumnChart() .addRange(spreadsheet.getRange('B1:D16')) .setMergeStrategy(Charts.ChartMergeStrategy.MERGE_COLUMNS) .setTransposeRowsAndColumns(false) .setNumHeaders(-1) .setHiddenDimensionStrategy(Charts.ChartHiddenDimensionStrategy.IGNORE_BOTH) .setOption('useFirstColumnAsDomain', true) .setOption('curveType', 'none') .setOption('domainAxis.direction', 1) .setOption('isStacked', 'absolute') .setOption('series.0.color', '#0b5394') .setOption('series.0.labelInLegend', 'AUM $bn') .setPosition(19, 6, 15, 5) .build(); sheet.insertChart(chart); };

Sekali lagi, jangan khawatir jika beberapa opsi terlihat membingungkan: ini semua dibuat secara otomatis, Anda hanya perlu cukup memahami untuk menghapus langkah-langkah yang tidak perlu dan mungkin membuat penyesuaian kecil nanti.

Contoh Skrip Google Apps Tingkat Lanjut: Menghubungkan Spreadsheet ke Google Drive dan Slide

Semuanya sekarang mulai terbentuk, tetapi bagaimana jika output sebenarnya yang kita inginkan bukan spreadsheet tetapi presentasi? Jika itu masalahnya, maka sebagian besar pekerjaan dari sini mungkin masih manual, dan kami tidak menghemat banyak waktu jika kami perlu melakukan ini secara berulang.

Sekarang mari kita jelajahi seperti apa mengotomatiskan pembuatan presentasi menggunakan contoh data dari spreadsheet kita.

Latihan ini sekarang menjadi lebih maju karena dua alasan:

1. Kita perlu membiasakan diri dengan cara bekerja dengan Google Slide (dan Google Drive) selain Spreadsheet.
2. Di Slide, atau saat bekerja di antara Aplikasi G Suite secara umum, tidak ada fungsi "Rekam makro" yang tersedia. Ini berarti Anda harus cukup tahu tentang Apps Script (dan merasa nyaman saat menavigasi dokumentasi untuk setiap produk G Suite) untuk menulis skrip dari awal.

Contoh berikut ini dimaksudkan untuk memberikan beberapa blok bangunan dasar untuk membantu Anda memulai dan membiasakan diri.

Untuk memulainya, mari buat template yang nantinya ingin kita isi dengan konten menggunakan skrip kita. Berikut adalah dua slide presentasi sederhana yang saya kumpulkan:

Selanjutnya, Anda perlu mendapatkan ID template ini karena Anda harus merujuknya ke skrip Anda. Tanpa sadar Anda akan melihat ID ini berkali-kali karena sebenarnya ini adalah urutan karakter dan angka yang tampak acak yang Anda lihat di URL browser Anda:

https://docs.google.com/presentation/p/ this_is_your_presentation_ID /edit#slide=id.p.

Sekarang kita harus menambahkan baris berikut ke skrip asli kita. Ini akan meminta otorisasi lagi, kali ini untuk mengakses Google Drive Anda.

 function createPresentation() { var template; var template = DriveApp.getFileById(templateId); var copy = template.makeCopy("Weekly report " + date).getId(); var presentation = SlidesApp.openById(copy); }

Anda tidak akan melihat umpan balik visual langsung jika Anda menjalankan cuplikan kode ini, tetapi jika Anda melihat di folder Google Drive tempat Anda menyimpan template, Anda akan menemukan bahwa salinannya memang telah dibuat, dan memiliki tanggal dalam nama file. Kami memulai dengan baik!

Sekarang mari kita gunakan lebih banyak blok penyusun untuk mulai mengisinya dengan konten, secara terprogram, bukan dengan tangan. Tambahkan baris berikut ke fungsi yang sama:

 presentation.getSlides()[0] .getPageElements()[0] .asShape() .getText() .setText("Weekly Report " + date);

Sekarang segalanya menjadi sedikit lebih menarik, karena kami telah mengubah halaman pertama untuk memasukkan tanggal hari ini. Di Slide, seperti di Spreadsheet, Anda bekerja dengan objek (diwakili oleh kelas) yang masing-masing memiliki properti dan metode (yaitu fungsionalitas terlampir). Ini diatur dalam hierarki, dengan SpreadsheetsApp, DriveApp, atau SlidesApp sebagai objek tingkat atas. Dalam cuplikan kode di atas, kita perlu menelusuri hierarki ini selangkah demi selangkah untuk mencapai elemen yang ingin kita edit, dalam hal ini: Teks dalam kotak teks. Secara praktis, ini berarti menjangkau melalui objek Presentation, Slide, PageElement, dan Shape, hingga akhirnya kita mendapatkan objek TextRange yang ingin kita edit.

Melacak jenis objek yang Anda hadapi dapat membingungkan dan bug yang dihasilkan dari mencoba menerapkan operasi ke objek yang salah mungkin sulit untuk dipecahkan. Sayangnya, fungsi bantuan dan pesan kesalahan di Script Editor itu sendiri tidak selalu memberikan banyak panduan di sini, lapisan peraknya adalah bahwa perhatian tersebut setidaknya akan meningkatkan praktik kontrol kualitas Anda.

Setelah membuat presentasi dan memperbarui judulnya, sekarang saatnya memasukkan salah satu bagan baru kita ke dalamnya. Dengan mengingat hierarki objek, kode berikut sekarang harus masuk akal:

 var spreadsheet = SpreadsheetApp.getActive(); var sheet = spreadsheet.getSheetByName(date); var chart = sheet.getCharts()[0]; var position = {left: 25, top: 75}; var size = {width: 480, height: 300}; presentation.getSlides()[1] .insertSheetsChart(chart, position.left, position.top, size.width, size.height);

Jika Anda menjalankan skrip lengkap, presentasi keluaran akan terlihat seperti ini:

Semoga contoh ini mengilustrasikan prinsip dan memberikan inspirasi untuk memulai eksperimen Anda sendiri. Jika Anda memikirkannya, saya yakin Anda dapat menemukan setidaknya beberapa contoh pekerjaan manual yang dilakukan di perusahaan Anda saat ini yang benar-benar harus diotomatisasi dengan cara ini. Berfungsi untuk membebaskan waktu untuk berpikir, menganalisis, dan menerapkan penilaian, daripada mengacak data secara mekanis dari satu format dan/atau tempat ke tempat lain. Meningkatkan Pengalaman Pengembangan Seperti disebutkan sebelumnya, versi JavaScript yang didukung di Google Apps Script sudah tua dan fungsionalitas Editor Skrip online sangat terbatas. Jika Anda hanya merekam makro atau menulis beberapa lusin baris, Anda tidak akan benar-benar menyadarinya. Namun, jika Anda memiliki rencana ambisius untuk mengotomatiskan semua aspek pelaporan mingguan atau bulanan Anda, atau ingin membuat plugin, maka Anda akan senang mengetahui bahwa ada alat baris perintah yang memungkinkan Anda mengembangkan menggunakan lingkungan pengembangan favorit Anda. .

Jika Anda berada pada tingkat kemahiran seperti itu, maka Anda mungkin juga ingin memanfaatkan fitur terbaru yang ditawarkan JavaScript, dan bahkan lebih potensial lagi, karena dengan alat baris perintah Anda juga dapat mengembangkan di TypeScript.

Menggunakan Python untuk Pemrograman Google Spreadsheet

Jika Anda menemukan bahwa bekerja dengan Apps Script bukanlah hal yang menyenangkan, maka ada opsi lain, tergantung pada kasus penggunaan. Jika Anda ingin melakukan penghitungan angka yang lebih maju, terhubung dengan API atau database, atau lebih memilih bahasa pemrograman Python daripada JavaScript, maka Google's Colaboratory adalah produk yang tak ternilai harganya. Ini memberi Anda notebook Jupyter yang berjalan di server Google yang memungkinkan Anda untuk menulis skrip Python yang terintegrasi dengan mulus dengan file Google Drive Anda dan, melalui pustaka 'gspread', membuatnya mudah untuk bekerja dengan data spreadsheet Anda.

Saya menguraikan banyak manfaat Python dalam sebuah artikel tentang cara menggunakannya untuk fungsi keuangan, yang juga berfungsi sebagai pengantar lembut untuk bekerja dengan notebook Python dan Jupyter dalam konteks bisnis dan keuangan. Satu manfaat yang sangat penting bagi saya adalah bahwa tidak seperti Apps Script, notebook Python di Colaboratory bersifat interaktif, jadi Anda melihat hasil (atau pesan kesalahan) setelah mengeksekusi setiap baris atau blok kecil kode.

Otomatisasi itu Adiktif

Tutorial Skrip Google Apps ini menunjukkan sekilas tentang apa yang mungkin dilakukan melalui bahasa pengkodean Google. Kemungkinannya hampir tidak terbatas. Namun, jika Anda tidak memiliki latar belakang teknis, contoh kode mungkin terlihat menakutkan dan Anda mungkin berpikir bahwa keuntungan produktivitas yang diperoleh dari mempelajari Skrip Google Apps mungkin tidak cukup untuk melebihi investasi yang signifikan dalam hal waktu yang dibutuhkan. untuk mempelajarinya.

Ini, tentu saja, tergantung pada banyak faktor, termasuk jenis peran apa yang Anda miliki, atau harapkan, di masa depan. Tetapi bahkan jika Anda tidak mengharapkan untuk melakukan sesuatu yang serupa dengan contoh yang ditunjukkan di sini, memiliki pemahaman tentang apa yang mungkin dan kira-kira berapa banyak pekerjaan yang diperlukan untuk menerapkan dapat memicu pemikiran dan ide seputar bagaimana meningkatkan produktivitas di perusahaan Anda, untuk klien Anda, atau diri Anda sendiri secara pribadi.

Secara pribadi, saya dapat membuktikan kepuasan duduk dan menekan tombol yang menyelesaikan pekerjaan manual yang membosankan selama satu jam dalam waktu kurang dari satu menit. Setelah melakukan ini untuk ke-50 kalinya, Anda akan berterima kasih atas beberapa jam yang dihabiskan untuk menyusun semuanya bersama-sama, yang pada akhirnya berfungsi untuk membebaskan waktu Anda untuk pengejaran yang lebih bernilai tambah. Setelah beberapa saat, manfaat skalabilitas ini memang membuat ketagihan.