Pada artikel ini saya akan menunjukkan kepada Anda bagaimana memungkinkan beberapa item dipilih menggunakan fitur validasi data drop-down di Google Sheets
kita akan menggunakan untuk contoh. Anda dapat membuat salinan ini untuk diedit sendiri dengan mengeklik function showDialog() { var html = HtmlService.createTemplateFromFile('Page').evaluate(); SpreadsheetApp.getUi().showSidebar(html); }6
Di bagian bawah artikel adalah panduan video yang saya rekam untuk solusi ini. 👇
Masalah 🤔
Putra saya yang berusia lima tahun mengajukan pertanyaan yang memicu penyelaman mendalam ke Google Apps Script. Dia ingin beberapa item dipilih dari daftar drop-down validasi data
Serahkan pada anak berusia lima tahun untuk mengirim saya ke Google, YouTube, dan lainnya untuk mencari solusi spreadsheet. 😅
Kami telah membuat spreadsheet yang menampilkan informasi tentang Teenage Mutant Ninja Turtles. Nama, ulang tahun, usia, warna favorit
Saya menyoroti kekuatan luar biasa dari spreadsheet untuk mengatur, menghitung, dan memvisualisasikan informasi. Hal-hal mengasuh anak berusia lima tahun yang khas
Untuk kolom warna favorit, kami menggunakan daftar drop-down untuk memilih dari daftar warna
Ini adalah fitur yang cukup sederhana untuk digunakan di Google Sheets. Untuk membuat daftar drop-down, pilih function showDialog() { var html = HtmlService.createTemplateFromFile('Page').evaluate(); SpreadsheetApp.getUi().showSidebar(html); }7 dari menu
Memperbarui. fitur yang sama sekarang juga tersedia saat Anda mengklik kanan sebuah sel
Dalam kedua kasus tersebut, menu validasi data akan muncul di mana Anda dapat mengatur kondisi Anda
Kami memiliki daftar warna kami di sel function showDialog() { var html = HtmlService.createTemplateFromFile('Page').evaluate(); SpreadsheetApp.getUi().showSidebar(html); }8 jadi kami memilih function showDialog() { var html = HtmlService.createTemplateFromFile('Page').evaluate(); SpreadsheetApp.getUi().showSidebar(html); }9 di bagian Kriteria dan kemudian memasukkan rentang itu
Kami ingin ini disalin ke sel lain tanpa memengaruhi rentang itu, jadi kami menguncinya di tempatnya, menggunakan tanda $. var valid = function(){ try{ return SpreadsheetApp.getActiveRange().getDataValidation().getCriteriaValues()[0].getValues(); }catch(e){ return null } } _0
Ini memungkinkan validasi untuk disalin ke sel lain sambil mempertahankan referensi sel tersebut untuk daftar nilai warna
Google Sheets Hanya Mengizinkan Satu Pilihan 🚩
Masalahnya adalah, Google Sheets hanya mengizinkan satu pilihan. Kami ingin Leonardo memiliki banyak warna favorit
Untungnya, Skrip Google Apps memungkinkan kode khusus ditulis dalam Google Sheets, dan kami menggunakan ini untuk menyelesaikan masalah kami
Saya menemukan kode untuk ini dari video YouTube oleh Alexander Ivanov dan mulai memperbarui informasi dengan video yang lebih jelas dan penjelasan saya sendiri
Cara Menggunakan Apps Script🧑💻
Buka layar Apps Script dengan memilih var valid = function(){ try{ return SpreadsheetApp.getActiveRange().getDataValidation().getCriteriaValues()[0].getValues(); }catch(e){ return null } } 1 dari bilah menu
Anda dapat membuat file menggunakan ikon plus var valid = function(){ try{ return SpreadsheetApp.getActiveRange().getDataValidation().getCriteriaValues()[0].getValues(); }catch(e){ return null } } 2. Untuk proyek ini, kami memerlukan file var valid = function(){ try{ return SpreadsheetApp.getActiveRange().getDataValidation().getCriteriaValues()[0].getValues(); }catch(e){ return null } } _3 dan file var valid = function(){ try{ return SpreadsheetApp.getActiveRange().getDataValidation().getCriteriaValues()[0].getValues(); }catch(e){ return null } } 4
Kode. File gs
Mulai di Kode. file gs
Hal pertama yang kita inginkan adalah drop-down dari toolbar Google Sheets untuk menjalankan kode kita
Untuk melakukannya, kami menggunakan kode berikut untuk menambahkan opsi UI var valid = function(){ try{ return SpreadsheetApp.getActiveRange().getDataValidation().getCriteriaValues()[0].getValues(); }catch(e){ return null } } 5 ke bilah menu. Setelah mengklik, itu akan menjalankan fungsi var valid = function(){ try{ return SpreadsheetApp.getActiveRange().getDataValidation().getCriteriaValues()[0].getValues(); }catch(e){ return null } } 6. Dengan menggabungkan metode ini dalam fungsi var valid = function(){ try{ return SpreadsheetApp.getActiveRange().getDataValidation().getCriteriaValues()[0].getValues(); }catch(e){ return null } } 7 bawaan, menu ini di toolbar akan ditambahkan segera setelah kita membuka spreadsheet
function onOpen(e) { SpreadsheetApp.getUi() .createMenu('Multiple Select Data Validation') .addItem('Show dialog', 'showDialog') .addToUi(); }Seringkali ada penundaan singkat beberapa detik sebelum menu khusus terlihat di menu. Berikan beberapa detik dan itu akan muncul
Fungsi var valid = function(){ try{ return SpreadsheetApp.getActiveRange().getDataValidation().getCriteriaValues()[0].getValues(); }catch(e){ return null } } 6 akan membuat variabel HTML dari template yang akan kita buat sebentar lagi. Itu kemudian menggunakan metode bawaan var valid = function(){ try{ return SpreadsheetApp.getActiveRange().getDataValidation().getCriteriaValues()[0].getValues(); }catch(e){ return null } } _9 untuk membuat sidebar dengan html itu
function showDialog() { var html = HtmlService.createTemplateFromFile('Page').evaluate(); SpreadsheetApp.getUi().showSidebar(html); }Kemudian kami memiliki fungsi lain, function fillCell(e){ var s = []; for(var i in e){ if(i.substr(0, 2) == 'ch') s.push(e[i]); } if(s.length) SpreadsheetApp.getActiveRange().setValue(s.join(', ')); }0, yang akan memeriksa sel saat ini untuk setiap kriteria validasi data dan mengembalikan nilai tersebut dalam array 2 dimensi
var valid = function(){ try{ return SpreadsheetApp.getActiveRange().getDataValidation().getCriteriaValues()[0].getValues(); }catch(e){ return null } } _Dan fungsi terakhir kita, function fillCell(e){ var s = []; for(var i in e){ if(i.substr(0, 2) == 'ch') s.push(e[i]); } if(s.length) SpreadsheetApp.getActiveRange().setValue(s.join(', ')); }_1, akan membuat sebuah array untuk menampung daftar resultan nilai yang kita inginkan untuk diisi sel. Itu kemudian mendorong string ini ke array dan memisahkannya dengan koma
Terakhir, ini menggunakan metode bawaan, function fillCell(e){ var s = []; for(var i in e){ if(i.substr(0, 2) == 'ch') s.push(e[i]); } if(s.length) SpreadsheetApp.getActiveRange().setValue(s.join(', ')); }2 untuk mengisi sel saat ini dengan nilai yang dipisahkan koma (warna favorit kami)
(function fillCell(e){ var s = []; for(var i in e){ if(i.substr(0, 2) == 'ch') s.push(e[i]); } if(s.length) SpreadsheetApp.getActiveRange().setValue(s.join(', ')); }1 dan function fillCell(e){ var s = []; for(var i in e){ if(i.substr(0, 2) == 'ch') s.push(e[i]); } if(s.length) SpreadsheetApp.getActiveRange().setValue(s.join(', ')); }0 disebut di Halaman. kode html yang akan kita bahas. )
function fillCell(e){ var s = []; for(var i in e){ if(i.substr(0, 2) == 'ch') s.push(e[i]); } if(s.length) SpreadsheetApp.getActiveRange().setValue(s.join(', ')); }Ini adalah kode lengkap untuk file var valid = function(){ try{ return SpreadsheetApp.getActiveRange().getDataValidation().getCriteriaValues()[0].getValues(); }catch(e){ return null } } _3
function onOpen(e) { SpreadsheetApp.getUi() .createMenu('Multiple Select Data Validation') .addItem('Show dialog', 'showDialog') .addToUi(); } function showDialog() { var html = HtmlService.createTemplateFromFile('Page').evaluate(); SpreadsheetApp.getUi().showSidebar(html); } var valid = function(){ try{ return SpreadsheetApp.getActiveRange().getDataValidation().getCriteriaValues()[0].getValues(); }catch(e){ return null } } function fillCell(e){ var s = []; for(var i in e){ if(i.substr(0, 2) == 'ch') s.push(e[i]); } if(s.length) SpreadsheetApp.getActiveRange().setValue(s.join(', ')); }Halaman. html
Sekarang buat dan buka file bernama var valid = function(){ try{ return SpreadsheetApp.getActiveRange().getDataValidation().getCriteriaValues()[0].getValues(); }catch(e){ return null } } 4
Ini akan mengontrol sidebar pop-up di mana kami akan menangani beberapa pilihan kami, dan akan berisi
- formulir dengan kotak centang di sebelah setiap opsi
- tombol untuk mengisi sel saat ini
- tombol untuk mendapatkan validasi dari sel saat ini
Kami akan menggunakan HTML templated untuk contoh kami. Pertama, buat variabel, function fillCell(e){ var s = []; for(var i in e){ if(i.substr(0, 2) == 'ch') s.push(e[i]); } if(s.length) SpreadsheetApp.getActiveRange().setValue(s.join(', ')); }_7, dengan memanggil fungsi function fillCell(e){ var s = []; for(var i in e){ if(i.substr(0, 2) == 'ch') s.push(e[i]); } if(s.length) SpreadsheetApp.getActiveRange().setValue(s.join(', ')); }8 yang kita buat di atas. Kami menggunakan function fillCell(e){ var s = []; for(var i in e){ if(i.substr(0, 2) == 'ch') s.push(e[i]); } if(s.length) SpreadsheetApp.getActiveRange().setValue(s.join(', ')); }_9 sintaks untuk menulis kode dalam template html
<? var data = valid(); ?>Kemudian kami membuat formulir untuk menampung semua data yang diambil dari fungsi function fillCell(e){ var s = []; for(var i in e){ if(i.substr(0, 2) == 'ch') s.push(e[i]); } if(s.length) SpreadsheetApp.getActiveRange().setValue(s.join(', ')); }8
<form id="form" name="form"> </form>Dan kami akan menjalankan beberapa kode untuk membuat daftar kotak centang kami di dalam formulir ini. Pertama, kami memeriksa apakah data dalam sel bertipe function onOpen(e) { SpreadsheetApp.getUi() .createMenu('Multiple Select Data Validation') .addItem('Show dialog', 'showDialog') .addToUi(); } function showDialog() { var html = HtmlService.createTemplateFromFile('Page').evaluate(); SpreadsheetApp.getUi().showSidebar(html); } var valid = function(){ try{ return SpreadsheetApp.getActiveRange().getDataValidation().getCriteriaValues()[0].getValues(); }catch(e){ return null } } function fillCell(e){ var s = []; for(var i in e){ if(i.substr(0, 2) == 'ch') s.push(e[i]); } if(s.length) SpreadsheetApp.getActiveRange().setValue(s.join(', ')); }1
<? if(Object.prototype.toString.call(data) === '[object Array]') { ?>function fillCell(e){ var s = []; for(var i in e){ if(i.substr(0, 2) == 'ch') s.push(e[i]); } if(s.length) SpreadsheetApp.getActiveRange().setValue(s.join(', ')); }_8 mengembalikan array dua dimensi karena menggunakan metode bawaan function onOpen(e) { SpreadsheetApp.getUi() .createMenu('Multiple Select Data Validation') .addItem('Show dialog', 'showDialog') .addToUi(); } function showDialog() { var html = HtmlService.createTemplateFromFile('Page').evaluate(); SpreadsheetApp.getUi().showSidebar(html); } var valid = function(){ try{ return SpreadsheetApp.getActiveRange().getDataValidation().getCriteriaValues()[0].getValues(); }catch(e){ return null } } function fillCell(e){ var s = []; for(var i in e){ if(i.substr(0, 2) == 'ch') s.push(e[i]); } if(s.length) SpreadsheetApp.getActiveRange().setValue(s.join(', ')); }3. Jadi, Anda dapat membayangkan ini dikembalikan sebagai array array dengan masing-masing individu menjadi salah satu warna favorit
//for illustration only; this is the type of 2D array that valid() will return favoriteColors = [ ["purple"], ["red"], ["white"], ["black"] ]Ini akan membantu saat kita melihat potongan kode berikutnya yang mungkin tampak berlebihan
Kita perlu mengakses setiap warna – string. Kami melakukan ini dengan membuat function onOpen(e) { SpreadsheetApp.getUi() .createMenu('Multiple Select Data Validation') .addItem('Show dialog', 'showDialog') .addToUi(); } function showDialog() { var html = HtmlService.createTemplateFromFile('Page').evaluate(); SpreadsheetApp.getUi().showSidebar(html); } var valid = function(){ try{ return SpreadsheetApp.getActiveRange().getDataValidation().getCriteriaValues()[0].getValues(); }catch(e){ return null } } function fillCell(e){ var s = []; for(var i in e){ if(i.substr(0, 2) == 'ch') s.push(e[i]); } if(s.length) SpreadsheetApp.getActiveRange().setValue(s.join(', ')); }_4 loop. Loop pertama berulang melalui setiap posisi dalam larik warna favorit
<? for (var i = 0; i < data.length; i++) { ?>Loop kedua mengulang melalui setiap elemen dalam array interior. Dalam kasus kita, ini akan selalu menjadi satu elemen karena masing-masing array ini memiliki panjang 1
function showDialog() { var html = HtmlService.createTemplateFromFile('Page').evaluate(); SpreadsheetApp.getUi().showSidebar(html); }0Ingat, function onOpen(e) { SpreadsheetApp.getUi() .createMenu('Multiple Select Data Validation') .addItem('Show dialog', 'showDialog') .addToUi(); } function showDialog() { var html = HtmlService.createTemplateFromFile('Page').evaluate(); SpreadsheetApp.getUi().showSidebar(html); } var valid = function(){ try{ return SpreadsheetApp.getActiveRange().getDataValidation().getCriteriaValues()[0].getValues(); }catch(e){ return null } } function fillCell(e){ var s = []; for(var i in e){ if(i.substr(0, 2) == 'ch') s.push(e[i]); } if(s.length) SpreadsheetApp.getActiveRange().setValue(s.join(', ')); }_5 adalah larik dengan panjang 1. Loop function onOpen(e) { SpreadsheetApp.getUi() .createMenu('Multiple Select Data Validation') .addItem('Show dialog', 'showDialog') .addToUi(); } function showDialog() { var html = HtmlService.createTemplateFromFile('Page').evaluate(); SpreadsheetApp.getUi().showSidebar(html); } var valid = function(){ try{ return SpreadsheetApp.getActiveRange().getDataValidation().getCriteriaValues()[0].getValues(); }catch(e){ return null } } function fillCell(e){ var s = []; for(var i in e){ if(i.substr(0, 2) == 'ch') s.push(e[i]); } if(s.length) SpreadsheetApp.getActiveRange().setValue(s.join(', ')); }6 tidak menghitung huruf dalam string di dalam array – kita hanya menghitung panjang array
Jadi, kami mengulangi setiap item dalam larik 2D dan membuat input kotak centang untuk masing-masing item
Ini menambahkan function onOpen(e) { SpreadsheetApp.getUi() .createMenu('Multiple Select Data Validation') .addItem('Show dialog', 'showDialog') .addToUi(); } function showDialog() { var html = HtmlService.createTemplateFromFile('Page').evaluate(); SpreadsheetApp.getUi().showSidebar(html); } var valid = function(){ try{ return SpreadsheetApp.getActiveRange().getDataValidation().getCriteriaValues()[0].getValues(); }catch(e){ return null } } function fillCell(e){ var s = []; for(var i in e){ if(i.substr(0, 2) == 'ch') s.push(e[i]); } if(s.length) SpreadsheetApp.getActiveRange().setValue(s.join(', ')); }7 dan function onOpen(e) { SpreadsheetApp.getUi() .createMenu('Multiple Select Data Validation') .addItem('Show dialog', 'showDialog') .addToUi(); } function showDialog() { var html = HtmlService.createTemplateFromFile('Page').evaluate(); SpreadsheetApp.getUi().showSidebar(html); } var valid = function(){ try{ return SpreadsheetApp.getActiveRange().getDataValidation().getCriteriaValues()[0].getValues(); }catch(e){ return null } } function fillCell(e){ var s = []; for(var i in e){ if(i.substr(0, 2) == 'ch') s.push(e[i]); } if(s.length) SpreadsheetApp.getActiveRange().setValue(s.join(', ')); }8 yang dimulai dengan "ch" dan kemudian menambahkan posisi array elemen. Itu juga menarik nilai (warna) itu sendiri sebagai function onOpen(e) { SpreadsheetApp.getUi() .createMenu('Multiple Select Data Validation') .addItem('Show dialog', 'showDialog') .addToUi(); } function showDialog() { var html = HtmlService.createTemplateFromFile('Page').evaluate(); SpreadsheetApp.getUi().showSidebar(html); } var valid = function(){ try{ return SpreadsheetApp.getActiveRange().getDataValidation().getCriteriaValues()[0].getValues(); }catch(e){ return null } } function fillCell(e){ var s = []; for(var i in e){ if(i.substr(0, 2) == 'ch') s.push(e[i]); } if(s.length) SpreadsheetApp.getActiveRange().setValue(s.join(', ')); }9 selain teks yang ditampilkan
Tangkapan layar ini dapat membantu menghubungkan titik-titik mental
Jika pernyataan <? var data = valid(); ?>0 awal kami gagal, kami akan menampilkan <? var data = valid(); ?>1 yang menunjukkan bahwa mungkin tidak ada aturan validasi data di sel itu saat menautkan ke artikel dukungan yang menunjukkan cara membuat daftar tarik-turun dalam sel
function showDialog() { var html = HtmlService.createTemplateFromFile('Page').evaluate(); SpreadsheetApp.getUi().showSidebar(html); }_2Cara Membuat Kode Tombol
Maka kita membutuhkan dua tombol kita
Tombol pertama menarik validasi data dari sel. Ini menjalankan fungsi <? var data = valid(); ?>2 yang kami buat di var valid = function(){ try{ return SpreadsheetApp.getActiveRange().getDataValidation().getCriteriaValues()[0].getValues(); }catch(e){ return null } } 3 dan membuat formulir entri kotak centang jika ada nilai validasi data
function showDialog() { var html = HtmlService.createTemplateFromFile('Page').evaluate(); SpreadsheetApp.getUi().showSidebar(html); }_3Tombol kedua mengisi nilai yang dipilih ke dalam sel saat ini. Inilah yang kami kejar selama ini. Ini menjalankan fungsi function fillCell(e){ var s = []; for(var i in e){ if(i.substr(0, 2) == 'ch') s.push(e[i]); } if(s.length) SpreadsheetApp.getActiveRange().setValue(s.join(', ')); }_1 di var valid = function(){ try{ return SpreadsheetApp.getActiveRange().getDataValidation().getCriteriaValues()[0].getValues(); }catch(e){ return null } } 3
function showDialog() { var html = HtmlService.createTemplateFromFile('Page').evaluate(); SpreadsheetApp.getUi().showSidebar(html); }_4Ini kode lengkap untuk var valid = function(){ try{ return SpreadsheetApp.getActiveRange().getDataValidation().getCriteriaValues()[0].getValues(); }catch(e){ return null } } _4
function showDialog() { var html = HtmlService.createTemplateFromFile('Page').evaluate(); SpreadsheetApp.getUi().showSidebar(html); }5Ringkasan
Ya, ini adalah solusi yang cukup rumit untuk sesuatu yang relatif sederhana. Mungkin Google akan menambahkan ini ke fungsi asli di beberapa titik. Mereka baru-baru ini memperbarui Validasi Data untuk menyertakan fungsionalitas yang lebih modern dan mudah digunakan, jadi tidak mustahil
Sampai saat itu, ini merupakan solusi yang bagus untuk saya, dan saya berterima kasih kepada Alexander atas kode awalnya
Saya harap artikel ini telah membantu Anda memahami kode sedikit lebih baik dan memberdayakan Anda untuk membuat solusi Google Sheets khusus Anda sendiri
Seperti yang dijanjikan, inilah video panduan saya
Panduan Video 📽️
Terima kasih
Terima kasih sudah membaca. Jika menurut Anda ini bermanfaat, saya akan senang jika Anda mengikuti saya dan menyapa 👋 di LinkedIn dan YouTube tempat Anda dapat menemukan lebih banyak konten seperti ini
Selamat bersenang-senang
IKLAN
IKLAN
IKLAN
IKLAN
IKLAN
IKLAN
IKLAN
IKLAN
IKLAN
IKLAN
Saya seorang pengembang, seniman, dan penikmat kopi dari Tennessee. Saya mengoperasikan jaringan kafe/roastery espresso, dan saya menikmati podcasting dan ultra-running
Jika Anda membaca sejauh ini, tweet ke penulis untuk menunjukkan bahwa Anda peduli. Tweet terima kasih
Belajar kode secara gratis. Kurikulum open source freeCodeCamp telah membantu lebih dari 40.000 orang mendapatkan pekerjaan sebagai pengembang. Memulai