Escrever um grande conjuntos de dados

A Range.setValues() API coloca dados em um intervalo. Essa API tem limitações dependendo de vários fatores, como tamanho dos dados e configurações de rede. Isso significa que, se você tentar gravar uma grande quantidade 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 intervalo grande.

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

Observação

Execute esses exemplos diretamente no Editor de Código de Scripts do Office. Para abrir o Editor de códigos, acesse Automatizar> a criação denovo script>no Editor de códigos. Substitua o código padrão pelo código de exemplo que você deseja executar e selecione Executar.

Exemplo 1: Gravar um conjunto de dados grande em lotes

Este script grava linhas de um intervalo em partes menores. Ele seleciona 1.000 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 oferece mais informações sobre o que está acontecendo.

Observação

Você pode alterar o número de linhas totais que estão sendo gravadas 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 conjunto de dados grande

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.xlsxcom o Excel.
Adicionar dados de exemplo. Você pode usar o script da seção Gravar um conjunto de dados grande em lotes para gerar esses dados.
Crie e salve os dois scripts a seguir. Use Automatizar>a criação de novo script>no Editor de código para colar o código e salvar os scripts com os nomes sugeridos.
Siga as etapas em Fluxo do Power Automa: Leia e grave 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;
}

Power Automate fluxo: ler e gravar dados em um loop

Entre no Power Automate e crie um novo fluxo de nuvem instantâneo.
Escolha Disparar manualmente um fluxo e selecione Criar.
Crie uma variável para acompanhar a linha atual que está sendo lida e gravada. No construtor de fluxo, selecione o + botão e Adicionar uma ação. Selecione a ação Inicializar variável e dê a ela os seguintes valores.
- 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, pode ser necessário que ele seja menor para evitar os limites de transferência de dados. Faça uma nova ação Inicializar variável com os seguintes valores.
- Nome: batchSize
- Tipo: Inteiro
- Valor: 10000
Adicione um controle Fazer até . O fluxo lerá partes dos dados até que todos tenham sido copiados. Você usará o valor de -1 para indicar que o fim dos dados foi alcançado. Dê ao controle os seguintes valores.
- Escolha um valor: currentRow (conteúdo dinâmico)
- é igual a (na lista suspensa)
- Escolha um valor: -1
As etapas restantes são adicionadas dentro do controle Do . 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-a 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
- data: result (conteúdo dinâmico de Read data)
  - Pressione Alternar entrada para toda a matriz 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 Incrementar variável com os seguintes valores.
- Nome: currentRow
- Valor: batchSize (conteúdo dinâmico)
Adicione um controle de condição para marcar se os scripts leram tudo. O script "Gravar dados no local da linha" retorna verdadeiro quando 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 Controle de condição com os valores a seguir.
- Escolha um valor: resultado (conteúdo dinâmico de Gravar dados)
- é igual a (na lista suspensa)
- Escolha um valor: verdadeiro (expressão)
Na seção True do controle Condition , defina a variável currentRow como -1. Adicione uma ação Definir variável com os seguintes valores.
- 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".

Comentários

Esta página foi útil?

Last updated on 2026-08-24