Ada banyak cara untuk mendorong data dari SQL Server ke Google Sheets, termasuk menggunakan Python atau Google Apps Script. Meskipun Python bagus untuk kueri SQL yang berat, saya merasa agak merepotkan dalam hal penjadwalan. Sementara itu, Skrip Google Apps bekerja dengan sangat baik untuk kueri SQL ukuran menengah dan pemicu membuatnya sangat mudah untuk dijadwalkan, juga tidak perlu menggunakan API apa pun
Pada artikel ini, saya akan berbagi dengan Anda cara mendorong data dari MS SQL Server ke Google Sheets menggunakan Google Apps Script dan tips yang saya pelajari saat menerapkan solusi tersebut
Langkah-langkah untuk mengimpor data dari MS SQL Server ke Google Sheets antara lain
- Buat pengguna yang memiliki akses ke SQL Server (opsional)
- Daftar putih Alamat IP Google
- Buat fungsi untuk membaca data dari SQL
- Buat fungsi untuk mendorong data ke Google Sheets
- Satukan semuanya
1. Buat pengguna yang memiliki akses ke SQL Server
Misalkan Anda sudah bekerja dengan SQL Server Anda, ini bukan langkah wajib. Anda juga dapat menggunakan akun Anda sendiri
Namun, saat menghubungkan ke SQL Server menggunakan Google Apps Script, Anda perlu secara eksplisit mengekspos nama pengguna dan kata sandi Anda yang tidak optimal untuk menggunakan akun reguler Anda. Oleh karena itu disarankan agar Anda membuat pengguna SQL khusus untuk Google Apps Script dan pengguna ini hanya dapat memiliki akses baca ke sekumpulan tabel
Jika Anda memiliki tim Infrastruktur yang mengelola akses ke berbagai alat TI di perusahaan Anda, mereka dapat membantu Anda dalam tugas ini
2. Daftar putih Alamat IP Google
Hanya setelah alamat IP masuk daftar putih, layanan Google JDBC dapat membuat koneksi database
Dalam praktiknya, itu berarti Anda perlu memasukkan daftar IP ini ke daftar putih (ini adalah dokumen resmi yang dirujuk Google dari dokumen JDBC). Ini juga bisa dilakukan oleh tim Infrastruktur
3. Berfungsi untuk Membaca data dari SQL
Pertama, Anda membuka SpreadSheet kosong, lalu buka Tools > Script Editor. Anda dapat menempelkan kode berikut ke Editor Skrip Anda
Fungsi ini mengimplementasikan logika berikut
- Hubungkan ke server SQL
- Meminta basis data
- Simpan hasil kueri SQL sebagai larik multidimensi
masukan fungsi
- Nama database SQL yang ingin Anda sambungkan
- Kueri SQL untuk mengambil data Anda
Keluaran fungsi
Sebuah array multi-dimensi dengan
- panjang array = jumlah baris yang dikembalikan oleh kueri SQL, dan
- panjang setiap elemen array = jumlah kolom yang dikembalikan oleh kueri SQL
Kiat saya
- Banyak tutorial memberi tahu Anda untuk memasukkan nama server Anda ke variabel server di atas. Jika Anda mencoba melakukan itu dan mendapatkan kesalahan ini,
coba dengan alamat IP publik basis data Anda (yang dapat ditemukan di Azure Portal).
- Layanan JDBC memungkinkan Anda terhubung ke database MySQL dan Oracle juga, baca selengkapnya di sini
4. Berfungsi untuk Mendorong data ke Google Sheets
Fungsi berikut dapat digunakan untuk menambahkan array multidimensi ke Google Sheets
- Ini pertama kali menemukan baris terakhir dengan data lembar
- Kemudian, di bagian bawah data yang ada, dapatkan rentang lembar yang sama dengan ukuran array
- Kemudian atur nilai rentang itu agar sama dengan nilai array
- Akhirnya, itu menghapus semua duplikat jika ada
masukan fungsi
- Data yang ingin Anda dorong. Data ini harus berupa array multi-dimensi
- Nama sheet tempat Anda ingin mendorong data
Keluaran fungsi
- Tidak ada, data didorong setelah fungsi dijalankan
Kiat saya
- Jika Anda ingin mengganti semua data yang ada alih-alih menambahkannya, kosongkan konten lembar sebelum menambahkan data. Caranya dengan menambahkan baris ini di atas baris 7
- Jika Anda ingin menghapus hanya sebagian dari konten yang ada, mis. g hapus semuanya kecuali tajuk kolom, gunakan
- Jika Anda memiliki larik satu dimensi, ubah baris 7 menjadi
(Di sini pada dasarnya Anda mendapatkan rentang yang hanya memiliki 1 baris dan jumlah kolom = panjang array)
5. Menyatukan semuanya
Sekarang Anda hanya memerlukan fungsi untuk memanggil fungsi readData dan meneruskan hasilnya ke fungsi pushDataToGoogleSheet
Seluruh kode akan terlihat seperti ini
Kiat saya
- Jika Anda memiliki beberapa database yang ingin Anda kueri datanya, Anda bisa mendapatkan daftar database dalam koneksi yang sama, lalu memanggil fungsi untuk setiap database yang memenuhi kriteria Anda
- Jika Anda memiliki kueri SQL yang panjang, letakkan di beberapa baris dan sambungkan baris dengan menggunakan gabungan (simbol "+") ATAU gunakan minifier SQL online untuk menghapus semua jeda baris
- Gunakan tanda kutip ganda “ ” untuk membuka dan menutup kueri SQL, karena SQL menggunakan tanda kutip tunggal, ini akan membantu Anda menghindari masalah yang tidak perlu
- Kueri SQL tidak boleh lebih dari 250 karakter. Jika Anda memiliki kueri besar, akan lebih baik menggunakan Python untuk mendorong data
- Jika Anda mengalami masalah waktu tunggu dengan kueri, nonaktifkan runtime Chrome V8 dengan masuk ke Pengaturan Proyek > Hapus centang Aktifkan runtime Chrome V8
- Jika Anda ingin memantau data Anda secara teratur, buka Pemicu (simbol jam di sisi kiri Editor Skrip Anda), pilih fungsi yang akan dijadwalkan sebagai "utama", sumber acara sebagai "Digerakkan oleh waktu" dan
Kesimpulan
Google Sheets adalah alat online yang hebat untuk melakukan visualisasi sederhana dan membuat laporan bersama, sedangkan MS SQL Server sangat bagus untuk menyimpan data. Kombinasi Google Sheets dan MS SQL Server akan sangat kuat dan memungkinkan Anda melakukan banyak analisis, terutama ketika Anda ingin memantau data Anda secara teratur.
Saya harap artikel ini memberi Anda wawasan yang bermanfaat tentang cara mengimpor data dari SQL Server ke Google Sheets. Fungsi-fungsi di atas juga dapat digunakan dengan cara yang berbeda sesuai dengan kebutuhan Anda. Jika Anda memiliki pertanyaan atau saran, jangan ragu untuk memberi tahu saya di komentar. D