Split multiple Sharepoint field entries into Rows on a Google sheet

[chuck1234]

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!

This is my current flow: 

thanks!

[efialttes]

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!

[chuck1234]

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

[efialttes · Accepted solution]

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

[chuck1234]

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

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??

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?)

[efialttes]

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

[chuck1234]

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