Dapatkan data dari spreadsheet ke html

Saya seorang kutu buku spreadsheet yang tidak terlalu rahasia. Saya bahkan berada dalam semacam Grup 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 dunia ini, Google Sheets adalah semacam pahlawan super. Spreadsheet Google Spreadsheet dapat secara dinamis mengumpulkan informasi untuk Anda saat Anda tidur, dan mengambil apa pun yang Anda inginkan (harga saham, analitik situs, dan banyak lagi) dari mana saja

Jadikan Google Spreadsheet berfungsi untuk Anda

Otomatiskan Google Spreadsheet

Namun bagaimana jika Anda ingin mengambil data dari web secara luas—mungkin untuk menyalin info dari tabel di situs web? . Menyalin dan menempelkannya akan memakan waktu lama—tetapi Google Sheets memiliki opsi yang lebih baik

Anda dapat mengimpor data dari halaman web mana pun menggunakan fungsi kecil yang disebut

Mari kita lakukan itu

Dasar-dasar XML dan HTML

Anda harus mengetahui beberapa HTML yang sangat mendasar—atau lebih tepatnya, markup XML yang menunjukkan sekumpulan data dalam halaman web—untuk memahami fungsi umum di sini, jadi inilah kursus kilatnya. Intinya, kumpulan <something> dan </something>—blok bangunan inti dari kode sumber halaman web—berarti bahwa kumpulan data tertentu terkandung di dalamnya (mungkin <something>like this</something). Halaman akan memiliki beberapa teks dalam <p>aragraph, terkadang berisi <b>teks lama dan mungkin <a>a tautan (diikuti dengan </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 kita ingin mengambil semua tautan di sebuah halaman, kita akan memberi tahu fungsi ImportXML untuk mengimpor semua informasi dalam tag <tag>0. Jika kita menginginkan seluruh teks halaman web karena kita sedang melakukan beberapa pekerjaan penggalian teks yang lebih lanjut, kita mungkin akan mulai dengan mengambil semua yang ada di dalam <tag>1 atau semua yang ada di dalam setiap contoh <tag>2, lalu bersihkan data kita secara bertahap setelahnya

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

Tip. Ingin menggali lebih dalam tentang HTML dan XML?

Cara Mengekstrak Daftar Kode Pos dan Kota Kabupaten

Salah satu proyek saya saat ini melibatkan pencocokan daftar pelanggan saya dengan kode pos mereka ke bangsal 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 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. Ayo coba ambil semua kode pos di Edmonton, Alberta. Kami akan pergi ke potongan "AB" dari sistem pos, yang dimulai dengan T. Buka halaman itu di jendela browser baru untuk mengikuti latihan ini

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

Sepertinya setiap kode pos terdapat di dalam tag (yang menentukan sel dalam tabel). Jadi kami akan mengimpor semua tag TD yang mengandung kata "Edmonton" di dalamnya

Untuk pelajaran pertama Anda, buat spreadsheet Google Sheets baru yang kosong. Kami akan mengambil semua konten tag TD, termasuk <tag>3 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 Sheets

<tag>_4

akan memberimu ini

Melihat kembali sumber halaman kami, kami melihat bahwa kode pos dicetak tebal, atau <tag>5, dan nama kota yang terhubung ke artikel Wikipedia, tentu saja, di <tag>0. Mari kita coba ambil hanya mata rantai pertama di setiap sel, yang merupakan kota besar, dan abaikan mata rantai lainnya, yaitu lingkungan. Ubah itu menjadi dua perintah, di kolom A dan B -

<tag>_7

<tag>_8

dan Anda akan menyempurnakan hasil Anda sedikit lebih banyak

Ini akan memberi Anda gambaran tentang cara kerja sintaks kueri XPath. sebuah tag dengan <tag>9 berarti "hanya beri saya contoh pertama dari <tag> di dalam </tag>1. " Jadi, </tag>_2 memberi Anda tautan pertama di dalam <tag>3 di dalam setiap </tag>4. Dengan cara yang sama, </tag>_5 memberi Anda teks tebal pertama di dalam setiap </tag>4—atau hanya kode pos dalam kasus kami

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

</tag>7

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

Selain itu, kami tidak menginginkan semua baris ini; . Ingatlah bahwa kami ingin mengembalikan kode pos, jadi kami ingin </tag>9 dari setiap </tag>4 yang memiliki "Edmonton" di <something>1. Masih bersamaku?

Untuk memilih hanya kode pos di kotak yang tautan pertamanya adalah 'Edmonton', kami akan menggunakan kode ini

<something>2

Kami menempatkan bagian "penelusuran"—teks kualifikasi yang mempersempit hasil kami—dalam <something>3, tanpa mengganggu jalur yang benar-benar memberikan hasil. Voila

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 <something>4 dan menggunakan tanda kurung dan garis miring untuk membagi konten, mengiris "Edmonton" ke dalam kolom pertama dan setiap nama lingkungan menjadi kolom selanjutnya. Dari proses dua langkah ini kami dapat mencocokkan kode pos dan nama lingkungan

<something>5

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

<something>6

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

Jika Anda memahaminya, Anda dapat memperbaiki metode ini. Pertimbangkan untuk memanggil hanya konten <tag>_3 setelah <something>8, atau hanya teks di dalam tanda kurung, atau semua yang tidak termasuk string "Edmonton", atau semua setelah jeda baris <something>9

Cara Menyalin Alamat Email Secara Otomatis dari Website

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

Melihat kode sumber seharusnya langsung memberi tahu Anda. Setiap alamat email dari setiap anggota tim Zapier ada di bidang dengan </something>0. Mudah. Saat Anda ingin menentukan atribut tag (katakanlah, "href" dalam <a>, atau "id" atau "class" dari </something>2) Anda menyebutnya dengan

</something>_3

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

Ekspresi reguler adalah apa yang kami gunakan untuk menangkap informasi yang sesuai 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 ada sebelum simbol </something>4 atau </something>5 atau </something>6"—ya, itu semua adalah karakter unicode yang berbeda

Jika kami ingin mengambil daftar email, kami akan mengatakan "berikan kami semua string yang sesuai dengan format namapengguna@host. akhiran. " Atau, dalam ekspresi reguler

</something>_7

Tarik napas dalam-dalam, dan kita akan menjalani langkah demi langkah ini. Anda dapat melihat simbol @, dan Anda dapat melihat bahwa ruang "nama pengguna" sebelum @ (atau </something>8) cukup dekat dengan area "host" setelah @ (atau </something>9)

Dan bit "akhiran" terlihat serupa, tetapi tidak sepenuhnya. Itu karena karakter yang diperbolehkan dalam alamat email dan nama host, sebagaimana ditentukan oleh Dewa Internet, terbatas. Anda mungkin ingat saat mendaftar alamat email dan mendapatkan pesan kesalahan saat mencoba memasukkan "~~f41ry~~" di dalamnya. Aku juga tahu rasa sakit itu. Itu karena email mengambil karakter huruf kecil (a-z), karakter huruf besar (A-Z), angka (0-9), garis bawah (_), tanda hubung (-), dan titik (. )—dan, kadang-kadang, tanda tambah (+)

Ada apa dengan garis miring dan tanda plus di ekspresi itu? . " Membatalkan dilakukan dengan meletakkan backslash (<something>like this</something1) di depannya

Tanda tambah di luar tanda kurung berarti "izinkan karakter yang cocok dengan itu, satu kali atau lebih. " Jadi, panjang nama email Anda bisa berapa saja, asalkan minimal satu

Kemudian kami melakukannya lagi untuk nama host. Satu atau beberapa karakter huruf kecil, huruf besar, angka, garis bawah, tanda hubung, dan titik—karena beberapa alamat email adalah "@mail. nama host. akhiran"

Bit terakhir, akhiran lebih dibatasi. <something>like this</something2

Kami hanya dapat memiliki karakter sederhana, dan kami hanya dapat memiliki 2 hingga 15 karakter (untuk menyertakan semua domain baru yang trendi seperti <something>like this</something3 dan <something>like this</something4 dan, yang terpanjang sejauh ini, <something>like this</something5). Jadi, alih-alih + yang berarti "panjang berapa pun", kami menetapkan panjang minimum dan maksimum dengan <something>like this</something6. (Anda dapat mengatur sesuatu seperti "tepatnya lima" hanya dengan <something>like this</something7. )

Singkatnya, ketika kita menginginkan satu karakter saja (seperti pada <something>like this</something8) kita ketik saja itu. Saat kami menginginkan karakter yang cocok dengan salah satu dari beberapa tipe karakter, kami menggabungkan semua karakter yang dapat diterima di dalam tanda kurung siku. Saat kita ingin mengalikannya dengan beberapa angka, kita menambahkan beberapa tanda kurung siku-siku yang menentukan jumlah karakter minimum dan maksimum yang cocok dengan deskripsi, atau menggunakan indikator untuk menyatakan "satu atau lebih" atau "tidak ada atau lebih. " Saat kita melakukan perkalian seperti itu, kita memasukkannya ke dalam tanda kurung biasa. Beberapa karakter memerlukan "pembatalan" dengan garis miring terbalik

Di sana, Anda belajar 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 berfungsi; . com — ya, seluruh situs web hanya untuk cara mencari alamat email (jangan baca komentarnya). Dan jika Anda ingin menggali lebih dalam tentang regex Google Sheets, inilah daftar fungsi Google Sheets rahasia khusus - rahasia karena Google sangat buruk dalam dokumentasi, jadi banyak pengguna telah menulis panduan mereka sendiri melalui trial-and-error

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

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

<p>_0

<p>_1

Dan itu memberi kita tabel ini

Bisakah Anda menggabungkan kedua fungsi ini? . Untuk menyatukan semuanya, Anda cukup memerintahkan Regexextract untuk menjadi rumus array sekali ini saja (dan masukkan IFERROR demi kesopanan, untuk membiarkan sel kosong di mana tidak ada alamat email yang dapat ditemukan)

<p>_2

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

Menjadi Pakar Google Spreadsheet dengan Zapier

Untuk bacaan lebih lanjut, kami telah menulis tentang pengikisan web lainnya. 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 sheet lain di spreadsheet

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

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

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

Atau, untuk cara yang lebih mudah mengimpor data ke spreadsheet Google Sheets Anda, Anda dapat menggunakan alat otomasi 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

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 itu bisa menambahkan setiap entri baru sebagai tugas baru di aplikasi manajemen proyek Anda—atau lebih banyak lagi

Bagaimana Anda mengimpor spreadsheet ke HTML?

Bagaimana cara mendapatkan data dari Google Sheets ke situs web saya?

Bagaimana cara menarik data dari Google Sheets?

Bisakah Google Sheet menarik data dari Excel secara otomatis?