Use Python to Query a LARGE SharePoint list.

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.

Create Approvals That NEVER Expire

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.

Trigger Conditions

@contains(triggerBody()?['msdyn_flow_approval_itemlink'],'https://www.sharepointed.com/stuff/')

@not(equals(triggerBody()?['msdyn_flow_approval_result'], null))

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.


Expression: last(split(triggerBody()?[‘msdyn_flow_approval_details’],’**SPItemID:** ‘))




YES, this is a lot, but the general idea is simple; create an approval and handle the response.

Download a File From SharePoint Online Using Python

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

Items needed to run the script in this example:
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

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

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

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/

Get and Set SharePoint Yes No Field Using Flow

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

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
    }
    

Flow Trigger On SharePoint Item Version

How do you run a Flow on a specific version SharePoint item version?

Create a Flow, then navigate into the Settings of the first step. Scroll down to Trigger Conditions and enter the following:

@equals(float(triggerBody()?['{VersionNumber}']),1.0)

Save the Flow and run a test.
The Flow should only process items / documents where the version is equal to 1.0.

PowerAutomate SharePoint Server relative urls must start with SPWeb.ServerRelativeUrl

{
    "inputs": {
        "variables": [
            {
                "name": "varURI-String",
                "type": "string",
                "value": "/_api/web/GetFolderByServerRelativeUrl('@{triggerBody()?['FolderPath']}')/ListItemAllFields/breakroleinheritance(copyRoleAssignments=false, clearSubscopes=true)"
            }
        ]
    }
}

Using a Power Automate Flow to break inheritance on a folder and this error was being returned. The issue turned out to be the path I was trying to use for the folder.

This did not work: LibraryName/Folder
This DID work: /sites/ParentSite/SubSite/LibraryName/Folder’