CONSOLIDAR INFORMACIÓN DE VARIOS ARCHIVOS EN UNA HOJA DE EXCEL ONLINE CON TYPESCRIPT

Hola a todos!.

El post de hoy va a ser bastante largo, el motivo es que para ejecutarlo es necesario utilizar dos scripts para Excel Online (en Typescript) y Power Automate.

Antes de comenzar debo añadir que ha basado parte de este trabajo con uno de los ejemplos que Microsoft mantiene publicado en su web: Combinar hojas de cálculo en un solo libro

Sin embargo he realizado modificaciones importantes en los scripts para que la información se agrupe únicamente en una hoja, en el ejemplo que muestran lo que hacen es agrupar en un archivo todas las pestanas / hojas de los otros archivos, y esto no es lo que estoy buscando, sino que toda la información de todos los archivos (con todas sus hojas) se listen en una hoja.

Antes de comenzar debéis contar con OneDrive versión empresa y es donde vamos a alojar los ficheros a consolidar y el fichero en el que vamos a consolidar todo.

En la carpeta ARCHIVOS, he incluido dos archivos con información ficticia de empleados de unos grandes almacenes, en cada archivo hay varias pestañas por departamentos y nuestro cometido es agrupar todos estos datos en el archivo que he colocado en la carpeta CONSOLIDADO.

Esta sería una muestra de los archivos a consolidar:

Antes de comenzar a construir el flujo de Power Automate necesitamos los dos scripts que previamente he modificado para hacer que todo funcione correctamente.

El primer script, que denominaré: SELECCIONAR RANGO EN CADA HOJA DE CADA ARCHIVO

function main(workbook: ExcelScript.Workbook): WorksheetData[] {
    var worksheetInformation: WorksheetData[] = [];
    workbook.getWorksheets().forEach((sheet) => {
        var filas = sheet.getUsedRange().getRowCount();
        var columnas = sheet.getUsedRange().getColumnCount();
        var values = sheet.getRangeByIndexes(1, 0, filas, columnas).getValues();;
        worksheetInformation.push({
            name: sheet.getName(),
            data: values as string[][]
        });
    });
    return worksheetInformation;
}
interface WorksheetData {
    name: string;
    data: string[][];
}

Con este proceso recorremos cada hoja y los valores que hemos seleccionado en la variable values. He dejado el nombre de la hoja (name) por si lo necesitáis.

El segundo script: CONSOLIDAR INFORMACION

function main(workbook: ExcelScript.Workbook, workbookName: string, worksheetInformation: WorksheetData[]) {
  var tit = workbook.getWorksheet("Hoja1");
  var titulos = ["ID", "NOMBRE COMPLETO", "SECCIÓN", "EDAD", "SEXO", "2º IDIOMA", "ESTUDIOS"]
  tit.getRange("A1:G1").setValues([titulos])
    worksheetInformation.forEach((seleccion) => {
        var sheet = workbook.getWorksheet("Hoja1");
        var nFila = sheet.getUsedRange().getRowCount();
        if (seleccion.data) {
            var range = sheet.getRangeByIndexes(nFila, 0, seleccion.data.length, seleccion.data[0].length);
            range.setValues(seleccion.data);
        }
    });
}
interface WorksheetData {
    name: string;
    data: string[][];
}

Donde colocamos los datos que hemos seleccionado el script anterior, en el archivo consolidado. Además colocamos el encabezado del archivo antes de empezar a consolidar.

Pues bien, ahora comenzamos con el Power Automate.

En primer lugar vamos a desencadenar el flujo de forma manual (podréis aquí elegir cualquier otra forma que más se ajuste a vuestras necesidades):

La siguiente acción es mostrar los archivos que vamos a consolidar. En concreto la ruta hasta la carpeta de OneDrive:

A continuación iniciamos un bucle por cada uno de los archivos, por eso debemos seleccionar Identificador (que pasará a renombrarse como «value»).

Luego tenemos que agregar otra acción, se trata de Ejecutar Script. Los dos primeros campos vienen prácticamente por defecto. EL archivo, debemos agregar contenido dinámico (identificador) y seleccionar el primer script de nuestra biblioteca de scripts.

Para finalizar, añadimos otra acción de Ejecutar Script, donde los dos primeros debéis seleccionar los que aparecen en este y en el anterior.

En el campo archivo seleccionamos la ruta hasta el archivo que consolidará los datos.

En el campo WorkbookName debemos seleccionar del contenido dinámico el nombre del archivo en OneDrive.

Y para finalizar en el campo worksheetInformation debemos seleccionar del contenido dinámico el valor «Result» de la acción Ejecutar Script

Ahora que ya tenemos nuestro flujo completo podemos pasar a ejecutar una prueba y ver si funciona correctamente.

Y el resultado es el esperado:

Y esto es todo, espero que sea de utilidad!!. Os dejo los dos archivos a consolidar:

¿Te ha resultado de interés?, puedes apoyar a Excel Signum con una pequeña donación.



¡¡Muchas gracias!!

Mediante la suscripción al blog, la realización comentarios o el uso del formulario de contacto estás dando tu consentimiento expreso al tratamiento de los datos personales proporcionados según lo dispuesto en la ley vigente (LOPD). Tienes más información al respecto en esta página del blog: Política de Privacidad y Cookies