Erstellen eines großen Datasets

Die Range.setValues() API platziert Daten in einem Bereich. Für diese API gelten Einschränkungen, die von verschiedenen Faktoren abhängig sind, z. B. datengröße und Netzwerkeinstellungen. Wenn Sie also versuchen, eine große Menge von Informationen als einzelner Vorgang in eine Arbeitsmappe zu schreiben, müssen Sie die Daten in kleineren Batches schreiben, um einen großen Bereich zuverlässig zu aktualisieren.

Der erste Teil des Beispiels zeigt, wie ein großes Dataset in Excel geschrieben wird. Im zweiten Teil wird das Beispiel als Teil eines Power Automate-Flows erweitert. Dies ist erforderlich, wenn die Ausführung Ihres Skripts länger dauert als das Power Automate-Aktionstimeout.

Informationen zu Leistungsgrundlagen in Office-Skripts finden Sie unter Verbessern der Leistung Ihrer Office-Skripts.

Beispiel 1: Schreiben eines großen Datasets in Batches

Dieses Skript schreibt Zeilen eines Bereichs in kleineren Teilen. Es wählt 1.000 Zellen aus, die gleichzeitig geschrieben werden sollen. Führen Sie das Skript auf einem leeren Arbeitsblatt aus, um die Updatebatches in Aktion zu sehen. Die Konsolenausgabe bietet einen weiteren Einblick in die Vorgänge.

Hinweis

Sie können die Anzahl der insgesamt geschriebenen Zeilen ändern, indem Sie den Wert von SAMPLE_ROWSändern. Sie können die Anzahl der zu schreibenden Zellen als einzelne Aktion ändern, indem Sie den Wert von CELLS_IN_BATCHändern.

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

Schulungsvideo: Schreiben eines großen Datasets

Sehen Sie sich sudhi Ramamurthy an, wie Sie dieses Beispiel auf YouTube durchgehen.

Beispiel 2: Schreiben von Daten in Batches aus einem Power Automate-Flow

Für dieses Beispiel müssen Sie die folgenden Schritte ausführen.

1. Erstellen Sie in OneDrive eine Arbeitsmappe mit dem Namen SampleData.xlsx.
2. Erstellen Sie in OneDrive eine zweite Arbeitsmappe mit dem Namen TargetWorkbook.xlsx.
3. Öffnen Sie SampleData.xlsx mit Excel.
4. Fügen Sie Beispieldaten hinzu. Sie können das Skript aus dem Abschnitt Schreiben eines großen Datasets in Batches verwenden, um diese Daten zu generieren.
5. Erstellen und speichern Sie die beiden folgenden Skripts. Verwenden Sie Automate>New Script , um den Code einzufügen und die Skripts mit den vorgeschlagenen Namen zu speichern.
6. Führen Sie die Schritte unter Power Automate-Flow: Lesen und Schreiben von Daten in einer Schleife aus, um den Flow zu erstellen.

Beispielcode: Lesen ausgewählter Zeilen

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

Beispielcode: Schreiben von Daten am Zeilenspeicherort

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-Flow: Lesen und Schreiben von Daten in einer Schleife

1. Melden Sie sich bei Power Automate an, und erstellen Sie einen neuen Instant Cloud Flow.

2. Wählen Sie Manuell einen Flow auslösen und dann Erstellen aus.

3. Erstellen Sie eine Variable, um die aktuelle Zeile nachzuverfolgen, die gelesen und geschrieben wird. Wählen Sie im Flow-Generator die + Schaltfläche und dann Aktion hinzufügen aus. Wählen Sie die Aktion Variable initialisieren aus, und weisen Sie ihr die folgenden Werte zu.

   • Name: currentRow
   • Typ: Integer
   • Wert: 0

   

4. Fügen Sie eine Aktion hinzu, um die Anzahl der Zeilen festzulegen, die in einem einzelnen Batch gelesen werden sollen. Abhängig von der Anzahl der Spalten muss dies möglicherweise kleiner sein, um die Datenübertragungsgrenzwerte zu vermeiden. Erstellen Sie eine neue Aktion Variable initialisieren mit den folgenden Werten.

   • Name: batchSize
   • Typ: Integer
   • Wert: 10000

   

5. Fügen Sie ein Do until-Steuerelement hinzu. Der Flow liest Blöcke der Daten, bis alle kopiert wurden. Sie verwenden den Wert -1 , um anzugeben, dass das Ende der Daten erreicht wurde. Weisen Sie dem Steuerelement die folgenden Werte zu.

   • Wählen Sie einen Wert aus: currentRow (dynamischer Inhalt)
   • ist gleich (aus der Dropdownliste)
   • Wählen Sie einen Wert aus: -1

   

6. Die verbleibenden Schritte werden innerhalb des Do-Steuerelements hinzugefügt. Rufen Sie als Nächstes das Skript auf, um die Daten zu lesen. Fügen Sie die Aktion Skript ausführen des Excel Online (Business)-Connectors hinzu. Benennen Sie sie in Daten lesen um. Verwenden Sie die folgenden Werte für die Aktion.

   • Location: OneDrive for Business
   • Document Library: OneDrive
   • Datei: "SampleData.xlsx" (wie von der Dateiauswahl ausgewählt)
   • Skript: Ausgewählte Zeilen lesen
   • startRow: currentRow (dynamischer Inhalt)
   • batchSize: batchSize (dynamischer Inhalt)

   

7. Rufen Sie das Skript auf, um die Daten zu schreiben. Fügen Sie eine zweite Aktion Skript ausführen hinzu. Benennen Sie sie in Write data (Daten schreiben) um. Verwenden Sie die folgenden Werte für die Aktion.

   • Location: OneDrive for Business
   • Document Library: OneDrive
   • Datei: "TargetWorkbook.xlsx" (wie von der Dateiauswahl ausgewählt)
   • Skript: Schreiben von Daten am Zeilenspeicherort
   • data: result (dynamischer Inhalt aus Daten lesen)
     • Drücken Sie zuerst Eingabe wechseln zum gesamten Array .
   • startRow: currentRow (dynamischer Inhalt)
   • batchSize: batchSize (dynamischer Inhalt)

   

8. Aktualisieren Sie die aktuelle Zeile, um anzugeben, dass ein Batch von Daten gelesen und geschrieben wurde. Fügen Sie eine Variable erhöhen-Aktion mit den folgenden Werten hinzu.

   • Name: currentRow
   • Wert: batchSize (dynamischer Inhalt)

   

9. Fügen Sie ein Condition-Steuerelement hinzu, um zu überprüfen, ob die Skripts alles gelesen haben. Das Skript "Daten an Zeilenspeicherort schreiben" gibt true zurück, wenn weniger Zeilen geschrieben wurden, als die Batchgröße zulässt. Dies bedeutet, dass es sich am Ende des Datasets befindet. Erstellen Sie die Bedingungssteuerelementaktion mit den folgenden Werten.

   • Auswählen eines Werts: Ergebnis (dynamischer Inhalt aus Daten schreiben)
   • ist gleich (aus der Dropdownliste)
   • Wählen Sie einen Wert aus: true (Ausdruck)

   

10. Legen Sie im Abschnitt True des Condition-Steuerelements die variable currentRow auf -1 fest. Fügen Sie die Aktion Variable festlegen mit den folgenden Werten hinzu.

    • Name: currentRow
    • Wert: -1

    

11. Speichern Sie den Flow. Der Flow-Designer sollte wie in der folgenden Abbildung aussehen.

    

12. Verwenden Sie die Schaltfläche Test auf der Flow-Editor-Seite, oder führen Sie den Flow über die Registerkarte Meine Flows aus. Achten Sie darauf, den Zugriff zuzulassen, wenn Sie dazu aufgefordert werden.

13. Die Datei "TargetWorkbook.xlsx" sollte nun die Daten aus "SampleData.xlsx" enthalten.