Rédiger un grand ensemble de données
L’API Range.setValues() place les données dans une plage. Cette API présente des limitations en fonction de différents facteurs, tels que la taille des données et les paramètres réseau. Cela signifie que si vous essayez d’écrire une quantité massive d’informations dans un classeur en une seule opération, vous devez écrire les données par lots plus petits afin de mettre à jour de manière fiable une grande plage.
La première partie de l’exemple montre comment écrire un jeu de données volumineux dans Excel. La deuxième partie développe l’exemple pour faire partie d’un flux Power Automate. Cela est nécessaire si l’exécution de votre script prend plus de temps que le délai d’expiration de l’action Power Automate.
Pour plus d’informations sur les bases des performances dans les scripts Office, consultez Améliorer les performances de vos scripts Office.
Exemple 1 : Écrire un jeu de données volumineux par lots
Ce script écrit les lignes d’une plage en parties plus petites. Il sélectionne 1 000 cellules à écrire à la fois. Exécutez le script sur une feuille de calcul vide pour voir les lots de mise à jour en action. La sortie de la console fournit des informations supplémentaires sur ce qui se passe.
Remarque
Vous pouvez modifier le nombre total de lignes écrites en modifiant la valeur de SAMPLE_ROWS. Vous pouvez modifier le nombre de cellules à écrire en tant qu’action unique en modifiant la valeur 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;
}
Vidéo de formation : Écrire un jeu de données volumineux
Regardez Sudhi Ramamurthy parcourir cet exemple sur YouTube.
Exemple 2 : Écrire des données par lots à partir d’un flux Power Automate
Pour cet exemple, vous devez effectuer les étapes suivantes.
- Créez un classeur dans OneDrive nommé SampleData.xlsx.
- Créez un deuxième classeur dans OneDrive nommé TargetWorkbook.xlsx.
- Ouvrez SampleData.xlsx avec Excel.
- Ajoutez des exemples de données. Vous pouvez utiliser le script de la section Écrire un jeu de données volumineux par lots pour générer ces données.
- Créez et enregistrez les deux scripts suivants. Utilisez Automatiser un>nouveau script pour coller le code et enregistrer les scripts avec les noms suggérés.
- Suivez les étapes sous Flux Power Automate : Lire et écrire des données dans une boucle pour créer le flux.
Exemple de code : lire les lignes sélectionnées
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[][];
}
Exemple de code : Écrire des données à l’emplacement de la ligne
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;
}
Flux Power Automate : lire et écrire des données dans une boucle
Connectez-vous à Power Automate et créez un flux cloud instantané.
Choisissez Déclencher manuellement un flux , puis sélectionnez Créer.
Créez une variable pour suivre la ligne actuelle en cours de lecture et d’écriture. Dans le générateur de flux, sélectionnez le + bouton et Ajouter une action. Sélectionnez l’action Initialiser la variable et attribuez-lui les valeurs suivantes.
- Nom : currentRow
- Type : Entier
- Valeur : 0
Ajoutez une action pour définir le nombre de lignes à lire dans un seul lot. Selon le nombre de colonnes, il peut être nécessaire d’être plus petit pour éviter les limites de transfert de données. Créez une nouvelle action Initialiser la variable avec les valeurs suivantes.
- Nom : batchSize
- Type : Entier
- Valeur : 10000
Ajoutez un contrôle Do jusqu’à . Le flux lit les blocs des données jusqu’à ce qu’elles soient toutes copiées. Vous allez utiliser la valeur de -1 pour indiquer que la fin des données a été atteinte. Donnez au contrôle les valeurs suivantes.
- Choisir une valeur : currentRow (contenu dynamique)
- est égal à (dans la liste déroulante)
- Choisissez une valeur : -1
Les étapes restantes sont ajoutées à l’intérieur du contrôle Do . Ensuite, appelez le script pour lire les données. Ajoutez l’action Exécuter le script du connecteur Excel Online (Business). Renommez-le En lecture de données. Utilisez les valeurs suivantes pour l’action.
- Emplacement : OneDrive Entreprise
- Bibliothèque de documents : OneDrive
- Fichier : « SampleData.xlsx » (tel que sélectionné par le sélecteur de fichiers)
- Script : lire les lignes sélectionnées
- startRow : currentRow (contenu dynamique)
- batchSize : batchSize (contenu dynamique)
Appelez le script pour écrire les données. Ajoutez une deuxième action Exécuter le script . Renommez-le en Écrire des données. Utilisez les valeurs suivantes pour l’action.
- Emplacement : OneDrive Entreprise
- Bibliothèque de documents : OneDrive
- Fichier : « TargetWorkbook.xlsx » (tel que sélectionné par le sélecteur de fichiers)
- Script : Écrire des données à l’emplacement de la ligne
- data : result (contenu dynamique à partir de Lire les données)
- startRow : currentRow (contenu dynamique)
- batchSize : batchSize (contenu dynamique)
Mettez à jour la ligne actuelle pour refléter qu’un lot de données a été lu et écrit. Ajoutez une action Incrémenter une variable avec les valeurs suivantes.
- Nom : currentRow
- Valeur : batchSize (contenu dynamique)
Ajoutez un contrôle Condition à case activée si les scripts ont tout lu. Le script « Écrire des données à l’emplacement de la ligne » retourne true lorsqu’il a écrit moins de lignes que la taille de lot le permet. Cela signifie qu’il se trouve à la fin du jeu de données. Créez l’action de contrôle Condition avec les valeurs suivantes.
- Choisir une valeur : résultat (contenu dynamique à partir de l’écriture de données)
- est égal à (dans la liste déroulante)
- Choisir une valeur : true (expression)
Sous la section True du contrôle Condition , définissez la variable currentRow sur -1. Ajoutez une action Définir une variable avec les valeurs suivantes.
- Nom : currentRow
- Valeur : -1
Enregistrez le flux. Le concepteur de flux doit ressembler à l’image suivante.
Utilisez le bouton Test de la page de l’éditeur de flux ou exécutez le flux via votre onglet Mes flux . Veillez à autoriser l’accès lorsque vous y êtes invité.
Le fichier « TargetWorkbook.xlsx » doit maintenant contenir les données de « SampleData.xlsx ».