Quickbooks Profit & Loss API - How Can I Capture Specific Data

Goal: Create an email for management to show them Total Income and Total Expenses for each department.

My Steps I need help with Step 2
Step 1) Run the Quickbooks Profit & Loss
Step 2) Extract the Summary Total Income & Summary of Total Expense for each department. (example: Total Tech Income and Total Tech Expenses)
Step 3) Utilize that to Create a Daily Email to management showing them the Total Income - Total Expense for each department.

Step 1
Running the Profit & Lost API
Summary: I am currently querying the data based on start_date, end_date and summarize_column_by = Total. *would love to know if there is a better way to get what I need.
Here is what I get for the output, and this is an example of the Tech Income I need.

Step 2:
What is the best next step? I would like to establish the following Variable and if I can learn how to do it for Tech Income, I should be able to figure out the rest (maybe).

I have attempted a lot and after several hours, threw in the towel and need advice from some of the greats in this community.

I think I made some progress but still need to figure out a little more. Getting it to output to XML makes the data better to work with.

Here is the xml Data


I have gone as far as doing a Text Parser (replace) and used Regex to find and replace all with a comma.

Here is the output


Any suggestion on get this data to export as CC Discount Forfeit, 817.04 or ideally, get them to output as a bundle.

Here is what I figured out so far. Need to know if there is a better way to do this to save on operations

  1. Quickbooks - Running the P&L API * I had to eliminate using summarize_column_by = Total because I couldn’t get the expenses by doing that.

  2. Transform to JSON - My first time using JSON

  3. Convert JSON to XML - Because it makes more sense to me.

  4. Text Parser - Extract everything between


  5. Markdown - Eliminate HTML

  6. Text Parser - Add a newline between the text and the number.

  7. Text Parser - (.)\n(.)


I just want to quickly say congrats on getting this up and running. :clap:

Let’s see if someone from the community has some scenario optimization tips to share. :slight_smile:

1 Like