Panduan Lengkap untuk Otomatisasi Sederhana menggunakan Google Sheets Macro

Google Sheets Macro adalah program kecil yang Anda buat di dalam Google Sheets tanpa perlu menulis kode apa pun.

Mereka digunakan untuk mengotomatiskan tugas yang berulang. Mereka bekerja dengan merekam tindakan Anda saat Anda melakukan sesuatu dan menyimpan tindakan ini sebagai “resep” yang dapat Anda gunakan kembali dengan satu klik.

Misalnya, Anda dapat menerapkan pemformatan yang sama ke bagan dan tabel Anda. Sangat membosankan untuk melakukan ini secara manual setiap kali. Alih-alih, Anda merekam makro untuk menerapkan pemformatan di klik tombol.

Pada artikel ini, Anda akan belajar cara menggunakannya, menemukan keterbatasannya dan juga melihat bagaimana mereka menjadi segue yang hebat ke dalam dunia yang luar biasa dari pengodean Script Aplikasi 😀

💡 Pelajari lebih lanjut

Isi

  1. Apa itu macro Google Sheets?
  2. Mengapa Anda harus menggunakan makro?
  3. Cara membuat makro pertama Anda
  4. Pilihan lain
  5. Praktik Terbaik untuk Google Sheets Macro
  6. Keterbatasan Makro Google Lembar
  7. Mengintip di bawah tudung Google Sheets Macros
  8. Contoh Makro Google Lembar
  9. Sumber daya

1. Apa itu macro Google Sheets?

Pikirkan hari-hari biasa di kantor dengan Google Sheets terbuka. Mungkin ada beberapa tugas yang Anda lakukan berulang kali, seperti memformat laporan agar terlihat dengan cara tertentu, atau menambahkan bagan yang sama ke data penjualan baru, atau membuat formula khusus yang unik untuk bisnis Anda.

Mereka semua butuh waktu, kan?

Mereka berulang. Membosankan juga mungkin. Anda hanya mengalami gerakan yang sama seperti kemarin, atau minggu lalu, atau bulan lalu. Dan segala sesuatu yang berulang adalah penantang hebat untuk otomatisasi.

Di sinilah makro Google Sheets masuk, dan inilah cara kerjanya:

  • Klik tombol untuk mulai merekam makro
  • Kerjakan barangmu
  • Klik tombol untuk berhenti merekam makro
  • Ulangi proses ini kapan pun Anda inginkan dengan mengklik tombol

Sesederhana itu.

^ Kembali ke Daftar Isi

2. Mengapa Anda harus menggunakan makro di Google Sheets?

Ada alasan jelas bahwa makro di Google Sheets dapat menghemat banyak waktu, memungkinkan Anda untuk fokus pada aktivitas bernilai lebih tinggi.

Tetapi ada sejumlah alasan lain yang tidak begitu jelas seperti: menghindari kesalahan, memastikan konsistensi dalam pekerjaan Anda, mengurangi kebosanan di tempat kerja (akibat wajar: peningkatan motivasi!) Dan terakhir, itu adalah pintu besar menuju dunia yang luar biasa dari pengodean Apps Script, di mana Anda benar-benar dapat meningkatkan spreadsheet dan pekerjaan G Suite Anda.

^ Kembali ke Daftar Isi

3. Langkah-langkah untuk merekam makro pertama Anda

Mari kita jalankan melalui proses membuat makro super dasar, dalam langkah-langkah:

1) Buka Google Sheet baru ( pro-tip 1: ketik sheet. Baru masuk ke browser Anda untuk membuat Sheet baru secara instan, atau pro-tip 2: di folder Drive Anda, tekan Shift + s untuk membuat Sheet baru di folder itu secara instan ).

Ketikkan beberapa kata dalam sel A1.

2) Buka menu makro: Alat> Makro> Rekam makro

Menu makro Google Sheets

3) Anda memiliki pilihan antara referensi Absolut atau Relatif. Untuk contoh pertama ini, mari kita pilih referensi relatif:

Makro dengan referensi relatif

Referensi absolut menerapkan pemformatan ke rentang sel yang sama setiap kali (jika Anda memilih A1: D10 misalnya, itu akan selalu menerapkan makro ke sel-sel ini). Ini berguna jika Anda ingin menerapkan langkah-langkah ke kumpulan data baru setiap kali, dan berada di lokasi rentang yang sama setiap kali.

Referensi relatif menerapkan pemformatan berdasarkan di mana kursor Anda berada (jika Anda merekam makro Anda diterapkan ke sel A1, tetapi kemudian menjalankan kembali makro ketika Anda memilih sel D5, langkah-langkah makro akan diterapkan ke D5 sekarang). Ini berguna untuk hal-hal seperti rumus yang ingin Anda terapkan ke sel yang berbeda.

4) Menerapkan beberapa pemformatan ke teks dalam sel A1 (mis. Membuatnya tebal, membuatnya lebih besar, mengubah warna, dll.). Anda akan melihat perekam makro mencatat setiap langkah:

Langkah logging makro

5) Setelah selesai, klik SIMPAN dan beri nama Makro Anda:

Simpan makro

(Anda juga dapat menambahkan tombol pintas untuk memungkinkan akses cepat untuk menjalankan makro Anda di masa mendatang.)

Klik SAVE lagi dan Google Sheets akan menyimpan makro Anda.

6) Makro Anda sekarang tersedia untuk digunakan dan diakses melalui menu Tools> Macros :

pilih menu makro

7) Pertama kali Anda menjalankan makro, Anda akan diminta untuk memberikan izin untuk menjalankannya. Ini adalah langkah keamanan untuk memastikan Anda senang menjalankan kode di latar belakang. Karena Anda sudah membuatnya, aman untuk melanjutkan.

Pertama, Anda akan mengklik Lanjutkan pada sembulan Otorisasi:

Otorisasi makro

Kemudian pilih akun Google Anda:

Makro memilih akun Google

Terakhir, tinjau izin, dan klik Izinkan :

Izin hibah makro

8) Makro kemudian berjalan dan mengulangi tindakan yang Anda rekam pada sel baru yang Anda pilih!

Anda akan melihat pesan status kuning berikut berkedip di bagian atas Google Sheet Anda:

Script yang menjalankan makro
Script jadi makro

dan kemudian Anda akan melihat hasilnya:

Hasil makro

Woo hoo!

Selamat atas makro Google Sheets pertama Anda! Anda lihat, itu mudah!

Berikut ini GIF cepat yang menunjukkan proses perekaman makro secara penuh:

Merekam makro

Dan inilah tampilannya saat Anda menjalankannya:

Jalankan makro Anda

^ Kembali ke Daftar Isi

4. Opsi lainnya

4.1 Pintasan Makro

Ini adalah fitur opsional saat Anda menyimpan makro di Google Sheets. Mereka juga dapat ditambahkan nanti melalui Alat> Makro> Kelolamenu makro .

Pintasan memungkinkan Anda menjalankan makro dengan menekan kombinasi tombol tertentu yang telah Anda atur, yang menghemat waktu Anda lebih jauh dengan tidak harus mengklik menu.

Setiap tombol pintas makro harus unik dan Anda dibatasi hingga maksimal 10 tombol pintas makro per Lembar Google.

Pintasan makro

Dalam contoh di atas, saya bisa menjalankan makro ini dengan menekan:

⌘ + opsi + shift + 1

pada saat bersamaan (membutuhkan latihan 😉). Akan menjadi kombo kunci yang berbeda pada PC / Chromebook.

4.2 Menghapus makro

Anda dapat menghapus makro Google Sheets dari Sheet Anda melalui menu kelola makro: Alat> Makro> Kelola makro

Di bawah daftar makro Anda, temukan yang ingin Anda hapus. Klik tiga titik vertikal di sisi kanan makro dan kemudian pilih Hapus makro :

hapus makro

4.3 Mengimpor makro lain

Terakhir, Anda dapat menambahkan fungsi apa pun yang Anda buat di file Aplikasi Script Anda ke menu Makro, sehingga Anda dapat menjalankannya tanpa harus pergi ke jendela editor skrip. Ini adalah opsi yang lebih canggih untuk pengguna yang lebih nyaman menulis kode Apps Script.

fungsi impor ke menu makro

Opsi ini hanya tersedia jika Anda memiliki fungsi di file Script Aplikasi Anda yang belum ada di menu makro. Kalau tidak, itu akan diklik.

^ Kembali ke Daftar Isi

5. Praktik Terbaik untuk Google Sheets Macro

Gunakan jumlah tindakan minimum yang Anda bisa ketika Anda merekam makro Anda agar tetap berkinerja sebaik mungkin.

Untuk makro yang membuat perubahan pada sel tunggal, Anda bisa menerapkan perubahan yang sama pada rentang sel dengan menyorot rentang pertama dan kemudian menjalankan makro. Jadi seringkali tidak perlu untuk menyorot seluruh rentang saat Anda merekam makro Anda.

^ Kembali ke Daftar Isi

6. Keterbatasan Makro Google Lembar

Macro terikat ke Google Sheet di mana mereka dibuat dan tidak dapat digunakan di luar Sheet itu. Demikian pula, makro yang ditulis dalam file Script Aplikasi mandiri diabaikan.

Macro tidak tersedia untuk alat G Suite lainnya seperti Google Documents, Slide, dll. (Setidaknya, belum.)

Anda tidak dapat mendistribusikan makro sebagai perpustakaan atau mendefinisikannya di Pengaya Lembar. Saya berharap distribusi makro ditingkatkan di masa mendatang, sehingga Anda dapat membuat katalog makro yang tersedia di semua Lembar di folder Drive Anda.

^ Kembali ke Daftar Isi

7. Mengintip di bawah tenda Google Sheets Macros

Di belakang layar, makro di Google Sheets mengubah tindakan Anda menjadi kode Apps Script, yang hanya versi Javascript yang dijalankan di Google Cloud.

Jika Anda baru menggunakan Aplikasi Script, Anda mungkin ingin memeriksa Google Apps Script saya: A Beginner’s Guide .

Jika Anda ingin melihat kode ini, Anda dapat melihatnya dengan membuka editor skrip ( Alat> Editor skrip atau Alat> Makro> Kelola makro ).

Anda akan melihat file Script Aplikasi dengan kode yang mirip dengan ini:

/ ** @OnlyCurrentDoc * /

function FormatText () {
  var spreadsheet = SpreadsheetApp.getActive ();
  spreadsheet.getActiveRangeList (). setFontWeight ('bold')
  .setFontStyle ('italic')
  .setFontColor ('# ff0000')
  .setFontSize (18)
  .setFontFamily ('Montserrat');
};

Pada dasarnya, kode ini mengambil spreadsheet dan kemudian mengambil rentang sel aktif yang saya pilih.

Makro kemudian membuat pilihan ini tebal (baris 5), miring (baris 6), merah (baris 7, ditentukan sebagai warna hex), ukuran font 18 (baris 8) dan akhirnya mengubah keluarga font menjadi Montserrat (baris 9).

Video di bagian atas halaman ini menjelaskan lebih banyak tentang Skrip Aplikasi ini, apa artinya dan bagaimana memodifikasinya.

Makro di Google Sheets adalah langkah pertama yang bagus ke dunia Apps Script, jadi saya mendorong Anda untuk membuka editor untuk makro yang berbeda dan melihat seperti apa bentuknya.

(Jika Anda bertanya-tanya, baris /** @OnlyCurrentDoc */memastikan bahwa prosedur otorisasi hanya meminta akses ke file saat ini di mana makro Anda tinggal.)

^ Kembali ke Daftar Isi

8. Contoh Makro Google Lembar

8.1 Memformat tabel

Rekam langkah-langkah saat Anda memformat tabel pelaporan Anda, sehingga Anda dapat dengan cepat menerapkan langkah-langkah pemformatan yang sama ke tabel lain. Anda akan ingin menggunakan referensi Relatif sehingga Anda bisa menerapkan pemformatan di mana pun rentang tabel Anda (jika Anda menggunakan absolut maka itu akan selalu menerapkan pemformatan ke rentang sel yang sama).

Lihat video di bagian atas halaman untuk melihat contoh ini secara terperinci, termasuk cara memodifikasi kode Skrip Aplikasi untuk menyesuaikan berbagai tabel berukuran berbeda.

8.2 Membuat grafik

Jika Anda menemukan diri Anda membuat bagan yang sama berulang-ulang, katakanlah untuk dataset baru setiap minggu, maka mungkin inilah saatnya untuk merangkumnya dalam makro.

Rekam langkah-langkah Anda saat Anda membuat bagan untuk pertama kalinya sehingga Anda dapat menggunakannya untuk masa depan.

Video di bagian atas halaman menunjukkan contoh secara detail.


Makro berikut dimaksudkan untuk disalin ke Editor Skrip Anda dan kemudian diimpor ke menu makro dan dijalankan dari sana.

8.3 Konversikan semua rumus menjadi nilai pada Lembar saat ini

Buka editor skrip Anda ( Alat> Editor skrip ). Salin dan tempel kode berikut ke baris baru:

// konversi semua rumus ke nilai di lembar aktif
function formulasToValuesActiveSheet () {
  var sheet = SpreadsheetApp.getActiveSheet ();
  var range = sheet.getDataRange ();
  range.copyValuesToRange (sheet, 1, range.getLastColumn (), 1, range.getLastRow ());
};

Kembali di Google Sheet Anda, gunakan opsi Impor Makro untuk mengimpor fungsi ini sebagai makro.

Saat Anda menjalankannya, itu akan mengonversi rumus apa pun di lembar ini ke nilai.

8.4 Konversi semua rumus ke nilai di seluruh Google Sheet

Buka editor skrip Anda ( Alat> Editor skrip ). Salin dan tempel kode berikut ke baris baru:

// konversi semua rumus menjadi nilai di setiap lembar Google Sheet
function formulaToValuesGlobal () {
  var sheets = SpreadsheetApp.getActiveSpreadsheet (). getSheets ();
  sheets.forEach (fungsi (sheet) {
    var range = sheet.getDataRange ();
    range.copyValuesToRange (sheet, 1, range.getLastColumn (), 1, range.getLastRow ());
  });
};

Kembali di Google Sheet Anda, gunakan opsi Impor Makro untuk mengimpor fungsi ini sebagai makro.

Saat Anda menjalankannya, itu akan mengubah semua rumus di setiap lembar Google Sheet Anda menjadi nilai.

8.5 Urutkan semua lembar Anda di Google Sheet secara alfabet

Buka editor skrip Anda ( Alat> Editor skrip ). Salin dan tempel kode berikut ke baris baru:

// urutkan lembar secara abjad
function sortSheets () {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet ();
  var sheets = spreadsheet.getSheets ();
  var sheetNames = [];
  sheets.forEach (fungsi (sheet, i) {
    sheetNames.push (sheet.getName ());
  });
  sheetNames.sort (). forEach (fungsi (sheet, i) {
    spreadsheet.getSheetByName (sheet) .activate ();
    spreadsheet.moveActiveSheet (i +1);
  });
};

Kembali di Google Sheet Anda, gunakan opsi Impor Makro untuk mengimpor fungsi ini sebagai makro.

Saat Anda menjalankannya, itu akan mengurutkan semua sheet Anda di Google Sheet secara alfabet.

8.6 Perlihatkan semua baris dan kolom di Sheet saat ini

Buka editor skrip Anda ( Alat> Editor skrip ). Salin dan tempel kode berikut ke baris baru:

// sembunyikan semua baris dan kolom dalam rentang data Sheet saat ini
function unhideRowsColumnsActiveSheet () {
  var sheet = SpreadsheetApp.getActiveSheet ();
  var range = sheet.getDataRange ();
  sheet.unhideRow (range);
  sheet.unhideColumn (range);
}

Kembali di Google Sheet Anda, gunakan opsi Impor Makro untuk mengimpor fungsi ini sebagai makro.

Saat Anda menjalankannya, itu akan menyembunyikan baris dan kolom tersembunyi dalam rentang data. (Jika Anda memiliki baris / kolom tersembunyi di luar rentang data, mereka tidak akan terpengaruh.)

8.7 Perlihatkan semua baris dan kolom di seluruh Google Sheet

Buka editor skrip Anda ( Alat> Editor skrip ). Salin dan tempel kode berikut ke baris baru:

// sembunyikan semua baris dan kolom dalam rentang data seluruh Google Sheet
function unhideRowsColumnsGlobal () {
  var sheets = SpreadsheetApp.getActiveSpreadsheet (). getSheets ();
  sheets.forEach (fungsi (sheet) {
    var range = sheet.getDataRange ();
    sheet.unhideRow (range);
    sheet.unhideColumn (range);
  });
};

Kembali di Google Sheet Anda, gunakan opsi Impor Makro untuk mengimpor fungsi ini sebagai makro.

Saat Anda menjalankannya, itu akan menyembunyikan baris dan kolom tersembunyi dalam rentang data di setiap lembar seluruh Lembar Google Anda.

8.8 Atur semua Lembar untuk memiliki warna tab tertentu

Buka editor skrip Anda ( Alat> Editor skrip ). Salin dan tempel kode berikut ke baris baru:

// atur semua tab Lembar menjadi merah
fungsi setTabColor () {
  var sheets = SpreadsheetApp.getActiveSpreadsheet (). getSheets ();
  sheets.forEach (fungsi (sheet) {
    sheet.setTabColor ("ff0000");
  });
};

Kembali di Google Sheet Anda, gunakan opsi Impor Makro untuk mengimpor fungsi ini sebagai makro.

Ketika Anda menjalankannya, itu akan mengatur semua warna tab menjadi merah .

Ingin warna yang berbeda? Ubah saja kode hex pada baris 5 menjadi apa pun yang Anda inginkan, mis. Biru cornflower akan di-6495

Gunakan panduan praktis ini untuk menemukan nilai hex yang Anda inginkan .

8.9 Hapus pewarnaan tab dari semua Lembar

Buka editor skrip Anda ( Alat> Editor skrip ). Salin dan tempel kode berikut ke baris baru:

// hapus semua warna tab Sheets
fungsi resetTabColor () {
  var sheets = SpreadsheetApp.getActiveSpreadsheet (). getSheets ();
  sheets.forEach (fungsi (sheet) {
    sheet.setTabColor (null);
  });
};

Kembali di Google Sheet Anda, gunakan opsi Impor Makro untuk mengimpor fungsi ini sebagai makro.

Ketika Anda menjalankannya, itu akan menghapus semua warna tab dari Sheet Anda (itu mengaturnya kembali ke nol, yaitu tidak ada nilai).

Berikut adalah GIF yang menunjukkan warna tab yang ditambahkan dan dihapus melalui Macro (periksa bagian bawah gambar):

tab warna dengan Macro

8.10 Sembunyikan semua lembar selain yang aktif

Salin dan tempel kode ini ke editor skrip Anda dan impor fungsi ke menu Makro Anda:

function hideAllSheetsExceptActive () {
  var sheets = SpreadsheetApp.getActiveSpreadsheet (). getSheets ();
  sheets.forEach (fungsi (sheet) {
    if (sheet.getName ()! = SpreadsheetApp.getActiveSheet (). getName ()) 
      sheet.hideSheet ();
  });
};

Menjalankan makro ini akan menyembunyikan semua Lembar di Lembar Google Anda, kecuali yang Anda pilih (lembar aktif).

8.11 Perlihatkan semua Lembar di Lembar Anda dalam sekali jalan

Buka editor skrip Anda ( Alat> Editor skrip ). Salin dan tempel kode berikut ke baris baru:

function unhideAllSheets () {
  var sheets = SpreadsheetApp.getActiveSpreadsheet (). getSheets ();
  sheets.forEach (fungsi (sheet) {
    sheet.showSheet ();
  });
};

Kembali di Google Sheet Anda, gunakan opsi Impor Makro untuk mengimpor fungsi ini sebagai makro.

Saat Anda menjalankannya, itu akan menampilkan Lembar tersembunyi di dalam Lembar Anda, untuk menyelamatkan Anda dari keharusan melakukannya 1-per-1.

Berikut adalah GIF yang menunjukkan cara kerja makro petak ump:

sembunyikan lembar unhide dengan makro

Anda bisa melihat bagaimana Sheet6 , Sheet aktif, adalah satu-satunya yang tidak disembunyikan ketika makro pertama dijalankan.

8.12 Filter Ulang

Oke, simpan yang terbaik untuk yang terakhir, ini adalah salah satu makro favorit saya! 🙂

Saya menggunakan filter pada tabel data saya sepanjang waktu, dan sedikit mengganggu bahwa tidak ada cara untuk menghapus semua filter Anda sekaligus. Anda harus secara manual mereset setiap filter secara bergantian (memakan waktu, dan kadang-kadang sulit untuk melihat kolom mana yang memiliki filter ketika Anda memiliki kumpulan data yang sangat besar) ATAU Anda dapat menghapus sepenuhnya filter dan menambahkan kembali dari menu.

Mari kita buat makro di Google Sheets untuk melakukannya! Kemudian kita bisa menjadi sangat efisien dengan menjalankannya dengan satu klik menu atau bahkan lebih baik, dari shortcut.

Buka editor skrip Anda ( Alat> Editor skrip ). Salin dan tempel kode berikut ke baris baru:

// atur ulang semua filter untuk rentang data pada Lembar saat ini
function resetFilter () {
  var sheet = SpreadsheetApp.getActiveSheet ();
  var range = sheet.getDataRange ();
  range.getFilter (). hapus ();
  range.createFilter ();
}

Kembali di Google Sheet Anda, gunakan opsi Impor Makro untuk mengimpor fungsi ini sebagai makro.

Saat Anda menjalankannya, itu akan menghapus dan kemudian menambahkan kembali filter ke rentang data Anda dalam sekali jalan.

Berikut adalah GIF yang menunjukkan masalah dan solusi makro:

Makro untuk mengatur ulang filter

^ Kembali ke Daftar Isi

9. Sumberdaya

Jika Anda tertarik untuk melakukan lebih jauh, lihat sumber daya berikut untuk memulai dengan Apps Script:

Panduan referensi makro dalam bantuan Google Documents

Panduan referensi makro dalam dokumentasi Pengembang Google

Dan jika Anda ingin benar-benar mulai menggali ke dalam kode Script Aplikasi, Anda ingin mem-bookmark dokumentasi Google untuk Layanan Spreadsheet.

Akhirnya, semua kode makro ini tersedia di GitHub .

Sumber: https://www.benlcollins.com/spreadsheets/google-sheets-macros/

Leave a Reply

Your email address will not be published. Required fields are marked *