Combinar planilhas em uma única pasta de trabalho

Artigo
06/19/2024

Este exemplo mostra como extrair dados de vários livros para um único livro centralizado. Utiliza dois scripts: um para obter informações de um livro e outro para criar novas folhas de cálculo com essas informações. Combina os scripts num fluxo do Power Automate que atua numa pasta inteira do OneDrive.

Importante

Este exemplo apenas copia os valores dos outros livros. Não preserva formatação, gráficos, tabelas ou outros objetos.

Solução

Crie um novo ficheiro do Excel no seu OneDrive. O nome de ficheiro "Combination.xlsx" é utilizado neste exemplo.
Crie e guarde os dois scripts deste exemplo.
Crie uma pasta no seu OneDrive e adicione um ou mais livros com dados à mesma. O nome da pasta "output" é utilizado neste exemplo.
Crie um fluxo (conforme descrito na secção fluxo do Power Automate deste artigo) para executar estes passos:
1. Liste todos os ficheiros da pasta "output".
2. Utilize o script de dados Devolver folha de cálculo para obter os dados de cada folha de cálculo em cada um dos livros.
3. Utilize o script Adicionar folhas de cálculo para criar uma nova folha de cálculo no livro "Combination.xlsx" para cada folha de cálculo em todos os outros ficheiros.

Código de exemplo: devolver dados da folha 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 exemplo: Adicionar folhas 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[][];
}

Fluxo do Power Automate: Combinar folhas de cálculo num único livro

Inicie sessão no Power Automate e crie um novo fluxo de cloud instantânea.
Selecione Acionar manualmente um fluxo e selecione Criar.
Obtenha todos os livros que pretende combinar a partir da respetiva pasta. Adicione uma ação e escolha os ficheiros lista do conector do OneDrive para Empresas na ação de pasta. Para o campo Pasta , utilize o seletor de ficheiros para selecionar a pasta "output".
Adicione uma ação para executar o script de dados Devolver folha de cálculo para obter todos os dados de cada um dos livros. Selecione a ação Executar script do conector do Excel Online (Empresas). Utilize os seguintes valores para a ação. Tenha em atenção que quando adicionar o ID do ficheiro, o Power Automate irá encapsular a ação num controlo Para cada , para que a ação seja executada em todos os ficheiros.
- Localização: OneDrive for Business
- Biblioteca de Documentos: OneDrive
- Ficheiro: ID (conteúdo dinâmico de Listar ficheiros na pasta)
- Script: devolver dados da folha de cálculo
Adicione uma ação para executar o script Adicionar folhas de cálculo no novo ficheiro do Excel que criou. Isto irá adicionar os dados de todos os outros livros. Após a ação executar script anterior e dentro do controlo Para cada controlo, adicione uma ação que utiliza 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: "Combination.xlsx" (o ficheiro, conforme selecionado pelo seletor de ficheiros)
- Script: Adicionar folhas de cálculo
- workbookName: Nome (conteúdo dinâmico de Listar ficheiros na pasta)
- worksheetInformation (veja a nota a seguir à imagem seguinte): resultado (conteúdo dinâmico de Executar script)
Observação

Selecione o botão Mudar para introduzir toda a matriz para adicionar o objeto de matriz diretamente, em vez de itens individuais para a matriz. Faça-o antes de introduzir o resultado.
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.
O ficheiro "Combination.xlsx" deverá agora ter novas folhas de cálculo.

Solução de problemas

Já existe um recurso com o mesmo nome ou identificador: este erro indica provavelmente que o livro "Combination.xlsx" já tem uma folha de cálculo com o mesmo nome. Isto acontecerá se executar o fluxo várias vezes com os mesmos livros. Crie sempre um novo livro para armazenar os dados combinados ou utilizar nomes de ficheiro diferentes na pasta "output".
O argumento é inválido ou está em falta ou tem um formato incorreto: este erro pode significar que o nome da folha de cálculo gerada não cumpre os requisitos do Excel. Isto deve-se provavelmente ao facto de o nome ser demasiado longo. Se os nomes das folhas de cálculo tiverem mais de 30 carateres, substitua o código em "Adicionar folhas de cálculo" que chama addWorksheet por algo que reduza a cadeia. Uma vez que o próprio nome do livro pode ser demasiado longo, adicione um número de incremento ao final do nome da folha de cálculo. Declare este número fora do forEach ciclo.
```
let worksheetNumber = 1;
// Add each new worksheet.
worksheetInformation.forEach((value) => {
    let worksheetName = `${workbookName}.${value.name}`;
    let sheet = workbook.addWorksheet(`${worksheetName.substr(0,30)}${worksheetNumber++}`);
```
Além disso, se os nomes dos livros tiverem mais de 30 carateres, terá de os encurtar no fluxo. Primeiro, tem de criar uma variável no fluxo para controlar a contagem de livros. Isto evitará que nomes abreviados idênticos sejam transmitidos para o script. Adicione uma ação de variável Initialize antes do fluxo (do Tipo "Número Inteiro") e uma ação de variável Incremento entre as duas ações Executar script . Em seguida, em vez de utilizar o Nome como o workbookName em "Executar script 1", utilize a expressão substring(items('Apply_to_each')?['Name'],0,min(length(items('Apply_to_each')?['Name']),20)) e o conteúdo dinâmico da variável. Isto reduz os nomes dos livros para 20 carateres e acrescenta o número do livro atual à cadeia que está a ser transmitida ao script.

Observação

Em vez de tornar o fluxo e o script mais complicados, poderá ser mais fácil garantir que os nomes dos ficheiros e das folhas de cálculo são suficientemente curtos.

Compartilhar via