Lets start with a confession, these scripts aren't Power Automate scripts, but Office Scripts (docs.microsoft.com). These Office Scripts can be called from Power Automate (so are kind of Power Automate scripts), but they are only for Excel (at the moment).
Office Scripts are, in some ways, the evolution of VBA for Excel online. Its based on Typescript (Typescriptlang.org), which is a subset of Javascript. What that means is most JavaScript functions work, but strongly typed.
So in my mind its JavaScript, with built in Excel objects, and intelliSense (and even has a record button to auto write code).
So why is Office Scripts great for Power Automate developers? Well, we all know there are plenty of missing features in Power Automate (Top 10 Missing Features), and having an ability to call JavaScript can be powerful (even outside of Excel).
So here are my top 5 scripts that add powerful features to Power Automate:
1. Write Data (in style)
So I know you can write to Excel in Power Automate, but:
- Has to be a table
- Its really slow
- Uses a lot of Power Platform API calls
And if you want to see how much quicker and efficient read this Power Automate the best way to write to excel.
So here's the script
function main(workbook: ExcelScript.Workbook, param: tab[]) {
let wb = workbook.getWorksheet("Sheet1");
let rowCount = param.length;
let startRow = 2;
let count = wb.getUsedRange().getRowCount();
wb.getRange("2:" + count).delete(ExcelScript.DeleteShiftDirection.up);
for (let i = 0; i < param.length; i++) {
const currentObject = param[i];
const formattedRow = [[currentObject.colA, currentObject.colB, currentObject.colC, currentObject.colD]];
const rowRange = `A${startRow + i}:D${startRow + i}`;
wb.getRange(rowRange).setValues(formattedRow);
}
}
interface tab {
colA: string;
colB: string;
colC: number;
colD: string;
}
param: tab[]
This is the parameter we are passing from Power Automate (called param) and the tab[] means its an array with tab object (schema)
interface tab {
colA: string;
colB: string;
colC: number;
colD: string;
}
interface is the schema of tab, so this has 4 object called colA,colB,colC,colD. With all but colC a string.
let wb = workbook.getWorksheet("Sheet1");
let rowCount = param.length;
let startRow = 2;
let count = wb.getUsedRange().getRowCount();
These are our variables, wb is our worksheet, rowCount the number of items in the param parameter (rows in Excel), startRow is the first row to write to (skipping row 1 as its the header) and count is current used rows in worksheet.
wb.getRange("2:" + count).delete(ExcelScript.DeleteShiftDirection.up);
Here we delete rows 2 to count (used rows) to make sure we don't write over anything.
for (let i = 0; i < param.length; i++) {*****}
Simple JavaScript loop for each item in param.
const currentObject = param[i];
const formattedRow = [[currentObject.colA, currentObject.colB, currentObject.colC, currentObject.colD]];
const rowRange = "A${startRow + i}:D${startRow + i}";
Now we are building our row, currentObject is set against param item, formattedRow is the array to match the row (so if the row is 4 columns the array should be 4 items long). rowRange is the row range we are going to write to (in a string).
wb.getRange(rowRange).setValues(formattedRow);
And last we are writing the array we have made to the range we built in the string.
2. Get Data from outside of a table
Working with tables is always easier, but sometimes we don't control out import files. So that means we need to add a table, which can be a little janky. And if the data isn't in same place every time then we can't do anything (in this example the same place isn't covered but its just needs some code to find the cell with first header in).
function main(workbook: ExcelScript.Workbook) {
let ws = workbook.getWorksheet("TableData");
let i=0;
let tab:GetTable[]=[];
let rng= ws.getRange("a2:d101").getValues();
let rows = ws.getRange("a2:d101").getRowCount();
for (i==0; i<rows; i++) {
tab.push({
text: rng[i][0] as string,
num: rng[i][1] as number,
pers: rng[i][2] as string,
dat: rng[i][3] as string
})
}
return(tab);
}
interface GetTable{
text: string,
num: number,
pers: string,
dat: string
}
So the first rows are the same as Write Data, but then there is
let tab:GetTable[]=[];
This is creating an array variable called tab, with the schema of GetTable (set by interface GetTable after the function)
let rng= ws.getRange("a2:d101").getValues();
let rows = ws.getRange("a2:d101").getRowCount();
Set rng as the range of data we want and rows is the number or rows. So we are getting range a2:d101, and rows should be 100
tab.push({
text: rng[i][0] as string,
num: rng[i][1] as number,
pers: rng[i][2] as string,
dat: rng[i][3] as string
})
The above is inside the loop, and its creating an object from the row and pushing (adding) to the tab array. We are setting the field text as the first column (0) of the current row (i).
return(tab);
And last we return the tab array to Power Automate.
We even get the array returned in right type with each field.
Get CSV
One of the most obvious things I think is missing from Power Automate is an out of the box get csv action (handling with loops and composes is just janky). Luckily there is an office script for that.
function main(workbook: ExcelScript.Workbook, csv: string) {
let tab: schema[] = [];
csv = csv.replace(/\r/g, "");
let rows = csv.split("\n");
const csvRegex = /(?:,|\n|^)("(?:(?:"")*[^"]*)*"|[^",\n]*|(?:\n|$))/g
rows.forEach((value, index) => {
if (value.length > 0) {
let row = value.match(csvRegex);
if (row[0].charAt(0) === ',') {
row.unshift("");
}
row.forEach((cell, index) => {
row[index] = cell.indexOf(",") === 0 ? cell.substr(1) : cell;
});
tab.push(
{
colA: row[0],
colB: row[1],
colC: row[2],
colD: row[3]
}
);
}
});
return(tab);
}
interface schema{
colA: string;
colB: string;
colC: string;
colD: string;
}
So we know the interface schema is (out csv in array output).
csv = csv.replace(/\r/g, "");
Removes unnecessary \r as we only want \n for out new lines
let rows = csv.split("\n");
Creates an array by each line (so creates rows)
const csvRegex = /(?:,|\n|^)("(?:(?:"")*[^"]*)*"|[^",\n]*|(?:\n|$))/g
This is a very cool regex that splits the row into cells (you would think split by comma would work, but what about commas within the cell). Shout out to 'awwsmm' from a Stack over flow comment :-)
rows.forEach((value, index) => {
if (value.length > 0) {
}
})
This is a loop for each row, with the value representing the item and the index the position in rows. Then we are checking the item isn't blank.
if (row[0].charAt(0) === ',') {
row.unshift("");
}
Removes leading comma of the row (first item in the row).
row.forEach((cell, index) => {
row[index] = cell.indexOf(",") === 0 ? cell.substr(1) : cell;
});
Loop over row, cell representing item and index position again. If first character is comma remove it.
tab.push(
{
colA: row[0],
colB: row[1],
colC: row[2],
colD: row[3]
}
);
Pushing each item of row to the tab array, ready to be returned to Power Automate.
4. Regex
Probably the biggest missing expression from Power Automate, but guess what, TypeScript/JavaScript does have a regex function, so we can use it nice and easy.
function main (workbook: ExcelScript.Workbook, inputString: string,rgex: string,flag: string) {
let regEx = new RegExp(rgex, flag);
let matches: string[] = [];
let aMatches = inputString.match(regEx);
if (aMatches) {
for (var i = 0; i < aMatches.length; i++) {
matches.push(aMatches[i]);
}
}
return matches;
}
let regEx = new RegExp(rgex, flag);
First we create our regex
let matches: string[] = [];
Then an empty array of strings.
let aMatches = inputString.match(regEx);
Pull out matches of the regex to an array
if (aMatches) {
for (var i = 0; i < aMatches.length; i++) {
matches.push(aMatches[i]);
}
}
If we have find matches, loop over the array and add the item to the string array (we cant pass unstructured arrays back to Power Automate).
5. Sort Arrays
We have filter out of the box in Power Automate, but no sort. Luckily there is an easy way to sort with Office Scripts.
function main(workbook: ExcelScript.Workbook, direct:string, sortType: string, data: schema[]) {
let sortArray: schema[] = [];
if(sortType=="Number"){
sortArray = data.sort((a, b) => {
if (direct=="asc"){
return a.ID - b.ID;
}else{
return b.ID - a.ID;
}
});
} else{
if (direct == "asc") {
sortArray = data.sort((a, b) => a.ToSort.localeCompare(b.ToSort));
}else{
sortArray = data.sort((a, b) => b.ToSort.localeCompare(a.ToSort));
}
};
return sortArray
}
interface schema {
Date: string;
ToSort: string;
ID: number;
}
if(sortType=="Number"){}
We have condition as we handle integers/numbers different to strings.
sortArray = data.sort((a, b) => {
if (direct=="asc"){
return a.ID - b.ID;
}else{
return b.ID - a.ID;
}
Set sortArray to sort function of data. If Parameter asc (ascending), sort asc (a-b) else descending (b-a).
a.ToSort.localeCompare(b.ToSort));
For strings we use the localCompare instead of minus. And again flip a and b if descending.
As you can see OfficeScripts exponentially increase the functionality of Power Automate and are great tool to have to hand when you need it.
Top comments (0)