I run the script every hour automatically via Power Automate Flow.
function main(workbook: ExcelScript.Workbook) {
//Take the used range in the first sheet
let range = workbook.getWorksheet("Current").getUsedRange();
let rows = range.getRowCount();
let values = range.getValues();
var valuesOfRows: (number | string | boolean)[][] = []
//check if any rows are more than 2 hours old
for (var i = 0; i < rows; i++) {
let tempdate = values[i][24];
let twohoursold = Date.now() + -1*3600*1000;
if (tempdate <= twohoursold)
valuesOfRows.push(values[i]);
}
//get the used range in the second sheet
let usedRange = workbook.getWorksheet("Archive").getUsedRange();
//get the range of where the rows will be added (below the used range of the second sheet)
let newRowRange = usedRange.getRowsBelow(valuesOfRows.length);
//make sure that the new row range has the right amount of columns for the new data to be added
let dataRange = workbook.getActiveWorksheet().getRangeByIndexes(newRowRange.getRowIndex(), newRowRange.getColumnIndex(), newRowRange.getRowCount(), valuesOfRows[0].length);
//set the row data to be added in the correct range
dataRange.setValues(valuesOfRows);
}
And the Power Automate flow is like this:
When running the script manually from excel, the script runs successfully.
However, when running it from Power Automate, it shows that it ran successfully but it doesn't actually run the script.
Is this a bug or a limitation in Power Automate?
