Combinar hojas de cálculo en un solo libro

  • Artículo

En este ejemplo se muestra cómo extraer datos de varios libros en un único libro centralizado. Usa dos scripts: uno para recuperar información de un libro y otro para crear nuevas hojas de cálculo con esa información. Combina los scripts de un flujo de Power Automate que actúa en una carpeta de OneDrive completa.

Importante

En este ejemplo solo se copian los valores de los demás libros. No conserva el formato, los gráficos, las tablas u otros objetos.

Solución

  1. Cree un nuevo archivo de Excel en OneDrive. El nombre de archivo "Combination.xlsx" se usa en este ejemplo.
  2. Cree y guarde los dos scripts de este ejemplo.
  3. Cree una carpeta en OneDrive y agréguele uno o varios libros con datos. El nombre de carpeta "output" se usa en este ejemplo.
  4. Cree un flujo (como se describe en la sección Flujo de Power Automate de este artículo) para realizar estos pasos:
    1. Enumere todos los archivos de la carpeta "output".
    2. Use el script de datos Devolver hoja de cálculo para obtener los datos de cada hoja de cálculo de cada uno de los libros.
    3. Use el script Agregar hojas de cálculo para crear una nueva hoja de cálculo en el libro "Combination.xlsx" para cada hoja de cálculo de todos los demás archivos.

Código de ejemplo: devolver datos de hoja de cálculo

/**
 * This script returns the values from the used ranges on each worksheet.
 */
function main(workbook: ExcelScript.Workbook): WorksheetData[] {
  // Create an object to return the data from each worksheet.
  let worksheetInformation: WorksheetData[] = [];

  // Get the data from every worksheet, one at a time.
  workbook.getWorksheets().forEach((sheet) => {
    let values = sheet.getUsedRange()?.getValues();
    worksheetInformation.push({
       name: sheet.getName(),
       data: values as string[][]
    });
  });

  return worksheetInformation;
}

// An interface to pass the worksheet name and cell values through a flow.
interface WorksheetData {
  name: string;
  data: string[][];
}

Código de ejemplo: Agregar hojas de cálculo

/**
 * This script creates a new worksheet in the current workbook for each WorksheetData object provided.
 */
function main(workbook: ExcelScript.Workbook, workbookName: string, worksheetInformation: WorksheetData[]) {
  // Add each new worksheet.
  worksheetInformation.forEach((value) => {
    let sheet = workbook.addWorksheet(`${workbookName}.${value.name}`);

    // If there was any data in the worksheet, add it to a new range.
    if (value.data) {
      let range = sheet.getRangeByIndexes(0, 0, value.data.length, value.data[0].length);
      range.setValues(value.data);
    }
  });
}

// An interface to pass the worksheet name and cell values through a flow.
interface WorksheetData {
  name: string;
  data: string[][];
}

Flujo de Power Automate: combinar hojas de cálculo en un único libro

  1. Inicie sesión en Power Automate y cree un nuevo flujo de nube instantánea.

  2. Elija Desencadenar manualmente un flujo y seleccione Crear.

  3. Obtenga todos los libros que desea combinar de su carpeta. Agregue una acción y elija la acción Lista de archivos en carpeta del conector de OneDrive para la Empresa. En el campo Carpeta , use el selector de archivos para seleccionar la carpeta "salida".

    El conector de OneDrive para la Empresa completado en Power Automate.

  4. Agregue una acción para ejecutar el script de datos de la hoja de cálculo Devolver para obtener todos los datos de cada uno de los libros. Elija la acción Ejecutar script del conector de Excel Online (Empresa). Use los siguientes valores para la acción. Tenga en cuenta que al agregar el identificador del archivo, Power Automate encapsulará la acción en un control For each , por lo que la acción se realizará en cada archivo.

    • Ubicación: OneDrive para la Empresa
    • Biblioteca de documentos: OneDrive
    • Archivo: Identificador (contenido dinámico de archivos de lista en la carpeta)
    • Script: devolver datos de la hoja de cálculo

    La acción Ejecutar script completada en el panel de tareas de acción.

  5. Agregue una acción para ejecutar el script Agregar hojas de cálculo en el nuevo archivo de Excel que creó. Esto agregará los datos de todos los demás libros. Después de la acción Ejecutar script anterior y dentro del control Para cada control, agregue una acción que use la acción Ejecutar script del conector de Excel Online (empresa). Use los siguientes valores para la acción.

    • Ubicación: OneDrive para la Empresa
    • Biblioteca de documentos: OneDrive
    • Archivo: "Combination.xlsx" (el archivo, seleccionado por el selector de archivos)
    • Script: Agregar hojas de cálculo
    • workbookName: nombre (contenido dinámico de archivos de lista en la carpeta)
    • worksheetInformation (vea la nota que sigue a la siguiente imagen): resultado (contenido dinámico del script de ejecución)

    La segunda acción Ejecutar script dentro del control Aplicar a cada control.

    Nota:

    Seleccione el botón Cambiar a la matriz completa de entrada para agregar el objeto de matriz directamente, en lugar de elementos individuales para la matriz. Haga esto antes de escribir el resultado.

    Botón para cambiar a la entrada de una matriz completa en un cuadro de entrada de campo de control.

  6. Guarde el flujo. El diseñador de flujo debe tener un aspecto similar al de la imagen siguiente.

    Diseñador de flujo que muestra las dos acciones ejecutar script dentro de un bucle de control For each.

  7. Use el botón Probar de la página del editor de flujo o ejecute el flujo a través de la pestaña Mis flujos . Asegúrese de permitir el acceso cuando se le solicite.

  8. El archivo "Combination.xlsx" ahora debería tener hojas de cálculo nuevas.

Solución de problemas

  • Ya existe un recurso con el mismo nombre o identificador: este error probablemente indica que el libro "Combination.xlsx" ya tiene una hoja de cálculo con el mismo nombre. Esto ocurrirá si ejecuta el flujo varias veces con los mismos libros. Cree un nuevo libro cada vez para almacenar los datos combinados o usar nombres de archivo diferentes en la carpeta "output".

  • El argumento no es válido o falta o tiene un formato incorrecto: este error puede significar que el nombre de la hoja de cálculo generado no cumple los requisitos de Excel. Es probable que esto se deba a que el nombre es demasiado largo. Si los nombres de la hoja de cálculo tendrán más de 30 caracteres, reemplace el código de "Agregar hojas de cálculo" que llama a addWorksheet con algo que abrevia la cadena. Dado que el propio nombre del libro puede ser demasiado largo, agregue un número de incremento al final del nombre de la hoja de cálculo. Declare este número fuera del forEach bucle .

    let worksheetNumber = 1;
// Add each new worksheet.
worksheetInformation.forEach((value) => {
    let worksheetName = `${workbookName}.${value.name}`;
    let sheet = workbook.addWorksheet(`${worksheetName.substr(0,30)}${worksheetNumber++}`);

    Además, si los nombres del libro tienen más de 30 caracteres, deberá acortarlos en el flujo. En primer lugar, debe crear una variable en el flujo para realizar un seguimiento del recuento de libros. Esto evitará que se pasen nombres abreviados idénticos al script. Agregue una acción Inicializar variable antes del flujo (de tipo "Integer") y una acción Incrementar variable entre las dos acciones ejecutar script . A continuación, en lugar de usar Name como workbookName en "Run script 1" (Ejecutar script 1), use la expresión substring(items('Apply_to_each')?['Name'],0,min(length(items('Apply_to_each')?['Name']),20)) y el contenido dinámico de la variable. Esto acorta los nombres del libro a 20 caracteres y anexa el número de libro actual a la cadena que se pasa al script.

    La segunda acción Ejecutar script con los cambios en el parámetro de nombre del libro.

    Los pasos Inicializar variable e Incrementar variable agregados al flujo.

    Nota:

    En lugar de complicar el flujo y el script, puede ser más fácil garantizar que los nombres de archivo y hoja de cálculo sean lo suficientemente cortos.