This article describes how two Excel files can be used for cross-refenced and formatted using Office Scripts.
The flow achieves this -
1. Extracts event master (key) data from Event.xlsx using one Run script action.
2. Passes that data to second Excel file containing event transaction data and uses that data to do basic validation of data and formatting of missing or incorrect data using Office Scripts.
3. Passes the result to an email.
See the flow in action below.
Download attachment to get Excel files and Office Scripts used. Construct PA flow on your own.
Return Unique Values
function main(workbook: ExcelScript.Workbook): EventData[] {
let table = workbook.getWorksheet('Keys').getTables()[0];
let range = table.getRangeBetweenHeaderAndTotal();
let rows = range.getValues();
let records: EventData[] = [];
for (let row of rows) {
let [event, date, location, capacity] = row;
records.push({
event: event as string,
date: date as number,
location: location as string,
capacity: capacity as number
})
}
console.log(JSON.stringify(records))
return records;
}
interface EventData {
event: string
date: number
location: string
capacity: number
}
Format missing data
function main(workbook: ExcelScript.Workbook, keys: string): string {
// Needed for testing purpose. Override the keys
// keys = `[{"event":"E123","date":43892,"location":"Montgomery","capacity":10},{"event":"E124","date":43892,"location":"Juneau","capacity":15},{"event":"E125","date":43897,"location":"Phoenix","capacity":15},{"event":"E126","date":43914,"location":"Boise","capacity":25},{"event":"E127","date":43918,"location":"Salt Lake City","capacity":20},{"event":"E128","date":43938,"location":"Fremont","capacity":3},{"event":"E129","date":43938,"location":"Vancouver","capacity":50}]`;
let table = workbook.getWorksheet('Transactions').getTables()[0];
let range = table.getRangeBetweenHeaderAndTotal();
range.clear(ExcelScript.ClearApplyTo.formats);
let overallMatch = true;
table.getColumnByName('Date').getRangeBetweenHeaderAndTotal().setNumberFormatLocal("yyyy-mm-dd;@");
table.getColumnByName('Capacity').getRangeBetweenHeaderAndTotal().getFormat()
.setHorizontalAlignment(ExcelScript.HorizontalAlignment.center);
let rows = range.getValues();
let keysObject = JSON.parse(keys) as EventData[];
for (let i=0; i < rows.length; i++){
let row = rows[i];
let [event, date, location, capacity] = row;
let match = false;
// console.log("Currently on event ID " + event + " row: " + i );
for (let keyObject of keysObject){
// console.log("Comparing: " + event + " with master event record: " + keyObject.event);
if (keyObject.event === event) {
match = true;
if (keyObject.date !== date) {
overallMatch = false;
range.getCell(i, 1).getFormat()
.getFill()
.setColor("FFFF00");
}
if (keyObject.location !== location) {
overallMatch = false;
range.getCell(i, 2).getFormat()
.getFill()
.setColor("FFFF00");
}
if (keyObject.capacity !== capacity) {
overallMatch = false;
range.getCell(i, 3).getFormat()
.getFill()
.setColor("FFFF00");
}
break;
}
}
if (!match) {
overallMatch = false;
range.getCell(i, 0).getFormat()
.getFill()
.setColor("FFFF00");
}
}
let returnString = "All the data is in the right order.";
if (overallMatch === false) {
returnString = "Mismatch found. Data requires your review.";
}
console.log("Returning: " + returnString);
return returnString;
}
interface EventData {
event: string
date: number
location: string
capacity: number
}
