How to get the full Sales by Product/Service Detail Report using QuickBooks API?

I have the below Python code trying to get Sales by Product/Service Detail Report data.

Under the documentation: https://developer.intuit.com/app/developer/qbo/docs/api/accounting/report-entities/SalesByProduct

It looks like the end point is going to be [ItemSales] even though the end point for the API documentation is [SalesByProduct] which is confusing.

How can I change the code below to be able to pull all the default metrics:

• Product/Service
• Date
• Transaction type
• Num
• Customer name
• Memo/Description
• Qty
• Rate
• Amount
• Balance

I attached a screenshot of how the report looks like under QuickBooks UI when I run the Sales by Product/Service Detail report.

Also, I want to be able to pull all the data, currently I only get 96 object but when I run my report via the UI I get 2315 rows. is there a pagination mechanism within reports API? if so, how can I implement it?

Note: When I run the report under the UI, I don't use any value for the grouping in order to see to get the data at the item line level, does that mean I should not use [summarize_column_by] attribute

def fetch_sales_by_product(access_token):

  url = f'https://quickbooks.api.intuit.com/v3/company/11111111111/reports/ItemSales' 
  headers = {
    'Authorization': f'Bearer {access_token}',
    'Accept': 'application/json'
  }

  params = {
    'start_date': '2021-01-01',
    'end_date': '2024-04-01',
    'summarize_column_by': 'ProductsAndServices'
  }

  all_data = []
  counter = 1

  while True:
    response = requests.get(url, headers=headers, params=params)
    print(f"{response.status_code=}")
    if response.status_code != 200:
      raise Exception(f"Failed to fetch report: {response.text}" )
    data = response.json()
    all_data.extend(data['Rows']['Row'])
    counter += 1

  return all_data

Answer

To modify the provided Python code to pull all the default metrics (Product/Service, Date, Transaction type, Num, Customer name, Memo/Description, Qty, Rate, Amount, Balance) and to handle pagination to retrieve all the data from the Sales by Product/Service Detail report in QuickBooks, you need to make the following adjustments:

1. Change the endpoint URL to /reports/SalesByProduct as per the QuickBooks API documentation.
2. Remove the summarize_column_by parameter because you want to get the data at the item line level and not summarize it.
3. Implement pagination by checking for the existence of a next_page_url in the response and updating the params with the page parameter to fetch the next page of data.
4. Ensure that you handle potential errors and exceptions gracefully.

Here's the modified Python code:

import requests

def fetch_sales_by_product(access_token): url = 'https://quickbooks.api.intuit.com/v3/company/11111111111/reports/SalesByProduct' headers = { 'Authorization': f'Bearer {access_token}', 'Accept': 'application/json' }

params = {
    'start_date': '2021-01-01',
    'end_date': '2024-04-01'
}

all_data = []

while True:
    response = requests.get(url, headers=headers, params=params)
    print(f"Status code: {response.status_code}")

    if response.status_code != 200:
        raise Exception(f"Failed to fetch report: {response.text}")

    data = response.json()
    all_data.extend(data['Rows']['Row'])

    # Check for pagination
    if 'next_page_url' in data:
        url = data['next_page_url']
        # Extracting page number from the next_page_url
        page_number = int(url.split('=')[-1])
        # Update params with the page number
        params['page'] = page_number
    else:
        break

return all_data

This modified code should now correctly fetch all the data from the Sales by Product/Service Detail report in QuickBooks by handling pagination and without summarizing the data. Additionally, it ensures error handling for any potential issues during the API requests.