Escrever um grande conjuntos de dados

A Range.setValues() API coloca os dados num intervalo. Esta API tem limitações consoante vários fatores, como o tamanho dos dados e as definições de rede. Isto significa que, se tentar escrever uma grande quantidade de informações num livro como uma única operação, terá de escrever os dados em lotes mais pequenos para atualizar de forma fiável um grande intervalo.

A primeira parte do exemplo mostra como escrever um grande conjunto de dados no Excel. A segunda parte expande o exemplo para fazer parte de um fluxo do Power Automate. Isto é necessário se o script demorar mais tempo a ser executado do que o tempo limite de ação do Power Automate.

Para obter noções básicas de desempenho nos Scripts do Office, leia Melhorar o desempenho dos seus Scripts do Office.

Exemplo 1: Escrever um grande conjunto de dados em lotes

Este script escreve linhas de um intervalo em partes mais pequenas. Seleciona 1000 células para escrever de cada vez. Execute o script numa folha de cálculo em branco para ver os lotes de atualização em ação. O resultado da consola fornece mais informações sobre o que está a acontecer.

function main(workbook: ExcelScript.Workbook) {
  const SAMPLE_ROWS = 100000;
  const CELLS_IN_BATCH = 10000;

  // Get the current worksheet.
  const sheet = workbook.getActiveWorksheet();

  console.log(`Generating data...`)
  let data: (string | number | boolean)[][] = [];
  // Generate six columns of random data per row. 
  for (let i = 0; i < SAMPLE_ROWS; i++) {
    data.push([i, ...[getRandomString(5), getRandomString(20), getRandomString(10), Math.random()], "Sample data"]);
  }

  console.log(`Calling update range function...`);
  const updated = updateRangeInBatches(sheet.getRange("B2"), data, CELLS_IN_BATCH);
  if (!updated) {
    console.log(`Update did not take place or complete. Check and run again.`);
  }
}

function updateRangeInBatches(
  startCell: ExcelScript.Range,
  values: (string | boolean | number)[][],
  cellsInBatch: number
): boolean {

  const startTime = new Date().getTime();
  console.log(`Cells per batch setting: ${cellsInBatch}`);

  // Determine the total number of cells to write.
  const totalCells = values.length * values[0].length;
  console.log(`Total cells to update in the target range: ${totalCells}`);
  if (totalCells <= cellsInBatch) {
    console.log(`No need to batch -- updating directly`);
    updateTargetRange(startCell, values);
    return true;
  }

  // Determine how many rows to write at once.
  const rowsPerBatch = Math.floor(cellsInBatch / values[0].length);
  console.log("Rows per batch: " + rowsPerBatch);
  let rowCount = 0;
  let totalRowsUpdated = 0;
  let batchCount = 0;

  // Write each batch of rows.
  for (let i = 0; i < values.length; i++) {
    rowCount++;
    if (rowCount === rowsPerBatch) {
      batchCount++;
      console.log(`Calling update next batch function. Batch#: ${batchCount}`);
      updateNextBatch(startCell, values, rowsPerBatch, totalRowsUpdated);

      // Write a completion percentage to help the user understand the progress.
      rowCount = 0;
      totalRowsUpdated += rowsPerBatch;
      console.log(`${((totalRowsUpdated / values.length) * 100).toFixed(1)}% Done`);
    }
  }
  
  console.log(`Updating remaining rows -- last batch: ${rowCount}`)
  if (rowCount > 0) {
    updateNextBatch(startCell, values, rowCount, totalRowsUpdated);
  }

  let endTime = new Date().getTime();
  console.log(`Completed ${totalCells} cells update. It took: ${((endTime - startTime) / 1000).toFixed(6)} seconds to complete. ${((((endTime  - startTime) / 1000)) / cellsInBatch).toFixed(8)} seconds per ${cellsInBatch} cells-batch.`);

  return true;
}

/**
 * A helper function that computes the target range and updates. 
 */
function updateNextBatch(
  startingCell: ExcelScript.Range,
  data: (string | boolean | number)[][],
  rowsPerBatch: number,
  totalRowsUpdated: number
) {
  const newStartCell = startingCell.getOffsetRange(totalRowsUpdated, 0);
  const targetRange = newStartCell.getResizedRange(rowsPerBatch - 1, data[0].length - 1);
  console.log(`Updating batch at range ${targetRange.getAddress()}`);
  const dataToUpdate = data.slice(totalRowsUpdated, totalRowsUpdated + rowsPerBatch);
  try {
    targetRange.setValues(dataToUpdate);
  } catch (e) {
    throw `Error while updating the batch range: ${JSON.stringify(e)}`;
  }
  return;
}

/**
 * A helper function that computes the target range given the target range's starting cell
 * and selected range and updates the values.
 */
function updateTargetRange(
  targetCell: ExcelScript.Range,
  values: (string | boolean | number)[][]
) {
  const targetRange = targetCell.getResizedRange(values.length - 1, values[0].length - 1);
  console.log(`Updating the range: ${targetRange.getAddress()}`);
  try {
    targetRange.setValues(values);
  } catch (e) {
    throw `Error while updating the whole range: ${JSON.stringify(e)}`;
  }
  return;
}

// Credit: https://www.codegrepper.com/code-examples/javascript/random+text+generator+javascript
function getRandomString(length: number): string {
  var randomChars = 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789';
  var result = '';
  for (var i = 0; i < length; i++) {
    result += randomChars.charAt(Math.floor(Math.random() * randomChars.length));
  }
  return result;
}

Vídeo de formação: Escrever um conjunto de dados grande

Veja Sudhi Ramamurthy a percorrer este exemplo no YouTube.

Exemplo 2: escrever dados em lotes a partir de um fluxo do Power Automate

Para este exemplo, terá de concluir os seguintes passos.

1. Crie um livro no OneDrive com o nome SampleData.xlsx.
2. Crie um segundo livro no OneDrive com o nome TargetWorkbook.xlsx.
3. Abra SampleData.xlsx com o Excel.
4. Adicionar dados de exemplo. Pode utilizar o script da secção Escrever um grande conjunto de dados em lotes para gerar estes dados.
5. Crie e guarde ambos os scripts seguintes. 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.
6. Siga os passos em Fluxo do Power Automate: Ler e escrever dados num ciclo para criar o fluxo.

Código de exemplo: ler linhas selecionadas

function main(
  workbook: ExcelScript.Workbook, 
  startRow: number, 
  batchSize: number
): string[][] {
  // This script only reads the first worksheet in the workbook.
  const sheet = workbook.getWorksheets()[0];

  // Get the boundaries of the range.
  // Note that we're assuming usedRange is too big to read or write as a single range.
  const usedRange = sheet.getUsedRange();
  const lastColumnIndex = usedRange.getLastColumn().getColumnIndex();
  const lastRowindex = usedRange.getLastRow().getRowIndex();

  // If we're starting past the last row, exit the script.
  if (startRow > lastRowindex) {
      return [[]];
  }

  // Get the next batch or the rest of the rows, whichever is smaller.
  const rowCountToRead = Math.min(batchSize, (lastRowindex - startRow + 1));
  const rangeToRead = sheet.getRangeByIndexes(startRow, 0, rowCountToRead, lastColumnIndex + 1);
  return rangeToRead.getValues() as string[][];
}

Código de exemplo: escrever dados na localização da linha

function main(
  workbook: ExcelScript.Workbook, 
  data: string[][], 
  currentRow: number, 
  batchSize: number
): boolean {
  // Get the first worksheet.
  const sheet = workbook.getWorksheets()[0];

  // Set the given data.
  if (data && data.length > 0) {
    sheet.getRangeByIndexes(currentRow, 0, data.length, data[0].length).setValues(data);
  }

  // If the script wrote less data than the batch size, signal the end of the flow.
  return batchSize > data.length;
}

Fluxo do Power Automate: ler e escrever dados num ciclo

1. Inicie sessão no Power Automate e crie um novo fluxo de cloud instantânea.

2. Selecione Acionar manualmente um fluxo e selecione Criar.

3. Crie uma variável para controlar a linha atual que está a ser lida e escrita. No construtor de fluxos, selecione o + botão e Adicionar uma ação. Selecione a ação Inicializar variável e atribua-lhe os seguintes valores.

   • Nome: currentRow
   • Tipo: Número inteiro
   • Valor: 0

   

4. Adicione uma ação para definir o número de linhas a ler num único lote. Consoante o número de colunas, poderá ter de ser menor para evitar os limites de transferência de dados. Crie uma nova ação inicializar variável com os seguintes valores.

   • Nome: batchSize
   • Tipo: Número inteiro
   • Valor: 10000

   

5. Adicione um controlo Do until . O fluxo irá ler segmentos dos dados até que todos sejam copiados. Irá utilizar o valor de -1 para indicar que o fim dos dados foi atingido. Dê ao controlo os seguintes valores.

   • Escolha um valor: currentRow (conteúdo dinâmico)
   • é igual a (na lista pendente)
   • Escolha um valor: -1

   

6. Os passos restantes são adicionados dentro do controlo Do . Em seguida, chame o script para ler os dados. Adicione a ação Executar script do conector do Excel Online (Empresas). Mude o nome para Ler dados. Utilize os seguintes valores para a ação.

   • Localização: OneDrive for Business
   • Biblioteca de Documentos: OneDrive
   • Ficheiro: "SampleData.xlsx" (conforme selecionado pelo seletor de ficheiros)
   • Script: Ler linhas selecionadas
   • startRow: currentRow (conteúdo dinâmico)
   • batchSize: batchSize (conteúdo dinâmico)

   

7. Chame o script para escrever os dados. Adicione uma segunda ação Executar script . Mude o nome para Escrever dados. Utilize os seguintes valores para a ação.

   • Localização: OneDrive for Business
   • Biblioteca de Documentos: OneDrive
   • Ficheiro: "TargetWorkbook.xlsx" (conforme selecionado pelo seletor de ficheiros)
   • Script: escrever dados na localização da linha
   • dados: resultado (conteúdo dinâmico de Dados de leitura)
     • Prima Primeiro Mudar a entrada para toda a matriz .
   • startRow: currentRow (conteúdo dinâmico)
   • batchSize: batchSize (conteúdo dinâmico)

   

8. Atualize a linha atual para refletir que um lote de dados foi lido e escrito. Adicione uma ação de variável Incremento com os seguintes valores.

   • Nome: currentRow
   • Valor: batchSize (conteúdo dinâmico)

   

9. Adicione um controlo Condição para marcar se os scripts tiverem lido tudo. O script "Escrever dados na localização da linha" devolve verdadeiro quando tiver escrito menos linhas do que o tamanho do lote permite. Isto significa que está no final do conjunto de dados. Crie a ação de controlo Condição com os seguintes valores.

   • Escolher um valor: resultado (conteúdo dinâmico de Dados de escrita)
   • é igual a (na lista pendente)
   • Escolher um valor: verdadeiro (expressão)

   

10. Na secção Verdadeiro do controlo Condição , defina a variável currentRow como -1. Adicione uma ação Definir variável com os seguintes valores.

    • Nome: currentRow
    • Valor: -1

    

11. Guarde o fluxo. O estruturador de fluxo deve ter um aspeto semelhante à imagem seguinte.

    

12. 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.

13. O ficheiro "TargetWorkbook.xlsx" deve agora ter os dados de "SampleData.xlsx".