Using bookings to excel

[FGRODRIGUEZ]

So in my organization we do a lot of appointments with Microsoft bookings, I'm making a power automate to collect all the information in one report that is easier to keep track on, the issue that I have is that we have some custom questions at the time of booking that we need to keep track of, this is what my power automate looks like now

 

So far i realized that if I use the compose connector I can turn the answers into the following string

[{"Answer":"12345","Question":"Código de Escuela","QuestionID":"7c7410b9-1e5f-4597-b3e9-fb47f1100bcf"},{"Answer":"Arecibo","AnswerOptions":["Arecibo","Bayamón","Caguas","Humacao","Mayagüez","Ponce","San Juan"],"Question":"Región Educativa","QuestionID":"558863af-2237-4206-8b77-4773a8537c84","SelectedOptions":[0]},{"Answer":"54321","Question":"TAL","QuestionID":"115bfd8a-2c99-42ab-846b-b5135d9e488d"}]

I need to extract from this the answers "12345" from "codigo de escuela", "Arecibo" from Answer options and "54321" from TAL

also, I need to be able to put this resulting answers into different columns within the excel sheet.

Is this possible?

[Angellyz]

Hi! Did you reach a solution?

[FGRODRIGUEZ]

Sadly, no

[ChadVKealey]

Apparently, this is currently a bug relating to 1:1 bookings (one client per appointment). Allegedly, when a "group booking" is created, the custom question response data is available. Since nobody in our org uses group bookings, I have not been able to verify this behavior. Hopefully Microsoft addresses this issue soon.

[RafaelSantana]

Were you able to solve this problem?
I have something similar, I need to get all the information from the booking and transfer it to excel

[ChadVKealey]

The connector was update a few months ago, not too long after I posted my previous message. The payload now includes custom questions and answers. See https://youtu.be/6fLyGFWKyPE for more information.

[RafaelSantana]

Thank you very much, the video is excellent, now I can work with it. I want my custom questions to be in separate columns, I'll work on that!

[ChadVKealey]

If you want to get real fancy, you could create a content type per appointment type so that only the relevant columns would appear in the create/update item action. If you only have one or two appointment types and not too many custom questions, it wouldn't be too bad to use the default content type, but if you've got a bunch, it could get messy very quickly.

[RafaelSantana]

In my case, there are 3 types of services with 4 custom questions (these are the same custom questions for services), but for me to be able to make filters in sharepoint, I need to put each custom question in a column, I hope it's not too difficult

[ChadVKealey]

It's not difficult if you understand array indexes (or indices?). Basically, the custom question/answer data will come in as an array. Question 1's question and answer will be the first node in the array, index 0. So, to get the answer to question 1, you need to get <array>[0]['Answer']. And so on for each additional question. 

[GraceGarwood]

Hi,

We do this and our results show up on an excel document.

Have you been able to do this? If not, I can send the flow we created for ours to pull through the custom answers.

If you have any questions, please do not hesitate to contact us. 

Kind Regards.

[ITSupport00]

Hi, 
Is the offer still available for you to send your flow? 

[GraceGarwood]

Hi,

Have you began the flow? If so, what do you have built so far?