The one thing I really wish Power Automate had was a script action like Logic Apps does. Luckily there is Office Scripts which is close, it is incredibly powerful but has some limitations and trade offs. But now I have found App Scripts, and they are what I wish Office Scripts were.
If you are not familiar with App Scripts, they are Google Sheets version of VBA. So it kind of went, Google copied VBA to create App Scripts, Microsoft copied App Scripts to create Office Scripts.
So what's the main differences, well
- Its JavaScript not TypeScript (but that's no big deal),
- It interacts with Google Sheets, Gmail and Drive instead of Excel, Outlook and OneDrive
- It has a standalone code editor not part of Sheets/Excel
- It can load libraries (kind of)
- It has out of the box connectors
- It can be called as an API
and the last one is the really cool bit, it means I can create my own custom api 😎 and run any script I like.
Damien Bird did a great demo on how you can use Logic Apps to create your own api and run code. And that solution is definitely better, as you can do everything under Microsoft umbrella. But it's not that simple, as you need to have Azure resource groups, spn's, and there is a cost, where as App Scripts are 100% free and require zero effort to set up.
In the blog there's a couple of things I want to show you:
- How to get App Script setup
- How to create a API
- How to call them in Power Automate/Power Apps
- Cool Demos
- Trade offs / negatives
1. How to get App Script setup
All you need is a Google account and to go to https://script.google.com/home
Here you can manage all of your scripts and create new ones (I love you Office Scripts but this is a lot nicer then going in through a Excel workbook and loading a script file).
When you create a new script you are shown a simple code editor, it has intellisense and is easy to use.
You can also add libraries (other scripts by you or others, there are lists available) and services. Services are like connectors and allow you to interact with Google services. Office Scripts allow you to call any api, but there is no built in authentication with the Graph api.
When you are ready to use it you have to deploy it. You have 4 options to deploy as:
- Web App - Returns html/json through api calls
- API Executable - Full API (like a Function App but requires same more complex setup)
- Add-On - For Google Sheets etc, like Excel Add-ins
- Library - Scripts that can be called by other scripts
We are going to use Web App. Once deployed you are given your url to call the Web App aka api. The deploying is a little different but has its reasons. Everytime you want to deploy a change you have to create a new version, and every version has a unique url. Now this is a pain but can also be a benefit (will explain why later), luckily there is a work around.
You crate at script that forwards everything on to a library. You then publish it once, and then you can edit the library without having to redeploy (as long as you link it as the 'Head (Development mode)' version).
The last thing is who can access the api. If you want to have authentication then we need to register an SPN, so to keep it simple we are just going to leave it open to everyone (you can see a trade off already).
2. How to create a API
To call the api you need to create either a GET or POST function, sadly there is no PATCH,PUT or DELETE. The only real difference between them in App Scripts is GET has no body, POST does.
function doGet(e) {
//do something
return something
}
function doPost(e) {
//do something
return something
}
Lets start with GET. We can add any query we want on our url, if we added ?message=hello world, our e value would be:
{
"parameters":{
"message":["hello world"]
},
"contentLength":-1,
"queryString":"message=hello%20world",
"contextPath":"",
"parameter":{"message":"hello world"}
}
If we wanted to get the parameter value the key to use is the parameter or parameters.So I would use e.paramete.message to get the value "hello world".
To return a value we need to convert any objects/arrays to strings. Below I'm just going to send the parameter back, so I turn it into a string.
return ContentService.createTextOutput(JSON.stringify(e.parameter) ).setMimeType(ContentService.MimeType.JSON);
if its a string you are returning you can remove the JSON.stringify() and just pass the value
It would return the "parameter":{"message":"hello world"}.
As I said the POST is similar, just with a body as well as query parameters. So if I sent the following body:
{
"hello":"world"
}
our e value would be
{
"contentLength": 25,
"queryString": "",
"parameters": {},
"contextPath": "",
"postData": {
"contents": "{\r\n \"hello\":\"world\"\r\n}",
"length": 25,
"name": "postData",
"type": "application/json"
},
"parameter": {}
}
The body is passed in the contents parameter, in the postData object. As you can see its been turned into a string, so to handle it we would need to use a JSON.parse().
To return a value we use the same function as the GET.
And that's the basics of the API, for me if its primitive data use GET, for non primitive use POST ie simple string=GET, array=POST.
3. How to call them in Power Automate/Power Apps
Now here's the fun bit, for use to return data as a JSON App Scripts throws us a curved ball, it doesn't do a straight return but a 302 redirect return. This means we don't get our response straight back, we get a header called location that has the redirect URL. We then do a GET request against that redirect URL to get our response.
Postman and other API clients handle this automatically so you don't even see it, unfortunately Power Automate doesn't. The easy way to handle it is 2 http requests, with the second run after set to failed and use the first header.location as it's url.
But this isn't easy to scale, luckily we can do it in a custom connector. Out of the box we have the same issue, but with a little custom c# code we can get the connector to handle it automatically.
Download code here.
And that's it, the rest of it is just standard custom connector stuff, if you want to know more check out this blog
4. Cool Demos
In theory we can do almost anything, the only limits we have is 6 min timeout (vs Office Scripts 2 min), and a few specific ones here.
I've got 2 demos to show, a GET and POST.
GET - Regex
I've done this one in Office Scripts so nice to compare, we are going to return matches from a string.
We have our 3 parameters
- String to search
- Regex
- Flag
the parameter length is limited, so for long search strings you might need a POST version
The script is very easy, we grab the parameters and pass them into the JavaScript find function.
function doGet(e)
{
var content = JSON.stringify(myFunction(e.parameter.string,e.parameter.regex,e.parameter.flag));
return ContentService.createTextOutput(JSON.stringify(content) ).setMimeType(ContentService.MimeType.JSON);
}
function myFunction(inputString,rgex,flag) {
let regEx = new RegExp(rgex, flag);
let aMatches = inputString.match(regEx);
Logger.log(aMatches)
return aMatches;
}
As the response could be more then one match we have to convert the array to a string and then return it.
You can try it here (desktop browser only due to that redirect):
as it's in the query we have to URL encode the parameters, so
([ a-zA-Z0-9.-]+@[a-zA-Z0-9.-]+.[a-zA-Z0-9-]+)
becomes
%28%5Ba-zA-Z0-9.-%5D%2B%40%5Ba-zA-Z0-9.-%5D%2B%5C.%5Ba-zA-Z0-9_-%5D%2B%29_
I then wrap it in a custom connector and it's all done.
For the POST demo I want to really show it's power. I'm going to port the code from my code review tool AutoReview (shameless plug alert, you can get it free here and here).
The API will accept a flow definition as the body, review it and then return a JSON object with key metrics and the actions in an easier to read format. I could have ported the html generation and returned a full report but that's for another day.
In this case I want to do the library trick so I don't have to change URL every deployment. The post function simply passed the data and returns what the library returns.
the GET just returns the version of AutoReview
The library holds all the complex code and is wrapped in a main function which then calls a secondary CreateReview function.
Again I wrap it in a custom connector and add it to a flow. To get the Flow definition I use the Power Automate Admin Get Flow action.
The idea was I could include this in my pipeline deployment.
Both connectors are available to download here but I can't promise the API will be up for long, so don't build it into your workflow (if you really need it reach out to me and might be able to help create something more permanent)
5. Trade offs / negatives
So what's the downside with this free API, well there are a few.
The first is it's outside of the Power Platform, so it adds complexity. Complexity to your development (it is probably code not low code), deployments and security. Very few companies have both Microsoft and Google accounts so now your data is flowing out of your network onto, most likely, someone's personal account.
Second and this is a big one, it blows a hole in your DLP policy. Office Scripts don't allow http calls when called by a flow, explicitly because the environment DLP policy can't control it. It also can't control the App Scripts http calls and there is nothing it can do about it. So if the DLP blocked business data going to X/Twitter, the App Script could pass the data. This is where the deployment new URL is a benefit. As in theory you could code review it, and at least no one could make DKP breaking edits without getting new URL approved.
Third is it's free and from Google. That means you can't expect great support, and Google could decide to kill it off at any moment (killedbygoogle.com).
Finally, and this is the big one, it's not secured. You can secure it but that adds much more complexity, and once we are at that level you really should go with Azure Function Apps.
So for me sadly it isn't a enterprise solution for me (will have to stick with Office Scripts / Function Apps), but I think it definitely still has some use cases:
- PoC's
- Just for fun projects
- Small low risk solutions
And I think it will be useful for some. I do love it, and if Office Scripts could borrow some functionality, that would be cool 😎
Top comments (2)
Entering the multiverse of Apps scripts.. Thanks for sharing
wow