Hello,
I have a power automate flow which runs office scripts in excel. The first script copies and returns certain contents of the file and first occurring date in the file and dynamically allocates these returns to another script to either paste the data in the correct place chronologically using the first occurring date or it pastes the data at the end of the existing data. It works perfectly fine for files below 5MB but the issue I am facing is that the copy script will only run on smaller files because of the restrictions with excel scripts and the range functions. I have read that if I am able to split the data into chunks or there are some ways around this limitation but I can't find a clear guide how to do it or anywhere that confirms this is even possible.
I thought about implementing a part into the copy function which first determines how big the file is (how many rows), then divides it by how many max rows we are allowed to deal with at a time and then loops the copying process for however many chunks are needed and returns multiple copied chunks. Then in the paste function i wanted to loop the pasting process for however many chunks were returned and paste them in order. An issue I see with this solution is that currently in my power automate flow I have certain boxes where I dynamically allocate the copied data, and there is no way to determine how many chunks there will be, therefore I can't assign what the second script is pasting.
Does anyone know if for my case it is possible to modify my script or PA flow so that it works for larger files or where I can find a guide on how to do so? Thanks in advance.
This is the copying script:
function main(workbook: ExcelScript.Workbook) {
// Get the worksheet named "SourceSheet"
let sourceSheet = workbook.getActiveWorksheet();
// Get the used range in the source sheet starting from row 6
let usedRange = sourceSheet.getRange("B6").getSurroundingRegion();
// Get the values from the used range
let values = usedRange.getValues();
// remove the top rows from the array
values = values.slice(1);
// Convert the values array to a JSON string
let jsonString = JSON.stringify(values);
// Get the top date from B6
let topDate = sourceSheet.getRange("B6").getValue();
// Convert the top date to a formatted date string
let firstDate = formatExcelDate(topDate);
// Log the JSON strings to the console (for debugging purposes)
console.log(jsonString);
console.log(firstDate);
// Return the JSON strings
return { jsonString: jsonString, firstDate: firstDate };
}
// Function to format Excel date to yyyy/mm/dd
function formatExcelDate(excelDate: number😞 string {
let date = new Date((excelDate - 25569) * 86400 * 1000); // Convert Excel date to JavaScript date
let year = date.getFullYear();
let month = ('0' + (date.getMonth() + 1)).slice(-2);
let day = ('0' + date.getDate()).slice(-2);
return `${year}/${month}/${day}`;
}
and this is the pasting script:
function main(workbook: ExcelScript.Workbook, jsonString: string, worksheetName: string, topDate: string) {
// Parse the JSON string to get the array of values
let values: (string | number | boolean)[][] = JSON.parse(jsonString);
let firstDate: string | number | boolean = JSON.parse(topDate);
// Get the worksheet with the name provided by the user
let targetSheet = workbook.getWorksheet(worksheetName);
// Find the first occurrence of the first date in column A
let searchRange = targetSheet.getRange("A:A");
let firstDateCell = searchRange.find(firstDate.toString(), {
completeMatch: true,
matchCase: false,
searchDirection: ExcelScript.SearchDirection.forward // Start at the beginning of the range and go to later columns and rows.
});
if (firstDateCell) {
// Get the used range in the target sheet
let usedRange = targetSheet.getUsedRange();
// Calculate the clear range starting from firstDateCell row down to the last used row
let clearStartRow = firstDateCell.getRowIndex();
let clearStartColumn = 0; // Column A (index 0)
let clearEndRow = usedRange.getRowIndex() + usedRange.getRowCount() - 1;
let clearEndColumn = usedRange.getColumnIndex() + usedRange.getColumnCount() - 1;
// Get clear range
let clearRange = targetSheet.getRangeByIndexes(clearStartRow, clearStartColumn, clearEndRow - clearStartRow + 1, clearEndColumn - clearStartColumn + 1);
// Clear the range contents (values and formulas)
clearRange.clear(ExcelScript.ClearApplyTo.contents);
// Insert new values starting from the row of firstDateCell
let startCell = targetSheet.getCell(clearStartRow, clearStartColumn);
startCell.getResizedRange(values.length - 1, values[0].length - 1).setValues(values);
} else {
// Find the last used row in column B
let lastUsedRange = searchRange.getUsedRange();
let lastRow = lastUsedRange.getLastRow();
// Calculate the start row for appending
let startRow = lastRow.getRowIndex() + 1;
// Get the starting cell in the target sheet for appending
let targetRange = targetSheet.getCell(startRow, 0); // Column A (index 0)
// Get the resized range based on the values array
let resizedRange = targetRange.getResizedRange(values.length - 1, values[0].length - 1);
// Set the values in the target sheet for appending
resizedRange.setValues(values);
}
}
