This scenario can be accomplished using "Run script" action on Excel online flow. It requires creation of 2 Office Scripts (TypeScript scripts from Excel for web). More details are here:
https://docs.microsoft.com/en-us/office/dev/scripts/develop/power-automate-integration
For your scenario, here's how I'd go about it -
(Assuming you only want to do comparison in file-2. If you need to highlight in file-1, simply repeat steps by switching files)
1. Trigger >
2. Write a script that returns the values from your worksheet/or table from File-1. Return unique values of companies. Run this as part of "run script action"
3. Pass that as input to second script, which then compares/looks-up against the input unique list of values being passed against File-2 and if not found will use the APIs to highlight the cell.
// Script named: Return unique companies
// Run this against source file containing company names. Assumes Sheet1 and a table within it containing "Company" column. Change as per need.
function main(workbook: ExcelScript.Workbook) {
// Your code here
let mySheet = workbook.getWorksheet('Sheet1');
let table = mySheet.getTables()[0];
let data2D = table.getColumnByName('Company').getRangeBetweenHeaderAndTotal().getValues();
let companies = data2D.map(row => row[0]);
let uniqueCompanies = companies.filter(function (item, pos) {
return companies.indexOf(item) == pos;
})
console.log(uniqueCompanies.join('|'))
return uniqueCompanies.join('|');
}
// Script named: Highlight missing companies
// Run this against target file where you want to highlight. Assumes Sheet1 and a table within it containing "Company" column. Change as per need.
function main(workbook: ExcelScript.Workbook, uniqCompaniesStr: string) {
uniqCompaniesStr = 'Microsoft|Google|Netflix|Apple'
let uniqCompanies = uniqCompaniesStr.split('|');
console.log(uniqCompanies);
let mySheet = workbook.getWorksheet('Sheet8');
let table = mySheet.getTables()[0];
// First clear all formats
table.getRange().clear(ExcelScript.ClearApplyTo.formats);
let dataRange = table.getColumnByName('Company').getRangeBetweenHeaderAndTotal();
let companies = dataRange.getValues();
for (let i=0; i< companies.length; i++) {
let row = companies[i]
if (uniqCompanies.indexOf(row[0]) < 0) {
// console.log("Not found: " + row[0] + " at: " + i)
dataRange.getCell(i,0).getFormat().getFill().setColor('Yellow');
}
}
return;
}
Save above scripts in your Office Scripts and run this in Power Automate using "Run Script".
PA flow with 2 scripts
Excel input
Excel output
Excel for web - create scripts
