Archivos de Excel de referencia cruzada con Power Automate

Artículo
12/06/2023

Esta solución muestra cómo comparar datos entre dos archivos de Excel para encontrar discrepancias. Usa scripts de Office para analizar datos y Power Automate para comunicarse entre los libros.

En este ejemplo se pasan datos entre libros mediante objetos JSON . Para obtener más información sobre cómo trabajar con JSON, lea Uso de JSON para pasar datos hacia y desde scripts de Office.

Escenario de ejemplo

Usted es un coordinador de eventos que está programando oradores para las próximas conferencias. Los datos de eventos se conservan en una hoja de cálculo y en los registros del hablante en otra. Para asegurarse de que los dos libros se mantienen sincronizados, use un flujo con scripts de Office para resaltar los posibles problemas.

Archivos de Excel de ejemplo

Descargue los siguientes archivos para obtener libros listos para usar para el ejemplo.

Agregue los siguientes scripts para probar el ejemplo usted mismo. En Excel, use Automatizar>nuevo script para pegar el código y guardar los scripts con los nombres sugeridos.

Código de ejemplo: Obtención de datos de eventos

function main(workbook: ExcelScript.Workbook): string {
  // Get the first table in the "Keys" worksheet.
  let table = workbook.getWorksheet('Keys').getTables()[0];

  // Get the rows in the event table.
  let range = table.getRangeBetweenHeaderAndTotal();
  let rows = range.getValues();

  // Save each row as an EventData object. This lets them be passed through Power Automate.
  let records: EventData[] = [];
  for (let row of rows) {
    let [eventId, date, location, capacity] = row;
    records.push({
      eventId: eventId as string,
      date: date as number,
      location: location as string,
      capacity: capacity as number
    })
  }

  // Log the event data to the console and return it for a flow.
  let stringResult = JSON.stringify(records);
  console.log(stringResult);
  return stringResult;
}

// An interface representing a row of event data.
interface EventData {
  eventId: string
  date: number
  location: string
  capacity: number
}

Código de ejemplo: Validación de registros de orador

function main(workbook: ExcelScript.Workbook, keys: string): string {
  // Get the first table in the "Transactions" worksheet.
  let table = workbook.getWorksheet('Transactions').getTables()[0];

  // Clear the existing formatting in the table.
  let range = table.getRangeBetweenHeaderAndTotal();
  range.clear(ExcelScript.ClearApplyTo.formats);

  // Compare the data in the table to the keys passed into the script.
  let keysObject = JSON.parse(keys) as EventData[];
  let speakerSlotsRemaining = keysObject.map(value => value.capacity);
  let overallMatch = true;

  // Iterate over every row looking for differences from the other worksheet.
  let rows = range.getValues();
  for (let i = 0; i < rows.length; i++) {
    let row = rows[i];
    let [eventId, date, location, capacity] = row;
    let match = false;

    // Look at each key provided for a matching Event ID.
    for (let keyIndex = 0; keyIndex < keysObject.length; keyIndex++) {
      let event = keysObject[keyIndex];
      if (event.eventId === eventId) {
        match = true;
        speakerSlotsRemaining[keyIndex]--;
        // If there's a match on the event ID, look for things that don't match and highlight them.
        if (event.date !== date) {
          overallMatch = false;
          range.getCell(i, 1).getFormat()
            .getFill()
            .setColor("FFFF00");
        }
        if (event.location !== location) {
          overallMatch = false;
          range.getCell(i, 2).getFormat()
            .getFill()
            .setColor("FFFF00");
        }

        break;
      }
    }

    // If no matching Event ID is found, highlight the Event ID's cell.
    if (!match) {
      overallMatch = false;
      range.getCell(i, 0).getFormat()
        .getFill()
        .setColor("FFFF00");
    }
  }

  

  // Choose a message to send to the user.
  let returnString = "All the data is in the right order.";
  if (overallMatch === false) {
    returnString = "Mismatch found. Data requires your review.";
  } else if (speakerSlotsRemaining.find(remaining => remaining < 0)){
    returnString = "Event potentially overbooked. Please review."
  }

  console.log("Returning: " + returnString);
  return returnString;
}

// An interface representing a row of event data.
interface EventData {
  eventId: string
  date: number
  location: string
  capacity: number
}

Flujo de Power Automate: comprobación de incoherencias en los libros

Este flujo extrae la información del evento del primer libro y usa esos datos para validar el segundo libro.

Inicie sesión en Power Automate y cree un nuevo flujo de nube instantánea.
Elija Desencadenar manualmente un flujo y seleccione Crear.
En el generador de flujos, seleccione el + botón y Agregar una acción. Seleccione 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: event-data.xlsx (seleccionado con el selector de archivos)
- Script: Obtención de datos de eventos
Cambie el nombre de este paso. Seleccione el nombre actual "Ejecutar script" en el panel de tareas y cámbielo por "Obtener datos de eventos".
Agregue una segunda acción que use la acción Ejecutar script del conector de Excel Online (Empresa). Esta acción usa los valores devueltos del script Get event data (Obtener datos de evento ) como entrada para el script validate event data (Validar datos de eventos ). Use los siguientes valores para la acción.
- Ubicación: OneDrive para la Empresa
- Biblioteca de documentos: OneDrive
- Archivo: speaker-registration.xlsx (seleccionado con el selector de archivos)
- Script: Validación del registro del hablante
- keys: result (contenido dinámico de Get event data)
Cambie también el nombre de este paso. Seleccione el nombre actual "Ejecutar script 1" en el panel de tareas y cámbielo a "Validar el registro del hablante".
En este ejemplo se usa Outlook como cliente de correo electrónico. Para este ejemplo, agregue la acción Enviar y correo electrónico (V2) del conector de Outlook de Office 365. Puede usar cualquier conector de correo electrónico compatible con Power Automate. Esta acción usa los valores devueltos del script Validate speaker registration (Validar el script de registro del hablante ) como contenido del cuerpo del correo electrónico. Use los siguientes valores para la acción.
- Para: Su cuenta de correo electrónico de prueba (o correo electrónico personal)
- Asunto: Resultados de validación de eventos
- Cuerpo: resultado (contenido dinámico de Validación del registro del hablante)
Guarde el flujo. El diseñador de flujo debe tener un aspecto similar al de la imagen siguiente.
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.
Debería recibir un correo electrónico que dice "Error de coincidencia encontrado. Los datos requieren su revisión". Esto indica que hay diferencias entre las filas de speaker-registrations.xlsx y las filas de event-data.xlsx. Abra speaker-registrations.xlsx para ver varias celdas resaltadas donde hay posibles problemas con las listas de registro del hablante.

Share via