Katakanlah Anda mendapatkan konten hebat dalam bentuk tabel atau daftar di situs web. Meskipun Anda mungkin langsung mencoba menyalin dan menempelkan konten ini ke spreadsheet Anda untuk analisis nanti, ini tidak hanya memakan waktu tetapi juga dapat menyebabkan potensi kesalahan. Alternatif yang lebih baik adalah mencari cara untuk mengikis data dan mengimpornya ke platform penyimpanan pilihan Anda
Proses scraping ini tidak memerlukan pelatihan lanjutan, terutama jika Anda sudah terbiasa dengan formula Google Sheets. Misalnya, IMPORTDATA dan IMPORTXML adalah fungsi bawaan di Google Spreadsheet yang memungkinkan Anda mengimpor data dari sumber eksternal. Fungsi IMPORTHTML di Google Sheets memungkinkan Anda mengimpor data dari tabel atau daftar yang dapat ditindaklanjuti serta memperbaruinya dengan menyegarkannya secara berkala
Dalam artikel ini, Anda akan mempelajari cara kerja fungsi IMPORTHTML, memahami sintaks rumus IMPORTHTML, dan cara menggunakannya untuk mengimpor data dari situs web. Anda dapat menemukan solusi penyelesaian di bagian akhir jika fungsi tidak berfungsi dengan baik
Mulailah Dengan Lapisan Sekarang
Bagikan bagian Google Spreadsheet Anda, pantau, tinjau, dan setujui perubahan, serta sinkronkan data dari berbagai sumber – semuanya dalam hitungan detik
Kita semua tahu betapa bergunanya aplikasi Google Sheets dalam kehidupan kita sehari-hari. Meskipun sangat bagus dengan semua kebutuhan spreadsheet dasar dan lanjutan, ini juga memungkinkan kita terhubung dengan informasi luar dengan mudah. Misalnya, jika kita perlu mengimpor tabel HTML web ke Google Spreadsheet, tabel tersebut memiliki fungsi asli yang disebut sebagai formula IMPORTHTML. Inilah sintaksnya
IMPORTHTML(url, kueri, indeks)
- url – ini adalah alamat (link/URL) ke halaman HTML di web, tempat tabel atau daftar yang ingin kita impor berada. Ini bisa berupa string URL yang memenuhi syarat seperti “https. // en. wikipedia. org/wiki/Demographics_of_the_world”. Atau bisa juga referensi ke sel (seperti B1) tempat URL disimpan
- kueri – ini akan berupa "daftar" atau "tabel" bergantung pada struktur HTML mana yang berisi data yang kami coba impor
- index – nomor indeks yang secara unik mengidentifikasi daftar atau tabel dalam halaman HTML. Harap perhatikan bahwa indeks untuk daftar dan tabel diperlakukan secara terpisah. Baik tabel maupun daftar bisa ada dengan nomor indeks yang sama
Contoh. impor tabel HTML web ke Google Sheets
Silahkan perhatikan gambar di bawah ini. B3 adalah sel tempat kita memasukkan rumus IMPORTHTML. Di halaman web tempat URL (disebutkan di atas) membawa kami, kami agak tertarik dengan tabel kedua. Karenanya kita akan menggunakan "tabel" untuk parameter kueri dan 2 untuk parameter indeks
Anggaplah Anda menemukan tabel yang dapat ditindaklanjuti di beberapa situs web dan ingin memasukkan data tabel yang berguna ini ke dalam spreadsheet untuk dianalisis. Anda dapat mencoba menyalin dan menempelkannya secara manual, tetapi itu cara orang awam. Google Sheets memiliki fungsi yang nyaman, IMPORTHTML, untuk melakukan pekerjaan itu. Ini akan mengimpor tabel dengan mudah dan menyegarkan data Anda secara berkala agar tetap diperbarui.
Tapi bagaimana cara kerjanya? . Kedengarannya menarik?
Daftar isi
Bagaimana cara kerja fungsi IMPORTHTML di Google Sheets?
Fungsi Google Sheets IMPORTHTML mencari tabel atau daftar HTML tertentu dan menyalin data darinya. Anda dapat menggunakannya untuk mengikis teks dalam tabel atau daftar. Tabel HTML ditentukan oleh tag var index = 1; [].forEach.call(document.getElementsByTagName("table"), function(elements) { console.log("Index: " + index++, elements); });_3, sementara daftar ditentukan oleh tag var index = 1; [].forEach.call(document.getElementsByTagName("table"), function(elements) { console.log("Index: " + index++, elements); });4 (untuk daftar tidak berurutan) dan var index = 1; [].forEach.call(document.getElementsByTagName("table"), function(elements) { console.log("Index: " + index++, elements); });5 (untuk daftar yang dipesan).
Cara menggunakan rumus IMPORTHTML di Google Sheets
Sebelum menggunakan rumus IMPORTHTML, mari kita pahami sintaksnya
=IMPORTHTML(URL, query_type, index)- var index = 1; [].forEach.call(document.getElementsByTagName("table"), function(elements) { console.log("Index: " + index++, elements); });6 — URL halaman, termasuk protokol (var index = 1; [].forEach.call(document.getElementsByTagName("table"), function(elements) { console.log("Index: " + index++, elements); });7 atau var index = 1; [].forEach.call(document.getElementsByTagName("table"), function(elements) { console.log("Index: " + index++, elements); });8). Pastikan untuk menyertakan URL dalam tanda kutip ganda
- var index = 1; [].forEach.call(document.getElementsByTagName("table"), function(elements) { console.log("Index: " + index++, elements); });9 — Gunakan “var index = 1; [].forEach.call(document.querySelectorAll("ul,ol"), function(elements) { console.log("Index: " + index++, elements); });0” jika Anda ingin mengimpor tabel, jika tidak “var index = 1; [].forEach.call(document.querySelectorAll("ul,ol"), function(elements) { console.log("Index: " + index++, elements); });1” jika Anda akan mengimpor daftar
- var index = 1; [].forEach.call(document.querySelectorAll("ul,ol"), function(elements) { console.log("Index: " + index++, elements); });2 — Indeks tabel atau daftar di halaman web. Ini dimulai pada 1. Tabel dengan indeks = var index = 1; [].forEach.call(document.querySelectorAll("ul,ol"), function(elements) { console.log("Index: " + index++, elements); });3 berarti tabel pertama, indeks = var index = 1; [].forEach.call(document.querySelectorAll("ul,ol"), function(elements) { console.log("Index: " + index++, elements); });4 berarti tabel kedua, dan seterusnya
Impor data situs web ke Google Spreadsheet dengan IMPORTHTML
Cara mendapatkan indeks tabel/daftar untuk menarik data dari situs web ke Google Sheets menggunakan IMPORTHTML
Halaman mungkin berisi satu atau lebih tabel dan/atau daftar. Jika Anda tidak tahu cara mengetahui indeks tabel pada halaman HTML, ikuti langkah-langkah di bawah ini
Langkah 1
Buka konsol Pengembang browser Anda. Untuk sebagian besar browser di Windows, Anda dapat membuka konsol dengan menekan F12. Jika Anda menggunakan Mac, gunakan Cmd+Opt+J untuk Chrome, dan Cmd+Opt+C untuk Safari. Perhatikan bahwa, untuk Safari, Anda harus mengaktifkan "" terlebih dahulu
Tampilan persisnya akan bergantung pada versi Google Chrome yang Anda gunakan. Ini dapat berubah dari waktu ke waktu, tetapi harus serupa
Langkah 2
Salin dan tempel kode berikut ke konsol untuk mendapatkan indeks semua tabel
var index = 1; [].forEach.call(document.getElementsByTagName("table"), function(elements) { console.log("Index: " + index++, elements); });Jika Anda mencari semua indeks daftar, Anda harus mendapatkan semua elemen dengan tag var index = 1; [].forEach.call(document.getElementsByTagName("table"), function(elements) { console.log("Index: " + index++, elements); });4 atau var index = 1; [].forEach.call(document.getElementsByTagName("table"), function(elements) { console.log("Index: " + index++, elements); });5. Kode berikut dapat membantu Anda
var index = 1; [].forEach.call(document.querySelectorAll("ul,ol"), function(elements) { console.log("Index: " + index++, elements); });_Langkah 3
tekan enter. Anda akan melihat angka yang mewakili indeks yang ditampilkan di hasil. Gerakkan kursor Anda ke atas elemen dalam hasil hingga tabel/daftar yang ingin Anda tampilkan disorot.
Seperti yang Anda lihat pada tangkapan layar di atas, tabel yang disorot memiliki index = var index = 1; [].forEach.call(document.querySelectorAll("ul,ol"), function(elements) { console.log("Index: " + index++, elements); });7.
Cara mengimpor tabel HTML ke Google Sheets
Mari kita lihat bagaimana kita dapat mengimpor tabel HTML. Kami akan menarik data nilai tukar mata uang terbaru dari Yahoo. Situs web Mata Uang Keuangan ke Google Sheets. Halaman hanya memiliki satu tabel, jadi kami akan menggunakan var index = 1; [].forEach.call(document.querySelectorAll("ul,ol"), function(elements) { console.log("Index: " + index++, elements); });3 untuk nilai indeks
Sekarang, buat spreadsheet Google kosong baru dan beri nama – misalnya, Mata Uang. Kemudian, salin dan tempel rumus berikut ke A1
=IMPORTHTML("//finance.yahoo.com/currencies","table",1)Kemudian, tekan Enter dan tunggu hingga seluruh tabel terisi dalam spreadsheet.
Pada gambar di atas, kita dapat melihat bahwa fungsi IMPORTHTML berhasil mengambil data kurs mata uang terbaru ke dalam Google Sheets.
Anda mungkin tertarik untuk memantau data nilai tukar. Dalam hal ini, Anda mungkin ingin melihat tutorial kami tentang cara membuat pelacak nilai tukar mata uang di Google Sheets tanpa coding
Cara mengimpor daftar ke Google Sheets
Anda dapat mengimpor daftar menggunakan metode yang sama. Satu-satunya perubahan adalah mengganti kata “var index = 1; [].forEach.call(document.querySelectorAll("ul,ol"), function(elements) { console.log("Index: " + index++, elements); });0” dengan “var index = 1; [].forEach.call(document.querySelectorAll("ul,ol"), function(elements) { console.log("Index: " + index++, elements); });1” di parameter. Langkah-langkah berikut menunjukkan cara menarik data dari daftar yang berisi bahasa pemrograman yang dimulai dengan huruf "C"
Buat spreadsheet Google kosong baru dan beri nama. Kemudian, salin dan tempel rumus berikut ke C1
=IMPORTHTML("//en.wikipedia.org/wiki/List_of_programming_languages","list",7)Tekan Enter dan tunggu hingga data terisi, seperti yang ditunjukkan tangkapan layar berikut
Opsi lain untuk menggores data ke Google Sheets
Jika Anda mencari metode lain untuk mengambil data dari struktur yang berbeda selain tabel dan daftar HTML, berikut adalah beberapa fungsi Google Sheets yang mungkin ingin Anda coba
Nama fungsiDeskripsi=IMPORTHTML("//finance.yahoo.com/currencies","table",1)1Fungsi ini mengimpor data dari berbagai tipe data terstruktur termasuk XML, HTML, CSV, TSV, serta feed XML RSS dan ATOM. =IMPORTHTML("//finance.yahoo.com/currencies","table",1)2Fungsi ini mengimpor rentang sel dari spreadsheet tertentu. =IMPORTHTML("//finance.yahoo.com/currencies","table",1)3Fungsi ini mengimpor umpan RSS atau ATOM. =IMPORTHTML("//finance.yahoo.com/currencies","table",1)4Fungsi ini mengimpor data dalam format CSV atau TSV dari URLJika Anda ingin mengimpor data dari sumber dan aplikasi lain, atau bahkan dapat memuat data melalui API tanpa coding, Anda dapat melihat Coupler. io
Alat prerangkai. io adalah solusi integrasi data untuk mengotomatiskan ekspor data.
- Aplikasi akuntansi seperti Xero dan QuickBooks
- CRM seperti Pipedrive dan HubSpot
- Database seperti MySQL dan BigQuery
- Banyak aplikasi dan sumber lain termasuk Microsoft Excel, Clockify, Shopify, Airtable, dll
Selain itu, Coupler. io menawarkan integrasi JSON untuk mengikis data melalui API ke Google Sheets tanpa pengkodean sama sekali. Coba Coupler. io dengan uji coba gratis 14 hari
Cara Menggunakan IMPORTRANGE di Google Sheets untuk Mengimpor Data di Seluruh Spreadsheet
Impor Data CSV Menggunakan Fungsi Google Sheets IMPORTDATA atau Alternatifnya
Cara mereferensikan sel di IMPORTHTML di Google Sheets
Anda mungkin ingin memasukkan URL dan parameter lainnya ke dalam sel, lalu merujuknya saat menggunakan rumus IMPORTHTML. Dalam hal ini, Anda dapat mengubah parameter lebih mudah dengan mengedit nilai sel.
Ini sebuah contoh
Semua parameter untuk URL, kueri, dan indeks diletakkan di B1, B2, dan B3. Dengan demikian, Anda dapat dengan mudah menulis rumus IMPORTHTML sebagai berikut
=IMPORTHTML(B1,B2,B3)Mari kita lihat contoh lainnya. Misalkan Anda ingin mendapatkan kurs historis terbaru dari pasangan mata uang EUR/USD dari halaman ini.
//finance.yahoo.com/quote/EURUSD%3DX/history?p=EURUSD%3DXAnda dapat meletakkan string =IMPORTHTML("//finance.yahoo.com/currencies","table",1)_5 di sel – misalnya, B1. Dalam hal ini, jika Anda ingin mengambil data mata uang lain, Anda hanya perlu mengubah nilainya di B1. Berikut adalah contoh cara merujuk ke sel B1 dalam rumus Google Sheets IMPORTHTML.
=IMPORTHTML("//finance.yahoo.com/quote/" & B1 & "%3DX/history?p=" & B1 & "%3DX", "table", 1)Sekarang, mari tambahkan rumus di atas ke dalam A3
Jika Anda ingin menarik data historis untuk AUD/USD, ubah nilai B1 menjadi =IMPORTHTML("//finance.yahoo.com/currencies","table",1)6, dan data Anda akan disegarkan secara otomatis.
=IMPORTHTML(SUBSTITUTE("//finance.yahoo.com/quote/{{CURRENCY}}%3DX/history?p={{CURRENCY}}%3DX", "{{CURRENCY}}", B1), "table", 1)Tip. Anda dapat menghindari mengetik B1 berkali-kali dengan menggunakan fungsi SUBSTITUTE. Inilah tampilan formula yang diperbarui
Cara menggunakan IMPORTHTML untuk mengimpor sebagian data tabel rentang ke Google Sheets
Ingin menarik beberapa kolom saja? .
IMPORTHTML. Mengimpor kolom tertentu
Misalkan Anda memiliki sheet dengan fungsi IMPORTHTML yang mengambil data kurs EUR/USD terbaru dari situs web ke Google Spreadsheet.
Sekarang, Anda hanya ingin mengambil kolom Tanggal dan Tutup yang merupakan kolom ke-1 dan ke-5. Untuk melakukan itu, Anda dapat menggabungkan formula yang ada dengan fungsi QUERY — inilah contohnya
=QUERY(IMPORTHTML("//finance.yahoo.com/quote/EURUSD%3DX/history?p=EURUSD%3DX", "table", 1), "SELECT Col1, Col5")Dengan mendefinisikan “=IMPORTHTML("//finance.yahoo.com/currencies","table",1)_7” dalam fungsi QUERY, Anda akan mendapatkan hasil ini
IMPORTHTML. Mengimpor baris tertentu
Anda juga dapat mengambil baris tertentu. Misalnya, berikut adalah cara menambahkan filter ke rumus kami sebelumnya untuk hanya mengambil data dengan nilai Tutup lebih tinggi dari 1. 2250
var index = 1; [].forEach.call(document.getElementsByTagName("table"), function(elements) { console.log("Index: " + index++, elements); });0Sekarang, mari tambahkan satu filter lagi untuk mengambil hanya 3 rasio tertinggi teratas. Inilah rumusnya.
var index = 1; [].forEach.call(document.getElementsByTagName("table"), function(elements) { console.log("Index: " + index++, elements); });1Cara menyetel interval khusus untuk menyegarkan IMPORTHTML secara otomatis di Google Spreadsheet
Secara default, periode penyegaran Google Sheets IMPORTHTML adalah setiap 1 jam. Namun, Anda dapat mempercepat interval penyegaran jika diinginkan. Saat rumus dihitung ulang saat argumennya berubah, Anda bisa menggunakan ini untuk memaksa interval penyegaran. Idenya adalah menggabungkan URL asli dengan string kueri yang berubah secara berkala berdasarkan waktu yang kami tetapkan – misalnya, setiap 5 menit. Berikut langkah-langkahnya
Pertama, tambahkan string kueri di URL asli
Misalkan kita memiliki nilai berikut di B1-B5. Fungsi IMPORTHTML didefinisikan dalam B5. Perhatikan bahwa string kueri =IMPORTHTML("//finance.yahoo.com/currencies","table",1)8 ditambahkan ke URL asli.
NoteCellValueURLB1=IMPORTHTML("//finance.yahoo.com/currencies","table",1)9query typeB2var index = 1; [].forEach.call(document.querySelectorAll("ul,ol"), function(elements) { console.log("Index: " + index++, elements); });0indexB3var index = 1; [].forEach.call(document.querySelectorAll("ul,ol"), function(elements) { console.log("Index: " + index++, elements); });3refreshB4var index = 1; [].forEach.call(document.querySelectorAll("ul,ol"), function(elements) { console.log("Index: " + index++, elements); });3formulaB5=IMPORTHTML("//en.wikipedia.org/wiki/List_of_programming_languages","list",7)3Lembaran itu terlihat sebagai berikut
Kami belum selesai. Mari lanjutkan ke langkah berikutnya
Selanjutnya, gunakan skrip dan pemicu untuk mengotomatiskan penyegaran
Kami akan memperbarui nilai B4 setiap 5 menit menggunakan skrip dan pemicu. Akibatnya, rumus IMPORTHTML Google Sheets juga akan disegarkan pada interval yang sama. Ikuti petunjuk ini
Langkah 1. Buka editor Skrip (Alat > Editor Skrip atau Ekstensi > Skrip Aplikasi)
Langkah 2. Salin dan tempel kode berikut di Kode. gs. Kemudian, simpan perubahan Anda dengan menekan ikon Disk di bilah alat
var index = 1; [].forEach.call(document.getElementsByTagName("table"), function(elements) { console.log("Index: " + index++, elements); });2Langkah 3. Buka menu Pemicu di sebelah kiri, lalu klik tombol Tambahkan Pemicu.
Langkah 4. Tetapkan pemicu untuk =IMPORTHTML("//en.wikipedia.org/wiki/List_of_programming_languages","list",7)_4 agar berjalan setiap 5 menit. Secara opsional, Anda dapat menyetel pengaturan Notifikasi kegagalan untuk Segera beri tahu saya agar Anda segera menerima notifikasi saat terjadi kesalahan
Langkah 5. Klik tombol Simpan. Jika Anda diminta mengotorisasi skrip untuk mengakses data Anda, berikan izin
Langkah 6. Jalankan skrip Anda untuk pertama kalinya
Sekarang, Anda dapat melihat data di lembar Anda disegarkan setiap 5 menit. Bahkan saat Google Sheet Anda ditutup, itu akan terus disegarkan
Berapa banyak IMPORTHTML yang dapat ditangani oleh Google Sheets?
Anda dapat menggunakan IMPORTHTML di spreadsheet Google sebanyak yang Anda mau. Sebelumnya, batasnya adalah 50 per spreadsheet Google untuk data eksternal, tetapi Google menghapus batasan ini pada tahun 2015. Karena Google Spreadsheet berbasis web, Anda mungkin mengalami penurunan kecepatan jika memiliki banyak rumus IMPORTHTML di spreadsheet, terutama jika koneksi internet Anda lambat.
Cara menarik data non-publik dari situs web ke Google Sheets menggunakan fungsi IMPORTHTML
Anda mungkin ingin menarik data dari URL non-publik di situs web ke Google Spreadsheet. Sayangnya, Anda tidak dapat melakukannya menggunakan fungsi IMPORTHTML. Lihat tangkapan layar berikut, yang menunjukkan apa yang terjadi jika Anda mencoba menggores daftar jaringan LinkedIn Anda.
Rumus hanya berfungsi jika halaman tersedia untuk umum dan tidak mengharuskan Anda masuk untuk mengakses data. Anda akan mendapatkan pesan kesalahan #N/A Tidak dapat mengambil url karena mengakses URL non-publik
Apa yang harus dilakukan jika formula IMPORTHTML tiba-tiba tidak berfungsi di Google Sheets Anda
Jika rumus Anda tiba-tiba berhenti berfungsi, kami menyarankan Anda untuk memeriksa hal-hal berikut
- Periksa perubahan URL. Meskipun ini kasus yang jarang terjadi, ada kemungkinan halaman yang Anda kikis telah dipindahkan ke URL lain.
- Periksa perubahan protokol. Misalnya, situs yang Anda scraping sekarang menggunakan https, bukan http, tetapi pengalihan otomatis ke https belum disiapkan oleh pemilik situs web
- Periksa perubahan indeks. Tabel atau daftar dengan indeks = 9 dapat memiliki indeks = 8 sekarang
If you still can’t pull the data you want, then it could be that the website owner now blocks bots/crawlers from reading their web content. Check the website’s robots.txt by navigating through /robots.txt.
Kesalahan IMPORTHTML Google Spreadsheet Memuat Data
Kesalahan Memuat Data ini adalah salah satu masalah paling umum dengan IMPORTHTML. Kemungkinan besar Anda menghadapinya saat mencoba menarik data dari situs web yang menggunakan skrip besar. Mereka membutuhkan waktu lama untuk berjalan dan karenanya bisa berisiko dalam hal keamanan. Ini berarti Anda tidak dapat mengurai halaman dengan JS menggunakan IMPORHTML ke Google Sheets.
Dalam hal ini, Anda dapat mencoba menemukan sumber lain dengan data yang diperlukan atau memilih opsi pengimporan data alternatif, misalnya melalui API menggunakan importir JSON oleh Coupler. io. Semoga beruntung