Skrip google sheet mendapatkan nilai dari sheet lain

Bayangkan kita memiliki spreadsheet Google yang terpisah, dan kita perlu memasukkan nilai sel B2 ke sel D5 di lembar kerja Anda saat ini

function copyValueandPaste()
{  
    var source = SpreadsheetApp.openById('spread sheet id is here'); //Separate spreadsheet book
    var sourcesheet = source.getSheetByName('Sheet1'); //Sheet tab with source data
    var sourceCellValue = sourcesheet.getRange('B2').getValue(); // get B2 cell value
    
    var thisBook = SpreadsheetApp.getActive(); // Active spreadsheet book
    var thisSheet = thisBook.getSheetByName('Sheet1'); // Target sheet
    thisSheet.getRange('D5').setValue(sourceCellValue); //Set value to target sheet D5 cell
}

Anda dapat menemukan id spreadsheet dari URL Anda

Anda memiliki lebih dari satu spreadsheet data. Anda memerlukan informasi dari satu spreadsheet untuk berinteraksi dengan data di spreadsheet lain. Ada 2 cara untuk melakukan ini di Google Sheets. Menyalin dan menempel dari satu spreadsheet ke spreadsheet lain memiliki masalah jika data di spreadsheet lain diperbarui, maka Anda menggunakan data lama di spreadsheet terkait

PENTING

IMPORTRANGE adalah fungsi spreadsheet yang memungkinkan Anda menghubungkan satu spreadsheet ke spreadsheet lainnya. Dapatkan tautan ke spreadsheet lain dan cantumkan dalam tanda kutip di spreadsheet Anda menggunakan fungsi IMPORTRANGE. Ini menempatkan data dari satu spreadsheet ke dalam tab di spreadsheet lain

IMPORTRANGE(spreadsheet_url, range_string)

Perhatikan bahwa sepertinya data berada di spreadsheet sekunder tetapi sebenarnya ditampilkan dari lembar pertama. Ini berarti Anda tidak dapat mengedit data di lembar sekunder. Ini adalah satu arah berbagi data. Ini bagus, Anda tidak ingin data yang sama di banyak tempat, yang menyebabkan masalah. IMPORTRANGE memungkinkan Anda melakukan VLOOKUP atau bahkan mengurutkan data. Anda tidak bisa mengedit data di spreadsheet sekunder

Skrip Google Apps

Opsi lain untuk mendapatkan data dari satu Google Sheet ke yang lain adalah mengkodekannya. Gunakan menu Alat untuk membuka Editor Skrip. Di sana tentukan setiap spreadsheet dengan variabel

SpreadsheetApp. openByUrl()

Tentukan lembar dengan data dengan variabel dan panggil data pada lembar itu

fungsi importData() {
var ssOne = SpreadsheetApp. openByUrl('https. //dokumen. google. com/spreadsheets/d/1wwx3d8nIA-SurqSqSVwuFn-A0jQFI5Xqk’);
var ssDua = SpreadsheetApp. openByUrl('https. //dokumen. google. com/spreadsheets/d/10xdV8FN6RlkyAglqkGbVmG6cCeMbX242PqJEpJjnif8’);
var sheet = ssDua. getSheetByName('data');
var data = lembar. getDataRange(). dapatkan Nilai();

}

Sekarang Anda memiliki lebih banyak fleksibilitas tentang apa yang ingin Anda lakukan dengan data tersebut. Anda dapat melepaskan seluruh kumpulan data sebagai nilai yang tidak ditautkan ke tab baru (mirip dengan IMPORTRANGE tetapi tanpa membuatnya dinamis) atau cukup memanggil potongan data tertentu dari lembar lain untuk digunakan dalam spreadsheet Anda, membuatnya lebih cepat dimuat

Pemicu

Kiat menggunakan pemicu untuk menjadwalkan kapan mengimpor data dari lembar lain untuk memastikan bahwa data diperbarui secara berkala. Anda malah bisa mengkodekannya untuk mengimpor data saat Anda memanggilnya

Kasus lain yang sering terjadi dapat menimbulkan persyaratan untuk spreadsheet cadangan yang akan menyalin nilai dan format dari file sumber, tetapi bukan rumusnya. Beberapa pengguna mungkin juga ingin dokumen master mereka diperbarui secara otomatis, sesuai jadwal yang telah ditentukan

Jadi, jika Anda kesulitan menemukan solusi untuk tugas di atas, teruslah membaca artikel ini. Anda akan menemukan tips tentang cara menautkan data dari sheet dan spreadsheet lain, serta menemukan cara alternatif untuk melakukannya. Pada akhirnya, saya akan memberikan perbandingan lengkap dari pendekatan yang disebutkan agar Anda dapat mengevaluasi dan memilih

Daftar isi

Bagaimana mereferensikan data dari sheet atau tab lain – apa saja pilihannya?

Ada banyak kasus dan cara untuk mereferensikan data di Google Sheets. Anda dapat mereferensikan sheet lain di Google Sheets, sel atau rentang sel, serta kolom dan baris. Selain itu, Anda mungkin perlu mengimpor data dari satu sheet/spreadsheet ke sheet lain berdasarkan kriteria tertentu atau bahkan menggabungkan data dari beberapa sheet ke dalam satu tampilan.  

Google Sheets menawarkan beberapa opsi asli untuk referensi data termasuk fungsi IMPORTRANGE. Namun, Anda harus ingat itu

Fungsi dan opsi asli Google Sheets hanya memungkinkan Anda mereferensikan data, bukan mengimpornya.  

Ya, Google Sheets tidak menyediakan fungsionalitas yang benar-benar mengimpor data dari satu sheet/spreadsheet ke sheet lain meskipun nama fungsi IMPORTRANGE menyarankan sebaliknya. Mereka hanya mereferensikan rentang tertentu, mis. e. , jika data di lembar sumber Anda tidak tersedia, Anda tidak akan memiliki akses ke lembar tujuan Anda. Ini adalah kelemahan. Jadi, jika Anda perlu mengimpor data (rentang sel, kolom, atau baris) dari satu sheet ke sheet lain, Anda harus memilih solusi pihak ketiga, baik aplikasi web atau add-on Google Spreadsheet. Atau, Anda cukup menyalin data dari sheet lain di Google Sheets, tetapi ini lebih untuk pemula 🙂

Di bawah ini, kami membahas opsi Google Sheets asli untuk referensi data dan alat pihak ketiga untuk mengimpor data antara Google Sheets. Mari kita mulai dengan yang asli

Jika spreadsheet Excel menjadi fokus Anda, buka postingan blog kami tentang cara menautkan sheet di Excel

Cara menautkan sel di Google Sheets

Cara menautkan sel dari satu sheet ke tab lain di Google Sheets

Gunakan petunjuk di bawah untuk menautkan sel di Google Spreadsheet

• Buka sheet di Google Sheets
• Tempatkan kursor Anda di sel tempat Anda ingin menampilkan data yang diimpor
• Gunakan salah satu rumus di bawah ini

=Sheet1!A1

di mana Sheet1 adalah nama persis dari lembar referensi Anda, diikuti dengan tanda seru, dan A1 adalah sel tertentu yang ingin Anda impor datanya

Atau

='Sheet_1'!A1

Jika nama sheet menyertakan spasi atau karakter lain seperti ). ;”. -_*&, dll. , Anda harus memasukkan nama dalam tanda kutip tunggal

Dalam kasus saya, mari kita rujuk sel B4 dari lembar bernama daftar siswa.  

Rumus siap pakai untuk mereferensikan sheet lain di Google Sheets akan terlihat seperti ini

='list of students'!B4

Catatan. jika Anda ingin menautkan rentang sel dari satu lembar ke lembar lainnya, cukup tempatkan kursor Anda pada sel di lembar kerja tujuan data Anda yang sudah berisi salah satu rumus yang disebutkan di atas (

='Sheet_1'!A1

='Sheet_1'!A1

Cara menautkan sel dari sheet saat ini ke tab lain di Google Sheets

Ikuti panduan ini untuk mereferensikan data dari sheet saat ini dan sheet lainnya

• Buka sheet di Google Sheets
• Tempatkan kursor Anda di sel tempat Anda ingin data yang direferensikan muncul
• Gunakan salah satu rumus di bawah ini.  

Untuk menautkan rentang sel dari lembar saat ini, gunakan rumus berikut

={cell-range}

Di mana

='list of students'!B4

Gunakan rumus berikut untuk menautkan ke tab lain di Google Sheets

={Sheet1!cell-range}

Di mana

='list of students'!B4

='list of students'!B4

Catatan. jangan lupa cantumkan nama sheet dalam tanda kutip tunggal jika ada spasi atau karakter lain seperti ). ;”. -_*&, dll

Inilah tampilannya dalam contoh saya

Cara menautkan kolom di Google Sheets

Pada dasarnya, untuk menautkan kolom di Google Sheets, Anda hanya perlu memilih rentang sel yang merupakan kolom atau beberapa kolom dan mereferensikannya seperti yang kami jelaskan di atas. Namun, ada cara yang sedikit lebih baik untuk melakukan ini

Cara menautkan kolom dari satu sheet ke tab lain di Google Sheets

Untuk menautkan kolom atau kolom dari satu sheet ke tab lain di Google Sheets, gunakan rumus berikut

={Sheet1!columns}

Di mana

='list of students'!B4

='list of students'!B4

Dalam kasus saya, rumus siap pakai untuk mereferensikan sheet lain di Google Sheets akan terlihat seperti ini

={'list of students'!A:D}

Cara menautkan baris di Google Sheets

Untuk menautkan satu atau beberapa baris dari satu sheet ke tab lain di Google Sheets, gunakan rumus berikut

={Sheet1!rows}

di mana

='list of students'!B4

='list of students'!B4

Dalam kasus saya, rumus siap pakai untuk mereferensikan sheet lain di Google Sheets akan terlihat seperti ini

={'list of students'!2:5}

Cara menautkan sheet di Google Sheets

Anda dapat menggunakan rumus untuk mereferensikan kolom atau baris yang diperkenalkan di atas untuk menautkan rentang sel di antara lembar yang berbeda dari spreadsheet yang sama. Untuk tugas lanjutan lainnya, lihat kasus penggunaan berikut

Impor data dari satu lembar Google ke lembar lain berdasarkan kriteria

Misalnya, Anda ingin memfilter kumpulan data menurut kriteria tertentu dan mengimpor nilai yang difilter ke sheet lain. Anda dapat melakukannya menggunakan fungsi FILTER yang ditampilkan dalam contoh di atas. Ini sintaksnya

=FILTER(data_set,criterium1, criterium2,...)

• ='list of students'!B4

  7 – rentang sel untuk difilter.  

• ='list of students'!B4

  8 – kriteria untuk memfilter kumpulan data

Sebagai contoh, kami akan memfilter pengguna menurut negara, Australia, dan mengimpor hasilnya ke sheet lain

Inilah tampilan formula kita

='Sheet_1'!A1

Baca tentang fungsi FILTER Google Spreadsheet untuk menemukan lebih banyak opsi pemfilteran.

Cara mengimpor data dari beberapa sheet ke dalam satu kolom

Mari tinjau contoh ketika seseorang perlu menautkan data dari beberapa kolom di lembar berbeda menjadi satu

Dalam contoh saya, saya memiliki tiga tab berbeda dengan data penjualan. Penjualan 1, Penjualan 2, dan Penjualan 3. Tugas saya adalah mengumpulkan semua nama pelanggan di lembar yang disebut Semua pelanggan

Untuk melakukannya, saya akan menggunakan rumus ini

='Sheet_1'!A1

Di mana

• ='list of students'!B4

  9– adalah nama yang diberikan untuk kolom saya,

• ={cell-range}

  0 – ungkapan ini berarti bahwa saya mengambil semua data dari kolom C dari “Penjualan 1”, tidak termasuk nilai yang sama atau kurang dari 0

Hasilnya, saya mendapatkan nama semua pelanggan saya dari tiga lembar berbeda yang dikumpulkan dalam satu kolom

Salah satu keuntungan dari pendekatan ini adalah saya dapat mengubah nama lembar sumber data saya (tempat saya mengambil data), dan secara otomatis akan diperbarui dalam rumus

Lihat cara kerjanya

Pada saat yang sama, ada opsi yang lebih baik untuk menggabungkan data Anda dari beberapa Google Sheets menjadi satu tampilan master – kami telah membahasnya

Cara mereferensikan spreadsheet/buku kerja lain di Google Sheets melalui IMPORTRANGE

Opsi yang diperkenalkan di atas berfungsi untuk mereferensikan data antar lembar dari satu dokumen Google Sheets. Jika Anda perlu menautkan ke spreadsheet lain (sheet atau tab dari dokumen Google Sheets yang berbeda), maka Anda memerlukan IMPORTRANGE. Ini adalah fungsi Google Sheets yang memungkinkan Anda mengimpor rentang data dari satu spreadsheet ke spreadsheet lainnya. Namun, itu sebenarnya tidak mengimpor data tetapi hanya mereferensikannya

Untuk mereferensikan sheet lain di spreadsheet Google Sheets, ikuti petunjuk ini

• Buka spreadsheet tempat Anda ingin mengekspor data. Salin URL-nya
• Buka sheet tempat Anda ingin mengunggah data
• Tempatkan kursor Anda di sel tempat Anda ingin menampilkan data yang diimpor
• Gunakan sintaks seperti yang dijelaskan di bawah ini.  

='Sheet_1'!A1

Di mana

={cell-range}

={cell-range}

Misalnya

• Gunakan

  ={cell-range}

  _3 untuk menamai sheet dan rentang untuk mendapatkan informasi.  
• Gunakan

  ={cell-range}

  _4 untuk menyatakan rentang sel saja. Dalam hal ini, jika Anda tidak menentukan asal sheet yang akan diimpor, perilaku defaultnya adalah mengunggah data dari sheet pertama di spreadsheet Anda.  

Anda juga dapat menggunakan

='Sheet_1'!A1

jika B19, dalam hal ini, memerlukan URL spreadsheet yang diperlukan untuk menautkan data

Catatan. penggunaan IMPORTRANGE mengantisipasi bahwa spreadsheet tujuan Anda harus mendapatkan izin untuk menarik data dari dokumen lain (sumber). Setiap kali Anda ingin mengimpor informasi dari sumber baru, Anda akan diminta untuk mengizinkan tindakan ini terjadi. Setelah Anda memberikan akses, siapa saja yang memiliki hak edit di spreadsheet tujuan Anda akan dapat menggunakan IMPORTRANGE untuk mengimpor data dari sumber. Akses akan valid selama orang yang menyediakannya ada di sumber data. Untuk informasi lebih lanjut tentang fungsi Google Sheets ini, baca Tutorial IMPORTRANGE kami

Dalam kasus saya, rumus saya terlihat seperti ini

='Sheet_1'!A1

Atau

='Sheet_1'!A1

karena siswa baru adalah satu-satunya lembar yang saya miliki di spreadsheet saya

Namun, solusi IMPORTRANGE memiliki beberapa kelemahan. Yang akan saya sebutkan berkaitan dengan dampak negatif pada kinerja spreadsheet secara keseluruhan. Anda dapat menggunakan Google untuk IMPORTRANGE di forum Komunitas Google untuk melihat sejumlah utas yang menjelaskan masalah ini secara lebih mendetail. Pada dasarnya, semakin banyak rumus IMPORTRANGE yang Anda miliki di lembar kerja Anda, semakin lambat produktivitas keseluruhannya. Spreadsheet akan berhenti bekerja atau membutuhkan banyak waktu untuk diproses dan karenanya menampilkan data Anda

Cara mereferensikan sheet lain di Google Sheets tanpa formula

Seperti yang kami catat di awal, tidak ada cara asli untuk mengimpor data dari satu sheet atau spreadsheet ke yang lain di Google Sheets. Jadi, untuk melakukan pekerjaan ini, Anda memerlukan add-on Google Sheets atau alat ETL. Alat prerangkai. io menyediakan kedua opsi di luar kotak

Alat prerangkai. io adalah platform otomatisasi dan analitik data. Ini menyediakan alat ETL untuk mengotomatiskan ekspor data dari berbagai sumber ke Google Sheets, Excel, atau BigQuery.  

Dengan Coupler. Integrasi Google Sheets .io Anda dapat menautkan sheet dan spreadsheet – mari kita lihat cara kerjanya

Cara mengimpor data dari lembar atau spreadsheet Google lain

Daftar ke Coupler. io, klik Tambahkan importir, dan pilih Google Spreadsheet sebagai aplikasi sumber dan tujuan

Beri nama importir Anda dan selesaikan tiga langkah. sumber, tujuan, dan jadwal

Tidak ada waktu untuk membaca? . io dan menyiapkan importir Google Sheets

Sumber

• Hubungkan akun Google Anda, lalu di Google Drive Anda, pilih spreadsheet dan sheet untuk mengimpor data. Anda dapat memilih beberapa sheet jika ingin menggabungkan data dari sheet tersebut menjadi satu tampilan master

Secara opsional, Anda dapat menentukan rentang untuk mengekspor data, misalnya dari A1. Z9, jika Anda tidak perlu menarik data dari seluruh lembar.  

Lompat ke pengaturan tujuan

Tujuan

• Sambungkan ke akun Google Anda, lalu pilih file di Google Drive Anda, dan sheet untuk memuat data, Anda dapat membuat sheet baru dengan memasukkan nama baru

Secara opsional, Anda dapat mengubah sel pertama tempat mengimpor rentang data Anda (sel A1 ditetapkan secara default) dan mengubah mode impor untuk data Anda. ganti informasi Anda sebelumnya atau tambahkan baris baru di bawah entri yang diimpor terakhir. Anda juga dapat mengaktifkan fitur kolom yang terakhir diperbarui jika Anda ingin menambahkan kolom ke spreadsheet dengan informasi tentang penyegaran tanggal dan waktu terakhir

Klik Simpan dan Jalankan untuk segera menjalankan impor. Jika Anda ingin mengotomatiskan impor data sesuai jadwal, selesaikan langkah lainnya

Jadwal

Aktifkan Penyegaran data otomatis dan sesuaikan jadwal

• Pilih Interval (dari 15 menit hingga setiap bulan)
• Pilih Hari dalam seminggu
• Pilih Preferensi waktu
• Jadwalkan zona waktu

Terakhir, klik Simpan dan Jalankan untuk menautkan Google Sheet Anda ke sheet lain.  

Catatan. Anda juga dapat menggunakan Coupler. io sebagai add-on Google Sheets untuk mendapatkan akses lebih cepat ke alat di spreadsheet Anda. Untuk ini, instal dari Google Workspace Marketplace dan siapkan seperti yang kami jelaskan di atas

Cara mereferensikan sel di buku kerja lain di Google Sheets dengan Coupler. io

Alat prerangkai. io memungkinkan Anda untuk tidak hanya mereferensikan buku kerja lain di Google Sheets tetapi juga mengimpor rentang sel yang tepat yang hanya sesuai dengan rentang yang ditentukan. Misalnya, Anda ingin menarik data dari rentang A1. C8 dari satu buku kerja dan masukkan ke dalam kisaran C1. E8 dari buku kerja lain. Untuk ini, lakukan penyetelan seperti yang dijelaskan di atas, tetapi tetapkan juga parameter berikut

• Rentang buku kerja sumber – di sini Anda harus menentukan rentang sel untuk mengimpor data. Dalam contoh kita, A1. C8

• Alamat sel / Rentang buku kerja tujuan – di sini Anda harus menentukan rentang sel untuk mengimpor data. Dalam contoh kita, C1. E8

Klik Simpan dan Jalankan dan sambut data Anda di rentang sel yang ditentukan

Mengimpor data bukan satu-satunya hal yang dapat Anda lakukan dengan Coupler. io. Solusi ETL ini juga memungkinkan Anda menggabungkan atau menggabungkan data dari berbagai Google Sheets dan bahkan sumber. Mari kita lihat contoh sederhana di bawah ini

Tarik data dari beberapa sheet dari satu dokumen Google Sheets

Kami memiliki dokumen Google Sheets dengan lima lembar yang berisi data tentang penawaran untuk tahun yang berbeda. 2016, 2017, 2018, 2019, dan 2020

Alih-alih menyalin data secara manual dari setiap sheet atau membuat formula IMPORTRANGE yang rumit, kita cukup mencantumkan semua sheet ini saat menyiapkan importir Google Sheets sebagai berikut

Klik Save and Run dan data dari sheet akan ditarik ke sheet tujuan kita. Apa manfaat utamanya? . Selain itu, baris judul dari setiap lembar kecuali yang pertama dilewati, sehingga Anda mendapatkan penggabungan data yang mulus

Jika Anda ingin melakukan hal yang sama menggunakan IMPORTRANGE, seperti inilah tampilan rumus Anda

='Sheet_1'!A1

Penting untuk menentukan rentang data yang tepat seperti

={cell-range}

Opsi mana yang berfungsi paling baik di Google Spreadsheet untuk ditautkan ke sheet atau tab lain?

Di bawah ini saya telah menyusun tabel perbandingan yang secara singkat menjelaskan pro dan kontra penggunaan fungsionalitas asli vs Coupler. io saat menghubungkan data antar spreadsheet

Jika Anda tertarik dengan perbandingan IMPORTRANGE vs. Alat prerangkai. io dalam hal menautkan spreadsheet, lihat posting blog khusus kami di

Google Sheets ke Google Sheets bukan satu-satunya integrasi yang disediakan oleh Coupler. io

Bisakah saya mengimpor data di Google Sheets dari sheet lain termasuk pemformatan?

Sayangnya, tidak satu pun dari opsi di atas yang memungkinkan Anda mengimpor pemformatan sel saat Anda mereferensikan buku kerja Google Sheets lainnya. Logika IMPORTRANGE, FILTER, dan opsi asli Google Sheets lainnya tidak memerlukan transfer data yang sebenarnya. Mereka hanya merujuk dan menampilkan data dari sel sumber. Alat prerangkai. io adalah satu-satunya opsi yang menyalin data dari sumber, tetapi hanya mengimpor data mentah tanpa pemformatan apa pun. Pada saat yang sama, Anda dapat menggunakan Coupler. io untuk menautkan file Excel, serta Excel dan Google Sheets

TETAPI Anda selalu dapat menggunakan manfaat Skrip Google Apps untuk membuat fungsi ubahsuaian sesuai kebutuhan Anda. Misalnya, skrip berikut akan memungkinkan Anda mentransfer data dari satu sheet atau spreadsheet ke yang lain

='Sheet_1'!A1

Anda harus membuka Ekstensi > Skrip Aplikasi. Kemudian masukkan skrip di Kode. gs dan tentukan parameter yang diperlukan

• ID spreadsheet sumber dan tujuan
• Nama lembar sumber dan tujuan

(Jika Anda mengimpor data antar-sheet, ID spreadsheet sumber dan tujuan akan sama)

Jika sudah siap, klik Jalankan dan data Anda termasuk pemformatan akan diimpor ke lembar tujuan.  

Catatan. Solusi ini mungkin tidak cocok untuk proyek Anda, jadi Anda perlu memperbarui skrip apa pun yang Anda perlukan

Apa yang harus dipilih untuk mereferensikan data dari sheet atau tab lain

Tidak ada solusi satu ukuran untuk semua dan Anda harus berhati-hati saat mengambil satu atau lain cara. Baik Anda ingin menautkan sheet, spreadsheet, membuat tampilan gabungan, atau mencadangkan dokumen – pastikan untuk mempertimbangkan semua kelebihan dan kekurangan keduanya dan memilih opsi yang tepat untuk mencapai hasil terbaik

Jika Anda hanya memiliki sedikit catatan di spreadsheet dan sedikit formula, maka Anda dapat menggunakan pendekatan berbasis formula termasuk IMPORTRANGE yang disediakan oleh Google Sheets. Ini akan berfungsi untuk pelaporan reguler atau analitik tingkat rendah

Namun, jika Anda memiliki banyak data dan ada banyak kalkulasi dalam dokumen Anda, maka Anda harus memilih metode impor data daripada mereferensikannya. Alat prerangkai. io akan menjadi solusi yang lebih stabil dalam kasus ini. Ini akan memberikan transfer data yang aman dan memastikan bahwa Anda memiliki akses ke data meskipun sumber data rusak atau tidak tersedia. Pilih dengan bijak dan semoga berhasil

Bagaimana cara mendapatkan data dari lembar lain di skrip Google?

Bagaimana cara menarik data dari sheet lain di Google Sheets berdasarkan kriteria?

Bagaimana cara saya memeriksa apakah ada nilai di lembar lain di Google Sheets?

Bagaimana cara mendapatkan nilai sel dalam skrip Google Sheets?