cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Regular Visitor

Split multiple Sharepoint field entries into Rows on a Google sheet

Hi there,

 

I'm new to the forum and just feeling my way through Power Automate at the moment.  I've got myself stuck on a problem and could really use some advice!

 

I have a sharepoint list, within this list there are 2 columns where multiple entries can be entered (delimited by semi colons).  

 

Each row in the list has a status column, and i've written a flow that when the status is set to 'Complete' this triggers a flow which passes the sharepoint row and generates a new row in separate google sheet. I have also worked out how to split 1 of the columns so that if multiple delimited entries are added to the sharepoint list, a new row is created in the google sheet for each delimited entry.  This works great.

 

However, my problem is that I can't figure out how to iterate over the second column, and if multiple entries are made in the same row, how do i insert the second columns details so it appears in the same split rows in the google sheet?  I used the Apply to each function to successfully iterate over my split array for the first column and tried a second apply to each function to 'update row' in google sheet and used the powerappID identifier, however this just loops over the first cell and repeats the first entry over and over. 

 

A crude example of what i'm trying to achieve would be this, any help would be much appreciated!

 

chuck1234_0-1600344451003.png

 

 

This is my current flow: 

 

thanks!

chuck1234_1-1600344577823.png

chuck1234_3-1600344655014.png

chuck1234_4-1600344691400.png

 

chuck1234_6-1600344735510.png

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Dual Super User III
Dual Super User III

Re: Split multiple Sharepoint field entries into Rows on a Google sheet

Hi!

That's a very tricky challenge!

So, in order to get all URLs from the SP item that triggered your flow, you need to use split() function.

First I would add an 'Initialize variable' action block, type array, let's call it 'myOutputArray', value empty

Next, I would add an 'Initialize variable' action block, type integer, let's call it 'currentIndex', value 0

Next, I would add an Apply to Each and assign as its input the following expression:

split(triggerBody()?['Column1'],';')

 

Inside the 'Apply to each' I would:

-add an Append to Array variable, name myOutputArray, value:

{
"Column1": "@{item()}", 
"Column2": "@{split(triggerBody()?['Column2'],';')[variables('currentIndex')]}"
}

-add an Increment variable action block, name 'currentIndex', value 1

Now outside the 'Apply to each', I would add dummy Compose action block just for troubleshooting purposes, assign as its value myOutputArray'. THis way when executing the flow you can inspect its content easily.

Next I would add a second Apply to each, assign as its input 'myOutputArray'. Inside this second 'Apply to each' I would add the action block that creates a new row in GoogleSheet

 

Haven't tested yet, sorry

 



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



View solution in original post

6 REPLIES 6
Highlighted
Dual Super User III
Dual Super User III

Re: Split multiple Sharepoint field entries into Rows on a Google sheet

Hi!

Taking your example, let me see if I understood your requirements:

From a Sharepoint list item, whose Column1 contains url1;url2;url3 and whose Column2 contains url4;url5;url6... you need to add 3 rows into a Google sheet:

* first row with Column1 taking url1 value; Column 2 taking url4 value

* second row with Column1 taking url2 value; Column 2 taking url5 value

* third row with Column1 taking url3 value; Column 2 taking url6 value

Is my assumption correct?

 

Now, how did you define Column1 and Column2 in your SP list? As 'single row of text'? As multichoice?

Thanx!

 



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



Highlighted
Regular Visitor

Re: Split multiple Sharepoint field entries into Rows on a Google sheet

Hi, thanks for the quick reply

 

yes that is exactly what i'm trying to do

 

To add some context to the existing sharepoint list where the data is collected.  The columns are set up as 'single line text' format.  The inputs are gathered from a Form that is connected to the list.  The user inputs for example, 3 urls in one field, and 3 in the other, and the goal is to eventually split them on the Google sheet exactly as you have confirmed in your response

 

Highlighted
Dual Super User III
Dual Super User III

Re: Split multiple Sharepoint field entries into Rows on a Google sheet

Hi!

That's a very tricky challenge!

So, in order to get all URLs from the SP item that triggered your flow, you need to use split() function.

First I would add an 'Initialize variable' action block, type array, let's call it 'myOutputArray', value empty

Next, I would add an 'Initialize variable' action block, type integer, let's call it 'currentIndex', value 0

Next, I would add an Apply to Each and assign as its input the following expression:

split(triggerBody()?['Column1'],';')

 

Inside the 'Apply to each' I would:

-add an Append to Array variable, name myOutputArray, value:

{
"Column1": "@{item()}", 
"Column2": "@{split(triggerBody()?['Column2'],';')[variables('currentIndex')]}"
}

-add an Increment variable action block, name 'currentIndex', value 1

Now outside the 'Apply to each', I would add dummy Compose action block just for troubleshooting purposes, assign as its value myOutputArray'. THis way when executing the flow you can inspect its content easily.

Next I would add a second Apply to each, assign as its input 'myOutputArray'. Inside this second 'Apply to each' I would add the action block that creates a new row in GoogleSheet

 

Haven't tested yet, sorry

 



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



View solution in original post

Highlighted
Regular Visitor

Re: Split multiple Sharepoint field entries into Rows on a Google sheet

Thanks for the great advise @efialttes  

 

this looks to be working pretty well

 

Your functions have split the data from the 2 columns exactly as intended.  This is what i'm getting back in Compose function

 
 

 

chuck1234_0-1600961438991.png

 

Excuse my ignorance here but i'm just having trouble in my final Apply to each loop that inserts the row into the Google sheet as I can't seem to split the array data into each column in the google sheet.  I'm sure i'm missing something obvious but how can i separate out the results into separate columns? If i reference the 'Outputs' block as shown below, it displays the entire array including both column1 and 2 data in each cell i add it to?

 

Do i need to use a split function on the output data as well??

 

 

chuck1234_0-1600962797967.png

 

 

Here is the result in the Google Sheet of using the 'Outputs' as shown above. It seems to loop over full array 3 times (guess that's because of the amount of indexes in each column?)

 

chuck1234_0-1600963043677.png

 

 
Highlighted
Dual Super User III
Dual Super User III

Re: Split multiple Sharepoint field entries into Rows on a Google sheet

Hi!

So, we are closer to a happy ending, right?

 

Now, on your new challenge. In order to understand how the last 'Apply to each' works I would add a dummy 'Compose' action block inside it, just before  'Insert row 2', and assign as its value the following WDL expression:

 

item()

 

Next, on your 'Insert row 2' I would assign the following WDL expressions to your target Google Sheet columns

 

item()?['Column1']
item()?['Column2']

 

Reexecute the flow, inspect each iteration of your last 'Apply to each' and you will realize it is iterating all through its input array elements,

Hope this helps



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



Highlighted
Regular Visitor

Re: Split multiple Sharepoint field entries into Rows on a Google sheet

Thank you for your help with this, it worked perfectly!

 

 

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Power Platform ISV Studio

Power Platform ISV Studio

ISV Studio is designed to become the go-to Power Platform destination for ISV’s to monitor & manage published applications.

Upcoming Events

Experience what’s next for Power Automate

See the latest Power Automate innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Top Solution Authors
Top Kudoed Authors
Users online (7,418)