Bagaimana cara mengotomatisasi di google sheets?

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

Mereka digunakan untuk mengotomatiskan tugas yang dapat diulang. 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 mungkin menerapkan pemformatan yang sama ke bagan dan tabel Anda. Sangat membosankan untuk melakukan ini secara manual setiap saat. Alih-alih, Anda merekam makro untuk menerapkan pemformatan dengan mengklik tombol

Dalam artikel ini, Anda akan mempelajari cara menggunakannya, menemukan keterbatasannya, dan juga melihat bagaimana mereka menjadi bagian yang hebat dalam dunia pengkodean Apps Script yang menakjubkan.

Isi

1. Apa itu makro Google Sheets?

Pikirkan hari-hari biasa di tempat kerja 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 membutuhkan waktu, bukan?

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

Di sinilah makro Google Sheets masuk, dan inilah cara kerjanya

• Klik tombol untuk mulai merekam makro
• Kerjakan tugasmu
• Klik tombol untuk berhenti merekam makro
• Ulangi proses kapan pun Anda mau dengan mengklik tombol

Mereka benar-benar sesederhana itu

2. Mengapa Anda harus menggunakan makro di Google Sheets?

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

Tapi ada banyak alasan lain yang kurang jelas seperti. menghindari kesalahan, memastikan konsistensi dalam pekerjaan Anda, mengurangi kebosanan di tempat kerja (akibat wajar. peningkatan motivasi. ) dan terakhir, mereka adalah pintu masuk yang bagus ke dunia pengkodean Apps Script yang luar biasa, tempat Anda benar-benar dapat meningkatkan spreadsheet dan pekerjaan Google Workspace

3. Langkah-langkah untuk merekam makro pertama Anda

Mari kita jalankan melalui proses pembuatan makro super dasar, secara bertahap

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

Ketik beberapa kata di sel A1

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

3) Anda memiliki pilihan antara referensi Absolut atau Relatif. Untuk contoh pertama ini, mari kita pilih 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 selalu berada di lokasi rentang yang sama

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

4) Terapkan beberapa pemformatan ke teks di sel A1 (mis. g. membuatnya tebal, membuatnya lebih besar, mengubah warnanya, dll. ). Anda akan melihat perekam makro mencatat setiap langkah

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

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

Klik SIMPAN lagi dan Google Spreadsheet akan menyimpan makro Anda.

6) Makro Anda sekarang tersedia untuk digunakan dan diakses melalui menu Alat > Makro

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

Pertama, Anda akan mengeklik Lanjutkan pada munculan Otorisasi.

Kemudian pilih akun Google Anda

Terakhir, tinjau izin, dan klik Izinkan .

8) Makro kemudian menjalankan dan mengulangi tindakan yang Anda rekam di sel baru yang Anda pilih

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

dan kemudian Anda akan melihat hasilnya

Woo hoo

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

Inilah GIF cepat yang menunjukkan proses perekaman makro secara penuh

Dan inilah tampilannya saat Anda menjalankannya

4. Pilihan lain

4. 1 Pintasan Makro

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

Pintasan memungkinkan Anda menjalankan makro dengan menekan kombinasi tombol tertentu yang telah Anda tetapkan, yang menghemat waktu lebih lanjut dengan tidak perlu mengklik menu

Setiap tombol pintasan makro harus unik dan Anda dibatasi hingga maksimal 10 tombol pintasan makro per Google Sheet

Pada contoh di atas, saya dapat menjalankan makro ini dengan menekan

⌘ + opsi + shift + 1

kunci pada saat yang sama (butuh latihan?). Akan menjadi kombo kunci yang berbeda di 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 lalu pilih Hapus makro

4. 3 Mengimpor makro lainnya

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

Opsi ini hanya tersedia jika Anda memiliki fungsi di file Apps Script yang belum ada di menu makro. Kalau tidak, itu akan menjadi abu-abu

5. Praktik Terbaik untuk Makro Google Spreadsheet

Gunakan jumlah minimum tindakan yang Anda bisa saat Anda merekam makro Anda untuk menjaga kinerjanya sebaik mungkin

Untuk makro yang membuat perubahan pada sel tunggal, Anda bisa menerapkan perubahan yang sama pada rentang sel dengan menyorot rentang terlebih dahulu, lalu menjalankan makro. Jadi seringkali tidak perlu menyorot seluruh rentang saat Anda merekam makro Anda

6. Keterbatasan Macro Google Sheets

Makro terikat ke Google Sheet tempat mereka dibuat dan tidak dapat digunakan di luar Sheet itu. Demikian pula, makro yang ditulis dalam file Skrip Aplikasi mandiri akan diabaikan begitu saja

Makro tidak tersedia untuk alat Google Workspace lainnya seperti Google Dokumen, Slide, dll. (Setidaknya, belum. )

Anda tidak dapat mendistribusikan makro sebagai pustaka atau mendefinisikannya di Add-on Spreadsheet. Saya harap distribusi makro ditingkatkan di masa mendatang, sehingga Anda dapat membuat katalog makro yang tersedia di semua Spreadsheet di folder Drive Anda

7. Mengintip di bawah kap Google Sheets Macros

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

Jika Anda baru menggunakan Skrip Aplikasi, Anda mungkin ingin melihat Skrip Google Apps saya. Panduan Pemula

Jika Anda ingin melihat kode ini, Anda dapat melihatnya dengan membuka editor skrip (Tools > Script editor atau Tools > Macros > Manage macros)

Anda akan melihat file Apps Script 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 aktif sel 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 detail tentang Skrip Aplikasi ini, apa artinya dan cara memodifikasinya

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

(Jika Anda bertanya-tanya, baris

// convert all formulas to values in the active sheet
function formulasToValuesActiveSheet() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getDataRange();
  range.copyValuesToRange(sheet, 1, range.getLastColumn(), 1, range.getLastRow());
};

8. Contoh Macro Google Sheets

8. 1 Memformat tabel

Catat langkah-langkah saat Anda memformat tabel pelaporan, sehingga Anda bisa dengan cepat menerapkan langkah pemformatan yang sama ke tabel lainnya. Anda akan ingin menggunakan Referensi relatif sehingga Anda dapat menerapkan pemformatan di mana pun rentang tabel Anda berada (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 mendetail, termasuk cara memodifikasi kode Apps Script untuk menyesuaikan dengan ukuran tabel yang berbeda

8. 2 Membuat grafik

Jika Anda mendapati diri Anda membuat bagan yang sama berulang kali, katakanlah untuk kumpulan data baru setiap minggu, maka mungkin inilah saatnya untuk merangkumnya dalam makro

Catat langkah Anda saat Anda membuat bagan pertama kali sehingga Anda memilikinya untuk digunakan di masa mendatang

Video di bagian atas halaman menunjukkan contoh secara mendetail

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

// convert all formulas to values in the active sheet
function formulasToValuesActiveSheet() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getDataRange();
  range.copyValuesToRange(sheet, 1, range.getLastColumn(), 1, range.getLastRow());
};

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

Saat Anda menjalankannya, itu akan mengonversi rumus apa pun di lembar saat ini menjadi nilai

8. 4 Konversikan semua rumus menjadi nilai di seluruh Lembar Google

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

// convert all formulas to values in every sheet of the Google Sheet
function formulasToValuesGlobal() {
  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  sheets.forEach(function(sheet) {
    var range = sheet.getDataRange();
    range.copyValuesToRange(sheet, 1, range.getLastColumn(), 1, range.getLastRow());
  });
};

Kembali ke 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 Lembar Google menurut abjad

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

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

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

Saat Anda menjalankannya, itu akan mengurutkan semua lembar Anda di Lembar Google menurut abjad

8. 6 Perlihatkan semua baris dan kolom di Lembar saat ini

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

// unhide all rows and columns in current Sheet data range
function unhideRowsColumnsActiveSheet() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getDataRange();
  sheet.unhideRow(range);
  sheet.unhideColumn(range);
}

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

Saat Anda menjalankannya, itu akan memperlihatkan baris dan kolom tersembunyi dalam rentang data. (Jika Anda menyembunyikan baris/kolom di luar rentang data, baris/kolom tersebut 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

// unhide all rows and columns in data ranges of entire Google Sheet
function unhideRowsColumnsGlobal() {
  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  sheets.forEach(function(sheet) {
    var range = sheet.getDataRange();
    sheet.unhideRow(range);
    sheet.unhideColumn(range);
  });
};

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

Saat Anda menjalankannya, itu akan memperlihatkan baris dan kolom tersembunyi apa pun dalam rentang data di setiap lembar dari seluruh Lembar Google Anda

8. 8 Atur semua Lembar agar memiliki warna tab tertentu

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

// set all Sheets tabs to red
function setTabColor() {
  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  sheets.forEach(function(sheet) {
    sheet.setTabColor("ff0000");
  });
};

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

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

Mau warna lain? . g. cornflower blue akan menjadi 6495ed

Gunakan panduan praktis ini untuk menemukan nilai hex yang Anda inginkan

8. 9 Hapus pewarnaan tab apa pun dari semua Lembar

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

// remove all Sheets tabs color
function resetTabColor() {
  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  sheets.forEach(function(sheet) {
    sheet.setTabColor(null);
  });
};

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

Saat Anda menjalankannya, itu akan menghapus semua warna tab dari Lembar Anda (mengaturnya kembali ke nol, mis. e. Tidak bernilai)

Inilah GIF yang menunjukkan warna tab yang ditambahkan dan dihapus melalui Makro (periksa bagian bawah gambar)

8. 10 Sembunyikan semua sheet 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(function(sheet) {
    if (sheet.getName() != SpreadsheetApp.getActiveSheet().getName()) 
      sheet.hideSheet();
  });
};

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

8. 11 Perlihatkan semua Lembar di Lembar Anda sekaligus

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

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

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

Saat Anda menjalankannya, itu akan menampilkan Lembar yang tersembunyi di Lembar Anda, untuk menghemat Anda harus melakukannya 1 per 1

Ini adalah GIF yang menunjukkan cara kerja makro sembunyikan dan sembunyikan

Anda dapat melihat bagaimana Sheet6, Sheet yang aktif, adalah satu-satunya yang tidak disembunyikan saat makro pertama dijalankan

8. 12 Mengatur Ulang Filter

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

Saya menggunakan filter pada tabel data saya sepanjang waktu, dan merasa agak mengganggu karena tidak ada cara untuk menghapus semua filter Anda sekaligus. Anda harus mengatur ulang setiap filter secara manual secara bergantian (memakan waktu, dan terkadang sulit untuk melihat kolom mana yang memiliki filter saat Anda memiliki kumpulan data yang sangat besar) ATAU Anda dapat sepenuhnya menghapus filter dan menambahkan kembali dari menu

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

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

// convert all formulas to values in the active sheet
function formulasToValuesActiveSheet() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getDataRange();
  range.copyValuesToRange(sheet, 1, range.getLastColumn(), 1, range.getLastRow());
};

Kembali ke 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 sekaligus

Inilah GIF yang menunjukkan masalah dan solusi makro

9. Sumber daya

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

Panduan referensi makro dalam bantuan Google Docs

Panduan referensi makro dalam dokumentasi Pengembang Google

Dan jika Anda ingin benar-benar mulai menggali kode Apps Script, Anda akan ingin mem-bookmark dokumentasi Google untuk Layanan Spreadsheet

Apakah mungkin mengotomatiskan Google Sheets?

Bisakah Google Sheet menarik data dari Excel secara otomatis?

Bagaimana cara memicu pengisian otomatis di Google Sheets?