Hace poco me preguntaban ¿cómo conectar las respuestas de una encuesta de Google Forms con Power BI?. Dado que actualmente no existe un conector específico para Google Sheets (donde se guardan estas respuestas), muchos usuarios lo que hacen es exponer sus datos públicamente y luego utilizar una conexión web.
Pero ¿cómo lo logramos sin tener que poner en riesgo la seguridad de los datos corporativos de nuestro cliente?, ¿cómo lo hago de manera segura 🔐?… lo probé de distintas maneras, y en su momento no fue posible con una conexión web utilizando oAuth, ni mucho menos con OData 😢.
Para quienes no han escuchado de Google Sheets, es una solución de Google similar a Microsoft Excel, y que va tomando fuerza.
Han pasado quizá dos meses desde esa pregunta, y hoy, buscando tema para compartirles decidí hallar solución a esta necesidad, y la he sudado 🥵 ya que probé muchas maneras y estuve a punto de tirar la toalla hasta me iluminé 💡. Posiblemente no sea una necesidad muy común, pero de eso quiero que se trate este blog, de lo que no consigues en los 3.454.478 blogs de Power BI existentes en la web… nah mentiras, no los he contado y tampoco creo que sean tantos.
Veamos la solución, es un poco de aquí y poco de allá pero aquí va, utilicé un query de Python y las APIs de Google. Como deben imaginarlo, primero debes instalar Python en tu maquina, para ello puedes ver la documentación oficial en su web, o dando clic aquí.
Contando con Python instalado en nuestra maquina, debemos dirigirnos ahora a la Google Developers Console, allí iniciaremos sesión con nuestra cuenta y seguiremos el siguiente paso a paso:
- Crear un nuevo proyecto, en la parte superior izquierda, opción “Selecciona un proyecto”
- en el listado de proyectos que se nos abre, debemos seleccionar la opción “NUEVO PROYECTO”
- ahora debes darle un nombre a tu proyecto. En cuanto a la organización, dejémoslo “Sin organización”, y demos clic en “CREAR”
- una vez creado, debemos seleccionarlo en la opción superior izquierda llamada “Selecciona un proyecto”
- ahora, habilitaremos los servicios requeridos, para ello verificamos que nos encontremos en el proyecto que acabamos de crear, luego damos clic en “+ HABILITAR API Y SERVICIOS”
- esto nos llevará a la biblioteca de APIs de Google, donde buscaremos y seleccionaremos la “Google Drive API”
- luego, simplemente la habilitamos dando clic en el botón “HABILITAR”
- ahora debes crear las credenciales de conexión, para eso, en las nuevas opciones visibles daremos clic en “CREAR CREDENCIALES”
- Las configuraciones de credenciales, deben ser así:
¿Qué API estás usando?
Google Dive API
¿Desde dónde llamarás a la API?
Servidor web (p. ej., node.js, Tomcat)
¿A qué tipo de datos accederás?
Datos de la aplicación
¿Piensas utilizar esta API con App Engine o Compute Engine?
No, no estoy usando ninguna
Luego da clic en “¿Que credenciales necesito?”
- damos un nombre a nuestra cuenta de servicio, será el usuario que tendrá permisos a nuestras sheets
- le asignamos los permisos de editor sobre nuestro servicio de Drive
- y damos clic en “Continuar”. Esto nos descargará un archivo en formato json
- Ahora, debemos habilitar la “Google Sheets API”. Para esto debemos ir nuevamente a la biblioteca, encontrarás en link en el panel izquierdo de donde te encuentras; ahí busca “Google Sheets API” y selecciona la opción correspondiente
- En las nuevas opciones disponibles, simplemente damos clic en “HABILITAR”
Los pasos aplicados hasta ahora nos habilitaron un usuario de servicio que podemos encontrar al abrir el archivo descargado, lo he resaltado en amarillo.
- Copiemos todo del contenido entre comillas del client_email. Y nos dirigimos a la web de Google Sheets
- buscamos el libro que deseamos leer con Power BI, y le damos compartir. Lo compartiremos al usuario de servicio generado, el client_email.
Para este ejercicio yo he preparado el siguiente libro llamado “MiGoogleSheetsProject”, contiene 3 hojas con datos distintos: “Ventas POS”, “Ventas OnLine” y “Fidelización”
Ya hemos terminado con Google. Ahora preparemos nuestro Python.
Iniciemos instalando las dependencias necesarias: gspread, pandas y oauth2client
- Para instalar las dependencias, debemos abrir la consola de Windows y ejecutar las siguientes líneas.
Si te llegase a salir algún error por la ausencia de pip, descarga el siguiente archivo get-pip.py de pypa.io. Guárdalo en la ubicación que prefieras y ejecutas primero la línea 5.
Ya hemos dejado las dependencias instaladas en nuestro Python.
Debemos configurar Power BI para ejecutar código de Python, te puedes basar en la siguiente documentación oficial de Microsoft, tranquilo, es un par de clics: Enable Python scripting
Finalmente debemos ejecutar nuestra consulta en Power BI.
- Vamos a “Home > Get data > More…”
- ahora buscamos la opción “Python script”
- En la nueva ventana que se nos habilitará, debemos colocar nuestro código de Python. Este código no lo explico aquí para no hacer más largo este post, pero lo dejo he documentado para que te bases en él y puedas leer tu propios sheets de Google
En cuanto demos clic en el botón “OK” de Power BI, obtendremos las tablas que resultaron de nuestro código, y que guardamos en un DataFrame de pandas
Fijense:
De aquí en adelante es historia, ya sabremos qué hacer con esos datos.
Saludos.
