A lengthy career working as a SharePoint developer, admin, and architect. I'm now working in the Power Platform and Azure spaces.
What happened to InfoPath?
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.
What happened to the Web URL link in Power BI? With the latest update to Power BI Desktop, Microsoft made some UI changes. If you are looking for the Web URL option, make sure your data grid / table is selected, click the Format visual button, expand Cell elements, at the bottom you’ll find Web URL. Switch it to On and you’ll be ready to go!
Below are all the steps required to add your company holidays to your Outlook calendar. This is a simple approach that can easily be updated to pull the holidays and dates from other data sources like Exel, SharePoint, Dataverse…
Overview of the flow that will be created:
All of the holidays (events) will be stored in an array using a compose action.
After that, a parse JSON event will be used to make the events available in the apply to each action. Note: after the parse JSON action is added to the flow, click the generate from sample button and paste in the holidays JSON from the step above.
Next, add an apply to each action to the flow. In the action, the output box will hold the body of the parse JSON action.
The convert time zone action is used to standardize the date format. For the base time use the Date field from the parse JSON action.
The last item that needs to be added is the outlook create event action. Note: for the End time property a formula is used: addDays(body(‘Convert_time_zone’),1)
Save the flow and run it.
Edit/Update – For the End time value, you can input this expression: addHours(body(‘Convert_time_zone’),23) . If you set Is all day event to Yes, you’ll need a timespan that covers most if not all of the day. Also, if you want to set Is reminder on, it must be input as minutes, so 17 hours would be 1020 minutes.
Unable to process template language expressions for action 'Apply_to_each_sftp_file' at line '1' and column '30517': 'The template language expression 'body('List_files_in_folder')?['body']' cannot be evaluated because property 'body' cannot be selected. Array elements can only be selected using an integer index. Please see https://aka.ms/logicexpressions for usage details.'.
From one day to the next one of my flows stopped working with the error above. The flow is super simple: Get files from an SFTP folder Loop through the files Copy file to another location Delete file
Looking at the apply to each step I noticed body element was referenced. This might have been related to how I set up the loop or the autogenerated action.
Action the flow was unhappy with: Updated the flow and it started working: Note how the ?[‘body’] element is missing from the second picture.
Fix? Created a new Apply to each loop by first setting a variable to the name of the file I’m looping on, then added my other actions. There might be a better way to fix this, but for now, this is what works.
Using Flow to get or check for files in a large SharePoint library can be a little tricky. If you are sure your library will always stay under 5,000 items the Get Files (properties only) Flow action is a quick n’ simple approach to use. When your library crosses over the mythical 5k mark or somewhere in that neighborhood, the Get Files action will fail to return results without warning. What I’m outlining below are other options when working with large libraries.
Setup:
Option 1: Get Files using an Odata filter query downside: only use this in small libraries
Result:
Option 2: use the SharePoint API downside: the lack of transparency from Microsoft related to how often data is crawled.
Result:
Option 3: use the SharePoint API along with a filter action on the library. This option does require that you have metadata set up on the library to filter on and there is not a wildcard / contains option. The filtered value needs to be exact. downside: you will need to set up your metadata ahead of time or create it after the fact then backfill.
Result:
The more I learn about Flow and SharePoint Online, the more I’m starting to like Option 3 when doing a lookup type of search. SharePoint Search is an extremely powerful tool if the search index is fresh.
from office365.runtime.auth.authentication_context import AuthenticationContext
from office365.sharepoint.client_context import ClientContext
from office365.sharepoint.files.file import File
app_settings = {
'url': 'https://YOURtenant.sharepoint.com/sites/somesite/',
'client_id': '12344-abcd-efgh-1234-1a2d12a21a2121a',
'client_secret': 'Oamytacohungry234343224534543=',
}
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'])
ctx = ClientContext(app_settings['url'], context_auth)
web = ctx.web
ctx.load(web)
ctx.execute_query()
response = File.open_binary(ctx, "/Shared Documents/Invoice.pdf")
with open("./Invoice.pdf", "wb") as local_file:
local_file.write(response.content)
If the above script does not work, step back and ensure you are connected to the site. The following script connects to a site and outputs its title. This is useful to validate that a site connection can be made.
from office365.runtime.auth.authentication_context import AuthenticationContext
from office365.sharepoint.client_context import ClientContext
from office365.sharepoint.files.file import File
app_settings = {
'url': 'https://YOURtenant.sharepoint.com/sites/somesite/',
'client_id': '12344-abcd-efgh-1234-1a2d12a21a2121a',
'client_secret': 'Oamytacohungry234343224534543=',
}
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'])
ctx = ClientContext(app_settings['url'], context_auth)
web = ctx.web
ctx.load(web)
ctx.execute_query()
print("Site title: {0}".format(web.properties['Title']))
You can also use a certificate and thumbprint to connect to SPO via an Azure App registration.
SharePlum connection example using a username and password to connect to SharePoint Online. More details about SharePlum can be found here: https://github.com/jasonrollins/shareplum
from shareplum import Site
from shareplum import Office365
sharepoint_url = 'https://YOURtenant.sharepoint.com/sites/spdev'
username = 'You@YourDomain.com'
password = 'Password'
authcookie = Office365('https://YOURtenant.sharepoint.com',
username=username,
password=password).GetCookies()
site = Site('https://YOURtenant.sharepoint.com/sites/DEV/',
authcookie=authcookie)
sp_list = site.List('Your List')
data = sp_list.GetListItems('All Items', row_limit=200)
If you get this error, you won’t be able to connect with a username and password, and you’ll need to use an App Password.
File “C:\Python311\Lib\site-packages\shareplum\office365.py”, line 80, in get_security_token raise Exception(‘Error authenticating against Office 365. Error from Office 365:’, message[0].text) Exception: (‘Error authenticating against Office 365. Error from Office 365:’, “AADSTS50076: Due to a configuration change made by your administrator, or because you moved to a new location, you must use multi-factor authentication to access ”.”)
Scenario: Each day I have a couple of Azure Runbooks export SharePoint list items and upload them to a SharePoint library. If one of the Runbooks fails, I needed to send an email alert that something went wrong.
Basic logic: If files created today in SharePoint <> X, send an email.
The easy solution would have been to loop through the files, check their created date, increment a variable, then make a condition statement.
More-better way: Run flow daily at 6:00 PM Send an HTTP request to SharePoint to get files Parse the response Condition statement — if true, send an email
Edit – If you want to search for files created before or after a date, you can adjust the API like this: and created %3E 2021-12-12T19:07:51.0000000Z This will fetch any files created after Dec 12th 2021. The unicode for greater than is %3E and less than is %3C
I’m in the process of reorganizing a document library and wanted to store all of the documents in alphabetical folders. Yes, I’m using metadata, but I’ve passed the magic 5,000 item threshold and want to rearrange the library and leverage a rich search experience.
So, using PowerShell, how do you create a bunch of folders going from A to Z?
Out of the box, if you upload a document to SharePoint and tag metadata to it, that data is attached to the document properties. This is the case for most all column types you create in a library, and on random occasions, Document IDs are copied.
If you are working with SharePoint on-prem you can disable this functionality at the SPWeb level, but that’s not the case with SharePoint Online.
Example of what I was seeing in my sample library: Create a new library titled DocLib Added a text column titled TacoFiller Uploaded an Excel file, then populate TacoFiller with the word Beans
Downloaded the file and look at the properties. File –> Info –> All Properties (Excel o365) Notice that property TacoFiller is set to Beans.
Create a local copy of the file, then upload it to DocLib.
SharePoint has read the TacoFiller property from the Excel file and applied the data to the column in the library.
Short of doing a document migration, I have NO idea why you’d want this enabled out of the box. If anything, this should be disabled, then allow admins to enable as needed. /rant
TL;DR / how-to disable this functionality: First, you can only disable this at the library level!
A couple of notes about the script. You can download the SharePoint DLLs from Microsoft: link In my tenant, using Get-Credential requires that I use my company email address and an App Password. This may/not be the case with your tenant. App Password info: link
And… after running the script, upload another copy of the Excel file to the library.
The TacoFiller property is no longer being extracted from the Excel file!
