Microsoft Bookings - Reports

[Deleted]

Would love to be able to report on Microsoft Bookings.
Future pipe line, previous appointments  ect.

is this in the roadmap at all?

[#2]

@Christine_Chong we did not find a way to do that, but would love to be able to...

[#3]

Thank you! This is great!

[#4]

@Christine_Chong 

I have been working on this over the past week and the only way I have found to accomplish this is to pull that information from MSGraph.  I'm not a programmer or powershell advanced user; I just learn by myself, so this was a big learning curve.

Anyways, there were quite a few steps but the biggest thing is authenticating to MSGraph.  I spent close to 2 days trying to get this right.  I can now pull up all booking appointments for a specific Booking email address.  

You'll need to have rights to add App registrations in Azure AD as well as have Full Access rights to the Booking Account's mailbox (this is so your account can read/pull the calendar info).  Then in Powershell, you'll need to install 2 modules:

Microsoft.Graph.Authentication

Microsoft.Graph.Bookings

Trying to get this to pull all appointments for all Booking accounts and export to excel.  Not quite there yet though.  If you want the specific instructions, just let me know.  Since I just got this working yesterday, I still have to document it for work so I can supply it.

[#5]

@brphillips Thank you! I will have to play around with that some time. I am not too techy myself either! I will definitely reach out to you if I have any questions.

Thanks!

[#6]

@brphillips 

Hello, i have been looking for a solution to export the report to Sharepoint List.  Are you able to provide the instruction for me as well.  Thanks in advance.

[#7]

@brphillips 

If you can send me the details that would be great too.  I have been asked to working on reporting for desk booking system created on MS Bookings..

Thank you!

Heath

[#8]

I would also be interested in this. I was looking for a way to pull it all through into power BI... 

[#9]

Doesnt this also show all other calendar entries not just Microsoft bookings entries?

[#10]

Did you find a solution yet? I am also looking to find how to extract these custom field notes.

[#11]

@brphillips Is your document still available?

[#12]

@brphillips Are you still able to provide documentation on this?

[#13]

@brphillipsI'd also love to see how you did this - I had a look at Graph but couldn't see how to access the custom fields.  I'm currently parsing the preview text in the confirmation emails, but this doesn't include the 'additional information' from custom fields.

[#14]

@brphillipsdo you have your notes please need to do this tomorrow so would be very useful if you don't mind sharing.  Thank you  Catherine

[#15]

@brphillips If you can provide me with the reporting guide you were able to discover this may be helpful for what we are attempting to complete with Bookings as well. 

[#16]

I have been using the bookings for about 1 year now, and it still does not show anything being booked in the home section of bookings, even though we are getting loads of appointments. This being said, when I export to TSV, there is no data. Am I missing a setting or something?

Thanks!

[#17]

I hope this is helpful

How to export Bookings to an Excel sheet

1.  

• 
  Find out the ID that Bookings uses for a calendar. In our case I defined the business name as “School Primary School” and so so the name it used was
  school@school.com

1. Go to the MS admin portal and change the password of that user
2. Log on to https://outlook.office.com/calendar with that user
3. Go to Settings – View all Outlook Settings -> Shared Calendars – Select the calendar
4. Check that your own user can view the calendar
5. Go to the desktop Outlook app
6. Go to File -> Account Settings -> Account Settings -> Change -> More settings -> Advanced -> Add mailbox and enter your equivalent of 
   school@school.com
7. Click OK -> Next -> Done -> Close
8. Click File -> Open and Export – Import/Export -> Export to File -> Next -> Comma Separated Values -> Next
9. Choose the calendar that matches your business name
10. Next -> Choose a file name (I chose calendar_output.CSV -> Next -> Finish
11. Choose a date range – OK
12. Open the exported CSV and open another new Excel sheet. The new excel sheet needs two rows:
13. Row one contains the following text fields
    1. Date
    2. Start Time
    3. End Time
    4. Length
    5. Staff Member
    6. Student Email
    7. Join link
    8. Name_Pos
    9. Name-Len
    10. Email_Pos
    11. Email_len
    12. Booking_pos
    13. Join_pos
    14. Join_len
    15. If_you_pos

1. Row two should contain the following formulas
   1. =(calendar_output.CSV!B2)
   2. =(calendar_output.CSV!C2)
   3. =(calendar_output.CSV!E2)
   4. =C2-B2
   5. =(calendar_output.CSV!K2)
   6. =MID(calendar_output.CSV!P2,L2,M2)
   7. =MID(calendar_output.CSV!P2,N2,O2)
   8. =MID(calendar_output.CSV!P2,Q2,R2)
   9. (…blank – for sanity)
   10. =HYPERLINK((MID(calendar_output.CSV!P2,Q2,R2)),"Join link")
   11. =(calendar_output.CSV!A2)
   12. =FIND("Name:",calendar_output.CSV!P2)+6
   13. =N2-L2-6
   14. =FIND("Email:",calendar_output.CSV!P2)+6
   15. =P2-N2-6
   16. =FIND("Booking Info",calendar_output.CSV!P2)+6
   17. =FIND("Join Teams Meeting",calendar_output.CSV!P2)+18
   18. =S2-Q2
   19. =FIND("If you need a local number, get one here",calendar_output.CSV!P2)
2. Copy/drag row two down to match the number of rows in calendar_output.CSV
3. You should see your data in an easy to read format.

[#18]

@SimonWindisch I found that the export in the bookings page does work, you just need to change your date range to when the bookings are expected to happen, not when the bookings take place. Once i changed the date to one day past our scheduled event, the TSV file was fully populated.

One limitation i did notice is that the when it is imported into Excel, the person who received the booking is only their initials, not their full name. This caused confusion because we had multiple employees with the same 2 initials.

Thanks for the Reply!

[#19]

you can get the data in microsoft graph also. 

login at Graph Explorer - Microsoft Graph

assign the proper permission (check screenshot 01 and 02)

run the below query in GET > beta and copy the id (check screenshot 03)

https://graph.microsoft.com/beta/bookingBusinesses/

replace tttttt with id in the below query 

Now run the below query and you have all the data (check screenshot 04)

https://graph.microsoft.com/beta/bookingBusinesses/tttttt/appointments

for more details check Microsoft Bookings and Microsoft Graph | Kurt Hatlevik – Dynamics 365 Blog

[#20]

Amazing! Thank you! This has saved me so long as was about to give up and manually create a list!

[#21]

Is there any update with this? We add notes during the appointment which would be advantageous to have access to in the reporting spreadsheet we create?