“I’ve got a configuration list with a ton of placeholders, is there a better way to replace them than lots of replace() Power Automate expressions?”
The previous article introduced you to a concept of placeholders to allow users to manage emails content. You define a few placeholders and replace them with dynamic contents in the flow. Users are happy as they can manage the emails, and you’re happy because users don’t touch your flow. But what if there’re more than a few placeholders? You don’t want to have 1 massive expression with 10 replace(…) expressions within each other. You want a better, more universal solution, and that’s what this post is about.
Define the placeholders
As it should be a more universal solution, you’ll need also a universal list of the placeholders. Since they won’t change that often you can keep them in the flow (but you could use another list as well).
Initialise an array variable, e.g. var_placeholders, and define all the placeholders in the format below. Placeholder is the placeholder, SPColumn is the internal name of the SharePoint column, e.g.
[
{
"Placeholder": "##Title##",
"SPColumn": "Title"
},
{
"Placeholder": "##Date##",
"SPColumn": "Date"
}
]
Get the emails from a configuration list
You have the placeholders, now it’s time to get the second part, the email templates. Initialise another two string variables, var_email and var_subject for the email content, returned by the ‘Get items’ action.
Store the subject and text in the two variables. Don’t worry about the ‘Apply to each’, it should return only 1 item anyway.
Replace the placeholders
You have the placeholders, you have the email content in the variables, you can start replacing them.
Since the placeholders are in an array variable, you can loop through them and replace them one by one. Add ‘Apply to each’ action and use the var_placeholders as the input
For each of the placeholders, check if the email text or email subject contain given placeholder. Start with the var_subject.
If it contains the placeholder, replace it. As explained in the JSON parsing article, you can reference dynamic contents using [ ] brackets and question marks. The same logic can be used here – item()?[‘placeholder’] is the currently processed placeholder, item()?[‘SPColumn’] is the related SP column.
Converted into an expression where the data I want to use comes from the trigger action:
replace(variables('var_subject'), item()?['Placeholder'], string(coalesce(triggerOutputs()?['body']?[item()?['SPColumn']],'')))Note: it uses the coalesce(…) expression to replace the placeholder with an empty value in case there’s nothing to replace.
Since Power Automate doesn’t allow variable self reference, add the expression in a ‘Compose’ action and then use it to update the variable. With each loop it’ll replace one of the placeholders in the var_subject.
Repeat the same process also for the var_email variable.
replace(variables('var_email'), item()?['Placeholder'], string(coalesce(triggerOutputs()?['body']?[item()?['SPColumn']],'')))
At the end of the loop you’ll have the email subject and email text in the two variables.
Expression to handle date/time and choice fields
The expression as it is above will work fine for the basic fields, but for more complex fields it might need some improvements. If it’s a date and time, you want to convert it into your local timezone. If it’s a choice field, you want to get only the value, etc.
The example expression below has hardcoded placeholder names to handle the dates (##Date1## and ##Date2##) and choices (##Choice1##, ##Choice2## and ##Choice3##).
if(or(equals(item()?['Placeholder'],'##Date1##'),equals(item()?['Placeholder'],'##Date2##')),
replace(variables('var_subject'), item()?['Placeholder'], if(empty(triggerOutputs()?['body']?[item()?['SPColumn']]),'',convertFromUtc(triggerOutputs()?['body']?[item()?['SPColumn']],'Central Europe Standard Time','dd.MM.yyyy HH:mm'))),
if(or(equals(item()?['Placeholder'],'##Choice1##'),equals(item()?['Placeholder'],'##Choice2##'),equals(item()?['Placeholder'],'##Choice3##')),
replace(variables('var_subject'), item()?['Placeholder'], coalesce(triggerOutputs()?['body']?[item()?['SPColumn']]?['value'],'')),
replace(variables('var_subject'), item()?['Placeholder'], string(coalesce(triggerOutputs()?['body']?[item()?['SPColumn']],''))))
)Summary
Replacing placeholders is another use of the “configuration list” approach. Instead of lots of replace expressions you can have a loop in your Power Automate flow, one ‘Apply to each’ that’ll replace all the placeholders one by one. Check the email configuration for each of the placeholders and replace them if needed.
The most complicated part is the expression to handle various column types, but if you stick to the basic columns it’s very straightforward.
Tom,
The idea of using placeholders intrigues me greatly. I understand that you’re using them to replace later with dynamic expressions, but is there a way to use placeholders with static information as well—like to change the fundamentals of a connection (like Excel’s ‘Get a row’ or Google’s ‘Get an event’ or ‘Update an event’)?
I’ve designed three Power Automate flows to keep my Google Calendar synced with a SharePoint (online) calendar (list) based on Alex Matulich’s work here: https://www.nablu.com/2020/03/syncing-office-365-to-google-calendar.html. While Alex’s employer uses Outlook, mine uses SharePoint.
The first flow is triggered when a SharePoint item (calendar event) is created or modified, and if that event pertains to me, it either adds a corresponding new Google Calendar event or modifies one that currently exists. If the event already existed (was modified as opposed to being a new event) the flow also looks to see that it still pertains to me. If not, it deletes the event and the table row. I use an excel table just as Alex Matulich (above) describes to keep these events synced—but my flows only flow in one direction (no data goes back into the SharePoint calendar [which serves as the calendar of record]).
The second flow looks daily for any SharePoint events (their IDs) in the Excel table (which once pertained to me) that no longer exist in the SharePoint calendar (in case somebody deleted an event from the SharePoint calendar). If the table row exists (but the SharePoint calendar event does not), the corresponding Google Calendar event and the table row are deleted.
The third flow is designed to pre-populate the Google Calendar with all my events that already exist in SharePoint—and this really flow was only run once (once all my testing was satisfactory).
This system of flows works great to keep my personal Google Calendar synced with my own SharePoint events, but I am responsible to schedule the events of twelve other employees into that SharePoint calendar. Ideally, I’d like to provide each of them with a Google calendar of their own (which we each use to reference from our mobile devices to establish or accept personal appointments around our working schedules). Currently I keep these twelve Google Calendars updated just once a week. I have a spreadsheet that helps do the conversions from SharePoint to Google Calendar, but the process takes about an hour each week.
So, I’m wondering if I need 3 flows for every employee or is there a way I can use placeholders to differentiate which Excel table or which Google Calendar to work with—based on the DisplayName of the employee which is passed through the flows via an ‘Apply to each’ step. I realize that this is static information, so I assume if it’s possible, I will have to do it differently than shown in this article. The real question is whether it is possible.
Thank you, and Happy Holidays!
-Mark
Hello Mark,
I never worked with Google Calendar so I don’t really know how it works, but in general if it’s some configuration in an action you can use configuration to store the information. But if it’s a connector, e.g. if the Google action uses a separate connection for each of the users (under 3 dots on the action -> My connections) then you can’t define the connections in the configuration.
Based on that I’d guess that you’ll need the same flows for each user as the Google action probably needs their personal connection.