Kumpulkan Data dari Mana Saja dengan Fungsi ImportXML Google Sheet

img utama google-sheets-importxml-guide

Saya seorang nerd spreadsheet yang tidak terlalu rahasia. Saya bahkan dalam semacam Kelompok Minat Spreadsheet. Jumlah orang yang bersemangat di sana memberi tahu saya bahwa kita semua mengandalkan spreadsheet lama yang bagus di beberapa titik dalam karier kita.

Bahkan di ranah ini, Google Sheets adalah semacam pahlawan super. Spreadsheet Google Spreadsheet dapat mengumpulkan informasi secara dinamis untuk Anda saat Anda tidur, dan mengambil apa pun yang Anda inginkan (harga saham, analisis situs, dan banyak lagi) dari mana saja.

Tetapi bagaimana jika Anda ingin mengambil data dari web secara luas—mungkin untuk menyalin info dari tabel di situs web? Mungkin ada daftar acara, kumpulan fakta, atau alamat email yang tersebar di sekitar halaman web. Menyalin dan menempelkannya akan memakan waktu lama—tetapi Google Spreadsheet memiliki opsi yang lebih baik.

Anda dapat mengimpor data dari halaman web mana pun menggunakan fungsi kecil bernama ImportXML, dan setelah Anda menguasainya, Anda akan merasa seperti Sheets Wizard bersertifikat. ImportXML menarik informasi dari bidang XML apa pun—yaitu, bidang apa pun yang diapit oleh a <tag>dan a </tag>. Jadi, Anda dapat mengambil data dari situs web apa pun dan metadata apa pun yang dihasilkan oleh situs web mana pun, di mana pun. Tentu, Anda dapat menyalin-menempel dan kemudian menghabiskan berjam-jam mengedit semuanya dengan tangan, tetapi mengapa tidak mengotomatiskan hal-hal yang membosankan?

Mari kita lakukan itu.

Dasar-dasar XML dan HTML

Anda harus mengetahui beberapa HTML yang sangat mendasar—atau lebih tepatnya, markup XML yang menentukan kumpulan data di halaman web—untuk memahami fungsi umum di sini, jadi inilah kursus kilat. Intinya, setiap set <something>dan </something>— blok pembangun inti dari kode sumber halaman web — berarti bahwa set data tertentu terkandung di dalamnya (mungkin <something>like this</something). Halaman akan memiliki beberapa teks dalam sebuah <p>paragraf, terkadang berisi <b>teks lama dan mungkin <a>sebuah tautan (diikuti oleh </a></b>.</p></body>untuk menutup semuanya).

Fungsi ImportXML Google Sheets dapat mencari kumpulan data XML tertentu dan menyalin data darinya.

Jadi, dalam contoh di atas, jika kami ingin mengambil semua tautan di halaman, kami akan memberi tahu fungsi ImportXML kami untuk mengimpor semua informasi di dalam <a></a>tag. Jika kami menginginkan seluruh teks halaman web karena kami melakukan beberapa pekerjaan penambangan teks yang lebih canggih, kami mungkin akan mulai dengan mengambil semua yang ada di dalam <body></body>atau semua yang ada di setiap instance dari <p></p>, dan kemudian membersihkan data kami secara bertahap setelah itu.

Jika kami memberi tahu ImportXML untuk mengambil tautan dari contoh di atas, kami akan mendapatkan teks ” tautan .” Itu mungkin tidak terlalu berguna, tetapi setidaknya Anda mendapatkan idenya.

Tip: Ingin menggali lebih dalam tentang HTML dan XML? Lihat tutorial Elemen Inspeksi kami untuk melihat bagaimana Anda dapat mengubah apa pun di halaman web mana pun dengan mengedit kodenya di browser Anda.


Cara Mengekstrak Daftar Kode Pos dan Kota Kecamatan

Salah satu proyek saya saat ini melibatkan pencocokan daftar pelanggan saya dengan kode pos mereka ke lingkungan kota di kota saya. Ini adalah proyek yang cukup kecil, karena saya hanya menggunakan beberapa lingkungan di pusat kota, tetapi agak sulit, karena di Kanada tidak ada kumpulan data kode pos kami. Tidak, sungguh— Canada Post pernah menggugat seseorang karena menerbitkan daftar semua kode pos.

Untungnya, beberapa individu yang giat telah memasang versi terbaik berikutnya di Wikipedia : tabel kode pos diikuti oleh kotamadya dan lingkungan yang dikandungnya.

Tabel Wikipedia adalah cara yang bagus untuk berlatih ImportXML. Mari kita coba ambil semua kode pos di Edmonton, Alberta. Kita akan pergi ke bagian “AB” dari sistem pos, yang dimulai dengan T . Buka halaman itu di jendela browser baru untuk mengikuti latihan ini.

Periksa elemen di Wikipedia

Mari kita lihat sumber halaman. Pilih salah satu kode pos, klik kanan padanya, dan pilih Inspect untuk membuka alat browser Anda untuk melihat kode sumber halaman.

Sepertinya setiap kode pos terkandung dalam tag (yang mendefinisikan sel dalam tabel). Jadi kita akan mengimpor semua tag TD yang mengandung kata “Edmonton” di dalamnya.

Untuk pelajaran pertama Anda, buat spreadsheet Google Spreadsheet baru yang kosong. Kami akan mengambil semua konten tag TD, termasuk <span>dan tautannya, dengan menentukan apa yang kami inginkan menggunakan sintaks XPath . ImportXML mengambil URL dan tag yang Anda cari sebagai argumen, jadi masukkan ini di Google Spreadsheet:

=importxml("https://en.wikipedia.org/wiki/List_of_T_postal_codes_of_Canada", "//td")

akan memberi Anda ini:

Tabel XML yang diimpor di Google Spreadsheet

Melihat kembali ke sumber halaman kami, kami melihat bahwa kode pos dicetak tebal, atau <b></b>, dan nama kota yang tertaut ke artikel Wikipedia, tentu saja, dalam <a></a>. Mari kita coba ambil hanya tautan pertama di setiap sel, yang merupakan kota besar, dan abaikan tautan lain, yang merupakan lingkungan. Ubah itu menjadi dua perintah, di kolom A dan B –

=importxml("https://en.wikipedia.org/wiki/List_of_T_postal_codes_of_Canada", "//td/span/a[1]")

=importxml("https://en.wikipedia.org/wiki/List_of_T_postal_codes_of_Canada", "//td/b[1]")

dan Anda akan lebih mempertajam hasil Anda:

Membersihkan tabel XML Google Spreadsheet

Ini akan memberi Anda gambaran tentang cara kerja sintaks kueri XPath: tag dengan [1]sarana “hanya beri saya contoh pertama<tag> di dalam <parent tag>.” Jadi, td/span/a[1]memberi Anda tautan pertama di <span>dalam masing-masing <td>. Dengan cara yang sama, td/b[1]memberi Anda teks tebal pertama di dalam masing-masing <td>—atau hanya kode pos dalam kasus kami.

Hal yang rapi yang dapat Anda lakukan adalah membuat dua kueri dari satu fungsi. Jadi, kita bisa menggabungkan dua permintaan ini dengan | (pipa) simbol di tengah:

=importxml("https://en.wikipedia.org/wiki/List_of_T_postal_codes_of_Canada", "//td/span/a[1] | //td/b[1]")

Namun, Anda tidak akan mendapatkan hasil yang sama seperti sebelumnya: itu akan menginterfile semua permintaan yang cocok ke dalam satu daftar panjang, bukan dua kolom. Ada banyak kegunaan untuk ini, tetapi tidak untuk tujuan kita di sini.

Tabel kolom tunggal

Selain itu, kami tidak ingin semua baris ini; kami hanya ingin yang cocok dengan “Edmonton” di td/span/a[1]bidang itu. Ingat bahwa kami ingin mengembalikan kode pos, jadi kami ingin b[1]setiap <td>yang memiliki “Edmonton” di span/a[1]. Masih bersamaku?

Untuk memilih hanya kode pos di kotak di mana tautan pertama adalah ‘Edmonton’, kami akan menggunakan kode ini:

=importxml("https://en.wikipedia.org/wiki/List_of_T_postal_codes_of_Canada", "//td[span/a='Edmonton']/b[1]")

Kami menempatkan bagian “pencarian”—teks kualifikasi yang mempersempit hasil kami—dalam [square brackets], tanpa mengganggu jalur yang benar-benar memberikan hasil. Voila!

kode pos di Google Spreadsheet

Sekarang kami ingin nama-nama lingkungan itu. Kami menulis fungsi importXML yang cocok untuk masuk ke kolom berikutnya, mengambil teks yang muncul setelah kata “Edmonton.”

Solusi saya mengambil seluruh konten span[1]dan menggunakan tanda kurung dan garis miring untuk membagi konten, mengiris “Edmonton” ke kolom pertama dan setiap nama lingkungan ke kolom selanjutnya. Dari proses dua langkah ini kita dapat mencocokkan kode pos dan nama lingkungan:

=importxml("https://en.wikipedia.org/wiki/List_of_T_postal_codes_of_Canada", "//td[span/a='Edmonton']/span[1]")

Dan kemudian, beberapa kolom kemudian menggunakan fungsi split dan concatenate untuk memisahkan dan mengelompokkan data yang sedang kita kerjakan:

=SPLIT(concatenate(B2:J2),"(/)")

Itu memberi kami meja terakhir kami yang dibersihkan hanya dengan kode pos, kota, dan info lingkungan yang kami butuhkan:

meja selesai

Jika Anda sudah menguasainya, Anda dapat meningkatkan metode ini. Pikirkan tentang memanggil hanya konten <span> setelah , a[1]atau hanya teks di dalam tanda kurung, atau semua yang tidak termasuk string “Edmonton”, atau semuanya setelah jeda baris <br>.


Cara Menyalin Alamat Email Secara Otomatis dari Situs Web

Salin email dari halaman Tentang

Yang ini mudah: Bisakah Anda menarik semua email staf Zapier dari halaman Tentang ?

Melihat kode sumber akan memberi tahu Anda segera: Setiap alamat email setiap anggota tim Zapier berada di bidang dengan ekstensi class="email". Mudah! Saat Anda ingin menentukan atribut dari sebuah tag (misalnya, “href” dalam an <a>, atau “id” atau “class” dari a <div>), Anda menyebutnya dengan:

=importxml("https://zapier.com/about//", "//span[@class='email']")

Meraih email tanpa jalan pintas seperti ini bisa dilakukan. Kami melakukannya dengan mencocokkan bentuk esensial mereka ( username@host.suffix , alias bob@gmail.com). Ini lebih rumit, tetapi memiliki lebih banyak potensi.

Ekspresi reguler adalah apa yang kami gunakan untuk menangkap informasi secara kategoris yang cocok dengan format tertentu. Katakanlah kami ingin mengetahui semua suhu yang tercantum di situs web cuaca. Kami akan menangkapnya dengan mengatakan “beri kami semua angka yang datang sebelum simbol °atau atau “—ya, itu semua adalah karakter unicode yang berbeda .

Jika kami ingin mengambil daftar email, kami akan mengatakan “beri kami semua string yang sesuai dengan format username@host.suffix .” Atau, dalam ekspresi reguler:

[a-zA-Z0-9_-\.\+]+@[a-zA-Z0-9-\.]+\.[a-zA-Z0-9-]{2,15}

Ambil napas dalam-dalam, dan kita akan menjalani langkah demi langkah ini. Anda dapat melihat simbol @, dan Anda dapat melihat bahwa spasi “nama pengguna” sebelum @ (atau [a-zA-Z0-9_\.\+-]+) cukup dekat dengan area “host” setelah @ (atau [a-zA-Z0-9-\.]+).

Dan bit “akhiran” terlihat serupa, tetapi tidak cukup. Itu karena karakter yang diizinkan dalam alamat email dan nama host, sebagaimana ditentukan oleh Dewa Internet, dibatasi. Anda mungkin ingat mendaftar untuk alamat email dan mendapatkan pesan kesalahan saat Anda mencoba memasukkan “~ ~f41ry~ ~” di dalamnya. Aku juga tahu rasa sakit itu. Itu karena email menggunakan karakter huruf kecil (az), karakter huruf besar (AZ), angka (0-9), garis bawah (_), tanda hubung (-), dan titik (.)—dan, terkadang, tanda tambah (+).

Ada apa dengan garis miring dan tanda tambah dalam ekspresi itu? Tanda hubung dan titik sudah menandakan hal-hal tertentu dalam ekspresi reguler, dan untuk menandakan “karakter dashdan bukan tanda hubung fungsi ekspresi reguler” kita harus “membatalkan” mereka, yang merupakan istilah bagus untuk “mengabaikan apa yang biasanya Anda lakukan di skenario ini.” Pembatalan dilakukan dengan meletakkan garis miring terbalik ( \) di depannya.

Tanda plus di luar tanda kurung berarti “izinkan karakter yang cocok dengan itu, satu kali atau lebih.” Jadi, nama email Anda dapat terdiri dari sejumlah karakter, asalkan minimal satu.

Kemudian kita melakukannya lagi untuk nama host: Satu atau lebih karakter huruf kecil, huruf besar, angka, garis bawah, tanda hubung, dan titik—karena beberapa alamat email adalah “@mail.hostname.suffix”.

Bit terakhir, sufiksnya lebih terbatas:([a-zA-Z0-9-]{2,15})

Kami hanya dapat memiliki karakter sederhana, dan kami hanya dapat memiliki 2 hingga 15 karakter (untuk memasukkan semua domain baru yang trendi seperti .coffeedan .gripedan, yang terpanjang sejauh ini, .cancerresearch). Jadi, alih-alih + yang berarti “panjang berapa pun”, kami menetapkan panjang minimum dan maksimum dengan {2,15}. (Anda dapat mengatur sesuatu seperti “tepat lima” hanya dengan {5}.)

Untuk rekap, ketika kita ingin satu karakter saja (seperti pada @) kita cukup mengetikkannya. Saat kita menginginkan karakter yang cocok dengan salah satu dari beberapa tipe karakter, kita membuang semua karakter yang dapat diterima di dalam tanda kurung siku. Ketika kita ingin mengalikannya dengan beberapa angka, kita menambahkan beberapa tanda kurung berlekuk-lekuk yang menentukan jumlah karakter minimum dan maksimum yang cocok dengan deskripsi, atau menggunakan indikator untuk mengatakan “satu atau lebih” atau “tidak ada atau lebih”. Saat kami melakukan perkalian seperti itu, kami memasukkannya ke dalam tanda kurung biasa. Beberapa karakter memerlukan “pembatalan” dengan garis miring terbalik.

Di sana, Anda mempelajari keterampilan baru yang kuat hari ini! Semua hanya untuk mengambil email. Wah.

Bahasa pemrograman yang berbeda menggunakan simbol dan sintaks yang berbeda untuk membuat sesuatu bekerja; untuk sedikit rasa, periksa emailregex.com—ya, seluruh situs web hanya untuk cara mencari alamat email (jangan baca komentar). Dan jika Anda ingin menggali lebih dalam ke dalam regex Google Sheets, inilah daftar fungsi Google Sheets rahasia khusus – rahasia karena Google sangat buruk dalam dokumentasi, sehingga banyak pengguna telah menulis panduan mereka sendiri melalui trial-and-error.


Cara Menggunakan Regex untuk Mengimpor Alamat Email Dari Situs Web di Google Spreadsheet

Mari ambil alamat Zapier tersebut menggunakan kekuatan regex yang baru kita temukan. Kami mengimpor <span>s yang sama, tetapi alih-alih mencari kelas yang sama dengan “email”, kami mencari konten yang cocok dengan ekspresi reguler. Sekali lagi, mari lakukan dalam dua langkah: kita akan memanggil banyak informasi dari halaman Zapier di kolom pertama, lalu menyortirnya untuk email di kolom kedua.

=importxml("https://zapier.com/about//", "//span")

=regexextract(A1, "[a-zA-Z0-9_\.\+-]+@[a-zA-Z0-9-\.]+\.[a-zA-Z0-9-]{2,15}")

Dan itu memberi kita tabel ini:

Tabel alamat email Regex Google Spreadsheet

Bisakah Anda menggabungkan kedua fungsi ini? Ingat, ImportXML akan mengisi kolom dan baris dengan sendirinya, bergantung pada apa yang ditemukannya (disebut rumus array), dan kueri regex harus diisi untuk setiap sel yang Anda inginkan hasilnya (yaitu, bukan rumus array ). Untuk menggabungkan semuanya, Anda cukup memerintahkan Regexextract untuk menjadi formula array sekali ini saja (dan masukkan IFERROR demi kesopanan, untuk membiarkan sel kosong di mana tidak ada alamat email yang dapat ditemukan):

=ArrayFormula(IFERROR(REGEXEXTRACT(IMPORTXML("https://zapier.com/about//", "//span"), "[a-zA-Z0-9_\.\+-]+@[a-zA-Z0-9-\.]+\.[a-zA-Z0-9-]{2,15}")))

Dan, dengan itu, inilah daftar alamat email bertenaga Regex kami yang sudah selesai dari halaman Tentang Zapier :

alamat email yang dihapus di Google Spreadsheet

Menjadi Pakar Google Spreadsheet dengan Zapier

Panduan Utama untuk Google Spreadsheet

Untuk bacaan lebih lanjut, kami telah menulis tentang pengikisan web lainnya di eBuku CRM Spreadsheet gratis kami . Anda juga dapat membaca tentang fungsi sepupu ImportXML:

  • ImportHTML —fungsi yang lebih lemah yang akan mengambil seluruh tabel atau daftar dari halaman web tertentu tanpa kontrol lebih lanjut

  • ImportRange —untuk mengambil data dari lembar lain di spreadsheet

  • ImportData —untuk mengimpor data dari file CSV atau TSV yang ditautkan

  • ImportFeed —yang bekerja sangat mirip dengan ImportXML, tetapi untuk mengimpor RSS atau Atom feed, yang bisa sangat bagus jika Anda mengalami masalah mengimpor XML dari situs web tertentu ( batuk Twitter ).

Bersamaan dengan itu, Anda akan mempelajari dasar-dasar spreadsheet jika perlu meninjau, bersama dengan kiat tentang cara membuat aplikasi lengkap di spreadsheet Anda, menggunakan Google Apps Script untuk mengotomatiskan spreadsheet Anda, dan panduan untuk menggunakan aplikasi pendamping Google Sheets, Google Formulir .

Atau, untuk cara yang lebih mudah dalam mengimpor data ke dalam spreadsheet Google Sheets Anda, Anda dapat menggunakan alat otomatisasi aplikasi integrasi Google Sheets Zapier untuk menambahkan data ke spreadsheet Anda secara otomatis. Itu dapat mencatat Tweet ke spreadsheet, menyimpan cadangan kontak MailChimp Anda, atau menyimpan data dari formulir dan acara Anda ke lembar.

Kumpulkan tanggapan Typeform baru sebagai baris di Google Spreadsheet

Google Spreadsheet + Typeform

Gunakan Zap . ini

Simpan peserta Eventbrite baru ke spreadsheet Google Spreadsheet

Eventbrite + Google Spreadsheet

Gunakan Zap . ini

Simpan pelanggan Mailchimp baru ke baris di spreadsheet Google Spreadsheet

Google Spreadsheet + Mailchimp

Gunakan Zap . ini

Simpan sebutan Twitter baru ke spreadsheet Google Spreadsheet

Google Spreadsheet + Twitter

Gunakan Zap . ini

Tidak melihat apa yang Anda cari?

Buat dari awal atau pelajari lebih lanjut

Zapier juga dapat membuat data Anda berfungsi. Katakanlah Anda menggunakan importXML untuk menarik daftar alamat email ke dalam spreadsheet. Zapier kemudian dapat menyalinnya dari spreadsheet Anda, dan mengirimi mereka pesan email atau menambahkannya ke milis Anda. Itu bisa menambahkan daftar tanggal ke Kalender Google Anda untuk cara mudah membuat daftar liburan atau acara. Atau dapat menambahkan setiap entri baru sebagai tugas baru di aplikasi manajemen proyek Anda—atau lebih banyak lagi.

Leave a Reply

Your email address will not be published.