Seguro que has leído bastante al respecto sobre Web Scraping, casi siempre con programación en VBA o similar... pero quizá no supieras que se puede conseguir también con 'sencillas' fórmulas en nuestra hoja de cálculo!!.
Hoy explicaré cómo recuperar información de YouTube... así que lo primero que necesitarás es una key de YouTube.
No es especialmente complicado, solo necesitas una cuenta de Gmail y acceder a la consola de API de Google (https://console.cloud.google.com/) y seguir ciertos pasos (te dejo un link de Google al respecto)...
Un breve resumen es que debes buscar, en el panel de la consola, el menú de APIS y servicios > Biblioteca > y entre las opciones YouTube Data v3.
Luego crea un Proyecto y agrega los Credenciales oportunos.
Tras algunos sencillos pasos conseguirás tu necesaria key.
No es el propósito de este artículo detallar el proceso de consecución de esta 'clave'. Una sencilla búsqueda en Google te resolverá dudas al respecto.
El caso es que disponemos de nuestra clave/llave.
Muy importante es que leas o estudies lo descrito en esta URL: https://developers.google.com/youtube/v3/docs
Ya que nos da las indicaciones de cómo construir la ruta para recuperar información de youTube... donde además tenemos categorizadas las distintas acciones o maneras de invocar a las API y recuperar información (o realizar otras acciones!!: borrar videos, subirlos, etc...).
En nuestro caso nos centraremos en tres casos sencillos:
1- recuperar ciertas estadísticas de un video cualquiera (número de vistas totales, número de likes/dislikes, número de comentarios y veces marcado como favorito).
2- recuperar las primeras coincidencias de una búsqueda general en YouTube
3- obtener el listado de videos de un canal.
Para ello buscamos en la web anterior la categoría: Videos > list
https://developers.google.com/youtube/v3/docs/videos/list
donde nos ofrece el detalle de la cadena de texto de la URL que necesitamos montar, que empezaría por:
https://www.googleapis.com/youtube/v3/videos
seguido de ciertos parámetros obligatorios, filtro y otros parámetros opcionales.
En este caso debemos informar obligatoriamente del parámetro 'part', que tomará alguno de estos elementos:
id, snippet, contentDetails, fileDetails, player, processingDetails, recordingDetails, statistics, status, suggestions y topicDetails
para nuestro ejemplo tomaremos part=statistics
Y emplearemos el filtro de 'id' para especificar una lista separada por comas de ID de video de YouTube para los recursos que se están recuperando. En un recurso video, la propiedad id especifica el ID de video (por ejemplo, de una url del siguiente video: https://www.youtube.com/watch?v=Ij_aZFslgrE , el id del video es: Ij_aZFslgrE
No necesitamos más para nuestro ejemplo, excepto añadir al final nuestra clave/llave...
Por lo que nuestra URL completa para recuperar datos estadísticos de nuestro video quedaría:
https://www.googleapis.com/youtube/v3/videos?part=statistics&id=Ij_aZFslgrE&key=AIzaxxxxxxxx
Como vemos nuestra URL compuesta la hemos escrito en A1, mientras que en A3 llamamos a la función SERVICIOWEB (lee aquí algo más):
=SERVICIOWEB(A1)
Esta función SERVICIOWEB de Excel recupera el código JSON de ese video, con la información solicitada!!.
Es fácil de leer e interpretar... Existen ciertos parámetros como:
ViewCount
likeCount
dislikeCount
favoriteCount
CommentCount
Tal como se aprecia a continuación:
¿Cómo recuperar la información?... bueno, hay muchas formas, pero una muy sencilla es mediante varias SUSTITUCIONES que dejarían preparado ese código para poder tratarlo y separarlo en diferentes partes. La fórmula que aplicaríamos sería, en E3:
="<t><f><d>"&SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(ESPACIOS(A3);"{";"");"}";"");"[";"");"]";"");CARACTER(34);"");":";"</d><d>");",";"</d></f><f><d>")&"</d></f></t>"
Esto convertira el código generado en lo siguiente:
Lo que tratado oportunamente con la función XMLFILTRO en F3:J3:
=INDICE(XMLFILTRO($E$3;"//f/d");1+COINCIDIR(F$2;XMLFILTRO($E3;"//f/d");0))
nos permite recuperar las estadísticas deseadas!!
El segundo caso propuesto consiste en recuperar las primeras coincidencias de una búsqueda general en youTube; para lo cual accederemos a la URL:
https://developers.google.com/youtube/v3/docs/search/list
donde nos informa que la base de la URL buscada es:
https://www.googleapis.com/youtube/v3/search
y como en el caso anterior tenemos parámetros obligatorios, filtros y parámetros opcionales.
En este caso el obligatorios 'part' tiene como posibles valores permitidos id y snippet.
Nos interesa la propiedad 'snippet' ya que contiene otras propiedades que identifican el título del resultado, la descripción y así sucesivamente.
Y también usaremos otros parámetros opcionales como:
- maxResults: El parámetro maxResults especifica el número máximo de elementos que se debe mostrar en el conjunto de resultados. Los valores aceptables son 0 a 50, ambos inclusive. El valor predeterminado es 5.
- order: El parámetro order especifica el método que se usará para ordenar los recursos en la respuesta de la API. El valor predeterminado es SEARCH_SORT_RELEVANCE.
Los valores aceptables son:
date: Los recursos se ordenan en forma cronológica inversa, según la fecha en que se crearon.
rating: Los recursos se ordenan de mayor a menor calificación.
relevance: Los recursos se ordenan según su relevancia para la consulta de búsqueda. Este es el valor predeterminado de este parámetro.
title: Los recursos se ordenan alfabéticamente por título.
videoCount: Los canales se ordenan en forma descendente, según el número de videos subidos.
viewCount: Los recursos se ordenan de mayor a menor, según el número de reproducciones.
-q: El parámetro q especifica el término de consulta que se debe buscar.
-type:l parámetro type restringe una consulta de búsqueda para recuperar solo un tipo de recurso en particular. El valor es una lista separada por comas de los tipos de recursos. El valor predeterminado es video,channel,playlist.
-videoType:El parámetro videoType permite restringir una búsqueda para incluir un determinado tipo de videos.
Los valores aceptables son:
any: Mostrar todos los videos.
episode: Recuperar solo episodios de programas.
movie: Recuperar solo películas.
Así pues montamos nuestra URL completa con esos parámetros y quedaría:
https://www.googleapis.com/youtube/v3/search?part=snippet&order=rating&maxResults=6&type=video&videoType=any&q=Excel&key=AIzaxxxxxxx
donde vemos que solicitamos un orden por 'rating' (calificación), de tipo 'vídeo', buscando la palabra 'Excel' y pedimos muestre todos los vídeos ('any'). Solicitamos un máximo de elementos de 6.
En A1 escribimos esa URL y en A3 llamamos a la función:
=SERVICIOWEB(A1), donde obtendríamos el código con el desglose de los seis videos mejor clasificados...
En E3 añadimos la cadena de sustituciones anterior:
="<t><f><d>"&SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(ESPACIOS(A3);"{";"");"}";"");"[";"");"]";"");CARACTER(34);"");":";"</d><d>");",";"</d></f><f><d>")&"</d></f></t>"
Para finalmente recuperar con esta fórmula en F3:I3 información sobre: title, description, videoId,channelId
La fórmula:
=LET(cadena;XMLFILTRO($E$3;"//f/d");
filas;(SI.ERROR(COINCIDIR(cadena;F$2;0);0)*SECUENCIA(FILAS(cadena);;2));
INDICE(cadena;FILTRAR(filas;filas<>0)))
Fórmula desbordada que nos permite recuperar la info deseada de esos seis primero vídeos...
El último caso propuesto consiste en obtener el listado de videos de un canal; para lo cual accederemos a la URL:
https://developers.google.com/youtube/v3/docs/channels/list
donde nos informa que la base de la URL buscada es:
https://www.googleapis.com/youtube/v3/channels
y como en los casos anteriores tenemos parámetros obligatorios, filtros y parámetros opcionales.
Para este caso podemos primero podemos emplear parámetros y filtros iguales a algunos ya vistos:
-Part: El parámetro part especifica una lista separada por comas de una o más propiedades de recursos de channel que la respuesta de API va a incluir. Los nombres de part que se pueden incluir en el valor del parámetro son id, snippet, brandingSettings, contentDetails, invideoPromotion, statistics y topicDetails.
-id: El parámetro id especifica una lista separada por comas de ID de canal de YouTube para los recursos que se están recuperando. En un recurso de channel, la propiedad id especifica el ID de canal de YouTube del canal.
La cadena completa que nos ofrece información general del canal sería:
https://www.googleapis.com/youtube/v3/channels?part=statistics&id=UCM4Qv8BrnvYLwIdzArpMoeA&key=AIzaxxxxxxx
Con las mismas fórmulas que en el caso anterior en A3:
=SERVICIOWEB(A1)
para obtener el código en JSON
En E3 añadimos la cadena de sustituciones anterior:
="<t><f><d>"&SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(ESPACIOS(A3);"{";"");"}";"");"[";"");"]";"");CARACTER(34);"");":";"</d><d>");",";"</d></f><f><d>")&"</d></f></t>"
Y en F3:H3 la fórmula anterior:
=LET(cadena;XMLFILTRO($E$3;"//f/d");
filas;(SI.ERROR(COINCIDIR(cadena;F$2;0);0)*SECUENCIA(FILAS(cadena);;2));
INDICE(cadena;FILTRAR(filas;filas<>0)))
Y para listar los vídeos del canal emplearemos el id de canal y la acción PlayListItems con sus parámetros y filtros:
-Part: El parámetro part especifica una lista separada por comas de una o más propiedades de recursos de playlistItem que la respuesta de API va a incluir. Los nombres de part que se pueden incluir en el valor del parámetro son id, snippet, contentDetails y status.
La propiedad snippet contiene numerosos campos, incluidos title, description, position
- El parámetro playlistId especifica el ID exclusivo de la lista de reproducción para la cual deseas recuperar los elementos de la lista de reproducción.
Ten en cuenta que, aunque que este sea un parámetro opcional, cada solicitud para recuperar elementos de listas de reproducción debe especificar un valor para el parámetro id o el parámetro playlistId
- El parámetro maxResults especifica el número máximo de elementos que se deben mostrar en el conjunto de resultados. Los valores aceptables son 0 a 50, ambos inclusive. El valor predeterminado es 5.
Así pues para obtener un listado de los vídeos de un canal crearemos una URL con basae en: https://www.googleapis.com/youtube/v3/playlistItems
el parámetro 'part=snippet'
y el parámetro 'playlistid=[el id del canal]'
https://www.googleapis.com/youtube/v3/playlistItems?part=snippet&playlistId=UUM4Qv8BrnvYLwIdzArpMoeA&key=AIzaxxxxx
Con las mismas funciones de los casos previos veríamos:
Lo que nos permite recuperar información de los vídeos respecto a 'title¡, 'description', position' o incluso el id del video 'videoId'.
Con lo que terminamos este artículo... Interesante para obtener diferente información desde YouTube.. y todo empleando algo de formulación estándar!!, sin macros!.
Hoy explicaré cómo recuperar información de YouTube... así que lo primero que necesitarás es una key de YouTube.
No es especialmente complicado, solo necesitas una cuenta de Gmail y acceder a la consola de API de Google (https://console.cloud.google.com/) y seguir ciertos pasos (te dejo un link de Google al respecto)...
Un breve resumen es que debes buscar, en el panel de la consola, el menú de APIS y servicios > Biblioteca > y entre las opciones YouTube Data v3.
Luego crea un Proyecto y agrega los Credenciales oportunos.
Tras algunos sencillos pasos conseguirás tu necesaria key.
No es el propósito de este artículo detallar el proceso de consecución de esta 'clave'. Una sencilla búsqueda en Google te resolverá dudas al respecto.
El caso es que disponemos de nuestra clave/llave.
Muy importante es que leas o estudies lo descrito en esta URL: https://developers.google.com/youtube/v3/docs
Ya que nos da las indicaciones de cómo construir la ruta para recuperar información de youTube... donde además tenemos categorizadas las distintas acciones o maneras de invocar a las API y recuperar información (o realizar otras acciones!!: borrar videos, subirlos, etc...).
En nuestro caso nos centraremos en tres casos sencillos:
1- recuperar ciertas estadísticas de un video cualquiera (número de vistas totales, número de likes/dislikes, número de comentarios y veces marcado como favorito).
2- recuperar las primeras coincidencias de una búsqueda general en YouTube
3- obtener el listado de videos de un canal.
Para ello buscamos en la web anterior la categoría: Videos > list
https://developers.google.com/youtube/v3/docs/videos/list
donde nos ofrece el detalle de la cadena de texto de la URL que necesitamos montar, que empezaría por:
https://www.googleapis.com/youtube/v3/videos
seguido de ciertos parámetros obligatorios, filtro y otros parámetros opcionales.
En este caso debemos informar obligatoriamente del parámetro 'part', que tomará alguno de estos elementos:
id, snippet, contentDetails, fileDetails, player, processingDetails, recordingDetails, statistics, status, suggestions y topicDetails
para nuestro ejemplo tomaremos part=statistics
Y emplearemos el filtro de 'id' para especificar una lista separada por comas de ID de video de YouTube para los recursos que se están recuperando. En un recurso video, la propiedad id especifica el ID de video (por ejemplo, de una url del siguiente video: https://www.youtube.com/watch?v=Ij_aZFslgrE , el id del video es: Ij_aZFslgrE
No necesitamos más para nuestro ejemplo, excepto añadir al final nuestra clave/llave...
Por lo que nuestra URL completa para recuperar datos estadísticos de nuestro video quedaría:
https://www.googleapis.com/youtube/v3/videos?part=statistics&id=Ij_aZFslgrE&key=AIzaxxxxxxxx
Como vemos nuestra URL compuesta la hemos escrito en A1, mientras que en A3 llamamos a la función SERVICIOWEB (lee aquí algo más):
=SERVICIOWEB(A1)
Esta función SERVICIOWEB de Excel recupera el código JSON de ese video, con la información solicitada!!.
Es fácil de leer e interpretar... Existen ciertos parámetros como:
ViewCount
likeCount
dislikeCount
favoriteCount
CommentCount
Tal como se aprecia a continuación:
"{
""kind"": ""youtube#videoListResponse"",
""etag"": ""36yKoEfQEmZ4XCI23IXbRTMuchY"",
""items"": [
{
""kind"": ""youtube#video"",
""etag"": ""E2_Uj0iotU7CQNZTDSBGfKBWwe0"",
""id"": ""Ij_aZFslgrE"",
""statistics"": {
""viewCount"": ""125"",
""likeCount"": ""3"",
""dislikeCount"": ""0"",
""favoriteCount"": ""0"",
""commentCount"": ""2""
}
}
],
""pageInfo"": {
""totalResults"": 1,
""resultsPerPage"": 1
}
}
"¿Cómo recuperar la información?... bueno, hay muchas formas, pero una muy sencilla es mediante varias SUSTITUCIONES que dejarían preparado ese código para poder tratarlo y separarlo en diferentes partes. La fórmula que aplicaríamos sería, en E3:
="<t><f><d>"&SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(ESPACIOS(A3);"{";"");"}";"");"[";"");"]";"");CARACTER(34);"");":";"</d><d>");",";"</d></f><f><d>")&"</d></f></t>"
Esto convertira el código generado en lo siguiente:
"" kind youtube#videoListResponse etag 36yKoEfQEmZ4XCI23IXbRTMuchY items kind youtube#video etag E2_Uj0iotU7CQNZTDSBGfKBWwe0 id Ij_aZFslgrE statistics viewCount 125 likeCount 3 dislikeCount 0 favoriteCount 0 commentCount 2 pageInfo totalResults 1 resultsPerPage 1
Lo que tratado oportunamente con la función XMLFILTRO en F3:J3:
=INDICE(XMLFILTRO($E$3;"//f/d");1+COINCIDIR(F$2;XMLFILTRO($E3;"//f/d");0))
nos permite recuperar las estadísticas deseadas!!
El segundo caso propuesto consiste en recuperar las primeras coincidencias de una búsqueda general en youTube; para lo cual accederemos a la URL:
https://developers.google.com/youtube/v3/docs/search/list
donde nos informa que la base de la URL buscada es:
https://www.googleapis.com/youtube/v3/search
y como en el caso anterior tenemos parámetros obligatorios, filtros y parámetros opcionales.
En este caso el obligatorios 'part' tiene como posibles valores permitidos id y snippet.
Nos interesa la propiedad 'snippet' ya que contiene otras propiedades que identifican el título del resultado, la descripción y así sucesivamente.
Y también usaremos otros parámetros opcionales como:
- maxResults: El parámetro maxResults especifica el número máximo de elementos que se debe mostrar en el conjunto de resultados. Los valores aceptables son 0 a 50, ambos inclusive. El valor predeterminado es 5.
- order: El parámetro order especifica el método que se usará para ordenar los recursos en la respuesta de la API. El valor predeterminado es SEARCH_SORT_RELEVANCE.
Los valores aceptables son:
date: Los recursos se ordenan en forma cronológica inversa, según la fecha en que se crearon.
rating: Los recursos se ordenan de mayor a menor calificación.
relevance: Los recursos se ordenan según su relevancia para la consulta de búsqueda. Este es el valor predeterminado de este parámetro.
title: Los recursos se ordenan alfabéticamente por título.
videoCount: Los canales se ordenan en forma descendente, según el número de videos subidos.
viewCount: Los recursos se ordenan de mayor a menor, según el número de reproducciones.
-q: El parámetro q especifica el término de consulta que se debe buscar.
-type:l parámetro type restringe una consulta de búsqueda para recuperar solo un tipo de recurso en particular. El valor es una lista separada por comas de los tipos de recursos. El valor predeterminado es video,channel,playlist.
-videoType:El parámetro videoType permite restringir una búsqueda para incluir un determinado tipo de videos.
Los valores aceptables son:
any: Mostrar todos los videos.
episode: Recuperar solo episodios de programas.
movie: Recuperar solo películas.
Así pues montamos nuestra URL completa con esos parámetros y quedaría:
https://www.googleapis.com/youtube/v3/search?part=snippet&order=rating&maxResults=6&type=video&videoType=any&q=Excel&key=AIzaxxxxxxx
donde vemos que solicitamos un orden por 'rating' (calificación), de tipo 'vídeo', buscando la palabra 'Excel' y pedimos muestre todos los vídeos ('any'). Solicitamos un máximo de elementos de 6.
En A1 escribimos esa URL y en A3 llamamos a la función:
=SERVICIOWEB(A1), donde obtendríamos el código con el desglose de los seis videos mejor clasificados...
En E3 añadimos la cadena de sustituciones anterior:
="<t><f><d>"&SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(ESPACIOS(A3);"{";"");"}";"");"[";"");"]";"");CARACTER(34);"");":";"</d><d>");",";"</d></f><f><d>")&"</d></f></t>"
Para finalmente recuperar con esta fórmula en F3:I3 información sobre: title, description, videoId,channelId
La fórmula:
=LET(cadena;XMLFILTRO($E$3;"//f/d");
filas;(SI.ERROR(COINCIDIR(cadena;F$2;0);0)*SECUENCIA(FILAS(cadena);;2));
INDICE(cadena;FILTRAR(filas;filas<>0)))
Fórmula desbordada que nos permite recuperar la info deseada de esos seis primero vídeos...
El último caso propuesto consiste en obtener el listado de videos de un canal; para lo cual accederemos a la URL:
https://developers.google.com/youtube/v3/docs/channels/list
donde nos informa que la base de la URL buscada es:
https://www.googleapis.com/youtube/v3/channels
y como en los casos anteriores tenemos parámetros obligatorios, filtros y parámetros opcionales.
Para este caso podemos primero podemos emplear parámetros y filtros iguales a algunos ya vistos:
-Part: El parámetro part especifica una lista separada por comas de una o más propiedades de recursos de channel que la respuesta de API va a incluir. Los nombres de part que se pueden incluir en el valor del parámetro son id, snippet, brandingSettings, contentDetails, invideoPromotion, statistics y topicDetails.
-id: El parámetro id especifica una lista separada por comas de ID de canal de YouTube para los recursos que se están recuperando. En un recurso de channel, la propiedad id especifica el ID de canal de YouTube del canal.
La cadena completa que nos ofrece información general del canal sería:
https://www.googleapis.com/youtube/v3/channels?part=statistics&id=UCM4Qv8BrnvYLwIdzArpMoeA&key=AIzaxxxxxxx
Con las mismas fórmulas que en el caso anterior en A3:
=SERVICIOWEB(A1)
para obtener el código en JSON
En E3 añadimos la cadena de sustituciones anterior:
="<t><f><d>"&SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(ESPACIOS(A3);"{";"");"}";"");"[";"");"]";"");CARACTER(34);"");":";"</d><d>");",";"</d></f><f><d>")&"</d></f></t>"
Y en F3:H3 la fórmula anterior:
=LET(cadena;XMLFILTRO($E$3;"//f/d");
filas;(SI.ERROR(COINCIDIR(cadena;F$2;0);0)*SECUENCIA(FILAS(cadena);;2));
INDICE(cadena;FILTRAR(filas;filas<>0)))
Y para listar los vídeos del canal emplearemos el id de canal y la acción PlayListItems con sus parámetros y filtros:
-Part: El parámetro part especifica una lista separada por comas de una o más propiedades de recursos de playlistItem que la respuesta de API va a incluir. Los nombres de part que se pueden incluir en el valor del parámetro son id, snippet, contentDetails y status.
La propiedad snippet contiene numerosos campos, incluidos title, description, position
- El parámetro playlistId especifica el ID exclusivo de la lista de reproducción para la cual deseas recuperar los elementos de la lista de reproducción.
Ten en cuenta que, aunque que este sea un parámetro opcional, cada solicitud para recuperar elementos de listas de reproducción debe especificar un valor para el parámetro id o el parámetro playlistId
- El parámetro maxResults especifica el número máximo de elementos que se deben mostrar en el conjunto de resultados. Los valores aceptables son 0 a 50, ambos inclusive. El valor predeterminado es 5.
Así pues para obtener un listado de los vídeos de un canal crearemos una URL con basae en: https://www.googleapis.com/youtube/v3/playlistItems
el parámetro 'part=snippet'
y el parámetro 'playlistid=[el id del canal]'
https://www.googleapis.com/youtube/v3/playlistItems?part=snippet&playlistId=UUM4Qv8BrnvYLwIdzArpMoeA&key=AIzaxxxxx
Con las mismas funciones de los casos previos veríamos:
Lo que nos permite recuperar información de los vídeos respecto a 'title¡, 'description', position' o incluso el id del video 'videoId'.
Con lo que terminamos este artículo... Interesante para obtener diferente información desde YouTube.. y todo empleando algo de formulación estándar!!, sin macros!.
No hay comentarios:
Publicar un comentario
Nota: solo los miembros de este blog pueden publicar comentarios.