Essential Functions for Importing Power Automate Forms to SharePoint Lists: A Complete Guide

I personally find Microsoft Forms incredibly convenient, particularly because of its zero UI adjustment requirement.

This is one of its most appealing features.

For interfaces that don’t require extensive input fields, I actually recommend using Forms over Power Apps.

When importing Microsoft Forms responses to SharePoint lists through Power Automate, you’ll need to use specific Power Automate functions. Here’s a comprehensive guide to these essential functions.

Initial Setup: Creating the Forms

Here’s what our sample Forms questionnaire looks like:

• Text
• Text (Integer)
• Date
• Text (Floating Point)
• Options (with ‘Other’)
• Rating

Initial Setup: SharePoint List

Here’s how our SharePoint list is structured to store the Forms data:

• Single line of text
• Number (Integer)
• Date and Time
• Number (Decimal)
• Choice
• Number (Integer)

We’ll be using Power Automate to transfer the Forms responses into this SharePoint list.

Initial Setup: Power Automate

Start from “Automated flow”

Select the “When a new response is submitted” trigger for Forms

After selecting your Forms in the trigger, add the “Get response details” action for Forms and input the ID from the “When a new response is submitted” action.

Finally, add the SharePoint “Create item” action, and your Power Automate setup is complete!

Functions Used in Forms → Power Automate → SharePoint List Integration

All responses from Forms are transferred to Power Automate as text type.

When storing these text values in SharePoint lists, you need to convert them to the appropriate data type for each column.

Forms “Text” → SharePoint List “Single line of text”

For text-to-text conversion, you can simply add dynamic content without any conversion

Forms “Text” → SharePoint List “Number (Integer)”

To convert Forms text input to an integer in SharePoint list, use the int() function.

Select “int” from “Expression” under “Dynamic content”

Then specify the Forms response you want to convert to integer as the argument for the int() function

Reference guide for expression functions - Azure Logic Apps

Reference guide to workflow expression functions for Azure Logic Apps and Power Automate.

learn.microsoft.com

Forms “Text” → SharePoint List “Number (Decimal)”

For decimal numbers, use the float() function.

Following the same process, select “float” from “Expression” under “Dynamic content”

Then specify the Forms response you want to convert to decimal as the argument for the float() function

Reference guide for expression functions - Azure Logic Apps

Reference guide to workflow expression functions for Azure Logic Apps and Power Automate.

learn.microsoft.com

Forms “Date” → SharePoint List “Date and Time”

To convert a Forms “Date” response to SharePoint list’s “Date and Time” format, use the formatDateTime() function.

Select “formatDateTime” from “Expression” under “Dynamic content”

Then specify the Forms date response you want to convert as the argument for the formatDateTime() function

Reference guide for expression functions - Azure Logic Apps

Reference guide to workflow expression functions for Azure Logic Apps and Power Automate.

learn.microsoft.com

Forms “Choice” → SharePoint List “Choice”

When transferring responses from Forms “Choice” to SharePoint List “Choice” column, you can simply add dynamic content without any conversion, just like with text fields.

Start from “Enter custom value”

Select the desired value.

Interestingly, when using a choice question with an “Other” option, the flow works perfectly fine even if the response doesn’t match any of the predefined choices in the SharePoint list.

For example, in a choice column with options A, B, O, and AB

Even if someone responds with “Bombay type” in Forms

The non-existing choice “Bombay” will be successfully stored in the SharePoint list column

Forms “Rating” → SharePoint List “Number (Integer)”

Finally, when transferring a Forms “Rating” response to a SharePoint List “Number (Integer)” column, use the int() function, just like we did for “Text to Number” conversion.

Select “int” from “Expression” under “Dynamic content”

Then specify the Forms rating response you want to convert as the argument for the int() function

Multiple Choice Questions

Due to the length of this topic, it is covered in a separate article.

Power Automate Formsで複数選択した回答をSPOリストの複数選択肢列に格納する

やりたいことFormsにこんな感じで複数選択可能な質問があって上のFormsで選択された回答を、SPOリストの複数選択可能な選択肢列に入れてみる。Formsで複数選択したときに受け取れる文字列まずはいつも通りFormsの応答の詳細を取得する...

ippu-biz.com

Handling Optional Fields (Blank Values) in Forms

In Microsoft Forms, you can have both “Required” and “Optional” fields. When using optional fields, blank responses may be sent to Power Automate.

For example, if we make all fields optional in our sample Forms and submit without filling any responses, Power Automate receives these blank values:

If we directly apply int(), float(), or formatDateTime() functions to these blank values, we’ll get argument errors since these functions cannot process empty values:

To handle this, we need to use the if() function when transferring optional Forms responses to SharePoint list “Number” or “Date and Time” columns.

if(
    empty(outputs('Get response details')?['body/r2cf09ac74d9b4a749b3e0c4d0c9a1ede']), 
    null, 
    int(outputs('Get response details')?['body/r2cf09ac74d9b4a749b3e0c4d0c9a1ede']))
)

if(
    empty(outputs('Get response details')?['body/r2cf09ac74d9b4a749b3e0c4d0c9a1ede']),
    null,
    formatDateTime(outputs('Get response details')?['body/r2cf09ac74d9b4a749b3e0c4d0c9a1ede']))
)

Note: The if() function isn’t necessary when transferring optional Forms responses to SharePoint “Single line of text” or “Choice” columns, as these columns can accept blank values.

Here’s how your Power Automate flow should look when all Forms fields are optional:

When executed, it creates a list item with empty values:

If your SharePoint list has required columns, this flow will generate errors. However, you can resolve this by replacing the “null” in the second argument with a default value appropriate for your requirements.