Other Questions

E3ITLLC · ‎June 11, 2020

I have an update on this!

It may be a little technical and challenging for some people to follow but I can confirm that it works and doesn't take much time to accomplish.

Here's how:

Download the Invoices JSON file
Open a New Excel spreadsheet
Go to the Data tab in the ribbon and click Get Data > From File > From JSON (drop down)
Select the JSON file you downloaded and hit Import
The Power Query Editor will open
Click Queries (on the left) then right click the query and choose Advanced Editor
Copy and paste the code below and hit Done (BE SURE that the path to and the file name of your JSON file is correct)
To put this data into the Excel file click Close & Load (top left)

P.S. The first code I provided removes some columns that I did not find helpful and set numbers to currency.

The second code is the same with the addition of expanding each invoice to show all line items.

The Power Query Editor makes it rather easy to change the query without having to actually write code yourself; just make changes to the content that is displayed (e.g. remove columns, change numbers to currency, etc...).

***You can also duplicate your query (right click > duplicate) to create a different view of the same data. A prime example would be a list of all invoices without the line items expanded. If you have multiple queries, when you Close and Load to your spreadsheet you can toggle between these 'views' by selecting the query that appears to the right of the spreadsheet. Oh, and you can rename your queries to make their function more obvious. Pretty cool and very useful!

The video from this blog really helped me understand this process:

https://theexcelclub.com/how-to-parse-custom-json-data-using-excel/

Here's the direct video link:

https://www.youtube.com/watch?v=q6sKs2KLnOo

1 - [Invoices Query]

let

Source = Json.Document(File.Contents("C:\Users\MasonMitchell\OneDrive - E3IT.tech\Downloads\QBSE_Invoices (1).json")),

AsTable = Table.FromRecords(Source),

#"Removed Columns" = Table.RemoveColumns(AsTable,{"dateCreated", "dateUpdated"}),

#"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"amount", Currency.Type}}),

#"Removed Columns1" = Table.RemoveColumns(#"Changed Type",{"message", "allowOnlineCreditCardPayment", "allowOnlineACHPayment", "businessNumber", "logoSource", "paymentDetails", "salesTaxType", "rememberPaymentDetails", "v4LocalId", "events", "invoiceSender", "salesTaxItems"}),

#"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns1",{{"dueDate", type date}})

in

#"Changed Type1"

2 - [Invoices with Line Items Query]

let

Source = Json.Document(File.Contents("[PATH TO YOUR FILE]\QBSE_Invoices.json")),

AsTable = Table.FromRecords(Source),

#"Removed Columns" = Table.RemoveColumns(AsTable,{"dateCreated", "dateUpdated"}),

#"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"amount", Currency.Type}}),

#"Removed Columns1" = Table.RemoveColumns(#"Changed Type",{"message", "allowOnlineCreditCardPayment", "allowOnlineACHPayment", "businessNumber", "logoSource", "paymentDetails", "salesTaxType", "rememberPaymentDetails", "v4LocalId", "events", "invoiceSender", "salesTaxItems"}),

#"Expanded invoiceLineItems" = Table.ExpandListColumn(#"Removed Columns1", "invoiceLineItems"),

#"Expanded invoiceLineItems1" = Table.ExpandRecordColumn(#"Expanded invoiceLineItems", "invoiceLineItems", {"description", "amount", "quantity", "type"}, {"invoiceLineItems.description", "invoiceLineItems.amount", "invoiceLineItems.quantity", "invoiceLineItems.type"}),

#"Changed Type1" = Table.TransformColumnTypes(#"Expanded invoiceLineItems1",{{"invoiceLineItems.amount", Currency.Type}, {"invoiceLineItems.quantity", Currency.Type}, {"dueDate", type date}})

in

#"Changed Type1"

!Note: You will still need to look up the actual names of your clients because only the customerId appears in the JSON file. Once you are able to create a reference of client name to customerId you can use a formula to show the name in addition to the ID.

This is how I got client names in my invoice data (this part sucks but the good news is that once you have this part done you won't have to do it again unless you've added a client and even then it is just the new ones need to be added to your reference list)

Copy and paste the customerId column to another sheet
Select the column and choose Remove Duplicates from the Data ribbon
Find the client name by either scrolling through the list of invoices by invoice number -OR- opening the invoices online using the invoiceUri link.

[TIP - to make all the links clickable insert a column and in the first cell of the new column type =HYPERLINK([@invoiceUri]) and hit enter, then use the up arrow key to focus back on the cell you were just in and double click the little square at the bottom right corner of the cell to apply that formula to the entire column.]

Type or copy/paste the name of the client to the right of the ID on the sheet where we consolidated the list of customerIds

This is much easier when both the invoice data and your list of IDs is in order (you can do this in the invoice table by clicking the upside down triangle at the top of the column and selecting Sort Smallest to Largest and in your list of reference IDs on the other page by first selecting the column then clicking the AZ[down-arrow] icon in the Data ribbon)

Once your list is complete add a column to the left of the ID column on the sheet with the JSON invoice data and enter this formula: =VLOOKUP(QBSE_Invoices__1[@customerId],Sheet1!A:B,2)
If your JSON invoice data is on Sheet2 and your consolidated customerId reference list is on Sheet1 then you should see the name of the client appear. Now just copy that formula to the rest of the cells in that column that line up with the invoice data table

…easy peasy, right? LOL!

Intuit, providing self employed users a reporting engine would mean the world to us. Going through this hassle just to get a detailed list of the invoices in our account is ridiculous and for many, not even possible.

Community, while Intuit is working on this functionality I will be work on a web project that will provide you with the ability to see your invoice data without having to go through all of these step. If I complete it before Intuit provides this functionality to use I will post a link to it in this thread. Also, feel free to message me if you need help with the steps outlined in this how-to.