Hi all,
I need to create a flow where i compare 2 excel files and check if they are same, if not same changing the font color or making the font bigger of the non-matching values in the any of the one excel file would be helpful to identify which all values are not matching.
So here Microsoft is not matching with Google so just Highlight any of the one Microsoft or Google.
Like this.
Solved! Go to Solution.
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
Passing multiple columns may be easier if you return an object out of the 1st script. It'll help you with comparison in the next script. But the flow and logic will remain identical.
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
Added 2 scripts you will need. Let me know if you need help with using them in power automate.
Done, see above screenshots in the original response.
If it works for you, please accept the answer.
If it doesn't work, please email and we can discuss offline.
Quick note - if you don't see the "Automate" tab in Excel for web (see my screenshot), please have your O365 Admin turn on Office Scripts. See here for details: https://docs.microsoft.com/en-us/microsoft-365/admin/manage/manage-office-scripts-settings?view=o365...
Within few weeks we should have it rolled out to all users (non GCC) by default.
Hi @sumurthy ,
The above scripts worked completely fine, and I was able to get the non-matching values colored.
Just wanted to know , if there are more then then 2-3 columns to compare how will the script be ,
Excel input 1
Excel input 2
Excel Output
And do we always need to pass the input data in the script?
Thanks.
Passing multiple columns may be easier if you return an object out of the 1st script. It'll help you with comparison in the next script. But the flow and logic will remain identical.
@sumurthy , is it possible to create a new file with the not matching values instead of highlighting them?
User | Count |
---|---|
25 | |
15 | |
14 | |
10 | |
9 |
User | Count |
---|---|
48 | |
29 | |
28 | |
25 | |
23 |