Bagaimana cara mengambil data dari google sheets ke sql server?

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

1. Buat pengguna yang memiliki akses ke SQL Server (opsional)
2. Daftar putih Alamat IP Google
3. Buat fungsi untuk membaca data dari SQL
4. Buat fungsi untuk mendorong data ke Google Sheets
5. 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,

Exception: Unexpected error while getting the method or property getConnection on object Jdbc.

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

sheet.clearContents();

• Jika Anda ingin menghapus hanya sebagian dari konten yang ada, mis. g hapus semuanya kecuali tajuk kolom, gunakan

sheet.getRange('A2:H').clearContent();

• Jika Anda memiliki larik satu dimensi, ubah baris 7 menjadi

sheet.getRange(lastRow+1, 1, 1, data.length).setValues(data)

(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

Bisakah Anda menautkan Google Sheets ke SQL Server?

Bagaimana cara mengekstrak data dari Google Sheets?

Bisakah Anda menghubungkan Google Sheets ke database?

Apakah SQL berfungsi dengan Google Spreadsheet?