Power Platform Community Forum Thread Details

I'm having trouble getting Power Automate to use the right date from an Excel spreadsheet. I have a flow that's supposed to read a spreadsheet with a column that has the formula '=today()', which is fundamental to the flow is supposed to do.

But the flow keeps using yesterday's date (or the last date I opened the spreadsheet) instead of today's. I've tried adding a script to the spreadsheet to refresh it but it was unsuccessful.

I also thought about using an Excel spreadsheet with a macro that runs before the flow starts. Unfortunately, I couldn't get Power Automate to work with the .xlsm file (when using ‘List rows present in a table’.

Any suggestions on how to get the flow to properly refresh the date column before running the next steps and without the need of opening the spreadsheet in my PC before it runs?

The spreadsheet is saved in a Sharepoint.

Categories:

Building flows

You can write an Office Script to do this. Here is an example:

function main(workbook: ExcelScript.Workbook) {
// Get the active worksheet
let sheet = workbook.getActiveWorksheet();

// Get all used ranges in the worksheet
let usedRange = sheet.getUsedRange();

// Loop through each cell in the used range
usedRange.getValues().forEach((row, rowIndex) => {
row.forEach((cell, colIndex) => {
// Check if the cell contains the =TODAY() function
if (cell === '=TODAY()') {
// Refresh the cell by setting its formula again
usedRange.getCell(rowIndex, colIndex).setFormula('=TODAY()');
}
});
});
}

Once you save it, use the save a copy feature to save it to your OneDrive in My Files > Documents > Office Scripts as an *.osts file. You can then use the action Run script from SharePoint library to call the file and run it. See https://learn.microsoft.com/en-us/office/dev/scripts/develop/power-automate-integration?tabs=run-script#excel-connector for more details.