Soap API data to Google Sheets

Question

Good morning,

I have been having troubles getting soap API data into google sheets. When i run the Soap request I get the data as shown in the image . [output data][1]

Then i tried getting this data into a google sheets using different methods, unfortunately no solution so far has worked. The solutions i have tried is pickling the data the setting it in a different file and pushing that file into google sheets.

The current solution I'm working on is setting the output_data in a pandas dataframe and pushing like that, this is the current code but this also doesn't seem to work. I will only leave out the credentials to authenticate with the API.

def pandas_to_sheets(pandas_df, sheet, clear = True):
    # Updates all values in a workbook to match a pandas dataframe
    if clear:
        sheet.clear()
    (row, col) = pandas_df.shape
    cells = sheet.range("A1:{}".format(gspread.utils.rowcol_to_a1(row + 1, col)))
    for cell, val in zip(cells, iter_pd(pandas_df)):
        cell.value = val
    sheet.update_cells(cells)

def iter_pd(df):
    for val in list(df.columns):
        yield val
    for row in df.values:
        for val in list(row):
            if pd.isna(val):
                yield ""
            else:
                yield val

optionsReportAffiliateSite = [ {'dateFrom' : '01-01-2020', }] 

client = Client(wsdl)
client.service.authenticate(username, password, sandbox, locale, demo)

testReportAffiliateSite = client.service.getReportAffiliateSite(idCampaigns,optionsReportCampaign )

input_dict = zeep.helpers.serialize_object(testReportAffiliateSite)
df = pd.DataFrame(input_dict)

affliatesite = pd.DataFrame(df.values.tolist())[0]
reportdata = pd.DataFrame(df.values.tolist())[1]

pd.json_normalize(affliatesite)
pd.json_normalize(reportdata)

pd.concat([pd.json_normalize(affliatesite), pd.json_normalize(reportdata).reindex(pd.json_normalize(affliatesite).index)], axis=1)
wks = gc.open_by_key('1uPdi2w_1TajnKNN8G3uahgrSHLAPnbAHtHSPeaZN3y0').sheet1
pandas_to_sheets(pd.concat([pd.json_normalize(affliatesite), pd.json_normalize(reportdata).reindex(pd.json_normalize(affliatesite).index)], axis=1), wks)

This gives me the error "TypeError: Object of type Decimal is not JSON serializable"

Many thanks in advance. [1]: https://i.stack.imgur.com/efOEN.png

Always provide a [mre], with **code, data, errors, current output, and expected output, as text**, not screenshots, because [SO Discourages Screenshots](https://meta.stackoverflow.com/questions/303812/). It is likely the question will be down-voted and closed. You are discouraging assistance because no one wants to retype your data or code, and screenshots are often illegible. [edit] the question and **add text**. — Trenton McKinney, Sep 16 '20 at 06:38

score 0 · Answer 1 · answered Sep 15 '20 at 13:10

The decimal values in the response you're getting cannot be serialized to JSON.

Because of this, you should transform this decimal values to another type which can be serialized. For example, float. So you can do the following:

Define this function, to check if an element is a decimal and return it converted to float:

def f(v):
    if isinstance(v, Decimal):
        return float(v)
    else:
        return v

Iterate through your list and call the previous function for every value in it, using this:

soapResponse = map(lambda el : {k: f(v) for k, v in el.items()}, soapResponse)

Note:

Import decimal via from decimal import Decimal.

Soap API data to Google Sheets

1 Answers1

Note:

Reference: