I am trying to create an automate that trigger every 12am and check if a specific excel file in sharepoint is updated or if the created_date column contain yesterday date. If there are any rows contains updated data or the column created_date contains yesterday date, those rows that met the condition is copy and sent to the corresponding email. How should I construct the power automate flow?
Current stage working with excel script which is shown as below
function main(workbook: ExcelScript.Workbook): string | null {
let logRecord = workbook.getTable("LogRecord");
if (!logRecord) {
console.log("Table 'LogRecord' not found.");
return null;
}
let column = logRecord.getColumnByName("CreatedDatetime");
if (!column) {
console.log("Column 'Created Date' not found in table 'LogRecord'.");
return null;
}
let filter = column.getFilter();
if (!filter) {
console.log("Filter not found in column 'Created Date'.");
return null;
}
let today = new Date();
let oneDayFromToday = new Date(today);
oneDayFromToday.setDate(oneDayFromToday.getDate() - 1);
let formattedDate = oneDayFromToday.toISOString().split('T')[0];
console.log(formattedDate);
filter.apply({
filterOn: ExcelScript.FilterOn.custom,
criterion1: `*${formattedDate}*`
});
let filteredRange = logRecord.getRange().getVisibleView();
if (!filteredRange) {
console.log("No visible range after applying filter.");
return null;
}
let filteredData = filteredRange.getValues();
console.log(filteredData.length)
return JSON.stringify(filteredData);
}
Issue 1: Currently stuck on the condition to check if the excel contains data. Not sure how to write the condition so that if the rows is equal to 1 or no data except header is retrieve
Issue 2: Besides if I by pass the condition statement and sent through e-mail I got an array instead of
How should I setup so that it return a table looks like below?
A power automate flow that perform:
1)schedule 12am
2)get data from specific excel file
3) get row(s) that is latest update or created_date column is yesterday’s date
4) construct those rows as a table and sent to fixed recepient