When you move from a SharePoint on-prem environment to SharePoint Online, you lose the server-side environment you’d normally use to run PowerShell scripts or tasks to interact with SharePoint. In my opinion, and please correct me if I’m wrong, the closest thing to a server-side environment in a cloud environment is Azure Runbooks or Azure Function Apps. I went with Azure Runbooks due to its ability to handle long-running tasks.
The error I recently encountered in my runbook was: runbook name error: Token request failed..Exception . At first, I thought there might be something wrong with the way I was connecting to Keyvault, but that wasn’t it. Next was my connection to SharePoint, this is handled using a SharePoint-generated client ID and secret. Oddly enough, I had just updated this a few months back, so it wasn’t an obvious candidate for a failure point.
I went to my target SharePoint site, created a new set of credentials using siteName/_layouts/15/AppRegNew.aspx and siteName/_layouts/15/appinv.aspx. After creating the credentials, I went back to the runbook and plugged them in, and it worked!
Long story short, if you get this error: Token request failed..Exception try creating a new client ID and secret and see if it helps clear things up.
This post is a mental brain dump of the issues I encountered when configuring Azure B2C in a new Power App Portal or Power Pages environment.
Error when trying to login via B2C: .powerappsportals.com/signin-aad-b2c_1 Page not found Open the Site Settings, filter the page to only display settings for the Website you are working with, then search for: authentication/openidconnect/
You will see all of the items related to the B2C items you set up on the front end. I’ve noticed, for whatever reason, that the settings don’t always sync with the backend.
To fix the Page not found error I switched Authentication/OpenIdConnect/AAD-B2C_1/RegistrationEnabled to true . Open the portal in a new private browser and try logging in again.
Power App Portal authentication settings not syncing. If you create a new B2C user flow and want to update your portal settings, save yourself time and open your portal site settings, search for authentication/openidconnect/ and update the setting there. For some reason, updating portal settings in the settings page don’t always sync to the backend.
Failed to create/update site setting for default provider. If you get this error, try setting a different provider as the default, then try your original selection again.
If you log in via B2C and see this, check the setting in the screenshot, and purge your cache. Registration is disabled Invalid sign-in attempt.
When querying a SharePoint list that has more than 5,000 items, you’ll likely receive an error like this:
This view cannot be displayed because it exceeds the list view threshold (5000 items) enforced by the administrator.
Microsoft.SharePoint.SPQueryThrottledException', 'The attempted operation is prohibited because it exceeds the list view threshold.', "500 Server Error: Internal Server Error for url
Or, your query will only return the default 100 items. To get around this, pagination can be used to query the list and return ALL of the items. Example: all_items = list_to_export.items.paged(1000).get().execute_query()
Full script using VS Code:
from office365.runtime.auth.authentication_context import AuthenticationContext
from office365.sharepoint.client_context import ClientContext
app_settings = {
'url': 'https://taco.sharepoint.com/sites/queso/',
'client_id': 'ID here',
'client_secret': 'shhhh its a secret',
}
context_auth = AuthenticationContext(url=app_settings['url'])
context_auth.acquire_token_for_app(client_id=app_settings['client_id'], client_secret=app_settings['client_secret'])
#connect to the site
ctx = ClientContext(app_settings['url'], context_auth)
ctx.execute_query()
#get the target list
list_title = "List of Tacos"
list_to_export = ctx.web.lists.get_by_title(list_title)
#get all of the list items
all_items = list_to_export.items.paged(1000).get().execute_query()
list_items = [item for item in all_items]
print("Item count: {0}".format(len(list_items)))
The example above connects to a SharePoint site using a client ID and secret, then queries the list. Again, the key here is using pagination (paged). You can adjust the page size to better fit your needs, but be sure to leave it under 5,000, or you will be back to square one.
This has bugged me for a long time, and I wasn’t sure how to get around it. I was tasked with creating a Power BI report using data from a reasonably big data set, but the report only displayed a really small part of it. The source is in the neighborhood of 10,000,000 rows, and my report displayed roughly 100,000. Why try to direct query, import, or incrementally refresh all of the data when such a small part of the data is needed? I was looking for a way to filter the data set before you add the applied steps in the query editor. Something like: Select * from Orders where Status = Open
The answer I was looking for is Native Query, and I’m not sure how I completely failed to learn about this until now.
For this example, I’m using the Northwind Traders orders dataverse table. Connect to the dataverse environment, select the orders table, then open the Advanced Editor window. Change the query to this:
let
Source = CommonDataService.Database("taco.crm.dynamics.com"),
superQuery = Value.NativeQuery(Source, "Select * from nwind_orders")
in
superQuery
All I’m doing here is checking to see if the newly formed query is working. Next, right-click on the step where you created the Native Query in the Applied Steps window and select view Native Query. Update the query only to show New orders. This can be done by adding a Where clause:
Select * From nwind_orders Where nwind_orderstatusidname = 'New'
Click Ok, and the query should refresh, only showing new orders.
let
Source = CommonDataService.Database("taco.crm.dynamics.com"),
superQuery = Value.NativeQuery(Source, "Select * From nwind_orders Where nwind_orderstatusidname = 'New'")
in
superQuery
That’s all it takes to filter the data set before transforming it! Be sure to check the linked documentation and take a look at query folding.
This post will be updated to link to a future post that outlines all the different Power App Portals areas. If you have spent more than a few minutes in Portals, you will quickly notice that there is no single place to update or change settings.
How do you purge the cache for a portal? Navigate to the Power Platform admin center https://admin.powerplatform.microsoft.com/ Click on Environments and select your environment Select the portal, then click Purge Cache in the ribbon
edit The purge cache button won’t always pop up the modal asking if you want to purge the cache. If you visit this page, select your portal site, click the Purge Cache button, and the modal will appear.
If you are reading this, you likely ran into an issue where you created an approval flow, but it expired before the recipient had time to approve or reject it. The timeout for an approval or any flow is thirty days; then, it stops running. Yes, there are some clever workaround to alert if the flow times out, but who wants to mess with that?
The approach I took to solve this was to leverage some of the existing tooling, then add to them. When you create an approval, a row is created in the dataverse Approval table. As we all know, a flow is trigger-based, so why not create one that simply monitors the Approval table, then handles things from there?
At a high level, here is the basic approach.
Start by creating a simple flow that initiates an approval, then run it. In my example, note the value in the Item Link field; this will come into play later. Next, navigate to make.powerapps.com, expand the Dataverse section, and click on Tables. After the page loads, click the All link under Tables, then search for approval. If you search for approval and do not get a result, make sure you click the All link.
Open the Approval table; in it, you will see your approval, possibly more depending on how old your environment is or if many people in your company are using approvals. When looking at the data, the takeaway is what is stored in the table and what can be used in the flow that handles the outcome of the approval. In my case, using the Item Link field is key to handling the approval response. With it, I can filter the value and know if I need to take action on the item or not.
When creating the flow that responds to the approval, you can filter it at the design level or in the trigger settings. I went with the trigger setting due to the number of approvals that could be firing across my organization in our default tenant. Why do you need to filter it? Just assume other approvals might be writing to the same dataverse table.
The above conditions filter the value I passed in the create approval flow (Item Link) and if the item has been approved or rejected.
Here is an overview of the flow that handles the outcome of the approval. I mixed dataverse connector types due to an issue with the trigger condition not working with the green dataverse connector. In the Expand Query field, I used the Fetch XML builder to query over to the Approval Response table to get the comment field; not used in the example, but nonetheless, it’s there. From the Get a row by ID action, the response of the approval is available to use to handle the outcome (Result) of the approval.
To my knowledge, there is no reason why you can’t create an approval that is active for months, if not years.
Notes: 1) You can access and review the approval records using PowerBI, Flow, Access, ___ 2) You can bulks update the records using PowerShell, Flow, Access (be real careful), __ 3) You can pass items in the Details field, then parse them out when handling the approval. Here is one simple example where I’m passing a SharePoint item ID from the approval and parsing it in the response flow:
Response flow compose statement that parses the Details field.
Over the years, some updates to Flow have been better than others, and others, not so much. If memory serves, the send an email action would use dynamic hyperlinks without much work, but something went sideways with one of the updates causing dynamic hyperlinks not to work as you’d want.
Here is a basic example of including a hyperlink in an outgoing email; further down the page, I’ll provide a more realistitc example.
In this example, I setup the Flow to trigger when an item is added to a SharePoint library. The key thing to note in this example is the value of the varHyerplink variable; note the double quotes around the link to item.
Using the SharePoint HTTP flow action to update a person or group field, I kept getting this error:
A 'PrimitiveValue' node with non-null value was found when trying to read the value of a navigation property; however, a 'StartArray' node, a 'StartObject' node, or a 'PrimitiveValue' node with null value was expected.
The field I was attempting to update is named Submitted By, with an internal name of Submitted_x0020_By. Each time I tried to update the field I was seeing the error noted above. It wasn’t until I looked at one of my previous flow runs did I notice what the issue was. It turns out, that the field name I should be using is Submitted_x0020_ById.
Update flow:
How do you update a Person field if the field allows for multiple selections? The example below will update the field with two different user values, but clearly, this could be extended to be more dynamic.
I was in the middle of streamlining an old flow and needed to compare two dates and times. To simplify things, I opted to use the Convert time zone flow action, but it gave me an error that seemed a little odd.
Unable to process template language expressions for action ‘Convert_time_zone’ at line ‘0’ and column ‘0’: ‘The template language function ‘convert timezone’ expects its fourth parameter to be a string that contains a date time format. The provided value is of type ‘Null’. Please see https://aka.ms/logicexpressions#ConvertTimeZone for usage details.’.
Notice that I did not select an option for the Format string field, this is required, but there’s not a red * next to the field.
Select an option for that field, and the universe will be back in alignment.
