Arquivos do Excel de referência cruzada com o Power Automate

Artigo
12/06/2023

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

Esse 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 do alto-falante em outra. Para garantir que as duas pastas de trabalho sejam mantidas em sincronização, use um fluxo com scripts do Office para destacar possíveis problemas.

Exemplo de arquivos do Excel

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

Adicione os scripts a seguir para experimentar o exemplo por conta própria! No Excel, use Automatizar>Novo Script 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 alto-falante

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 do evento da primeira pasta de trabalho e usa esses dados para validar a segunda pasta de trabalho.

Entre no Power Automate e crie um novo fluxo de nuvem instantânea.
Escolha Disparar manualmente um fluxo e selecione Criar.
No construtor de fluxos, selecione o + botão e Adicione uma ação. Selecione a ação executar script do conector do Excel Online (Business). Use os valores a seguir para a ação.
- Localização: OneDrive for Business
- Biblioteca de Documentos: OneDrive
- Arquivo: event-data.xlsx (selecionado com o seletor de arquivo)
- Script: Obter dados de evento
Renomeie esta etapa. Selecione o nome atual "Executar script" no painel de tarefas e altere-o para "Obter dados de evento".
Adicione uma segunda ação que usa 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 de dados de evento Validar . 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 arquivo)
- Script: Validar o registro de alto-falante
- chaves: resultado (conteúdo dinâmico de Obter dados de evento)
Renomeie esta etapa também. Selecione o nome atual "Executar script 1" no painel de tarefas e altere-o para "Validar registro de alto-falante".
Este exemplo usa o Outlook como o cliente de email. Para este exemplo, adicione a Office 365 a ação Enviar e enviar e enviar email (V2) do conector do Outlook. Você pode usar qualquer conector de email compatível com o Power Automate. Essa ação usa os valores retornados do script de registro do alto-falante Validar 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 da validação de eventos
- Corpo: resultado (conteúdo dinâmico do registro de alto-falante validado)
Salve o fluxo. O designer de fluxo deve se parecer com a imagem a seguir.
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.
Você deve receber um email dizendo "Incompatibilidade encontrada. Os dados exigem sua revisão.". Isso indica que há diferenças entre linhas em speaker-registrations.xlsx e linhas no event-data.xlsx. Abra speaker-registrations.xlsx para ver várias células realçadas em que há possíveis problemas com as listagens de registro de alto-falante.

Partilhar via