DEV Community

Cover image for Power Automate - Scripts with App Scripts
david wyatt
david wyatt Subscriber

Posted on • Updated on

Power Automate - Scripts with App Scripts

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.

spiderman meme

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.

jealous meme

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:

  1. How to get App Script setup
  2. How to create a API
  3. How to call them in Power Automate/Power Apps
  4. Cool Demos
  5. 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

app script menu

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.

new script

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.

google services

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

deploy options

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).

library mode

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).
authentication

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
}


Enter fullscreen mode Exit fullscreen mode

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"}
}


Enter fullscreen mode Exit fullscreen mode

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); 


Enter fullscreen mode Exit fullscreen mode

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"
}


Enter fullscreen mode Exit fullscreen mode

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": {}
}


Enter fullscreen mode Exit fullscreen mode

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.

sequence diagram

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.

run after failed 302 redirect

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.

custom code

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;
}


Enter fullscreen mode Exit fullscreen mode

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):

https://script.google.com/macros/s/AKfycbyRspY19D2G92V15YozfF3NTVn32NXE1STz9lQvrg4lmdhbMyYmDgXP0Ec5f92wFjJt7A/exec?string=hello david.wyatt@mail.com&regex=%28%5Ba-zA-Z0-9.-%5D%2B%40%5Ba-zA-Z0-9.-%5D%2B%5C.%5Ba-zA-Z0-9_-%5D%2B%29&flag=g

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.

custom connector regex

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.

forward script
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.

autoreview library

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.

autoreview flow

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)

Collapse
 
balagmadhu profile image
Bala Madhusoodhanan

Entering the multiverse of Apps scripts.. Thanks for sharing

Multiverse

Collapse
 
narrinddhar_52 profile image
Narendra gorantla

wow