SharePoint Online - Angelo Gulisano

Power Automate – Send email from SharePoint Rest Api

Posted on 02/11/2024by Angelo

Hello, today I want to share with you a method to send emails with Power Automate by using a REST API available in SharePoint. There is a method called “sendMail” in SP.Utilities.Utility that can be used to send emails to users.

Some attention points:

Recipients must be authenticated SharePoint users.
Attachments cannot be included through this API method (You can include inline attachments likes images uploaded in the same SharePoint domain).
It doesn’t send emails to distribution groups or lists.
“To” field must be an array.

Build flow

In this article, I’m going to create a cloud flow that can be run manually. Of course, you can use any trigger that you need, or you can incorporate a SharePoint HTTP request to send an email wherever necessary.

To send mail you should simply add “Send an HTTP request to SharePoint” connector:

So, you must use there parameters:

Site address: SharePoint to use to make call
Method: POST
URI: /_api/SP.Utilities.Utility.SendEmail
Headers:

{
  "accept": "application/json;odata=verbose",
  "content-type": "application/json;odata=verbose"
}

Body:

{  
        'properties': {  
            '__metadata': {  
                'type': 'SP.Utilities.EmailProperties'  
            },  
  
            'To': {  
                'results':[ 'email address']
            },  
            'Body': '<p>Hello Angelo,<br>  
Mail sent from SharePoint Rest Api....<br>
</p>',
            'Subject': 'Say hello!'  
        }  
}

In the body section, you should add “TO” as an object with a result property that accepts an array of email addresses. Similarly, if you want to add “CC” or “BCC“, you can also include “Body“, which can contain HTML, and “Subject” as a string.

This is result:

Considerations

Some people ask me, “Why should we use this option?” I wrote this article inspired by a recent training course on Power Automate that I delivered for a customer. In this instance, my client needed to send emails, but we didn’t have the option to use a service account to send emails with the standard Outlook 365 connector, and he didn’t want to send them from his email address.

I hope these information can help you.

Contact me for questions! Have a nice day!

Microsoft 365 SharePoint SharePoint Online

SharePoint – Team Site without Microsoft 365 Group

Posted on 09/13/2022by Angelo

Hello, here really simple post to explain how to create a SharePoint site without Microsoft 365 group associated.

Create Site

Follow there instructions:

Clicck on App Launcher and then on “Admin”. Then click on “Expand all” , SharePoint and “Active sites”

Then we can create site:

Click on Create and “Other options”:

Now we can create our site:

Considerations

Why we should create site with no M365 group? If we don’t need Outlook, planner or Teams associated but just need a SharePoint Team Site. Just to remind…to do that, we should be are SharePoint Administrator.

Contact me for questions! Have a nice day!

Microsoft 365 SharePoint Online

SPO – JSON form custom formatting

Posted on 07/27/2022by Angelo

Hello, Today we spoke (write 🙂 )about SharePoint list form customization. Basically, we have two way to customize existing form:

JSON Formatting
Edit form with Power Apps

We try to Explain how to customize form formatting with Json.

Format Form

Go to your list, and click on New, click on the top right Edit form icon, e click on “Configure layout”.

Basically We can write some JSON to format these sections:

Header
Body
Footer

Header customization

Choose “Header”. How can we customize it?

We can start from official MS Docs , copy header JSON code and try it:

{
    "elmType": "div",
    "attributes": {
        "class": "ms-borderColor-neutralTertiary"
    },
    "style": {
        "width": "99%",
        "border-top-width": "0px",
        "border-bottom-width": "1px",
        "border-left-width": "0px",
        "border-right-width": "0px",
        "border-style": "solid",
        "margin-bottom": "16px"
    },
    "children": [
        {
            "elmType": "div",
            "style": {
                "display": "flex",
                "box-sizing": "border-box",
                "align-items": "center"
            },
            "children": [
                {
                    "elmType": "div",
                    "attributes": {
                        "iconName": "Group",
                        "class": "ms-fontSize-42 ms-fontWeight-regular ms-fontColor-themePrimary",
                        "title": "Details"
                    },
                    "style": {
                        "flex": "none",
                        "padding": "0px",
                        "padding-left": "0px",
                        "height": "36px"
                    }
                }
            ]
        },
        {
            "elmType": "div",
            "attributes": {
                "class": "ms-fontColor-neutralSecondary ms-fontWeight-bold ms-fontSize-24"
            },
            "style": {
                "box-sizing": "border-box",
                "width": "100%",
                "text-align": "left",
                "padding": "21px 12px",
                "overflow": "hidden"
            },
            "children": [
                {
                    "elmType": "div",
                    "txtContent": "='Contact details for ' + [$Title]"
                }
            ]
        }
    ]
}

So , now we can see result.

Before:

After:

How to edit?

Basically, our JSON going to apply style to these section:

{
    "elmType": "div",
    "attributes": {
        "class": "ms-borderColor-neutralTertiary"
    },
    "style": {
        "width": "99%",
        "border-top-width": "0px",
        "border-bottom-width": "1px",
        "border-left-width": "0px",
        "border-right-width": "0px",
        "border-style": "solid",
        "margin-bottom": "16px"
    },

Element:

 "elmType": "div",
    "attributes": {
        "class": "ms-borderColor-neutralTertiary"
    },

Style

  },
    "style": {
        "width": "99%",
        "border-top-width": "0px",
        "border-bottom-width": "1px",
        "border-left-width": "0px",
        "border-right-width": "0px",
        "border-style": "solid",
        "margin-bottom": "16px"
    },

Some people asking me, “How can I find witch section I’m modifing? I’m not expert” , an Idea is : Add background color red to style to indentify section:

Not beautiful to see, but simple to identify 🙂

Body customization

To customize Body, you should simply define Sections and fields, in my case:

{
    "sections": [
        {
            "displayname": "Basic information",
            "fields": [
                "Title",
                "Description"
            ]
        },
        {
            "displayname": "Worfkflow status",
            "fields": [
                "Step"
            ]
        },
        {
            "displayname": "Other",
            "fields": [
                "Attachments"
            ]
        }
    ]
}

Footer Customization

Now the last part, footer. Similar to header, we can write JSON for a part to customize

{
    "elmType": "div",
    "style": {
        "width": "100%",
        "text-align": "left",
        "overflow": "hidden",
        "border-top-width": "1px"
    },
    "children": [
        {
            "elmType": "div",
            "style": {
                "width": "100%",
                "padding-top": "10px",
                "height": "24px"
            },
            "children": [
                {
                    "elmType": "a",
                    "txtContent": "='This is footer section! ' + [$Title]",
                    "attributes": {
                        "target": "_blank",
                        "href": "='https://aka.ms/contacts?email=' + [$Title]",
                        "class": "ms-fontColor-themePrimary ms-borderColor-themePrimary ms-fontWeight-semibold ms-fontSize-m ms-fontColor-neutralSecondary–hover ms-bgColor-themeLight–hover"
                    }
                }
            ]
        }
    ]
}

As you can see , you can also use field of the record , in our example title: [$Title].

References & info

Syntax

https://docs.microsoft.com/en-us/sharepoint/dev/declarative-customization/formatting-syntax-reference

Advanced formatting concepts

https://docs.microsoft.com/en-us/sharepoint/dev/declarative-customization/formatting-advanced

Where you can find icons name

https://developer.microsoft.com/en-us/fluentui#/styles/web/icons

Column formatting

https://docs.microsoft.com/en-us/sharepoint/dev/declarative-customization/column-formatting#creating-custom-json

Considerations

This is one way. Another option is modify form with Power Apps, let me know if you want an article to understand how.

Contact me for questions! Have a nice day!

Microsoft 365 Power Apps SharePoint Online

Power Apps – Improve performance

Posted on 05/21/2022by Angelo

Hello, Today I want to explain how to improve performance of yours Canvas App. Any comments, integrations or suggestions are welcome 🙂

Cache local data

Does your data chanche during session of your app? If you reply “No”, you should store your data into local variable and get record from it, instead always ask to database.

ClearCollect(CollProject,Projects_1)

PS: Keep mind delegation problem.

ClearCollect Function more informations here.

You should also cache for user data. Example, if you you email of connected user:

User().Email

Consider:

Set(UserEmail,User().Email)

Optimize OnStart Event – Concurrent

If you create and load collection on “OnStart” event (or on visibile), consider use of Concurrent Function. People tipically write this:

ClearCollect(CollProject,Projects_1);
ClearCollect(CollManager,Manager)

In this case, formula loads each tables one at a time. If you want optimize, you should write this:

Concurrent(
ClearCollect(CollProject,Projects_1),
ClearCollect(CollManager,Manager))

Now your app fetches the tables in parallel.

Use delegations

You shouden’t retrieve all data and then filter locally. Delegate data processing to data source (If is possible). See connectors documentation to learn about delegable formula.

For SharePoint check this link.

For SQL Server:

Use Indexes Column

Simple, but people sometimes forgot to set indexes on column. For SQL Server and SharePoint, check wich columns you’re using to filter, and set Index on these columns.

How to set index on SharePoint columns.

How to set index on SQL Server and Azure SQL.

Replace Defaults() with Variable

When you want to create new record in your table you , tipically you use Default() function. When you use it, there is a call to your datasource …

Defaults(Projects_1)

And Idea is store result of this function on variable, and use it instead call Default() every time:

Set(
    newProject,
    Defaults(Projects_1)
);

Debug – Monitoring Time

If you want to understand what happens “behind the scenes”, you should start a debugging session. From Power Apps portal, select your app and click Monitor:

Now you can understand durations and respons size of your function to prevent performance issue.

Considerations

Check this official link for other tips .I hope this post was usefull for you.Leave me comment if you has appreciate it.

Contact me for questions! Have a nice day!

SharePoint SharePoint Online

SharePoint Online – Get alert on documents changes

Posted on 05/14/2022by Angelo

Hello, This is one of a series of post where I want to help you to improve your experience with SharePoint Online. This article,I’ll explane how you can ask to SPO to inform you whene anyone makes change to your documents.

Document Library

We have SharePoint site, and a document library called “Docs” where we collaborate with our collegues (PS: Do you know Co authoring? See this link)

SharePoint Document Librart — Simple SharePoint Document Library

Basically We don’t want go every day on our document library to check if our collegues has make change on documents…So how can SharePoint help us?

Alerting

Simple ,As I usually try to explain…:-) One way is configure an Alert on Document Library (Or Lists). Go to your DL, and looking for “Alert Me” on Ribbon:

SharePoint Alert me option — Document Library – Alert Me

Next Page page ask us a series of parameters to configure Alert. In Order:

Title: Title of the Alert (also included in subject mail).
Send Alert To: People to receives alert.
Delivery method: Choose if you want receive mail or SMS.ù
Change type: Very important. The changes typology that trigger the alert. For Example Item modified, or Deleted.
Send Alerts for These Changes: Useful to restrict alert, for example you can get alert only for document created by you.
When to Send Alerts: How frequently you want to be alerted? My suggestion , generally, is Daily recap to limits numers of mails.

SharePoint Alert me page — Configure Alert Options

Considerations

This is really simple way. Another option is create a Power Automate flow with appropriate trigger. I hope this is usefull for you.

Contact me for questions! Have a nice day!

Lists Microsoft 365 SharePoint Online

Microsoft Lists in 1 Minute

Posted on 10/31/2021by Angelo

What is Microsoft List

Let’s talk about Microsoft List. You can see “List” and others Mmicrosoft 365 App’s. You can use this awesome tool to create list to track informations (Assets, invetory, customers, issues ecc) and collaborate with your team members. If you know SharePoint List’s, this is appear very familiar.

Create new list

Click on the top-left corner:

App “Lists” and others Mmicrosoft 365 Apps…

Now you can View and Open your recent Lists or create New List. Click on button “+ New List”.

Now you have 4 choises:

“Blank list” to create new list from scratch (we do this for this tutorial…)
“Form Excel” to create new list , starting from excel file. It’s good starting point if you have some excel files and want to use List instead of them.
“From existing list” to create list , starting from another list (also from list create inside SharePoint site).
“Templates” if you click on one on template, you can create list starting from it.

Click on “Blank list“, add list Name, description, color, icon, choose where do you want to save your list (In “My List” or one of your SharePoint sites) and click create.

Customize your list

Now we have our Customer list and we can start to use it. First of all, we need to create some columns. Click on “+ Add Column”

Now you must choose type of date you want to store in this column. Don’t worry if you don’t know what it is it…Ask yourself: “what I want to store in this column? Name of customer? Descriptions? Logo? if the customer is active or now?” This is the key, understand what you need to save. Let’s see common type of column:

Single line of text: Simply “text”: Use it for Customer Name or short description.
Multiple line of text.:Use it when you want to store more the one text rows…for example descriptions, notes etc.
Location: When you neet to track “where”.
Number: No description need 🙂
Yes/no: Use it if you want to track simply yes or no (Boolean) value. Fors example “is valid customer?” or “Is active customer?”
Person:To save People or group in your Microsoft 365 Tenant.
Date and Time: To store date, for example “Expiration date”
Choice: To choice value inside a list of value. For example “New customer, Old customer, Inactive”. Or to save status: “Draft, Approved, Rejected”
Hyperlink: To save a link.
Currency: To store currency value…
Image: To store an image such as logo

Keep mind, you have by default “Title” field , in your list. You can rename it.

Now I rename “Title” with “Customer”, and create “Headquarters” and “Acquisitiond date”:

Use your list

Now your list should appear like this:

Now you can click on “New” to add one record a time, or click on “Edit in grid view” to edit it like’s Excel file:

Keep mind, remember to click on “Exit grid view” to save.

I hope it help you..let me know with comment if you want another article on this argumeent. For example how to collaborate with colleague on this list, or how to create rules to send an email when people do something to this list or something else.

Microsoft 365 Power Automate SharePoint Online

Power Automate – SharePoint Get Items – Filter

Posted on 09/14/2021by Angelo

Power Automate – SharePoint Get Filtered Items

With this article I want to explain how we can get items with Power Automate (From SharePoint Online) and Filter them.

We have:

SharePoint list’s called “Offices” ,with some data:

Excel file where we save filtered items. I create and Excel file with same column of list, and store it in OneDrive. Keep mind, you must format sheet as a Table if you want to use it in Power Automate to add rows.

Power Automate flow who get data and put it into Excel File. I used “Get Items” connector to retrieve data from SharePoint list.

Get Items

No Query

We can get data with no query. In this case, you mus put only “Site Adress” and “List Name Parameters”…keep mind “Top Count” parameter and it returs “All” items. I do not suggest this approach (And also Power Automate, who show you a Warning 🙂 )

Filter Query

Best approach, is write a Filter Query to retrieve only items you need. Basically you should write a formula like this: fieldname operation value.

As fieldName you must use iternal field name of SharePoint (Write me a comment if you need a post on this argoument).
Operation available are:
- eq (Equal to)
- lt (Less Than)
- gt (Greater Than)
- ge (Greater than or Equal to)
- le (Less than or Equal to)
- ne (No Equal to)
Value: value to find, date and string must be inside ‘

We can use also logical operator like “and” and “or”

Let’s see some example (I show query and result on Excel sheet):

Filter “eq”

Result:

Filter “lt”

Result:

Filter Query with Functions

You can also use functions for your filter query.

endswith
startswith
substringof
….

The syntax is: functions(fieldName, value)

An Example:

Result:

I hope it help you

PowerShell SharePoint Online

Connect-SPOService-is-not-recognized

Posted on 07/25/2021by Angelo

The term ‘Connect-SPOService’ is not recognized as the name of a cmdlet

If you’re trying to execute powershell script and you got this message, you need to install SPO Management Shell and import the module.

Execute this script (as Admin)

Install-Module Microsoft.Online.SharePoint.PowerShell -Force

Now, you can try to connect:

Connect-SPOService -url https://yourorganization-admin.sharepoint.com

Could not load type Microsoft.SharePoint.Administration.DesignPackageType

If you get this error (It was my problem), you can try solve it following these steps:

From Windows Control Panel, uninstall “SharePoint Client Components”
Execute (Admin mode) Uninstall-Module -Name Microsoft.Online.SharePoint.PowerShell -AllVersions -Force
Execute (Admin mode) Install-Module Microsoft.Online.SharePoint.PowerShell -Force

I hope it help you…Have a good day!

Excel SharePoint Online

SPO Lists VS Excel File

Posted on 06/14/2021by Angelo

“Are SharePoint Lists better then Excel Files?” or “Why shloud I use a SharePoint list intead Excel Files?”

These are 2 of the most asked question of my customers/students.

First of all, in my opinion there isn’t “best technology” or best “solutions” for all problems. There is more appropriate solution for your problem (or budget 🙂 ), so the “key” is: know different options to choose the most appropriate.

With these premises, let’s me explain some reasons why, personally, generally I prefer SPO List, intead Excel files.

List item versioning. SharePoint can store N versions on your list item, so you can see differents beetwneen versiones and restore previus version of item. Isn’t this cool?
Set item level permissions. You can decide , for each list item, who can see or edit it.
Different views. You can create different views of list. Users can create personal view with filters ,group or sort by columns.
Save author, last modified user, created date , last edit date. Automatically, for each rows.
Better integration with Power Automate & Power Apps, so your simple list becomes part of business process.

Have a good day!

SharePoint Online

SPO Parent/Child relationship

Posted on 05/08/2021by Angelo

Hi, here’s to know “How can we create relationship” beetwen two SharePoint Online lists.

My idea is create relation with a list of costs rows, to my list “Travel requests”, created for previous tutorial (Build an app in a few minutes), to understand costs details for each travel 🙂

1. Create list “Expense rows”

As a first step, we need a new list to store costs rows, so I add new list to my SPO site.

Then, we should add column to store amount of cost and column to create relationship with travel rows (PS: I use Title column for title of cost row)

Ok now we must create column to create relationship beetwen cost row and travel. To do this, need a LookUp column or for Italian People (“Ricerca”). Please keep mind this kind of column doesnt’t listed on list of common type column (see previous screenshot). One way to create it: click on “Add column” , “More” .

Here we can create lookup column.

Put column name (my case “Travel”) and choose type “Lookup”… (Wait for page reload).

Now you should set “Get information from” field : In this field you must choose another list where get row (or multiple rows) to create relationship.

Set “In this column” field. Here you can set column of other list, used to choose row.

In my case I also checked “Require that this column contains information“, because , in my opinion, when we add cost rows, travel is mandatory 🙂

There are others option, but for this tutorial we can click ok, and come back to our list.

2. Show relationship in SPO Page

First, create some travel rows and cost rows. Now , when we add some cost rows, we must choose “travel” row to associate it 🙂

Now it simple 🙂 To show relationship beetwen two list, we must edit a SharePoint page, add two “list” webpart:

Select Parent list first:

And then we do same process for expense rows.

Finally we have 2 lists in page:

Finally, add Dynamic filtering on “Expense row” webpart:

Configure filtering on “Expense rows” webpart

Result:

Thanks and Stay tuned 🙂

Power Apps SharePoint Online

Build an app in a few minutes

Posted on 05/01/2021by Angelo

There are two ways to build an app with Power Apps. “Canvas App” (My favourite) and “Model-driven app”. In this article , let’s see how we can build an app in less then ten minutes with Canvas App, starting from data.

1. Start from data

We should start from data using one of connectors available, such as Excel, SQL Server, Dataverse or SharePoint.

For this article, I created a SharePoint list , starting from template “Travel requests”. The Idea is create a mobile app to allow users create travel requests.

2. Build App

Let’s build 🙂 Go to https://make.powerapps.com/ , login with our account, on “Start on data”, click on “SharePoint”:

Then, choose “Connect Directly” (In my case , I use a list on SharePoint Online):

Click on “Create” button, and put url of SPO Site who host our list:

Click “Go” , then choose the list, click “connect” and wait….

Now? we have our app , ready to use or customize!

Our App, is ready to use with Add, edit, delete and search functions.

I spent more time to write this article then create app 🙂

In my opinion, this is the fastest way to create a Power Apps, If you don’t like it, you can use it as starting point to learn how to build and app, or to create a poc for your users or customer.