Nested Values Json and Python

Question

I have a JSON output im getting from an API and im trying to extract the nested values from 'rows' and to access 'cells' key within rows.

Here is the JSON output

"rows": [
        {
            "id": 6770063759566724,
            "rowNumber": 1,
            "expanded": true,
            "createdAt": "2022-01-27T17:04:11Z",
            "modifiedAt": "2022-01-27T17:04:11Z",
            "cells": [
                {
                    "columnId": 2775064444725124,
                    "value": "Smith",
                    "displayValue": "Smith"
                },
                {
                    "columnId": 7278664072095620,
                    "value": "John",
                    "displayValue": "John"
                },
                {
                    "columnId": 1649164537882500,
                    "value": 12.0,
                    "displayValue": "12"
                },
                {
                    "columnId": 6152764165252996,
                    "value": "Gilbert",
                    "displayValue": "Gilbert"
                },
                {
                    "columnId": 3900964351567748,
                    "value": "Webhook Test",
                    "displayValue": "Webhook Test"
                }
            ]
        },

Here is my code

I need each columnID within cells and the value within cells. These values will then be recorded into my Django project. Appreciate the help.

This is the output of of print(cells)

{6770063759566724: [{'columnId': 2775064444725124, 'value': 'Smith', 'displayValue': 'Smith'}, {'columnId': 7278664072095620, 'value': 'John', 'displayValue': 'John'}, {'columnId': 1649164537882500, 'value': 12.0, 'displayValue': '12'}, {'columnId': 6152764165252996, 'value': 'Gilbert', 'displayValue': 'Gilbert'}, {'columnId': 3900964351567748, 'value': 'Webhook Test', 'displayValue': 'Webhook Test'}]}

 for r in response_info ['rows']:
             try:
               row_id = r['id']
               rowNumber = r['rowNumber']  
               if  SmartSheetData.objects.filter(sheet_id = sheet, row_id = row_id): 
                print("Updating Sheet rows")
                print('<--Row ID-->%s' %row_id)
                print('<--Row Number-->%s' %rowNumber)
                SmartSheetData.objects.filter(sheet_id = sheet, row_id = row_id).update(row_number = rowNumber)
                # GETS CELL VALUES  
                print('Getting Cell Values')
                cells = {}
                cells[r['id']] = r['cells'] 
                print(cells)
                columnId = cells.get('columnId')
                value = cells.get('value')
                print('<--Column ID-->%s' %column_id)
                print('<--Value-->%s' %value)
                if SmartSheetData.objects.filter(sheet_id = sheet, row_id = row_id):
                    print("Updating row cell values")
                    print('<--Row ID-->%s' %row_id)
                    print('<--Row Number-->%s' %rowNumber)
                    print('<--Column ID-->%s' %column_id)
                    print('<--Cell Value-->%s' %value)
                    SmartSheetData.objects.filter(sheet_id = sheet, row_id = row_id).update(row_cell = value)
                   
             except Exception as err5: 
              print ("Missing row values, skipping", str(err5))

Initialize cells = {} outside of the loop. Then check again. — Md Mahfuzur Rahman
– Md Mahfuzur Rahman, Commented Jan 27, 2022 at 17:53
I moved it outside the for loop and no change. The .get is now no longer available. — Ray Zuchowski
– Ray Zuchowski, Commented Jan 27, 2022 at 18:10
Please provide an executable, minimal, reproducible example of your problem, preferable not using smartsheet. It would be necessary to install smartsheet to reproduce your problem. I have found that making the emre frequently solves the problem. Thank You — Jim Robinson
– Jim Robinson, Commented Jan 27, 2022 at 18:38
Ah.. it's the Smartsheet api, right? Well familiar with it :-) — Wizard.Ritvik
– Wizard.Ritvik, Commented Jan 28, 2022 at 2:32

Wai Ha Lee · Accepted Answer · 2022-01-28 08:47:21Z

1

Hopefully this is correct and you'll be able to take it from here

for nrow, row in enumerate( response_info ['rows']) :

    # use dict.get method to supply a default for missing keys instead of crashing. 
    # Here, an empty list can be iterated over (zero times) in the inner loop.
    cells = row.get('cells', [])  

    for ncol, cell in enumerate( cells):

        col_id = cell.get( "columnId", None)
        value =  cell.get( "value", None)

        # what you do with these things is up to you
        print( f"Row {nrow} cell {ncol} id : value = {col_id} : {value}")

edited Jan 28, 2022 at 8:47

Wai Ha Lee

8,856102 gold badges61 silver badges100 bronze badges

answered Jan 27, 2022 at 18:40

nigel222

8,3221 gold badge19 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Nested Values Json and Python

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related