Combinar hojas de cálculo en un solo libro

  • Artículo
  • Tiempo de lectura: 5 minutos

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. Agregue un nuevo paso para obtener todos los libros que desea combinar de su carpeta. Use el conector OneDrive para la Empresa y la acción Enumerar archivos en carpeta. 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 un nuevo paso para ejecutar el script de datos de la hoja de cálculo Return para obtener todos los datos de cada uno de los libros. Use el conector de Excel Online (Empresa) con la acción Ejecutar script . 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 Aplicar a cada control, 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

  5. Agregue un nuevo paso 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 Aplicar a cada control, agregue un conector de Excel Online (Empresa) con la acción Ejecutar script . 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)

    Las dos acciones 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. 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.

  7. 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 2" (Ejecutar script 2), 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.

    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.