Passing variables to get data from SharePoint list

[AndreiV]

Hi,

I have a sharepoint list that has 89 columns, each column named Question1 through Question89. I would like to build a flow where I initialize an integer variable called QNo and the have a Do Until action that replaces the values in an HTML file with the values of Question1 through Question89 from a SharePoint list item.

How can I pass the variable to get the values of Question1 through Question89? The flow is manually triggered and gets the column values using a Get Item action.

Many thanks,

Andrei 

[eliotcole]

Hi, @AndreiV , without questioning your knowledge here ... is there a reason why you can't just take the data as is from SharePoint?

Each list item will contain something similar to the following:

{
  "Question01": "Answer01",
  "Question02": "Answer02",
  "Question03": "Answer03",
  "Question04": "Answer04",
  "Question05": "Answer05",
  "Question06": "Answer06",
  "Question07": "Answer07",
  "Question08": "Answer08",
  "Question09": "Answer09",
  "Question10": "Answer10",
  "Question11": "Answer11"
}

Even if you directly make an HTML table (using the Power Automate action) from this, you'll have a capable list of all the answers to each question.

If you would rather not use a table, you can simply create an Apply to each with some HTML code in a Compose or Variable, with applicable labelling. Then place the field values in the right place in said HTML. Each loop will place a the next item's answer in there.

The only tiresome things you'll have to do is write the HTML, and insert each of the items.

If you wanted to make that easier on yourself, assuming the Apply to each is named 'Apply to each', then you could write the code of the Compose/Variable elsewhere, and paste it in. Then for each question you would refer to it with:

@{items('Apply_to_each')?['QuestionNN']}

 Where the 'NN' in 'QuestionNN' should be a number, example 'Question05'.

Test it out yourself. 🙂

[AndreiV]

Many thanks @eliotcole ! I'm definitely a newb when it comes to all things power automate!

What I am in essence doing (or rather trying to) is to follow this video to create a PDF of a selected questionnaire from the SharePoint list. However, since I have 89 questions, I was hoping to replace each Compose action in the video with some sort of loop. So to go back to your suggested solution, is there a way to write the QuestionNN something like concat('Question', variables(QNo))? What I am trying to avoid is having to write 89 statements.

Many thanks,

Andrei

[eliotcole]

I should point out that the link you've provide points to nowhere. Also, yeah, like I said, you're good to go with the above, you won't have to write all of them.

Oh, and just another thing ... I don't think that what you're attempting to do (unless you are regularly coming up with 89 different questions) is probably going to be (for you) a good use of Power Automate.

You'd be far better off using excel and NotePad++ shortcuts to quickly creating your HTML, then paste that into a compose and do whatever PDF creations that you need to do.

Anyhoo ... I just created a table in excel in a couple of minutes that has column names of Question01-89 with a drag and transpose. Then I filled in the rest of it with nonsense, here's a sample:

  Question01 Question02 Question03

Actual Question

asdfasdf1

asdfasdf2

asdfasdf3

etc

I then made a SharePoint list from the table.

So ... I now have (what I can only assume is) a list like yours.

If I simply want to use that one row to make something out of, then the Select action that used the value of the list pull and set the advanced version of the map field to this:

{
  "Question01": "@{item()?['field_Question01']}",
  "Question02": "@{item()?['field_Question02']}",
  "Question03": "@{item()?['field_Question03']}",
  "Question04": "@{item()?['field_Question04']}",
  "Question05": "@{item()?['field_Question05']}",
  "Question06": "@{item()?['field_Question06']}",
  "Question07": "@{item()?['field_Question07']}",
  "Question08": "@{item()?['field_Question08']}",
  "Question09": "@{item()?['field_Question09']}",
  "Question10": "@{item()?['field_Question10']}",

etc

... that should give whatever thing comes next a load of questions labelled with Question 01-89 with an actual question available to them.

I use notepad++ to do most of this, though, on my desktop.

A simpler way that I could handle it would be, again, in NotePad++, to use the block copy/paste commands to create the desired HTML, or use excel/g-sheets accordingly. The only difference would be that I would have something akin to this:

<p><span class="questionLabel">Question01</span> - <span class="question" id="question01">@{outputs('Get_items')?['body/value'][0]?['field_Question01']}</span></p>
<p><span class="answerLabel">Answer:</span> <span class="answerSpace">&nbsp;</span></p>
<p><span class="questionLabel">Question02</span> - <span class="question" id="question02">@{outputs('Get_items')?['body/value'][0]?['field_Question02']}</span></p>
<p><span class="answerLabel">Answer:</span> <span class="answerSpace">&nbsp;</span></p>
<p><span class="questionLabel">Question03</span> - <span class="question" id="question03">@{outputs('Get_items')?['body/value'][0]?['field_Question03']}</span></p>
<p><span class="answerLabel">Answer:</span> <span class="answerSpace">&nbsp;</span></p>
<p><span class="questionLabel">Question04</span> - <span class="question" id="question04">@{outputs('Get_items')?['body/value'][0]?['field_Question04']}</span></p>
<p><span class="answerLabel">Answer:</span> <span class="answerSpace">&nbsp;</span></p>
<p><span class="questionLabel">Question05</span> - <span class="question" id="question05">@{outputs('Get_items')?['body/value'][0]?['field_Question05']}</span></p>
<p><span class="answerLabel">Answer:</span> <span class="answerSpace">&nbsp;</span></p>
<p><span class="questionLabel">Question06</span> - <span class="question" id="question06">@{outputs('Get_items')?['body/value'][0]?['field_Question06']}</span></p>
<p><span class="answerLabel">Answer:</span> <span class="answerSpace">&nbsp;</span></p>
<p><span class="questionLabel">Question07</span> - <span class="question" id="question07">@{outputs('Get_items')?['body/value'][0]?['field_Question07']}</span></p>
<p><span class="answerLabel">Answer:</span> <span class="answerSpace">&nbsp;</span></p>
<p><span class="questionLabel">Question08</span> - <span class="question" id="question08">@{outputs('Get_items')?['body/value'][0]?['field_Question08']}</span></p>
<p><span class="answerLabel">Answer:</span> <span class="answerSpace">&nbsp;</span></p>
<p><span class="questionLabel">Question09</span> - <span class="question" id="question09">@{outputs('Get_items')?['body/value'][0]?['field_Question09']}</span></p>
<p><span class="answerLabel">Answer:</span> <span class="answerSpace">&nbsp;</span></p>
<p><span class="questionLabel">Question10</span> - <span class="question" id="question10">@{outputs('Get_items')?['body/value'][0]?['field_Question10']}</span></p>
<p><span class="answerLabel">Answer:</span> <span class="answerSpace">&nbsp;</span></p>

But I'm not going to do it all for ya, I reckon you can figure that out. 👍

I don't think that it seems like PA is the tool for the job, here, mate.

[AndreiV]

Thank you once again, @eliotcole !

Apologies for the non-working link! Let me try that again:

https://www.youtube.com/watch?v=ZLpRwQzkP58&ab_channel=LernenTech 

We do get quite a few of these questionnaires submitted. There's a PowerApps app to fill these out, and we also have a PowerBI report where the questionnaires can be viewed. Unfortunately Microsoft's printing solutions for both PowerApps and PowerBI are lacking - you get a screenshot , so anything that needs to be scrolled doesn't show. 

Like in the linked video, I do have an HTML template that gets populated with the question answers. Your advice on using another editor made me realize that I can easily use Excel to generate several nested replace functions in a compose action to accomplish what I needed. Definitely not something I could have written in power automate with the interface provided. 

Many thanks!

replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(outputs('Status'), '{Q01 Answer}', outputs('Get_item')?['body/Question1/Value']), '{Q02 Answer}', outputs('Get_item')?['body/Question2/Value']), '{Q03 Answer}', outputs('Get_item')?['body/Question3/Value']), '{Q04 Answer}', outputs('Get_item')?['body/Question4/Value']), '{Q05 Answer}', outputs('Get_item')?['body/Question5/Value']), '{Q06 Answer}', outputs('Get_item')?['body/Question6/Value']), '{Q07 Answer}', outputs('Get_item')?['body/Question7/Value']), '{Q08 Answer}', outputs('Get_item')?['body/Question8/Value']), '{Q09 Answer}', outputs('Get_item')?['body/Question9/Value']), '{Q10 Answer}', outputs('Get_item')?['body/Question10/Value']), '{Q11 Answer}', outputs('Get_item')?['body/Question11/Value']), '{Q12 Answer}', outputs('Get_item')?['body/Question12/Value']), '{Q13 Answer}', outputs('Get_item')?['body/Question13/Value']), '{Q14 Answer}', outputs('Get_item')?['body/Question14/Value']), '{Q15 Answer}', outputs('Get_item')?['body/Question15/Value']), '{Q16 Answer}', outputs('Get_item')?['body/Question16/Value']), '{Q17 Answer}', outputs('Get_item')?['body/Question17/Value']), '{Q18 Answer}', outputs('Get_item')?['body/Question18/Value']), '{Q19 Answer}', outputs('Get_item')?['body/Question19/Value']), '{Q20 Answer}', outputs('Get_item')?['body/Question20/Value']), '{Q21 Answer}', outputs('Get_item')?['body/Question21/Value']), '{Q22 Answer}', outputs('Get_item')?['body/Question22/Value']), '{Q23 Answer}', outputs('Get_item')?['body/Question23/Value']), '{Q24 Answer}', outputs('Get_item')?['body/Question24/Value']), '{Q25 Answer}', outputs('Get_item')?['body/Question25/Value']), '{Q26 Answer}', outputs('Get_item')?['body/Question26/Value']), '{Q27 Answer}', outputs('Get_item')?['body/Question27/Value']), '{Q28 Answer}', outputs('Get_item')?['body/Question28/Value']), '{Q29 Answer}', outputs('Get_item')?['body/Question29/Value']), '{Q30 Answer}', outputs('Get_item')?['body/Question30/Value']), '{Q31 Answer}', outputs('Get_item')?['body/Question31/Value']), '{Q32 Answer}', outputs('Get_item')?['body/Question32/Value']), '{Q33 Answer}', outputs('Get_item')?['body/Question33/Value']), '{Q34 Answer}', outputs('Get_item')?['body/Question34/Value']), '{Q35 Answer}', outputs('Get_item')?['body/Question35/Value']), '{Q36 Answer}', outputs('Get_item')?['body/Question36/Value']), '{Q37 Answer}', outputs('Get_item')?['body/Question37/Value']), '{Q38 Answer}', outputs('Get_item')?['body/Question38/Value']), '{Q39 Answer}', outputs('Get_item')?['body/Question39/Value']), '{Q40 Answer}', outputs('Get_item')?['body/Question40/Value']), '{Q41 Answer}', outputs('Get_item')?['body/Question41/Value']), '{Q42 Answer}', outputs('Get_item')?['body/Question42/Value']), '{Q43 Answer}', outputs('Get_item')?['body/Question43/Value']), '{Q44 Answer}', outputs('Get_item')?['body/Question44/Value']), '{Q45 Answer}', outputs('Get_item')?['body/Question45/Value']), '{Q46 Answer}', outputs('Get_item')?['body/Question46/Value']), '{Q47 Answer}', outputs('Get_item')?['body/Question47/Value']), '{Q48 Answer}', outputs('Get_item')?['body/Question48/Value']), '{Q49 Answer}', outputs('Get_item')?['body/Question49/Value']), '{Q50 Answer}', outputs('Get_item')?['body/Question50/Value']), '{Q51 Answer}', outputs('Get_item')?['body/Question51/Value']), '{Q52 Answer}', outputs('Get_item')?['body/Question52/Value']), '{Q53 Answer}', outputs('Get_item')?['body/Question53/Value']), '{Q54 Answer}', outputs('Get_item')?['body/Question54/Value']), '{Q55 Answer}', outputs('Get_item')?['body/Question55/Value']), '{Q56 Answer}', outputs('Get_item')?['body/Question56/Value']), '{Q57 Answer}', outputs('Get_item')?['body/Question57/Value']), '{Q58 Answer}', outputs('Get_item')?['body/Question58/Value']), '{Q59 Answer}', outputs('Get_item')?['body/Question59/Value']), '{Q60 Answer}', outputs('Get_item')?['body/Question60/Value']), '{Q61 Answer}', outputs('Get_item')?['body/Question61/Value']), '{Q62 Answer}', outputs('Get_item')?['body/Question62/Value']), '{Q63 Answer}', outputs('Get_item')?['body/Question63/Value']), '{Q64 Answer}', outputs('Get_item')?['body/Question64/Value']), '{Q65 Answer}', outputs('Get_item')?['body/Question65/Value']), '{Q66 Answer}', outputs('Get_item')?['body/Question66/Value']), '{Q67 Answer}', outputs('Get_item')?['body/Question67/Value']), '{Q68 Answer}', outputs('Get_item')?['body/Question68/Value']), '{Q69 Answer}', outputs('Get_item')?['body/Question69/Value']), '{Q70 Answer}', outputs('Get_item')?['body/Question70/Value']), '{Q71 Answer}', outputs('Get_item')?['body/Question71/Value']), '{Q72 Answer}', outputs('Get_item')?['body/Question72/Value']), '{Q73 Answer}', outputs('Get_item')?['body/Question73/Value']), '{Q74 Answer}', outputs('Get_item')?['body/Question74/Value']), '{Q75 Answer}', outputs('Get_item')?['body/Question75/Value']), '{Q76 Answer}', outputs('Get_item')?['body/Question76/Value']), '{Q77 Answer}', outputs('Get_item')?['body/Question77/Value']), '{Q78 Answer}', outputs('Get_item')?['body/Question78/Value']), '{Q79 Answer}', outputs('Get_item')?['body/Question79/Value']), '{Q80 Answer}', outputs('Get_item')?['body/Question80/Value']), '{Q81 Answer}', outputs('Get_item')?['body/Question81/Value']), '{Q82 Answer}', outputs('Get_item')?['body/Question82/Value']), '{Q83 Answer}', outputs('Get_item')?['body/Question83/Value']), '{Q84 Answer}', outputs('Get_item')?['body/Question84/Value']), '{Q85 Answer}', outputs('Get_item')?['body/Question85/Value']), '{Q86 Answer}', outputs('Get_item')?['body/Question86/Value']), '{Q87 Answer}', outputs('Get_item')?['body/Question87/Value']), '{Q88 Answer}', outputs('Get_item')?['body/Question88/Value']), '{Q89 Answer}', outputs('Get_item')?['body/Question89/Value'])

[eliotcole]

Glad you're good, @AndreiV ... I also accidentally just found something that could make life a lot easier for your HTML. It's the 'counter' section of this:

https://www.youtube.com/watch?v=0gayskscLY4