I wrote a report to spreadsheet array converter... interested in adding this to the code?

[jlvanhulst]

The report API is pretty powerful but the resulting JSON imposing. I could not find an easy processor. The class below converts the report JSON in an multi dimensional array that could go straight to xls or google sheets with a single line of code. Is there an interest of adding this to the codebase? (In that case I would have to add a test, which does not seem too hard)

Example:

def getProfitandloss(client): report = client.get_report('ProfitAndLoss', qs = { "date_macro":'This Fiscal Year-to-date', "accounting_method":'Accrual',"summarize_column_by":'Month'}) return convertReport(report).mainArray

class convertReport:

Converts a Quickbooks report Json in a spreadsheet like Array

def __init__(self,report):
    self.report = report
    self.mainArray = [[]]
    self.colTypes = [] # to keep track of the type of each column
    self.indent = 0 # indent level for the first column text field
    self.header() #create header row and set column types
    self.processRows(report["Rows"]["Row"]) #process the rows

def header(self):
    self.header = self.report["Header"]
    self.columns = self.report["Columns"]["Column"]  
    for column in self.columns:
        self.colTypes.append(column["ColType"])
        self.mainArray[0].append(column["ColTitle"])    

def cells(self,row):
    # convert a single 'row' of cells into an array, using the column types to convert the values in Floats where type is 'Money'
    # empty money cells are converted to None
    # the first cell of the row is indented by the indent level
    array=[]
    for col in row:
        if (self.colTypes[len(array)] == "Money"):
            if col["value"] == '':
                array.append(None)
            else:
                array.append(float(col["value"]))
        else:
            array.append(col["value"])
    array[0] = " "*self.indent + array[0]
    return array

def processRows(self,row):
    # recursively process the rows of the report
    for r in row:
        if "Header" in r: # process a header row, increment indent
            self.mainArray.append(  self.cells( r["Header"]["ColData"]) )
            self.indent += 1

        if "Rows" in r: # this is a row with subrows, process the subrows
            self.processRows(r["Rows"]["Row"])
        elif "ColData" in r: # an actual data row
            self.mainArray.append(  self.cells( r["ColData"]) )

        if "Summary" in r: # summary row, decrease indent
            self.mainArray.append( self.cells( r["Summary"]["ColData"]))
            self.indent -= 1  

[jaredthecoder]

@jlvanhulst This is great! I'd be meaning to get around to writing something like this to make QBO's report output more useful. I support adding it in the next release, but obviously that decision is @ej2's.

[ej2]

Report features are lacking in this library. Ultimately I would like to add report objects for all of the QBO reports. Those objects could have alternate ways of accessing the data.

That said, if @jlvanhulst is interested in putting together a PR I will review it.

[jlvanhulst]

Happy to look at that over the next few weeks. The current code supports all types of reports as far as I have seen, they all seem to follow the same structure.

347-453-7686

On Wed, Jan 3, 2024 at 3:15 PM Edward Emanuel @.***> wrote:

Report features are lacking in this library. Ultimately I would like to add report objects for all of the QBO reports. Those objects could have alternate ways of accessing the data.

That said, if @jlvanhulst https://github.com/jlvanhulst is interested in putting together a PR I will review it.

— Reply to this email directly, view it on GitHub https://github.com/ej2/python-quickbooks/issues/325#issuecomment-1875915380, or unsubscribe https://github.com/notifications/unsubscribe-auth/AFPPQ6RABTUR3W4BZAJHP73YMW36LAVCNFSM6AAAAAA6AR7UYWVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMYTQNZVHEYTKMZYGA . You are receiving this because you were mentioned.Message ID: @.***>