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

Esta solução mostra como comparar dados em dois ficheiros do Excel para encontrar discrepâncias. Utiliza Scripts do Office para analisar dados e o Power Automate para comunicar entre os livros.

Este exemplo transmite dados entre livros com objetos JSON . Para obter mais informações sobre como trabalhar com JSON, leia Utilizar JSON para transmitir dados de e para Scripts do Office.

Cenário de exemplo

É um coordenador de eventos que está a agendar oradores para as próximas conferências. Mantém os dados do evento numa folha de cálculo e os registos de orador noutra. Para garantir que os dois livros são mantidos sincronizados, utilize um fluxo com Scripts do Office para realçar eventuais problemas.

Ficheiros do Excel de exemplo

Transfira os seguintes ficheiros para obter livros prontos a utilizar para o exemplo.

Adicione os seguintes scripts para experimentar o exemplo! No Excel, utilize Automatizar a Criação> deNovo Script>no Editor de Código para colar o código e guardar os scripts com os nomes sugeridos.

Código de exemplo: Obter dados 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 exemplo: Validar registos 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
}

Fluxo do Power Automate: verificar a existência de inconsistências nos livros

Este fluxo extrai as informações do evento do primeiro livro e utiliza esses dados para validar o segundo livro.

Inicie sessão no Power Automate e crie um novo fluxo de cloud instantânea.
Selecione Acionar manualmente um fluxo e selecione Criar.
No construtor de fluxos, selecione o + botão e Adicionar uma ação. Selecione a ação Executar script do conector do Excel Online (Empresas). Utilize os seguintes valores para a ação.
- Localização: OneDrive for Business
- Biblioteca de Documentos: OneDrive
- Ficheiro: event-data.xlsx (selecionado com o selecionador de ficheiros)
- Script: Obter dados de eventos
Mude o nome deste passo. Selecione o nome atual "Executar script" no painel de tarefas e altere-o para "Obter dados de eventos".
Adicione uma segunda ação que utiliza a ação Executar script do conector do Excel Online (Business). Esta ação utiliza os valores devolvidos do script Obter dados do evento como entrada para o script Validar dados de eventos . Utilize os seguintes valores para a ação.
- Localização: OneDrive for Business
- Biblioteca de Documentos: OneDrive
- Ficheiro: speaker-registration.xlsx (selecionado com o selecionador de ficheiros)
- Script: Validar o registo de orador
- chaves: resultado (conteúdo dinâmico de Obter dados de eventos)
Mude também o nome deste passo. Selecione o nome atual "Executar script 1" no painel de tarefas e altere-o para "Validar o registo de orador".
Este exemplo utiliza o Outlook como cliente de e-mail. Para este exemplo, adicione o Office 365 ação Enviar e e-mail (V2) do conector do Outlook. Pode utilizar qualquer conector de e-mail suportado pelo Power Automate. Esta ação utiliza os valores devolvidos do script Validar registo de orador como o conteúdo do corpo do e-mail. Utilize os seguintes valores para a ação.
- Para: a sua conta de e-mail de teste (ou e-mail pessoal)
- Assunto: Resultados da validação de eventos
- Corpo: resultado (conteúdo dinâmico de Validar registo de orador)
Guarde o fluxo. O estruturador de fluxo deve ter um aspeto semelhante à imagem seguinte.
Utilize o botão Testar na página do editor de fluxos ou execute o fluxo através do separador Os meus fluxos . Certifique-se de que permite o acesso quando lhe for pedido.
Deverá receber um e-mail a dizer "Erro de correspondência encontrado. Os dados requerem a sua revisão." Isto indica que existem 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 onde existem potenciais problemas com as listagens de registo de orador.