Fill Out Word through Power Apps No Premium Licenses Needed

Summary

In this tutorial, Andrew Hess demonstrates how to create a recipe book using a free SharePoint template without premium connectors. He guides viewers through setting up site columns, content types, and a document library, then creates a Word template with placeholders for recipe data. Hess also shows how to integrate Power Apps and Power Automate to generate documents directly from the SharePoint site, all within the scope of Office 365 licenses.

Takeaways

• 😀 Andrew Hess demonstrates creating a recipe book using a SharePoint template without premium connectors, leveraging free Office 365 licenses.
• 👨‍💻 To start, you need to be a site or team owner in SharePoint to create site columns and content types.
• 📝 Six site columns are created for the recipe book: Step One to Step Six, Author, and Ingredients, all set as single-line text for simplicity.
• 🔑 A content type named 'Recipe Book' is created, utilizing the document content type and incorporating the custom site columns.
• 📚 A new document library called 'Recipes' is set up, and content types are enabled, allowing the addition of the 'Recipe Book' content type.
• 📑 A Word template for the recipe book is created, including a table for steps and a logo, which is later saved to the SharePoint site assets.
• 🔗 Document properties in Word are mapped to the SharePoint site columns using Quick Parts to synchronize data between the document and SharePoint.
• 📱 A Power App is developed to interact with the SharePoint list, featuring a vertical gallery to display recipes and a PDF viewer for document previews.
• 🔧 Power Automate is used to automate the creation of new recipe documents in SharePoint based on the template, filling in properties via Power Apps inputs.
• 🛠 Permissions and setup requirements are discussed, emphasizing the need for site ownership and the avoidance of complexity to utilize free licensing models.

Q & A

What is the main objective of the video?

The main objective of the video is to create a recipe book using a template in SharePoint without using the Power Automate premium connector, leveraging the free license model that comes with Office 365.

Why is the presenter creating site columns and content types in SharePoint?

The presenter is creating site columns and content types to organize and structure the recipe book data within SharePoint, making it easier to manage and display the information in a consistent format.

What is the significance of the 'recipe author' column in the SharePoint site?

The 'recipe author' column is significant as it attributes the creator of each recipe, allowing for proper credit and organization of recipes within the SharePoint library.

Why does the presenter advise against using a people picker for the 'recipe author' column?

The presenter advises against using a people picker for the 'recipe author' column because it requires a premium connector, which goes against the goal of creating the recipe book using only the free license model.

How does the presenter ensure that the document template is associated with the correct content type in SharePoint?

The presenter ensures the document template is associated with the correct content type by uploading the template to the site assets and then specifying it in the advanced settings of the 'recipe book' content type.

What is the role of Power Apps in this SharePoint recipe book project?

Power Apps plays a role in creating a user interface that allows users to interact with the SharePoint data, such as viewing, adding, and editing recipes, without directly manipulating the SharePoint site.

Why is the presenter using a Power Automate flow to create a new recipe document in SharePoint?

The presenter uses a Power Automate flow to automate the creation of new recipe documents in SharePoint, which simplifies the process and allows for data to be written directly from Power Apps to SharePoint.

What is the purpose of the 'open in client application' setting in the SharePoint document library?

The 'open in client application' setting is used to ensure that the document opens in Word, which is necessary for the document properties to be editable and for the Power Apps integration to function correctly.

How does the presenter handle the document properties in the Word template?

The presenter handles the document properties by using Quick Parts in Word to map the SharePoint site columns to document properties, which allows for metadata to be filled out directly in the Word document.

What is the final outcome of the video tutorial?

The final outcome is a complete Power App with a Power Automate flow that uses SharePoint, all within the free license model. This setup allows for a functional recipe book where recipes can be added, viewed, and managed through SharePoint and Power Apps.

Outlines

00:00

📚 Introduction to Creating a Recipe Book in SharePoint

Andrew Hess introduces a tutorial on creating a recipe book using SharePoint and Power Automate, all within the free license model included with Office 365. He demonstrates how to update a steak recipe and shows the integration of Power Automate with SharePoint. The process begins with accessing site settings and creating site columns for the recipe book, emphasizing the need for ownership permissions to perform these actions. Andrew then guides through creating six steps for the recipe and organizing them into a group called 'my recipe book'.

05:02

🔧 Setting Up Site Columns and Content Types

The tutorial continues with the creation of additional site columns such as 'author' and 'ingredients', and placing them into the appropriate groups. Andrew explains the importance of keeping things simple to avoid the need for premium connectors. He then moves on to creating a content type called 'recipe book', which utilizes a document content type and includes the previously created site columns. The process involves updating all site and list content types inheriting from the new content type.

10:03

📖 Creating a Document Library and Template

Andrew proceeds to create a new document library named 'recipes' and enables content types for this library. He then adds the 'recipe book' content type and removes the default 'document' content type, ensuring only the custom recipe book template is available. The next step is to create a Word template for the recipe book, which includes a table for the six recipe steps and an option to insert a logo. The template is then saved to the desktop and uploaded to SharePoint as the document template for the 'recipe book' content type.

15:05

✍️ Linking Document Properties to SharePoint Columns

The video script describes how to open a document in Word and use Quick Parts to link document properties to the SharePoint site columns created earlier. This allows for the document properties to be editable both within the document and in SharePoint. Andrew saves the template with the linked properties to the desktop and then uploads it to SharePoint, replacing the existing template. The process ensures that when a new recipe book is created, the document properties are pre-populated and can be edited directly in SharePoint.

20:06

📱 Building a Power App to Interact with SharePoint

In the final part of the script, Andrew sets up a Power App with a vertical gallery connected to the SharePoint list. He includes a PDF viewer and a form for editing recipe properties. The app is designed to interact with SharePoint, allowing users to create new recipes using the custom template. He also demonstrates creating a Power Automate flow to handle the creation of new recipe documents in SharePoint based on the data entered in the Power App. The flow retrieves a template from site assets, creates a new file in the 'recipes' library, and updates file properties based on the data provided through the Power App.

Keywords

💡Power Apps

Power Apps is a service for building custom applications. In the context of the video, Power Apps is used to create a recipe book application that interfaces with SharePoint. The script describes how to set up screens, galleries, and forms within Power Apps to manage recipes, showcasing the platform's capabilities for data-driven app development.

💡Power Automate

Power Automate, formerly known as Microsoft Flow, is a service that helps in automating workflows and tasks across various applications and services. In the video, Power Automate is used to automate the process of creating and updating documents in a SharePoint library, demonstrating how it can be integrated with Power Apps for seamless data operations.

💡SharePoint

SharePoint is a web-based collaborative platform that integrates with Microsoft 365. It is used in the video to host a document library for storing recipe templates and data. The script details how to configure SharePoint site columns, content types, and document libraries to work with Power Apps and Power Automate for creating a recipe book.

💡Site Columns

Site columns in SharePoint are reusable columns that can be added to multiple lists or libraries. The video script explains creating site columns for attributes like 'step one' through 'step six', 'author', and 'ingredients', which are then used to structure the recipe data within SharePoint lists and libraries.

💡Content Types

Content types in SharePoint define a collection of columns and also specify the document template to be used. In the script, a custom content type 'recipe book' is created to associate the site columns with a document template, which is crucial for structuring how recipes are stored and displayed in the document library.

💡Document Library

A document library in SharePoint is used to store and organize documents. The video describes creating a 'recipes' document library and enabling content types for it, which allows users to upload and manage recipe documents in a structured manner.

💡Document Template

A document template in SharePoint is a pre-configured file that defines the structure and content of new documents. The script demonstrates creating a Word document template for recipes, which includes placeholders for recipe data such as steps, author, and ingredients, and then uploading this template to SharePoint for use with the recipe content type.

💡Quick Parts

Quick Parts in Microsoft Word allow users to insert pre-defined content into a document. In the video, Quick Parts are used to map SharePoint site columns to document properties within the Word template, enabling the automatic population of document metadata from the recipe data.

💡Office 365

Office 365 is a subscription-based service provided by Microsoft that includes access to various applications like Word, Excel, PowerPoint, and SharePoint. The video mentions that the demonstrated solution uses the free license model that comes with Office 365, indicating that no additional premium licenses are required for the described setup.

💡Site Assets

Site assets in SharePoint are a location for storing and reusing files such as images, documents, and other media across the site. The script refers to uploading a document template to site assets, which is then used as a source for new documents created through Power Automate, highlighting the organization and reusability features of site assets.

Highlights

Creating a recipe book using a SharePoint template without Power Automate premium connector.

Using Office 365 licenses for Power Automate with a free license model.

Creating site columns for the recipe book in SharePoint.

Organizing site columns into groups for better structure.

Limitation of using people picker without Power Automate premium.

Creating a content type for the recipe book using site columns.

Enabling content types in a document library for custom document handling.

Removing default document content type to use only the custom recipe book type.

Creating a Word template with steps and a logo for the recipe book.

Using mid-journey to generate logos for the recipe book template.

Uploading the custom Word template to SharePoint for document creation.

Setting the document library to open documents in the client application for metadata access.

Linking document properties to SharePoint site columns for seamless data integration.

Creating a Power App to interact with the SharePoint recipe book library.

Using a vertical gallery in Power Apps to display recipe thumbnails from SharePoint.

Integrating a PDF viewer in Power Apps to display recipe documents.

Creating a Power Automate flow to automate document creation in SharePoint.

Using Power Automate to fill in document properties from Power Apps data.

Demonstrating the complete process of creating, saving, and viewing recipes in the Power App.

Highlighting the permissions required for creating site columns and content types in SharePoint.

Providing a solution for choices in Power Apps by using single line text in SharePoint.

Concluding with a complete Power App and Power Automate setup using only the free license model.

Transcripts

in hit save click back it then now has

my steak recipe here

it then updates and shows the new steak

recipe we have a complete power app with

a power automate using SharePoint using

the free license model which means it

comes with your Office 365 licenses

[Music]

hey everyone my name is Andrew Hess and

today we're going to create a recipe

book and we're going to use a template

in SharePoint to fill out this document

and this document is not going to use

the power automate premium connector

we're going to do this all for free so

when I do this I've done this before but

I'm going to try and make this better

and more fun this is a brand new

SharePoint site it is connected to a a

group

so I'm going to go to the gear icon

site information view all site settings

and I'm going to create some site

columns for my recipe book so we'll

start with sitecom so in order to do

this you do need to be an owner of the

group or you need to be an owner of the

SharePoint site or an owner if it's a

team site you need to be an owner of the

team

so in a recipe book we're going to say

there's going to be six steps and I'm

not going to leave any spaces in here

that's just habit so I'm going to say

step one single line text and we're

going to create a new group

and we're going to put this group in my

recipe book

okay and then I'm going to do six steps

real quick

just like that so I'll probably fast

forward I'm going to do six steps so

this is step two

and when I do step two I'm going to use

an existing group I'm going to do my

recipe book and attach it to that group

this just helps me organize things

okay now I have six steps if you come

all the way to the bottom you see my

recipe book we have six steps

so now I'm going to create another

column let's just call this

um author I guess is that the creator of

a recipe is we'll call it author and I'm

not going to use a people picker a

people picker is not going to work if

you need a people picker and you have to

have a people picker after we do all

this create a power app and then the

power app use a people picker and then

write a single line text to SharePoint

all right

it looks like my name is already

reserved look at that so we're going to

call this recipe author this is just

another thing to learn I'm going to

leave my mistake in there never try and

you know don't go in there and delete a

a column that's out of the box with

SharePoint just create a new column I'll

call it recipe author

and then finally we'll have um maybe

ingredients

and we'll put it in the right group

okay we have eight different

um Fields here they're all single line

text I'm keeping it simple as soon as

you add complexity to this you need to

use that premium connector right we're

trying to do this without power automate

power apps premium

okay now that I did that I'm going to go

back to site settings right here at the

top I can just click on that and go to

site settings and now I'm going to do

site content types

now what is a content type A content

type is a group of content it can be

Fields it can be items it can be folders

it can be

um you know many different things but

we're going to create a new content type

and it's going to be

a recipe book

and it's going to use a document content

type

and we will have document content type

here

and it's going to be a document so we're

going to create a document and it's

going to have a template and those

columns are going to be the group of

content that we're going to use in this

content type

so I'm going to click create

all right now it wants me to add site

columns so I'm going to add

from existing site columns you could

have done it before the other way and

you know created them here but I'm just

going to add all of these and I'm going

to do it in let's see recipe author

ingredients and then the steps

all right I'm going to keep this checked

update all site and list content types

inheriting so I'm just going to say save

all right so I've created a content type

with my site columns

so now I'm going to go back to my site

and I have a document library but in my

opinion maybe that document library is

different I'm going to create a new

document library and it's going to be

recipes

so site contents

I'm going to create a new library

and we'll call it recipes and keep your

space it don't put spaces in these

things just trust me after many years of

SharePoint

all right I have a new SharePoint

um Library it's called recipes we're

going to go to the gear icon again and

then Library settings

more Library settings

and we're going to turn on content types

for this document Library so in order to

do that you go to advanced settings here

and let's see allow management of

content types it's right here at the top

we're going to say yes

and then we'll click ok

all right so now you can see we have a

new section here this new section is

called content types and it has document

but we don't want a regular document

right like a blank Word document no we

want our recipe book so I'm going to add

from existing content type

and here's my recipe book I just created

that and we're going to add it

and click ok

so now we've added recipe book now what

I'm going to do is I'm going to remove

document I'm going to come in here

and delete this content type

I only want my document the recipe book

so if we come back to the top of our

document Library you can see new recipe

book that's all that's there we don't

have that other document content type we

have our recipe book okay so now we want

to make our word template okay so we

need to make a word template

so this was a question someone had they

wanted you know like this Gallery they

wanted this Excel format in their Word

document

in the template I believe we can insert

a table

and we can just insert

the table we want six because there's

six steps one two three four five six

okay and then I'm just going to add in

here

step one

now you can't do pictures

um at least I'm going to assume that you

can't do a picture in a template you

could automatically do a one picture

that's in every template right like so

you could have a logo in here maybe

we'll do that let's put a logo in our um

in our document

okay so I've been learning this um in

the past a few months but I'm going to

use mid-journey to you know generate me

some logos

all right so I have some generated logos

I I like this one right here I'm just

gonna you know for this scenario since I

just want to be fast

I'm going to take this logo here and I'm

going to put it in my template

all right I have my template for my

document I'm going to save this to my

desktop

all right I saved my template to my

desktop now I'm going to go to settings

the gear icon Library settings more

Library settings

and I'm going to go to I believe I click

on the content type recipe book

and I'm going to click on advanced

settings

and now you can see that you know

there's a document template we're going

to upload the document template that we

just created right so upload that

all right I uploaded my recipe template

I'm going to click OK it's not read only

we're going to say okay

now just to show you where we are in the

content type and creating the template

if you click new and you do new recipe

book

it's going to open up in browser our

template so we now have our template

without the metadata the data The

Columns attached to the document right

so we have our template

so now what I'm going to do

is I'm going to create a new one which I

just did

and this is going to be recipe

template

two and I'm going to save this to my

SharePoint library right

so it should automatically save to my

SharePoint library right there

so if we come back to our site click

refresh I have my template here

in my SharePoint library now this is one

of the downsides of doing it this way is

in order for this to work you can only

open the document in the client

application which is word so we have to

go back up to the gear icon

go to library settings more Library

settings

advanced settings and we have to say

open in client application it's the only

way this will work if you don't open in

a client application you won't be able

to see the quick parts of the document

and then I'm going to click ok

and I'm going to go back to recipes and

I'm going to open my document

and it's going to open in Word

all right so we have our document open

now what we're going to do

is we're going to come up to insert I

believe and we're going to do quick

parts

now we'll be able to see our document

properties now you notice that the

properties the site columns that we

created in SharePoint are now showing up

as document properties in our document

so for recipe we're going to put in

quick Parts document property I believe

it's name

or its title

author I'll put a space in quick parts

we did recipe author right recipe author

ingredients

ingredients step one

and so forth all the way down

so now we have our recipe book now more

about this document if you go to file

and info you'll also see those columns

that we created in the properties of the

document so file info you can see in the

properties you'll see the same columns

so we're going to save this to our

desktop so this is recipe template we're

going to rename this one this is going

to be named three number three save to

your desktop

right so the first document we saved to

our desktop the second one we saved to

SharePoint call to this one we're going

to rename and call it three and I'm just

doing this for demonstration purposes

we're saving this number three to our

desktop save

all right so recipe number template

three is on our desktop we're going to

go back to the template Library settings

more Library settings we're going to

click on the content type here

we're going to go back to the template

in advanced settings

and we're now going to upload the new

one recipe three

so the one that we just attached the

document properties to we're going to

upload and replace the template and

we're going to click ok

so now we're going to go back to our

document library now let's check out

where we are in the process so when I do

new recipe book and we open up word

we can now see our document properties

are here and even on the right side it

shows the document properties now you

can write in the document or you can

write in the document properties on the

on the right side

so for title we'll put in a spaghetti

and don't make fun of my um skills here

for making spaghetti I see I can't even

spell my name right so one we need

noodles

two we need water three we need salt

four sauce

and five garlic

okay and then this is just for this

first time we're not gonna have to do

this every time we want to make sure I

save to our SharePoint site in the

document Library so this will be called

Spa get t Okay so we're going to save

back into our SharePoint site spaghetti

click save I'm going to close down word

click refresh we notice we have

spaghetti in here now I'm going to add

the columns to the view right so I'm

going to go to settings Library settings

more Library settings than down here I'm

going to go to all documents

and I'm going to add all those columns

in the view so I'm going to remove

modified Modified by and then I'm going

to add

recipe author

ingredients

and the steps and it does matter which

order you click these in

I mean you could fix it later but that

just helps and I'm going to click ok

now if you notice those properties are

now in SharePoint I did not write in

SharePoint oh thank goodness right isn't

that wonderful so we have a document

template with our logo

our recipe logo and the steps for our

recipe

okay so now we can do power apps this is

where we really get so I'm a Power

Platform developer this is where I

really like to have fun what I'm going

to do

is I have two screens set up I'm going

to insert a vertical gallery

the vertical Gallery is going to be

connected to our SharePoint list we can

see we have

um my spaghetti I'm just going to remove

the picture here I'm going to remove the

arrow we'll leave that as it is I like

the the title and the ingredients and

you notice I'm using the new galleries

that just came out kind of has the blue

bar and the invisible white line now I'm

going to insert the PDF viewer now you

notice this is experimental

but I'm going to insert the the PDF

viewer

and

then I'm just going to have a simple

code that I have saved in there let's

see

paste it right in so substitute Gallery

one get the thumbnail large of a

document so now

you can see we can see our document in

power app so let's um

make it a little bigger there we go I

fixed it so now it fits in there we have

our gallery

let's um create a new button just so we

can have another one let's do new

I'm just doing this really quick right

I'm not doing a full

um Power app so we're just going to say

navigate

to screen two on a fade

see fade

new if you want more on this then it's

another video but we'll just do new

so it brings us to screen two insert a

form

and we'll do edit form and I'll just you

know default it to a new

um

for now so the default mode is new

and we got it connect it to our

properties recipe properties

now we're gonna have to save I'm going

to use power automate create a new flow

this power automate is going to create

the file for me in the document Library

I want

so I'm going to create from blank

all right so step one for this power

automate is to actually have the

template somewhere that it can pick it

up

so I'm going to go back to my SharePoint

site

and in site contents in site assets

I'm going to upload my template and that

was recipe number three so if I just

upload I have it right here recipe

template number three

all right so if you remember number

three from the beginning of the video

that is this file

so now we're going to go back to

powerapps new Step git file

from content is that right um let me see

if I can find it not get file git file

content using path

all right so then we're going to go to

the our SharePoint site

content type site the file path is going

to be our site assets

and we want to get the file all right so

we have our file template then we're

going to create a file I believe is

probably next so create file

so we're going to create a file in the

same SharePoint site

the folder path is going to be our

recipes

so we want to put it in our recipes

document Library the name of the file

will be we'll ask that in powerapps I

believe

we'll get to that the file content will

be here

it does require a file name so we'll ask

in powerapps

and then next what we need to do is we

need to fill in those file properties so

update

file properties is that right let's see

if we can find it

so oh update file properties that looks

correct okay and then we're going to use

that SharePoint site

the library name is the recipes the ID

is going to be the created file item ID

and then title will ask in powerapps or

let's see if we got it already no we

don't so this is where the trick is a

lot of people can't find this and

Microsoft hides it you see this little

c-more right here this is how you get

more ask and powerapps ask in powerapps

come down see more

ask and powerapps author see more ask

and powerapps and we're going to go all

the way down through each one and we're

going to ask in powerapps

and I don't think we need a description

I didn't add that

and then finally we have content type ID

recipe book

uh we're going to click save

we are done our power automate creating

it so now we need to fix the button so

the button as soon as that loads we

could probably rename this if we wanted

to but for now I'm not going to this is

the name of

my power app power apps this one here

and then we're going to do dot run in

parentheses and if you look in the

intellisense it's going to help you out

file name is going to be select

hopefully these are in order data card

value one I haven't renamed them

uh data

card value one dot text

um the next one is the title again

oh so this is where so we have to say

and

maybe this will work like this we'll see

dot dot X we'll see if that works I'm

just playing this by ear

all right so now we're going to do data

card value two dot text

all right that's the author no that's

ingredients so this is actually three

this one is two

so recipe author here you can see the

colors the color codes helps you recipe

author okay

so next is four

that's step one all right I think we're

uh on a roll we can just go down to five

and straight on down

this one will be six

and then seven

eight

nine

and hopefully that is all of them

so now

let's fill this out so we're on the new

page does our save button navigate back

let's also

on the save button navigate to screen

one on a fade

once we uh we'll do that different ways

but I'm just rushing through this all

right so now we're going to do a stake

Andrew Hess ingredients is meat

one

neat

to Salt three pepper

Step One is salt the steak

uh pepper in this tank

then we're gonna let it sit let it sit

for 10 15 minutes

then we're going to sear the steak

we're not going to do a reverse steer a

sear if you're looking at that but and

then we'll cook the steak

then we'll let rest for 30 minutes and

I'll hit save and pray this power

automate works

hit save

stake value cannot be converted to a

number looks like I added a multiply

here instead of a ampersand

I clicked it was a missed click all

right so let's try again hit save

this time it ran

did it create a file in my library let's

check out the library

it did create a file in my library the

gallery did not refresh yet so let's

um

on visible of this screen I'll refresh

our data source which is recipes now you

maybe don't want to do this but I I'm

just doing this for demonstration

purposes so if I click new I created a

back button click back it then now has

my steak recipe here

it then updates and shows the new steak

recipe and we could probably you know

print this do all kinds of things with

power apps

all right so we have a complete power

app with a power automate using

SharePoint using the free license model

which means it comes with your Office

365 licenses you don't need that premium

connector you do need to be an owner of

the site

you do need to create the site columns

and site content types the way I showed

you you have to upload the template to

the site assets and now you have a

SharePoint site with documents with the

columns right here in SharePoint

so hopefully this answers all your

questions Russell this all sparked from

a question from Russell

um you know what level of permission do

you need you know can you do choices no

don't try and add complexity to it you

could do a choice and then convert it to

a single line text so you could do a

choice in power apps but it's a single

line text in SharePoint that's one way

you can do choices right is do that in

powerapps side

um

and so thank you all for watching

hopefully this answers your questions

Russell if you want to do choices do a

choice on PowerApp side why you don't

need to do it on the

um SharePoint side and then you can have

your choices and write directly to your

template so thank you all for watching

I'll see you next time