Job select an engineer this is a person type column pick the status of the job it's a choice column tools is a multi-select choice column subcontractors multi-select person column job site Choice column due date is a date column start date is a date column that also includes the time aspect.
I have a URL column and then another text column that tracks the job number and then pick the type of the job which is a lookup column that looks up to another list and I'll click save this will create a record inside my SharePoint list the lookup column is looking up to a list called job types that has the list of all the types of jobs the scenario here is importing data from Excel into our SharePoint list and we.
Want to automate that process in order to do that we need an Excel file that we are going to receive from users that will have information about Job tracking that will be loaded into a SharePoint document library and power automate will read those Excel files get every row of data from it and start adding it into this job tracking list first step is to create an Excel file that is based on a specific template.
Now one option is directly go to excel create a table in Excel start creating our columns in there which are in line with our SharePoint list columns and then that template is what we can provide to users who can add information in there now a quick way of creating that template experience is by directly heading over to export and clicking on export to excel this will download a file for us if we were to open this file it will launch Excel and here it will.
Since we are creating the template head over to table design and go ahead and click unlink so that we disconnect this from our SharePoint list I'll select ok the table here has a specific name associated with it I'll go ahead and rename this to job table here I want you to focus on a few specific columns person type columns it has exported it with the name of my.
User now if we were to import this data using power automate name is not enough we need something unique related to a user and the answer to that is their email address so here what I would do is I will change this to the email address of that specific user I've made that change next I have a multi-select choice column called tools now this is where you need to Define how.
You want to delineate your choice values when the user is plugging the information in now it's already delineated with semicolon and hash now you could keep that or you can make it more simple and I'll just keep it semicolon separated so when I am being provided the choice values they need to be semicolon separated subcons is a multi-select person type column so once again I would need the email addresses so I've plugged in the first email address I'll put a semicolon as my.
Separator and I can plug in additional email addresses and then at the end here I have two columns called item type and path these are like the internal Columns of SharePoint so I don't need them as part of my template so I will select them right click and delete them since it's a template I only want the columns I don't want the rows in there so I can go ahead I can delete the rows that completes my template definition I will go to file go to save.
I'll give it a name job template and save it on my desktop since I want the users to leverage this template file in my SharePoint site and my documents Library I can go ahead and upload that template file users can come in download this template file start adding the data in there and then submit it to the job drop off Library that's where all the Excel files will be held so let's create that drop-off Library create a document Library.
Posts Related:
I'll call it the job drop off Library this is where users will upload their
Excel files based on that template in a document Library if you go to new it has this option you have to create files directly on the Fly one thing we can do right here is ADD our own templates so if I click add template I can upload that job template document that I had right to this Library so that when users go to new they have the template available right here.You only want users to work with job templates in this Library we can go to edit new menu and uncheck all the other options and just leave job template as one of the options so now if I come into this Library I can go to new click job template and this is where I can start adding my data that I would like to import now as part of the template the key here is to make sure that the users provide information relative to our SharePoint list.
Let's take one example job status is a choice type column and the only three options that need to be provided are new completed and in progress however in the Excel file job status is a text field so I can put in a status that does not even exist in my system so to ensure that you're getting clean data from users you can add data validations right inside Excel.
As an example I can go to data data validation here I can pick allow list and I can provide a list of values which are new completed and in progress and click ok the user will get a list picker option here where they can pick the desired values and these are in line with our SharePoint list I have leveraged my template and plugged in information.
And that file is what I will upload in my job drop off Library in power automate I will create a scheduled Cloud flow I've given my flow a name and this flow is scheduled to run every day at 8 am I'll click create I'll add a new step we need the Excel files that are placed in this job drop off library in SharePoint so I will use the get files properties.
Only SharePoint action I'll pick my SharePoint site I will pick my document Library this will give me all the files in that Library Next Step Loop through every file that it has captured from this document Library from Dynamic content I'll pick the list of items now I need to read the data within those Excel files and for that we will add an.
Action and from the Excel online business connector I will pick list rows present in a table the location you can either pick your SharePoint site or head over to enter custom value and plug in the URL of your SharePoint site the document Library will be the job drop off Library and the file that we are pointing to from Dynamic content I will leverage full path since we are dynamically pointing to a.
File in our document Library the table drop down will not load any results however in our library the files that are coming in are based on a template the template has a table which we had called job table for table I'll go to enter custom value and paste job table I will add another apply to each Loop this time to Loop through all the rows of my Excel file I will rename this to apply to each row.
The loop will be on the dynamic content value which has all the list of items or the rows in our Excel file and in this is where we would like to add an action for SharePoint to create item the URL of my SharePoint site the name of the list where we want to create the item it's job tracking and this will now load up all the columns related to my SharePoint list.
Next step is to start mapping the columns that we have in our Excel file
To our SharePoint list columns title and SharePoint is a text column in order to get the current rows title column if we look at Dynamic content it is not providing Dynamic content for the Excel connector information and the reason is because we are dynamically loading data from the Excel.File so what we need to do here is we need to play with expressions to do that I will head over to the expression tab to get the current running item in my apply to each row Loop I will use the function item and in this I need the column title the syntax we use is square brackets and within the square brackets under single quotes you will plug in the name.
Of that column and that name has to match the name of the column in the Excel file you can always put a question mark before this question mark meaning if for some reason the Excel file did not contain the title column it will plug in the value empty otherwise the flow will break and throw an error I'll click OK this should put in the value of that title column for that row next column is job ID so same steps as the title column we.
Select job ID go to expression item off in single quotes I need the exact name of this column so I'll copy it and paste it in here and click ok the column is engineer it's a single select person type column the create item action gives engineer claims it's expecting the claims token to be plugged in right here now if I open the drop down it won't give me any data but I can head over to.
Enter custom value and once again plug in an expression here I need the email address of the engineer the Excel file has that in a column called engineer I'll click ok job status single select Choice column which has three Choice values in my Excel file the column is called job status if you open the drop down it will provide you the standard three values.
But our data is coming from the Excel file so we need to write that expression I'll head over to enter custom value go to expression and type in item off my column name job status job site is also a choice column here's job site enter custom value plug in the column name from our Excel.
File due date is date only start date and end date is date and time key thing to understand is when the data is returned from list rows present in a table action it Returns the dates in numeric format and previously we had to do a lot of calculations to get the accurate date value the advantage now is if we head over to show Advanced options for the list rows.
Action we have something known as date time format here we can pick ISO 8601 it will return the date and time in that specific format and it will be in UTC coordinated universal type for all my date columns I can leverage the same technique item of due date start date and end date notes is a multi-line text column.
Simply write the same expression for notes and info is a URL type column I will leverage the column coming in from Excel which is info now let's look at some challenging column types multi-select Choice column which is called tools value in my Excel file the column for tools the multiple choices are provided as follows and they are semicolon separated to plug in those values it's very.
Important to understand the format that this create item expects so if I was to open this drop down here and let's say I randomly pick one choice value let's say hammer and I click on add new items let's pick another one I pick gloves if you were to switch over to input entire array you will observe the format in which this specific column expects its data it needs to be an array that has an.
Object with a key value and then it has the text related to the choice option and SharePoint so we need to create this array data based upon this text information that's coming in from our Excel file column tools to do this I need to take a step before create item action I'll add add an action I will Leverage a data operation function called select.
The from property expects an array now I need to convert this into an array and to do that I need to split it based on my delineation character in my case I defined that as semicolon so for the select action I will write an expression split item off tools tools is the name of my column in my Excel file this I want to split with.
Semicolon this will return an array of data the create item action expects a key called value and then it needs the actual text value so in the map property I will Define value that's my key now select it's an array so when you're mapping I can again use that item property in my expression and this time it will map to the current running item.
In this select action which is nothing but the value the values that it has obtained from my split function this action I will rename to select tools and now since I want the create item action to be performed after select tools I will select this drag it and place it right below this in create item where I have my tools I will remove all of this and you need to make sure that you have switched over to input entire array.
Right here is where I can pick the output of Select tools on similar lines for multi-select person type columns if you look at my Excel file I have my data once again semicolon separated this since I want to look at the format I'll go to enter custom value and enter some sample data I'll enter a add another item go to enter custom value add B and let's switch over to the input entire array.
And this is the format that this specific column expects it requires an array this time with the key claims which would have those email addresses I will add an action before create item I will pick my select action the from property would be the expression split on my column called subcons split by semicolon this will give me the array of email.
Addresses and I need to create the key needs to be called claims and the value will be the current running item I'll rename this to select subcons and the create item action for subcons I'll pick the output of Select subcons lookup column called job type here it expects the ID of the job type if I was to manually open the drop down it will list out all those lookup column values from SharePoint.
However we need the data that's coming in from our Excel file and in the Excel file job type is coming back as text I don't have the IDS of the job types in SharePoint in my job types column I'm looking up to the text however every SharePoint list has a column called ID which maintains the internal unique ID this is the ID that I need in my Excel file I can't expect my users.
To enter or know the IDS they would enter the text sort of map to those IDs directly in flow right after get files I will add an action called get items get items from my SharePoint site and the list now that I will point to is job types I will rename this to get job types inside my apply to each row before I am creating the item I will add an action.
Filter data operation filter array I want to filter my array of job types Dynamic content the value property coming in from the get job types action where the title column of this array to get that I'll head over to expression item off title is equal to here I need the job type column value for my.
Excel row that's running in the loop however filter array itself is running on a collection so if I use item there will be a conflict so at this moment I need to use the items of apply to each row to get that if I search for current item apply to each row has this current item property so I'll select this it will plug in this formula here do a Ctrl a control C to copy this.
I will close this now head over to expression and paste what I copied I'll remove the add symbol at the beginning items of apply to each row that's the formula to give me the current item context of the running row here I will put a question mark and plug in the name of my column which is job type in my Excel file and click ok.
For create item I will go to enter custom value and write the following expression body of the name of my action is called filter array so filter spaces are replaced by underscores when you're using the body attribute array question mark I'll pick zero to give me the first row index and then from this I need the ID so as you can see there's a lot of work involved for me to get that lookup column ID value from my SharePoint list.
I will go ahead and click on save to save my flow my flow has been saved the flow Checker does give me a warning that says you're trying to read data from the job types list but you are not applying any queries so you might have to take into account some performance considerations my lookup column has a limited set of data so I'm not too worried about that this flow is scheduled to run every day at 8 am for now I will manually run it so we can look at the output of the slope I'll.
Click on run and click on run the flow the moment I do that the flow has triggered and the flow has completed if I go to my job tracking list I can see three new records that got created all this information has flown in from my Excel file directly into my SharePoint list sample build 2 has this URL information in this info column I'll set this to.
Blank and let's take sample build three I will take my multi-select choice column and make it empty I'll close my Excel file let's go ahead and run the flow again now my flow run has completed but the status is failed if I was to select my flow run and look at where this has failed if I go to apply to each row and go to the.
Next failed item the create item action has failed and the error message here is the info column is required to be of type string URI the runtime value which is empty is not in the expected format and if we look at our SharePoint list only two rows got added it did not add sample build 2. and the reason is because I made this column empty so there are going to be scenarios like this that you will have to handle empty values.
In the info column I will remove the value go back to expression I need to check if the incoming value is equal to blank so I'll use the equals function my incoming row value for column info is this equal to empty string if it is then I want to put the value null else I will put the value of that info column.
So I had to add this null check to my existing process I'll click OK and save my flow I have gone ahead and cleared up my job tracking list let's run the flow again the flow this time has completed successfully and if we look at our job tracking list I have all the three records come in and the info column for record number two has come in as empty let's focus on the start date column for sample build 2 it is the first of.
