Escribir un conjunto de datos grande

Artículo
02/08/2024

La Range.setValues() API coloca los datos en un intervalo. Esta API tiene limitaciones en función de varios factores, como el tamaño de los datos y la configuración de red. Esto significa que si intenta escribir una gran cantidad de información en un libro como una sola operación, tendrá que escribir los datos en lotes más pequeños para actualizar de forma confiable un intervalo grande.

En la primera parte del ejemplo se muestra cómo escribir un conjunto de datos grande en Excel. La segunda parte expande el ejemplo para formar parte de un flujo de Power Automate. Esto es necesario si el script tarda más en ejecutarse que el tiempo de espera de la acción de Power Automate.

Para conocer los conceptos básicos de rendimiento en los scripts de Office, lea Mejora del rendimiento de los scripts de Office.

Ejemplo 1: Escritura de un conjunto de datos grande en lotes

Este script escribe filas de un intervalo en partes más pequeñas. Selecciona 1000 celdas para escribir a la vez. Ejecute el script en una hoja de cálculo en blanco para ver los lotes de actualización en acción. La salida de la consola proporciona más información sobre lo que está sucediendo.

Nota:

Puede cambiar el número total de filas que se escriben cambiando el valor de SAMPLE_ROWS. Puede cambiar el número de celdas que se van a escribir como una sola acción cambiando el 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 entrenamiento: Escritura de un conjunto de datos grande

Vea cómo Sudhi Ramamurthy recorre este ejemplo en YouTube.

Ejemplo 2: Escritura de datos en lotes desde un flujo de Power Automate

Para este ejemplo, deberá completar los pasos siguientes.

Cree un libro en OneDrive denominado SampleData.xlsx.
Cree un segundo libro en OneDrive denominado TargetWorkbook.xlsx.
Abra SampleData.xlsx con Excel.
Agregar datos de ejemplo. Puede usar el script de la sección Escribir un conjunto de datos grande en lotes para generar estos datos.
Cree y guarde los dos scripts siguientes. Use Automatizar>nuevo script para pegar el código y guardar los scripts con los nombres sugeridos.
Siga los pasos descritos en Flujo de Power Automate: Leer y escribir datos en un bucle para crear el flujo.

Código de ejemplo: Leer las filas seleccionadas

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 ejemplo: Escritura de datos en la ubicación de fila

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;
}

Flujo de Power Automate: lectura y escritura de datos en un bucle

Inicie sesión en Power Automate y cree un nuevo flujo de nube instantánea.
Elija Desencadenar manualmente un flujo y seleccione Crear.
Cree una variable para realizar un seguimiento de la fila actual que se está leyendo y escribiendo. En el generador de flujos, seleccione el + botón y Agregar una acción. Seleccione la acción Inicializar variable y asígnele los siguientes valores.
- Nombre: currentRow
- Tipo: Integer
- Valor: 0
Agregue una acción para establecer el número de filas que se van a leer en un único lote. En función del número de columnas, puede que sea necesario que sea menor para evitar los límites de transferencia de datos. Realice una nueva acción Inicializar variable con los siguientes valores.
- Nombre: batchSize
- Tipo: Integer
- Valor: 10000
Agregue un control Do until . El flujo leerá fragmentos de los datos hasta que se hayan copiado todos. Usará el valor -1 para indicar que se ha alcanzado el final de los datos. Asigne al control los siguientes valores.
- Elija un valor: currentRow (contenido dinámico)
- es igual a (de la lista desplegable)
- Elegir un valor: -1
Los pasos restantes se agregan dentro del control Do . A continuación, llame al script para leer los datos. Agregue la acción Ejecutar script del conector de Excel Online (Empresa). Cámbiele el nombre a Leer datos. Use los siguientes valores para la acción.
- Ubicación: OneDrive para la Empresa
- Biblioteca de documentos: OneDrive
- Archivo: "SampleData.xlsx" (seleccionado por el selector de archivos)
- Script: Leer las filas seleccionadas
- startRow: currentRow (contenido dinámico)
- batchSize: batchSize (contenido dinámico)
Llame al script para escribir los datos. Agregue una segunda acción Ejecutar script . Cámbiele el nombre a Escribir datos. Use los siguientes valores para la acción.
- Ubicación: OneDrive para la Empresa
- Biblioteca de documentos: OneDrive
- Archivo: "TargetWorkbook.xlsx" (seleccionado por el selector de archivos)
- Script: Escritura de datos en la ubicación de fila
- data: resultado (contenido dinámico de lectura de datos)
  - Presione Cambiar entrada a toda la matriz en primer lugar.
- startRow: currentRow (contenido dinámico)
- batchSize: batchSize (contenido dinámico)
Actualice la fila actual para reflejar que se ha leído y escrito un lote de datos. Agregue una acción Incrementar variable con los valores siguientes.
- Nombre: currentRow
- Valor: batchSize (contenido dinámico)
Agregue un control Condition para comprobar si los scripts lo han leído todo. El script "Escribir datos en la ubicación de fila" devuelve true cuando ha escrito menos filas de las que permite el tamaño del lote. Esto significa que está al final del conjunto de datos. Cree la acción de control Condición con los siguientes valores.
- Elegir un valor: resultado (contenido dinámico de Escritura de datos)
- es igual a (de la lista desplegable)
- Elegir un valor: true (expresión)
En la sección True del control Condition , establezca la variable currentRow en -1. Agregue una acción Establecer variable con los siguientes valores.
- Nombre: currentRow
- Valor: -1
Guarde el flujo. El diseñador de flujo debe tener un aspecto similar al de la imagen siguiente.
Use el botón Probar de la página del editor de flujo o ejecute el flujo a través de la pestaña Mis flujos . Asegúrese de permitir el acceso cuando se le solicite.
El archivo "TargetWorkbook.xlsx" ahora debe tener los datos de "SampleData.xlsx".