Referência cruzada de arquivos do Excel com o Power Automate

Esta solução mostra como comparar dados em dois arquivos do Excel para encontrar discrepâncias. Ele usa Scripts do Office para analisar dados e o Power Automate para se comunicar entre as pastas de trabalho.

Este exemplo passa dados entre pastas de trabalho usando objetos JSON . Para obter mais informações sobre como trabalhar com JSON, leia Usar JSON para passar dados de e para Scripts do Office.

Cenário de exemplo

Você é um coordenador de eventos que está agendando palestrantes para as próximas conferências. Você mantém os dados do evento em uma planilha e os registros dos palestrantes em outra. Para garantir que as duas pastas de trabalho sejam mantidas em sincronia, use um fluxo com Scripts do Office para realçar possíveis problemas.

Arquivos Excel de exemplo

Baixe os arquivos a seguir para obter pastas de trabalho prontas para uso para o exemplo.

  1. event-data.xlsx
  2. speaker-registrations.xlsx

Adicione os seguintes scripts para experimentar o exemplo por conta própria! No Excel, use Automatizar>a Criação de Novo Script>no Editor de Códigos para colar o código e salvar os scripts com os nomes sugeridos.

Código de exemplo: Obter dados de evento

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 exemplo: validar registros de locutor

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
}

Fluxo do Power Automate: verifique se há inconsistências nas pastas de trabalho

Esse fluxo extrai as informações de evento da primeira pasta de trabalho e usa esses dados para validar a segunda pasta de trabalho.

  1. Entre no Power Automate e crie um novo fluxo de nuvem instantâneo.

  2. Escolha Disparar manualmente um fluxo e selecione Criar.

  3. No construtor de fluxo, selecione o + botão e Adicionar uma ação. Selecione a ação Executar script do conector do Excel Online (Business). Use os valores a seguir para a ação.

  4. Renomeie esta etapa. Selecione o nome atual "Executar script" no painel de tarefas e altere-o para "Obter dados do evento". O conector do Excel Online (Business) concluído para o primeiro script no Power Automate.

  5. Adicione uma segunda ação que use a ação Executar script do conector do Excel Online (Business). Essa ação usa os valores retornados do script Obter dados de evento como entrada para o script Validar dados de evento . Use os valores a seguir para a ação.

    • Localização: OneDrive for Business
    • Biblioteca de Documentos: OneDrive
    • Arquivo: speaker-registration.xlsx (selecionado com o seletor de arquivos)
    • Script: validar o registro do locutor
    • chaves: resultado (conteúdo dinâmico de Obter dados de evento)

  6. Renomeie esta etapa também. Selecione o nome atual "Executar script 1" no painel de tarefas e altere-o para "Validar registro do locutor". O conector do Excel Online (Business) concluído para o segundo script no Power Automate.

  7. Este exemplo usa o Outlook como cliente de email. Para este exemplo, adicione a ação Enviar e enviar email (V2) do conector do Outlook do Office 365 do Microsoft 365. Você pode usar qualquer conector de email compatível com o Power Automate. Essa ação usa os valores retornados do script Validar registro do locutor como o conteúdo do corpo do email. Use os valores a seguir para a ação.

    • Para: Sua conta de email de teste (ou email pessoal)
    • Assunto: Resultados de validação de evento
    • Corpo: resultado (conteúdo dinâmico de Validar registro de palestrante)

    O conector do Outlook do Office 365 concluído no Power Automate.

  8. Salve o fluxo. O designer de fluxo deve se parecer com a imagem a seguir.

    Um diagrama do fluxo concluído que mostra quatro etapas.

  9. Use o botão Testar na página do editor de fluxo ou execute o fluxo por meio da guia Meus fluxos . Certifique-se de permitir o acesso quando solicitado.

  10. Você deve receber um email dizendo "Incompatibilidade encontrada. Os dados exigem sua revisão." Isso indica que há diferenças entre as linhas em speaker-registrations.xlsx e linhas em event-data.xlsx. Abra speaker-registrations.xlsx para ver várias células realçadas onde há possíveis problemas com as listagens de registro do alto-falante.

  • Last updated on