Escrever um grande conjuntos de dados

Artigo
02/08/2024

A Range.setValues() API coloca os dados em um intervalo. Essa API tem limitações dependendo de vários fatores, como tamanho de dados e configurações de rede. Isso significa que, se você tentar gravar uma quantidade maciça de informações em uma pasta de trabalho como uma única operação, precisará gravar os dados em lotes menores para atualizar de forma confiá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. Isso será necessário se o script demorar mais tempo para ser executado do que o tempo limite de ação do Power Automate.

Para noções básicas de desempenho em Scripts do Office, leia Melhorar o desempenho dos scripts do Office.

Exemplo 1: gravar um conjunto de dados grande em lotes

Este script grava linhas de um intervalo em partes menores. Ele seleciona 1000 células para gravar por vez. Execute o script em uma planilha em branco para ver os lotes de atualização em ação. A saída do console fornece mais informações sobre o que está acontecendo.

Observação

Você pode alterar o número de linhas totais que estão sendo escritas alterando o valor de SAMPLE_ROWS. Você pode alterar o número de células a serem gravadas como uma única ação alterando o valor de CELLS_IN_BATCH.

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 treinamento: gravar um grande conjunto de dados

Assista Sudhi Ramamurthy percorrer este exemplo no YouTube.

Exemplo 2: gravar dados em lotes de um fluxo do Power Automate

Para este exemplo, você precisará concluir as etapas a seguir.

Crie uma pasta de trabalho no OneDrive chamada SampleData.xlsx.
Crie uma segunda pasta de trabalho no OneDrive chamada TargetWorkbook.xlsx.
Abra SampleData.xlsx com o Excel.
Adicione dados de exemplo. Você pode usar o script na seção Gravar um conjunto de dados grande em lotes para gerar esses dados.
Crie e salve os dois scripts a seguir. Use Automatizar>Novo Script para colar o código e salvar os scripts com os nomes sugeridos.
Siga as etapas em Fluxo do Power Automate: ler e gravar dados em um loop 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: gravar dados no local 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 gravar dados em um loop

Entre no Power Automate e crie um novo fluxo de nuvem instantânea.
Escolha Disparar manualmente um fluxo e selecione Criar.
Crie uma variável para acompanhar a linha atual que está sendo lida e escrita. No construtor de fluxos, selecione o + botão e Adicione uma ação. Selecione a ação Inicializar variável e dê-lhe os valores a seguir.
- Nome: currentRow
- Tipo: Inteiro
- Valor: 0
Adicione uma ação para definir o número de linhas a serem lidas em um único lote. Dependendo do número de colunas, isso pode precisar ser menor para evitar os limites de transferência de dados. Faça uma nova ação de variável Initialize com os valores a seguir.
- Nome: batchSize
- Tipo: Inteiro
- Valor: 10000
Adicione um controle Fazer até o controle. O fluxo lerá partes dos dados até que todos eles sejam copiados. Você usará o valor de -1 para indicar que o fim dos dados foi atingido. Dê ao controle os valores a seguir.
- Escolha um valor: currentRow (conteúdo dinâmico)
- é igual a (da lista suspensa)
- Escolha um valor: -1
As etapas restantes são adicionadas dentro do controle Fazer . Em seguida, chame o script para ler os dados. Adicione a ação executar script do conector do Excel Online (Business). Renomeie-o para Ler dados. Use os valores a seguir para a ação.
- Localização: OneDrive for Business
- Biblioteca de Documentos: OneDrive
- Arquivo: "SampleData.xlsx" (conforme selecionado pelo seletor de arquivos)
- Script: ler linhas selecionadas
- startRow: currentRow (conteúdo dinâmico)
- batchSize: batchSize (conteúdo dinâmico)
Chame o script para gravar os dados. Adicione uma segunda ação executar script . Renomeie-o para Gravar dados. Use os valores a seguir para a ação.
- Localização: OneDrive for Business
- Biblioteca de Documentos: OneDrive
- Arquivo: "TargetWorkbook.xlsx" (conforme selecionado pelo seletor de arquivos)
- Script: gravar dados no local da linha
- dados: resultado (conteúdo dinâmico dos dados de leitura)
  - Pressione a entrada do Switch para a matriz inteira primeiro.
- startRow: currentRow (conteúdo dinâmico)
- batchSize: batchSize (conteúdo dinâmico)
Atualize a linha atual para refletir que um lote de dados foi lido e gravado. Adicione uma ação de variável Increment com os valores a seguir.
- Nome: currentRow
- Valor: batchSize (conteúdo dinâmico)
Adicione um controle Condição ao marcar se os scripts tiverem lido tudo. O script "Gravar dados no local da linha" retorna true quando ele grava menos linhas do que o tamanho do lote permite. Isso significa que ele está no final do conjunto de dados. Crie a ação de controle condição com os valores a seguir.
- Escolha um valor: resultado (conteúdo dinâmico dos dados de gravação)
- é igual a (da lista suspensa)
- Escolha um valor: true (expressão)
Na seção True do controle Condição , defina a variável currentRow como -1. Adicione uma ação definir variável com os valores a seguir.
- Nome: currentRow
- Valor: -1
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.
O arquivo "TargetWorkbook.xlsx" agora deve ter os dados de "SampleData.xlsx".