Automatically import reviews data into Excel

Preparation

You first need to set up SmileBack's Dropbox integration to get an automatically updated export of your reviews. Read about how to do that here.

Set up

1. Open a new, blank Excel file.
2. Go to the Data tab.
3. Click on Get Data, then From File, then From JSON.
   
4. Navigate to your Dropbox folder, then the "Apps" folder, then the "SmileBack" folder, select "SmileBack_RecentReviews.json".
5. This will open the Power Query Editory, click on the View tab and then Advanced Editor
6. Replace these last two lines of the query

   in
    Source

   with this

     ,
    asTable = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    expandedReview = Table.ExpandRecordColumn(asTable, "Column1", {"id", "rating", "comment", "ticket", "contact", "company", "status", "tags", "has_marketing_permission", "viewed_on", "rated_on", "permalink", "last_modified"}, {"id", "rating", "comment", "ticket", "contact", "company", "status", "tags", "has_marketing_permission", "viewed_on", "rated_on", "permalink", "last_modified"}),
    expandedTicket = Table.ExpandRecordColumn(expandedReview, "ticket", {"id", "title", "segment", "agents", "closed_on"}, {"ticket.id", "ticket.title", "ticket.segment", "ticket.agents", "ticket.closed_on"}),
    expandedTicketSegment = Table.ExpandRecordColumn(expandedTicket, "ticket.segment", {"id", "name"}, {"ticket.segment.id", "ticket.segment.name"}),
    expandedTicketAgentList = Table.TransformColumns(expandedTicketSegment, {"ticket.agents", each Text.Combine(List.Transform(_, Text.From), ";"), type text}),
    expandedContact = Table.ExpandRecordColumn(expandedTicketAgentList, "contact", {"id", "name", "email"}, {"contact.id", "contact.name", "contact.email"}),
    expandedCompany = Table.ExpandRecordColumn(expandedContact, "company", {"id", "name"}, {"company.id", "company.name"}),
    expandedTags = Table.TransformColumns(expandedCompany, {"tags", each Text.Combine(List.Transform(_, Text.From), ";"), type text}),
    strictlyTypedColumns = Table.TransformColumnTypes(expandedTags, {{"rated_on", type datetimezone}, {"viewed_on", type datetimezone}, {"rating", Int64.Type}, {"id", Int64.Type}, {"ticket.closed_on", type datetimezone}, {"has_marketing_permission", type logical}, {"last_modified", type datetimezone}}),
    sortedRows = Table.Sort(strictlyTypedColumns, {{"rated_on", Order.Descending}})
   in
    sortedRows

7. Click Done.
8. The query will now show the expanded JSON file, click on the Home tab and then Close & Load.
9. You will now see the data formatted as a table in your Excel workbook. You can then simply click on the Refresh button when you want to add new data to the table.