Author Archives: Ian Hayse

About Ian Hayse

SharePoint Admin. SharePoint Guy. I have been working with SharePoint nonstop for the past nine years. In those nine years, I've done everything from 'normal' Admin work, development, migrations, training, planning, search, workflow, InfoPath, to crazy fixing of the hands-off SharePoint databases. I'm located in Austin, TX and can always help out with small projects.

Flow Error Unable to process template language expressions for action

Posted on by
Reply 
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.



Flow and SharePoint Online Large Libraries

Posted on by
Reply

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.

Download a File From SharePoint Online Using Python

Posted on by
Reply

How do you download a file from a SharePoint Online library using Python?

Items needed to run the script in this example:
Install and run a simple Python script.
Office365 Rest Python Client library:
https://pypi.org/project/Office365-REST-Python-Client/
SharePoint App Only Client Id and Secret:
Microsoft documentation:
https://docs.microsoft.com/en-us/sharepoint/dev/solution-guidance/security-apponly-azureacs
You can create an app principle that is limited to a single site, list, library, or a combination of them:
https://piyushksingh.com/2018/12/26/register-app-in-sharepoint/

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, take a step back and make sure you are connected to the site. 

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']))

Use Flow to Get Files Created or Modified Today in a SharePoint Library

Posted on by
Reply

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

Uri text from the HTTP call:

_api/search/query?querytext='Path%3Ahttps%3A%2F%2Fsharepointed.sharepoint.com%2Fsites%2Fsitename%2Fsubsite%2Fexports%2F*%20LastModifiedTime%3Dtoday'

Parse JSON schema

{
    "type": "object",
    "properties": {
        "odata.metadata": {
            "type": "string"
        },
        "ElapsedTime": {
            "type": "integer"
        },
        "PrimaryQueryResult": {
            "type": "object",
            "properties": {
                "RelevantResults": {
                    "type": "object",
                    "properties": {
                        "TotalRows": {
                            "type": "integer"
                        },
                        "TotalRowsIncludingDuplicates": {
                            "type": "integer"
                        }
                    }
                }
            }
        }
    }
}

Power BI and SharePoint Person or Group Field

Posted on by
Reply

I created a simple Power BI report to pull data from a SharePoint list and quickly ran into a problem with the Person or Group column type.

Error:
Expression.Error: We cannot convert the value “” to type Table.
Details:
Value=
Type=[Type]

In the report, I was trying to expand a Person or Group column, but not all of the field values were populated.

Here is the fix:

let
    Source = SharePoint.Tables("https://taco.sharepoint.com/sites/food", [Implementation="2.0", ViewMode="All"]),
    #"abcc-b8fe-4b23-be01-abc5f2c3320c" = Source{[Id="abcc-b8fe-4b23-be01-abc5f2c3320c"]}[Items],
    #"Expanded Assigned User2" = Table.TransformColumns(#"abcc-b8fe-4b23-be01-abc5f2c3320c", {{"Assigned User", each if Value.Is(_,type list) then _{0} else [title = "not assigned"], type record}} ),
    #"Expanded Assigned Analyst" = Table.ExpandRecordColumn(#"Expanded Assigned User2", "Assigned User", {"title"}, {"Assigned User.title"})
in
    #"Expanded Assigned User"

Use PowerShell PNP to Create an Alphabetical Directory of Folders in SharePoint

Posted on by
Reply

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?

$siteURL = "https://sharepointed.sharepoint.com/sites/parent/child"

$conn = Connect-PnPOnline -Url $siteURL -Credentials (Get-Credential) -ReturnConnection

try{
(65..(65+25)).ForEach({     
$xy = [char]$_    
Add-PnPFolder -Name $xy -Folder "/mylibrary" -Connection $conn
})
}

catch{ Write-host -f Red "Error:" $_.Exception.Message}

More information about creating folders using ASCII:
https://devblogs.microsoft.com/scripting/use-powershell-and-ascii-to-create-folders-with-letters/

SharePoint Online and Copied Metadata

Posted on by
4

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.

on-prem code example:
$web = $ctx.Web
$web.ParserEnabled=$false
$web.Update()

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! 

#Load SharePoint CSOM Assemblies
Add-Type -Path "C:\Code\DLL\Microsoft.SharePoint.Client.dll"    
Add-Type -Path "C:\Code\DLL\Microsoft.SharePoint.Client.Runtime.dll"    
     
#Config Parameters
$SiteURL= "https://YourSite.sharepoint.com/sites/spdev/testsite"
$ListName="DocLib"
   
#Get Credentials to connect
$Cred = Get-Credential
 
Try {
    #Setup the context
    $Ctx = New-Object Microsoft.SharePoint.Client.ClientContext($SiteURL)
    $Ctx.Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($Cred.UserName,$Cred.Password)
     
    #get list sharepoint online powershell
    $List=$Ctx.Web.Lists.GetByTitle($ListName)
    $Ctx.Load($List)
    $Ctx.ExecuteQuery()    
 
    Write-host "Parser Disabled value " $List.ParserDisabled

    $List.ParserDisabled = $true
    $List.Update()
    $ctx.ExecuteQuery()

}
Catch {
    write-host -f Red "Error:" $_.Exception.Message
}

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!

I’d like to thank Abhijit Rajurkar for helping with this!

Get and Set SharePoint Yes No Field Using Flow

Posted on by
Reply

Simple question: Using a Flow condition, how do you check the value of a SharePoint Yes No field?

My first attempt was to set a variable equal to the SharePoint list value, then check the condition like this:



And, for good reason, this did not work.
My next attempt was to try replacing the condition value with true or false.



And, again, this did not work!
Sooo, what if I convert the true or false to a string?


It worked!



How do you set or update a SharePoint Yes No field using a Flow variable?


Create a string variable, then set the value to true or false.
SharePoint display values:
Yes = true
No = false


Get Files From a Folder Using PNP

Posted on by
Reply

How do you get all of the files from a folder in SharePoint using PowerShell PNP?

  $devConn = Connect-PnPOnline -Url "https://sharepointed.sharepoint.com/sites/siteA/siteB" -Credentials $userCredential -ReturnConnection

    $folderName = "/Shared Documents/myfolder/anotherfolder"

    $folderItems = Get-PnPFolderItem -FolderSiteRelativeUrl $folderName -Connection $devConn

    foreach($item in $folderItems)
    {
        Write-Host $item.Name
    }

    Write-Host "done"

You could also use a search query with a path filter to get the files, just depends on your needs.

Example of using the Get-PnPListItem cmdlet with the FolderServerRelativeUrl parameter. 

$devConn = Connect-PnPOnline -Url "https://sharepointed.sharepoint.com/sites/siteA/siteB" -Credentials $userCredential -ReturnConnection

    $folderName = "/sites/spdev2/bw2/Shared Documents/myfolder/anotherfolder"

    $folderItems = Get-PnPListItem -List "Shared Documents" -FolderServerRelativeUrl $folderName -Connection $devConn 

    foreach($item in $folderItems)
    {
        Write-Host $item
    }