Recopile datos desde cualquier lugar con la función ImportXML de Google Sheet
Soy un nerd no tan secreto de las hojas de cálculo. Incluso estoy en una especie de grupo de interés de hojas de cálculo. La cantidad de gente apasionada que hay me dice que todos hemos confiado en una buena hoja de cálculo antigua en algún momento de nuestras carreras.
Incluso en este ámbito, Google Sheets es una especie de superhéroe. Las hojas de cálculo de Google Sheets pueden recopilar información de forma dinámica mientras duerme y obtener todo lo que desee (precios de las acciones, análisis del sitio y mucho más) desde cualquier lugar.
Pero, ¿qué sucede si desea obtener datos de la web en general, tal vez para copiar información de una tabla en un sitio web? Tal vez haya una lista de eventos, una cuadrícula de hechos o direcciones de correo electrónico esparcidas por una página web. Copiarlos y pegarlos llevaría una eternidad, pero Google Sheets tiene una mejor opción.
Puede importar datos desde cualquier página web usando una pequeña función llamada ImportXMLy, una vez que lo domines, te sentirás como un asistente de hojas certificado. ImportXML extrae información de cualquier campo XML, es decir, cualquier campo entre corchetes <tag> y un </tag>. Por lo tanto, puede obtener datos de cualquier sitio web y cualquier metadato generado por cualquier sitio web, en cualquier lugar. Claro, copia y pega y luego pasa horas editando todo a mano, pero ¿por qué no automatizar las cosas aburridas?
Hagamos precisamente eso.
Conceptos básicos de XML y HTML
Necesitará conocer algo de HTML muy básico, o más bien, el marcado XML que designa conjuntos de datos en una página web, para comprender las funciones comunes aquí, así que aquí hay un curso intensivo. En esencia, cualquier conjunto de <something> y </something>—Los componentes básicos del código fuente de una página web— significan que un determinado conjunto de datos está contenido dentro de ellos (tal vez <something>like this</something). El de una página tendrá algo de texto en un <p>aragraph, que a veces contiene <b>texto antiguo y tal vez <a>un enlace (seguido de </a></b>.</p></body> para cerrarlo todo).
La función ImportXML de Google Sheets puede buscar un conjunto de datos XML específico y copiar los datos de él.
Entonces, en el ejemplo anterior, si quisiéramos capturar todos los enlaces en una página, le diríamos a nuestra función ImportXML que importe toda la información dentro del <a></a> etiquetas. Si quisiéramos el texto completo de una página web porque estamos haciendo un trabajo de minería de texto más avanzado, probablemente comenzaríamos por tomar todo dentro del <body></body> o todo dentro de cada instancia de <p></p>, y luego limpiar nuestros datos en etapas posteriores a eso.
Si le dijéramos a ImportXML que tomara los enlaces del ejemplo anterior, obtendríamos el texto «.» Puede que eso no sea muy útil, pero al menos entiendes la idea.
Consejo: ¿Quiere profundizar un poco más en HTML y XML? Consulte nuestro tutorial Inspeccionar elemento para ver cómo puede cambiar en cualquier página web editando su código en su navegador.
Cómo extraer una lista de códigos postales y distritos de la ciudad
Uno de mis proyectos actuales consiste en hacer coincidir mi lista de clientes por su código postal con un distrito municipal de mi ciudad. Este es un proyecto bastante pequeño, ya que solo estoy usando un puñado de distritos del centro, pero algo difícil, porque en Canadá no hay un conjunto de datos de nuestros códigos postales. No, en serio, Canada Post demandó a alguien una vez por publicar una lista de todos los códigos postales.
Afortunadamente, algún individuo emprendedor ha puesto una mejor versión en Wikipedia: una tabla de códigos postales seguidos por los municipios y vecindarios que contiene.
Las tablas de Wikipedia son una excelente manera de practicar ImportXML. Intentemos obtener todos los códigos postales de Edmonton, Alberta. Iremos al fragmento «AB» del sistema postal, los que comienzan con T. Abra esa página en una nueva ventana del navegador para seguir con este ejercicio.
Echemos un vistazo a la fuente de la página. Seleccione uno de los códigos postales, haga clic derecho sobre él y seleccione para abrir la herramienta de su navegador para ver el código fuente de la página.
Parece que cada código postal está contenido dentro de una etiqueta (que define una celda en la tabla). Por tanto, vamos a importar todas las etiquetas TD que contengan la palabra «Edmonton».
Para su primera lección, cree una nueva hoja de cálculo de Hojas de cálculo de Google vacía. Tomaremos todo el contenido de la etiqueta TD, incluido el <span> y los enlaces, especificando lo que queremos usando la sintaxis XPath. ImportXML toma la URL y las etiquetas que está buscando como argumentos, así que ingrese esto en Google Sheets:
=importxml("https://en.wikipedia.org/wiki/List_of_T_postal_codes_of_Canada", "//td")
te dará esto:
Mirando hacia atrás en el código fuente de nuestra página, vemos que el código postal está en negrita, o <b></b>, y los nombres de las ciudades que enlazan con los artículos de Wikipedia están, por supuesto, en <a></a>. Intentemos tomar solo el primer enlace de cada celda, que es la ciudad principal, e ignorar los otros enlaces, que son vecindarios. Modifíquelo en dos comandos, en las columnas A y 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]")
y refinará un poco más sus resultados:
Esto debería darle una idea de cómo funciona la sintaxis de la consulta XPath: una etiqueta con [1] significa «solo dame la instancia de <tag> adentro <parent tag>.» Entonces, td/span/a[1] te da el primer enlace dentro del <span> dentro de cada uno <td>. Del mismo modo, td/b[1] le da el primer texto en negrita dentro de cada <td>—O solo el código postal en nuestro caso.
Una buena cosa que puede hacer es realizar dos consultas a partir de una función. Entonces, podemos combinar estas dos solicitudes con un | símbolo (tubería) en el medio:
=importxml("https://en.wikipedia.org/wiki/List_of_T_postal_codes_of_Canada", "//td/span/a[1] | //td/b[1]")
Sin embargo, no obtendrá el mismo resultado que antes: archivará todas las solicitudes coincidentes en una lista larga, en lugar de dos columnas. Hay muchos usos para esto, pero no para nuestros propósitos aquí.
Además, no queremos todas estas filas; solo queremos los que coincidan con «Edmonton» en ese td/span/a[1] campo. Recuerda que queremos devolver el código postal, por eso queremos el b[1] de cada <td> que tiene «Edmonton» en span/a[1]. ¿Aún conmigo?
Para seleccionar solo los códigos postales en las casillas donde los primeros enlaces son ‘Edmonton’, usaremos este código:
=importxml("https://en.wikipedia.org/wiki/List_of_T_postal_codes_of_Canada", "//td[span/a='Edmonton']/b[1]")
Colocamos la parte de «búsqueda», el texto de calificación que limita nuestros resultados, dentro de la [square brackets], sin perturbar el camino que realmente ofrece resultados.
Ahora queremos esos nombres de vecindarios. Escribimos una función importXML coincidente para ir en la siguiente columna, tomando el texto que viene con la palabra «Edmonton».
Mi solución toma todo el contenido de span[1] y usa los paréntesis y la barra para dividir el contenido, dividiendo «Edmonton» en la primera columna y el nombre de cada vecindario en columnas posteriores. A partir de este proceso de dos pasos, podemos hacer coincidir códigos postales y nombres de vecindarios:
=importxml("https://en.wikipedia.org/wiki/List_of_T_postal_codes_of_Canada", "//td[span/a='Edmonton']/span[1]")
Y luego, algunas columnas más tarde usan las funciones de división y concatenación para separar y agrupar los datos con los que estamos trabajando:
=SPLIT(concatenate(B2:J2),"(/)")
Eso nos da nuestra tabla final, limpia con solo el código postal, la ciudad y la información del vecindario que necesitamos:
Si le coge el truco, puede mejorar este método. Piense en llamar solo el contenido de <span> la a[1], o solo el texto entre paréntesis, o todo lo que no incluya la cadena «Edmonton», o todo lo que esté después del salto de línea <br>.
Cómo copiar automáticamente direcciones de correo electrónico desde un sitio web
Este es fácil: ¿puedes extraer todos los correos electrónicos del personal de Zapier desde la página Acerca de?
Mirar el código fuente debería decirle de inmediato: cada dirección de correo electrónico de cada miembro del equipo de Zapier está en un campo con un class="email". ¡Fácil! Cuando desee especificar un atributo de una etiqueta (por ejemplo, «href» en un <a>o el «id» o la «clase» de un <div>) lo llamas con:
=importxml("https://zapier.com/about//", "//span[@class='email']")
Se puede tomar un correo electrónico sin atajos como estos. Lo hacemos haciendo coincidir su forma esencial (, también conocido como bob@gmail.com). Es más complicado, pero tiene mucho más potencial.
Una expresión regular es lo que usamos para capturar categóricamente información que coincide con un formato determinado. Digamos que queríamos saber todas las temperaturas enumeradas en un sitio web meteorológico. Captaríamos eso diciendo «danos todos los números que vienen antes del símbolo ° o ℃ o ℉«—Sí, todos esos son caracteres Unicode diferentes.
Si quisiéramos obtener una lista de correos electrónicos, diríamos «danos todas las cadenas que se ajusten al formato». O, en una expresión regular:
[a-zA-Z0-9_-.+]+@[a-zA-Z0-9-.]+.[a-zA-Z0-9-]{2,15}
Respire hondo y lo recorreremos paso a paso. Puede ver el símbolo @ y puede ver que el espacio «nombre de usuario» antes de @ (o [a-zA-Z0-9_.+-]+) está bastante cerca del área «host» después de @ (o [a-zA-Z0-9-.]+).
Y el bit de «sufijo» parece similar, pero no del todo. Eso es porque los caracteres permitidos en un La dirección de correo electrónico y el nombre de host, según lo determinado por Dioses de Internet, son limitados. Es posible que recuerde que se registró para obtener una dirección de correo electrónico y recibió un mensaje de error cuando intentó poner «~~» en ella. Yo también conozco ese dolor. Esto se debe a que los correos electrónicos toman caracteres en minúsculas (az), caracteres en mayúsculas (AZ), números (0-9), guiones bajos (_), guiones (-) y puntos (.) Y, ocasionalmente, signos más (+).
¿Qué pasa con las barras y los signos más en esa expresión? Los guiones y puntos ya indican cosas específicas en expresiones regulares, por lo que significan «el carácter dash y no el guión de la función de expresión regular «tenemos que» cancelarlos «, que es un término elegante para» ignorar lo que normalmente haría en este escenario «. La cancelación se realiza poniendo una barra invertida () en frente de eso.
El signo más fuera de los corchetes significa «permitir un carácter que coincida con eso, una o más veces». Por lo tanto, su nombre de correo electrónico puede tener cualquier número de caracteres, siempre que sea al menos uno.
Luego lo volvemos a hacer para el nombre de host: Uno o más caracteres en minúsculas, mayúsculas, números, guiones bajos, guiones y puntos, porque algunas direcciones de correo electrónico son «@ mail.hostname.suffix».
El último bit, el sufijo es más restringido: ([a-zA-Z0-9-]{2,15})
Solo podemos tener caracteres simples, y solo podemos tener de 2 a 15 caracteres (para incluir todos los nuevos dominios de moda como .coffee y .gripe y, aparentemente, el más largo hasta ahora, .cancerresearch). Entonces, en lugar del + que significa «cualquier longitud», establecemos una longitud mínima y máxima con {2,15}. (Puede establecer algo como «exactamente cinco» con solo {5}.)
En resumen, cuando queremos un personaje solo (como en el @) simplemente escribimos eso. Cuando queremos un carácter que se ajuste a cualquiera de los varios tipos de caracteres, colocamos todos los caracteres aceptables entre corchetes. Cuando queremos multiplicar eso por algún número, agregamos algunos corchetes que definen el número mínimo y máximo de caracteres que coinciden con la descripción, o usamos indicadores para decir «uno o más» o «ninguno o más». Cuando hacemos una multiplicación así, la ponemos entre corchetes. Algunos caracteres requieren «cancelar» con una barra invertida.
¡Allí, hoy aprendiste una nueva y poderosa habilidad! Todo solo para recibir correos electrónicos. Uf.
Los diferentes lenguajes de programación utilizan diferentes símbolos y sintaxis para hacer que las cosas funcionen; para una pequeña muestra, consulte emailregex.com; sí, un sitio web completo solo para conocer las formas de buscar una dirección de correo electrónico (no lea los comentarios). Y si desea profundizar aún más en la expresión regular de Google Sheets, aquí hay una lista secreta especial de funciones de Google Sheets, secreta porque Google es infamemente malo en la documentación, por lo que un grupo de usuarios ha escrito sus propias guías a través de prueba y error.
Cómo utilizar Regex para importar direcciones de correo electrónico desde un sitio web en Google Sheets
Tomemos esas direcciones de Zapier usando nuestros nuevos poderes de expresiones regulares. Estamos importando lo mismo <span>s, pero en lugar de buscar una clase que sea igual a «correo electrónico», buscamos contenido que coincida con la expresión regular. Nuevamente, hagámoslo en dos pasos: llamaremos mucha información de la página de Zapier en la primera columna, luego la clasificaremos para los correos electrónicos en la segunda columna.
=importxml("https://zapier.com/about//", "//span")
=regexextract(A1, "[a-zA-Z0-9_.+-]+@[a-zA-Z0-9-.]+.[a-zA-Z0-9-]{2,15}")
Y eso nos da esta tabla:
¿Puedes combinar estas dos funciones? Recuerde, ImportXML llenará las columnas y filas por sí mismo, dependiendo de lo que encuentre (llamado fórmula de matriz), y la consulta de expresiones regulares debe completarse para cada celda en la que desee obtener un resultado (es decir, no una fórmula de matriz ). Para unirlos todos, simplemente ordene Regexextract a una fórmula de matriz solo por esta vez (y agregue un IFERROR por el bien de la decencia, para dejar las celdas en blanco donde no se puede encontrar una dirección de correo electrónico):
=ArrayFormula(IFERROR(REGEXEXTRACT(IMPORTXML("https://zapier.com/about//", "//span"), "[a-zA-Z0-9_.+-]+@[a-zA-Z0-9-.]+.[a-zA-Z0-9-]{2,15}")))
Y, con eso, aquí está nuestra lista terminada de direcciones de correo electrónico impulsada por Regex de la página de Zapier:
Conviértete en un experto en Google Sheets con Zapier
Para obtener más información, hemos escrito sobre otros webscraping en nuestro libro electrónico gratuito Spreadsheet CRM. También puede leer sobre las funciones primarias de ImportXML:
-
ImportHTML: Una función más débil que capturará una tabla o lista completa de una página web determinada sin más controles
-
ImportRange—Para obtener datos de otras hojas de la hoja de cálculo
-
Datos de importacion: Para importar datos de un archivo CSV o TSV vinculado
-
ImportFeed—Que funciona de manera muy similar a ImportXML, pero para importar feeds RSS o Atom, lo que puede ser excelente si tiene problemas para importar XML desde un sitio web determinado ().
Junto con eso, aprenderá los conceptos básicos de la hoja de cálculo si necesita revisar, junto con consejos sobre cómo crear una aplicación completa en su hoja de cálculo, usar Google Apps Script para automatizar sus hojas de cálculo y una guía para usar la aplicación complementaria de Google Sheets. Formularios de Google.
O, para una manera más fácil de importar datos a su hoja de cálculo de Google Sheets, puede usar la herramienta de automatización de aplicaciones Zapier, las integraciones de Google Sheets para agregar datos a su hoja de cálculo automáticamente. Puede registrar Tweets en una hoja de cálculo, mantener una copia de seguridad de sus contactos de MailChimp o guardar datos de sus formularios y eventos en una hoja.
Zapier también puede poner a trabajar sus datos. Supongamos que usa importXML para extraer una lista de direcciones de correo electrónico en una hoja de cálculo. Luego, Zapier podría copiarlos de su hoja de cálculo y enviarles un mensaje de correo electrónico o agregarlos a su lista de correo. Podría agregar una lista de fechas a su Calendario de Google para una manera fácil de crear una lista de vacaciones o eventos. O podría agregar cada nueva entrada como una nueva tarea en su aplicación de administración de proyectos, o mucho, mucho más.
