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