Utilice scripts de Office con Power Automate

En esta publicación, veremos cómo usar Office Scripts con Power Automate. Ambas son herramientas relativamente nuevas en el mundo en línea y, cuando se usan juntas, crean un nuevo ecosistema para la automatización con Excel.

Power Automate es una herramienta de código bajo/sin código basada en la nube que permite la automatización a través de conectores en muchas aplicaciones de Microsoft y de terceros.

Los scripts de Office son una característica de Excel en línea que permite a los usuarios grabar o escribir scripts que automatizan tareas basadas en libros de Excel.

Me gusta pensar en ello como dos niveles: el nivel micro y el nivel ambiental.

• Nivel micro: los scripts de Office solo funcionan en un libro a la vez. Hay funciones limitadas para trabajar fuera de un libro de trabajo, e incluso pasar datos entre varios libros de trabajo es complicado.
• Nivel de entorno: Power Automate interactúa con diferentes aplicaciones; tiene conectores para Outlook, Excel, Gmail, Twitter, Adobe y muchos, muchos más.

Lo más importante de estas dos aplicaciones es que pueden funcionar juntas. Podemos llamar a un script de Office desde Power Automate y pasar valores de los scripts de Office a Power Automate. Al usar ambos, crean un marco en el que podemos lograr cosas sorprendentes.

En esta publicación, no cubriremos los conceptos básicos de Power Automate u Office Scripts. En cambio, nos centraremos en lograr que los dos interactúen entre sí y en cómo hacerlo de la manera más reutilizable.

Tabla de contenido

• El escenario
• guión explícito
• Valores de retorno de un script
• Guión dinámico
• Hacer que el guión sea dinámico
• Configurar una tabla de parámetros
• Usando el flujo dinámico
• Guiones reutilizables
• Cambiar un valor de celda (script reutilizable)
• Codificar una hoja (script reutilizable)
• Eliminar hojas específicas (guión reutilizable)
• Valores de retorno de celdas (script reutilizable)
• Actualización del script dinámico
• Impacto en el rendimiento
• Conclusión

Descargue el archivo de ejemplo: únase al programa Insiders gratuito y obtenga acceso al archivo de ejemplo utilizado para esta publicación.

Nombre del archivo: 0060 User Office Scripts con Power Automate.zip

Tener acceso

El escenario

Para demostrar los beneficios de usar Office Scripts y Power Automate, lo llevaré en un viaje a través de múltiples etapas. El escenario que estamos viendo es este:

• Una empresa tiene 4 regiones Norte, Este, Central y Oficina Central, cada una de las cuales está a cargo de un gerente regional diferente.
• A partir de un informe consolidado, tenemos que crear un correo electrónico separado para cada gerente regional, con su informe individual codificado adjunto.
• Los datos no se comparten, sólo el informe resumido final.

Archivo de ejemplo

El archivo contiene:

• Pestaña de datos que contiene una tabla llamada Datos:
  
• Pestaña de informe que contiene una declaración de ganancias y un gráfico por cliente. Cambiar el valor en la celda C2 actualiza la declaración de ganancias y el gráfico de la región seleccionada.
  

guión explícito

La versión inicial de nuestro Office Script realiza las siguientes acciones:

1. Cambia el valor de la celda C2 de la pestaña Informe al valor Norte (esto hace que el libro se vuelva a calcular automáticamente)
2. Codifica los valores en la pestaña Informe para eliminar cálculos.
3. Elimina todas las hojas excepto la pestaña Informe

Cada paso numerado corresponde a los números del siguiente guión.

function main(libro de trabajo: ExcelScript.Workbook){ //Asignar nombredehojadeinformeavariable letwsReportName="Reporte";letwsReport=workbook.getWorksheet(wsReportName); //[1] Cambiar el valor de la celda letselectedRegion="North";wsReport.getRange("C2").setValue(selectedRegion); //[2] Copiar y pegar valores en la hoja letrng = wsReport.getUsedRange();rng.setValues(rng.getValues()); //[3] Recorre cada hoja, elimina si no es la hoja del informe let wsArr = workbook.getWorksheets(); wsArr.forEach(ws = { if(ws.getName()!=wsReport.getName()){ ws.delete(); } })}

Al leer el código anterior, tome nota de los parámetros utilizados (los usaremos más adelante):

• “Informe”: este es el nombre de la pestaña
• “Norte” – el nombre de la región
• “C2” – la celda en la que se ingresa el nombre de la región

Dentro de Power Automate, existe una acción llamada ejecutar script, que requiere 4 parámetros:

• Ubicación, Biblioteca de documentos y Archivo se utilizan para hacer referencia al libro de trabajo en el que se ejecuta el script.
• Referencias de script al script a ejecutar (veremos más adelante que podemos pasar parámetros adicionales a un script).

El flujo completo de Power Automate para este proceso tiene 5 pasos:

• En un disparador manual
• Copie el archivo para crear una versión para la región Norte.
• Ejecute el script en el nuevo archivo
• Obtener el contenido del nuevo archivo
• Enviar el nuevo archivo como archivo adjunto a un correo electrónico

Aquí están los 5 pasos en detalle:

Al ejecutar el flujo anterior, creará y enviará por correo electrónico el informe para la región Norte. Si quisiéramos hacer esto para las 4 regiones, podríamos crear 3 scripts de Office más; uno para cada región, luego expanda nuestro flujo para realizar el proceso de creación y envío de correo electrónico 3 veces más.

Funcionaría, pero no es particularmente reutilizable; necesitaríamos crear nuevos scripts y cambiar significativamente el flujo de Power Automate para agregar regiones. Entonces, en una sección posterior, haremos esto más dinámico recorriendo una tabla de parámetros.

Valores de retorno de un script

La primera mejora es hacer que el correo electrónico sea más dinámico. Sugerimos que en nuestro escenario queremos incluir valores del informe directamente dentro del cuerpo del correo electrónico. Supongamos que el mensaje que queríamos era:

Hola Dave,

Adjunto se encuentra el informe diario de la Región Norte. La ganancia obtenida fue de $59 . que se generó a partir de $184 de Ventas.

Las secciones resaltadas en el mensaje anterior deben ser valores dinámicos basados ​​en el informe de Excel. Para lograr esto, agregamos una declaración de devolución a nuestro script (consulte la sección en negrita en la parte inferior, todo lo demás sigue igual).

function main(libro de trabajo: ExcelScript.Workbook){ //Asignar nombredehojadeinformeavariable letwsReportName="Reporte";letwsReport=workbook.getWorksheet(wsReportName); //[1] Cambiar el valor de la celda letselectedRegion="North";wsReport.getRange("C2").setValue(selectedRegion); //[2] Copiar y pegar en la hoja letrng=wsReport.getUsedRange();rng.setValues(rng.getValues()); //[3] Recorre cada hoja, elimina si no es la hoja del informe let wsArr = workbook.getWorksheets(); wsArr.forEach(ws = { if(ws.getName()!=wsReport.getName()){ ws.delete(); } })   //[4] Devuelve los valores de ganancias y ventas letprofitValue = wsReport.getRange( "C12").getValue(); let salesValue = wsReport.getRange("C8").getValue(); devolver {valor de ganancias, valor de ventas}; }

Se ha utilizado la declaración de devolución, seguida de los 2 valores que se devolverán entre llaves.

Mientras estamos aquí, tome nota mental de estos dos parámetros adicionales:

• “C12” – la celda que contiene el valor de la ganancia
• “C8” – la celda que contiene el valor de ventas

Cuando ejecutamos el script a través de un flujo, los valores de ganancias y ventas ahora se devuelven a Power Automate. Esto nos permite utilizar esos valores en nuestro flujo. Mire el extracto del correo electrónico a continuación; La sección Cuerpo se ha actualizado, por lo que los valores de ganancias y ventas ahora se basan en los valores devueltos por Office Script.

Guión dinámico

Ahora pasemos a dinamizar todo. Como se indicó anteriormente, realmente no queremos escribir scripts de Office separados para cada región.

Hacer que el guión sea dinámico

A modo de recordatorio, los 5 parámetros que les he pedido que anoten hasta ahora son:

• “Informe”: este es el nombre de la pestaña
• “Norte” – el nombre de la región seleccionada
• “C2” – la celda en la que se ingresa el nombre de la región
• “C12” – la celda que contiene el valor de la ganancia
• “C8” – la celda que contiene el valor de ventas

Ahora cambiamos la declaración de función en la parte superior del script para incluir los 5 parámetros.

• wsNombreReporte
• región seleccionada
• regiónCelda
• celda de beneficio
• ventasCelular

Nota: Al agregar los parámetros requeridos a un script, ya no se puede ejecutar manualmente en Excel en línea; solo se puede utilizar con Power Automate.

En el siguiente script, se resaltaron los parámetros y su uso.

functionmain(libro de trabajo: ExcelScript.Workbook, wsReportName : cadena,   región seleccionada : cadena, regiónCelda : cadena, celda de beneficio : cadena,    celda de ventas : cadena) { //Asignar nombre de hoja de informe a variableletwsReport=workbook.getWorksheet ( wsReportName ); //[1] Cambiar el valor de la celdawsReport.getRange( regionCell ).setValue( selectedRegion ); //[2] Copiar y pegar valores en la hoja letrng=wsReport.getUsedRange();rng.setValues(rng.getValues()); //[3] Recorre cada hoja, elimina si no es la hoja del informe let wsArr=workbook.getWorksheets(); wsArr.forEach(ws = { if(ws.getName()!=wsReport.getName()){ws.delete(); }}) //[4] Devuelve los valores de ganancias y ventas letprofitValue=wsReport.getRange( profitCell ).getValue();letsalesValue=wsReport.getRange( salesCell ).getValue(); retorno {valor de ganancias, valor de ventas}}

Ahora no hay valores codificados en nuestro script. Podemos cambiar cualquiera de los parámetros y el script se ejecutará.

Configurar una tabla de parámetros

Una acción de Power Automate, que se encuentra en el nivel del entorno, es recorrer cada elemento de una tabla de Excel. Si creamos una tabla de cada parámetro que necesitamos, todo nuestro flujo puede basarse en esa tabla.

Podemos modificar/agregar/eliminar detalles sobre cualquier región en la tabla de parámetros. Si cambia un administrador de región, no hay problema, actualizamos la tabla de parámetros con el nuevo nombre y dirección de correo electrónico, y el flujo continúa ejecutándose.

Nuestra tabla de parámetros tiene 9 parámetros, incluidos los 5 utilizados para Office Script.

• Región: el nombre de la región a seleccionar en el archivo de Excel.
• Nombre: el nombre de la persona que recibirá el informe.
• Dirección de correo electrónico: la dirección de correo electrónico a la que se debe enviar el archivo.
• Ruta del archivo: la ruta del archivo al archivo adjunto que se enviará
• Nombre de archivo: el nombre del archivo adjunto que se enviará
• Nombre de la pestaña: el nombre de la pestaña que se enviará
• Celda de región: la referencia de celda donde se ingresa el nombre de la región
• Celda de ventas: la celda de la pestaña que contiene el valor de las ventas.
• Celda de ganancias: la celda de la pestaña que contiene el valor de las ganancias

Usando el flujo dinámico

Nuestro flujo ahora necesita cambiar:

• En un disparador manual
• Recuperar todas las filas de la tabla de parámetros
• Para cada fila de la tabla de parámetros:
  • Copie el archivo para crear una nueva versión.
  • Ejecute el script en el nuevo archivo
  • Obtener el contenido del nuevo archivo
  • Enviar el nuevo archivo como archivo adjunto en un correo electrónico

Ahora podemos enviar informes para tantas regiones como deseemos; simplemente agregamos o eliminamos filas de la tabla de parámetros.

Aquí están los 5 pasos en detalle:

Tómate un segundo para mirar el paso Ejecutar script. Notará que Power Automate requiere los 5 parámetros de script que definimos en nuestro Office Script. Cada uno de estos parámetros se ha completado con valores tomados de la tabla de parámetros.

Guiones reutilizables

Pero espera... antes de comenzar a utilizar esta técnica, déjame preguntarte esto: ¿Qué tan reutilizable es Office Script? No muy.

Toma parámetros específicos y proporciona un resultado específico, pero lo hace mediante 4 pasos muy comunes.

1. Cambiar un valor de celda
2. Copiar y pegar valores en una hoja.
3. Eliminar todas las hojas excepto una
4. Valores de retorno de las celdas

Si usamos mucho Power Automate y Office Scripts, es probable que utilicemos estas 4 acciones una y otra vez en diferentes automatizaciones. Por lo tanto, adoptemos un enfoque modular y creemos 4 scripts reutilizables separados, uno para cada paso común.

No repasaré cada guión en detalle; esto tiene como objetivo demostrar los principios del uso de módulos reutilizables en lugar de crear módulos reutilizables.

Cambiar un valor de celda (script reutilizable)

functionmain(workbook:ExcelScript.Workbook,wsName:string,cellList:string, valueList: string, separator: string){ //Scriptvariables letws=workbook.getWorksheet(wsName); letcellListArr:string[]=cellList.split(separador);letvalueListArr:string[] = valueList.split(separador); //recorre todas las celdas y aplica un nuevo valor for(leti=0;icellListArr.length;i++){ ws.getRange(cellListArr[i]).setValue(valueListArr[i]);}}

Parámetros:

• wsName: el nombre de la hoja de trabajo en la que cambiar los valores
• cellList: la cadena de texto que contiene las referencias de celda de los valores a cambiar. Cuando hay varias celdas (por ejemplo, “A10,B12,A11”), deben estar separadas por caracteres separadores.
• valueList: la cadena de texto que contiene los valores que se aplicarán a las referencias de celda equivalentes. Cuando hay varios valores, deben estar separados por caracteres separadores.
• separador: la cadena de texto utilizada para separar la lista de valores y la lista de celdas (por ejemplo, en la lista de celdas y la lista de valores anteriores, el separador es una coma “,”)

Notas

• CellList y valueList deben contener la misma cantidad de elementos para que el script funcione correctamente.
• Este script no funciona con elementos con nombre, solo con referencias de celda.

Codificar una hoja (script reutilizable)

function main(workbook: ExcelScript.Workbook, wsList: string){ //Variables de script letwsArr=workbook.getWorksheets();letwsListArr:string[]=wsList.split(","); //Copiar y pegar valores para el rango utilizado para cada hoja de trabajo en la matriz wsArr.forEach(ws= wsListArr.forEach(wsInList={ if(ws.getName()==wsInList){ ws.getUsedRange().setValues(ws. getUsedRange().getValues()); } }))}

Parámetros:

• wsList: valor de cadena de los nombres de las hojas de trabajo separados por una coma.

Eliminar hojas específicas (guión reutilizable)

function main(libro de trabajo: ExcelScript.Workbook, método: cadena, wsList: cadena){ //Scriptvariables letwsArr=workbook.getWorksheets(); letwsListArr:string[]=wsList.split(",");letbooleanVar:boolean; //Ifmethod=="Listed"theapplytosheetsinarray if(method=="Listed"){ wsArr.forEach(ws= wsListArr.forEach(wsInList={ if(ws.getName()==wsInList){ ws.delete(); } }) )} //Ifmethod=="No listado"thenapplytosheetsnotinarray if(method=="No listado"){ wsArr.forEach(ws={ booleanVar=false; wsListArr.forEach(wsInList={ if(ws.getName()= =wsInList){ booleanVar=true; } }); if(booleanVar==false){ ws.delete(); } });}}

Este script es un poco complicado de entender ya que contiene un parámetro de método. El parámetro método se utiliza para determinar si las hojas que se eliminarán son las incluidas o excluidas del parámetro wsList.

Parámetros:

• método – valor de cadena de:
  • “Listado”: ​​el script se aplica a las hojas de trabajo en wsList
  • “No listado”: ​​el script se aplica a todas las hojas de trabajo que no están en wsList
• wsList: valor de cadena de los nombres de las hojas de trabajo separados por una coma

Valores de retorno de celdas (script reutilizable)

function main(workbook: ExcelScript.Workbook, wsName: string, cellRef: string){ //Scriptvariables letws=workbook.getWorksheet(wsName);letcellValue:string|number //GetthecellvaluecellValue=ws.getRange(cellRef).getValue(); // Devuelve el valor devuelve el valor de la celda;}

Parámetros

• wsName: el nombre de la hoja de trabajo desde la cual devolver el valor
• cellRef: cadena de texto que contiene las referencias de celda del valor que se devolverá.

Actualización del script dinámico

Ahora podemos actualizar nuestro flujo para usar los 4 scripts reutilizables (resaltados a continuación):

• En un disparador manual
• Recuperar cada fila de la tabla de parámetros.
• Para cada fila de la tabla:
  • Copie el archivo para crear una versión.
  • Ejecute el script para cambiar el valor de la celda
  • Ejecute el script para copiar y pegar valores.
  • Ejecute el script para eliminar hojas
  • Ejecute el script para devolver el valor de la ganancia.
  • Ejecute el script para devolver el valor de las ventas.
  • Obtener el contenido del nuevo archivo
  • Enviar el archivo como archivo adjunto a un correo electrónico

Nuestro flujo ahora se ve así:

Impacto en el rendimiento

Power Automate y Office Scripts no son particularmente rápidos. Llamar a varios scripts, en lugar de uno, añade mucho tiempo al proceso:

• Flujo dinámico original: 34 segundos para ejecutarse.
• Flujo dinámico reutilizable: 204 segundos para ejecutarse.

Cada llamada de un Office Script agrega aproximadamente 10 segundos sin importar lo que esté haciendo. Por lo tanto, el uso de pequeños scripts reutilizables implica una importante sobrecarga de tiempo.

Power Automate se ejecuta en línea y no bloquea ningún otro recurso, por lo que si un flujo tarda 170 segundos más, ¿realmente importa? Probablemente no. Sin embargo, para todos nosotros habrá un punto de inflexión en el que nos llevará demasiado tiempo. En estos escenarios, un script específico será una mejor opción, ya que el impacto en el tiempo debería reducirse significativamente.

Conclusión

Ahí vamos, así es como se usan Office Scripts con Power Automate. Hemos visto cómo pasar valores/parámetros hacia/desde Office Scripts y Power Automate. También hemos visto cómo hacer que nuestros scripts sean dinámicos y reutilizables. Con suerte, esta publicación le ha brindado algunas técnicas nuevas para usar.

Para nuestro escenario, utilizamos un disparador manual. Sin embargo, los activadores automáticos, como cuando se recibe un correo electrónico o cuando se crea un archivo, convertirían esto en un flujo verdaderamente automatizado.

Si comenzamos a crear una biblioteca de scripts de Office reutilizables, pronto tendremos una gran colección de scripts a los que podremos recurrir cuando sea necesario. Esto nos permite crear nuevos flujos con Power Automate en minutos, en lugar de las horas que antes habíamos tardado con VBA.

---