Mover datos entre libros (Power Automate+Office Scripts)

Los scripts de Office tienen alcance de libro de trabajo. Lo que significa que no pueden mover datos de un libro a otro. De hecho, ni siquiera saben que existe otro libro. Sin embargo, pueden pasar datos a Power Automate, que tiene la capacidad de pasar datos a otros libros. Entonces, en esta publicación, veremos cómo mover datos entre libros automáticamente con Power Automate y scripts de Office.

Tabla de contenido

• Ejemplo
• Office Script para obtener datos de Excel
• Office Script para pegar datos desde Power Automate
• Power Automate Flow para mover datos entre libros
• Automatizar pasos
• Ejecutando el flujo
• Conclusión

Ejemplo

El ejemplo que estamos viendo en esta publicación es el siguiente:

El libro de trabajo Complete Data.xlsx contiene nuestro conjunto de datos completo. Incluye lo siguiente en la pestaña Datos .

Cada vez que recibimos un archivo con datos nuevos, queremos que Complete Data.xlsx se actualice automáticamente con esos datos nuevos. A continuación se muestra un ejemplo de cómo se ve cada nuevo archivo de datos. Está en una pestaña llamada Datos .

Descripción general de la solución:

Para esta solución, utilizamos solo 3 pasos.

1. Usaremos el activador Cuando se crea un nuevo archivo para OneDrive para la Empresa . Por lo tanto, el flujo se ejecuta cada vez que agregamos un archivo a una carpeta específica.
2. Después del desencadenador, se ejecuta un script de Office para recuperar los datos del nuevo libro y los devuelve a Power Automate.
3. Finalmente, se ejecuta otro script de Office para agregar los datos del paso 2 al libro de trabajo Complete Data.xlsx .

Office Script para obtener datos de Excel

El primer paso es crear el script para obtener los datos de Excel.

Primero, creemos el script para obtener los datos de Excel.

Hay algunas peculiaridades a tener en cuenta:

• No sabemos cuántas filas hay en un nuevo conjunto de datos. Usamos el método getSurroundingRegion() para mover todos los datos en el rango contiguo.
• Es probable que los nuevos datos incluyan filas de encabezado o filas de cálculo en la parte inferior. Sin embargo, estos no son parte del conjunto de datos. Por lo tanto, el siguiente script de Office tiene parámetros para eliminar un número específico de filas en la parte superior e inferior.
• La instrucción de retorno final pasa la matriz de datos a Power Automate en un objeto llamado wsRngArr .

Copie y pegue lo siguiente en el editor de código de Office Scripts. En el ejemplo, guardé el script como PA – Obtener datos de Excel .

Los comentarios proporcionan más detalles sobre cada línea de código.

function main(workbook: ExcelScript.Workbook,   wsName: string, startCell: string,   headerRowsToRemove: number,   footerRowsToRemove: number) {  //Declare and assign the worksheet  let ws = workbook.getWorksheet(wsName);  //Delare and assign the data range  let wsRng = ws.getRange(startCell).    getSurroundingRegion()  //Get the values of the data range in array  let wsRngArr = wsRng.getValues();  //Count the rows in array  let wsRngArrRowCount = wsRngArr.length;  //Calcualte the number of rows to return  let wsRngArrRowLength = wsRngArrRowCount -     headerRowsToRemove - footerRowsToRemove + 1;  //Remove the top and bottom rows  wsRngArr = wsRngArr.slice(headerRowsToRemove, wsRngArrRowLength)  //Return the range  return { wsRngArr };}

Los parámetros necesarios para el script:

• libro de trabajo: el objeto del libro de trabajo del que se obtienen los datos.
• wsName: el nombre de la hoja de trabajo que contiene los datos.
• startCell: la primera celda que contiene los datos (por ejemplo, "B2")
• headerRowsToRemove: este es el número de filas de encabezado que se eliminarán de la parte superior del rango (el valor puede ser 0 si no hay una fila de encabezado)
• footerRowsToRemove: este es el número de filas de pie de página que se eliminarán de la parte inferior del rango (el valor puede ser 0 si no hay ninguna fila de pie de página)

Office Script para pegar datos desde Power Automate

En el script anterior, recuperamos los datos del libro de trabajo y los pasamos a Power Automate. Ahora necesitamos una segunda secuencia de comandos para pegar los datos de Power Automate en otro libro de trabajo.

El ejemplo que estamos viendo aquí agrega los datos a un conjunto de datos existente.

Copie y pegue lo siguiente en el editor de código de Office Scripts. En el ejemplo, guardé el script como PA: Pegar y agregar datos a Excel .

Los comentarios proporcionan más detalles sobre cada línea de código.

function main(workbook: ExcelScript.Workbook, wsName: string,  startCell: string, strArr: string) {  //Convert the strArr to an array  let newDataArr: string[][] = JSON.parse(strArr)  //Declare and assign the worksheet  let ws = workbook.getWorksheet(wsName);  //Get the old data  let dataArr = ws.getRange(startCell).getSurroundingRegion().    getValues()  //Join the dataArr and newDataArr  dataArr = dataArr.concat(newDataArr)  //Get the size of the range to paste to  let dataRng = ws.getRange(startCell).    getAbsoluteResizedRange(dataArr.length, dataArr[0].length)  //Set the values of the new data set  dataRng.setValues(dataArr)}

Los parámetros necesarios para el script son:

• libro de trabajo: el objeto del libro de trabajo en el que pegar los datos.
• wsName: el nombre de la hoja de trabajo en la que pegar los datos.
• startCell: la primera celda del rango de datos a la que se agregan los nuevos datos
• strArr: la matriz de datos, como una cadena, pasada desde el primer script (es decir, el objeto llamado wsRngArr ).

Power Automate Flow para mover datos entre libros

El flujo completo de Power Automate para este ejemplo es el siguiente:

Automatizar pasos

Notas:

• En el paso Ejecutar script , no conocemos el nombre del archivo. Por lo tanto, el valor del identificador de archivo del paso Cuando se crea un archivo se ha utilizado para capturar el nombre del archivo de forma dinámica.
• Como se muestra en el paso Ejecutar script , los nuevos datos tienen 1 fila de encabezado para eliminar, pero cero filas de pie de página.
• Usando el paso Ejecutar script , wsRngArr se pasa a Power Automate desde el script PA – Obtener datos de Excel .
• En el paso Ejecutar script 2 , wsRngArr se pasa al script PA – Pegar datos adjuntos .

Ejecutando el flujo

Para ejecutar el flujo, solo necesitamos cargar un archivo con los nuevos datos en la carpeta One Drive /Ejemplo/Nuevos datos/ . Esto inicia el gatillo.

Dale al script unos segundos para que se ejecute y ¡Ta-Dah! Los nuevos datos se agregan automáticamente.

NOTA: El paso cuando se crea un archivo no se ejecuta cuando un archivo se mueve dentro de OneDrive, solo cuando se agrega un nuevo archivo a la carpeta.

Conclusión

Al utilizar Office Scripts y Power Automate, podemos mover fácilmente datos entre libros de forma automática. Esta es una excelente manera de controlar nuestros datos, ya que podemos mantener un único archivo que contenga todos los registros.

Artículos Relacionados:

• Utilice scripts de Office con Power Automate
• Cómo enviar por correo electrónico o guardar imágenes de Excel con Power Automate
• Scripts de Office: signos numéricos inversos

---