Nested Elements return with null values - Help

[akliewer99]

I finally got the "Nested Elements" to be exactly where I need them. However, there is no data. (see screenshot) Everything is null. Did I miss something? (FYI: Used "mouse" instead of "records", just in case "records" was a reserved word.)

I work with a non-profit on a fundraising event. This is urgent. Thanks in advance for your time!

• Arlene

[Synthoid]

Can you link to an example sheet or give an example of desired data? That will help me figure the issue out faster.

Also, what settings are you using?

[akliewer99]

Thanks for the reply! I am making progress. In the Advanced JSON section, I changed “Empty value format” and “Null value format”. Now I only get one record and not the entire sheet. Much better than null.

See screenshots. I have created a public sheet here. https://docs.google.com/spreadsheets/d/1jtAJKfgFgOmNUJMdYd_Ecfifi4sZIAfHSvo0bbYYE68/edit?usp=sharing

Thanks again!

— Respectfully, Arlene Kliewer

PS - Sorry for the resolution of the last screenshot. I had to leave high-res so you could read the data.

[Synthoid]

Does this seem like the data format you were expecting?

{
  "Sheet2": {
    "records": [
      {
        "id": 10880791,
        "collection": "Participants2020_test",
        "action": "patch",
        "data": [
          {
            "lookupID": 10880791,
            "registrantID": "01DVABPTWG12WN6JF5T",
            "racerLName": "Data",
            "racerFName": "Commander",
            "gender": "male",
            "isScholar": "N",
            "racer_dob": "8-6-1970",
            "age": 50,
            "race_name": "1 Mile",
            "reg_choice_name": "1 Mile Adult",
            "bibNumber": 1502,
            "raceShirt1": "NAVY Adult-Medium",
            "designatedCampus": "EDH",
            "scholarLName": "Oxford",
            "scholarFName": "Ashely",
            "grade": "3rdGrade",
            "gradeNumber": 3,
            "couponCode": "VTEER100",
            "zipCode": 95762,
            "status": "CONF",
            "raceWaiver": true,
            "teacher": "langtonKaralee"
          }
        ]
      },
      {
        "id": 10880792,
        "collection": "Participants2020_test",
        "action": "patch",
        "data": [
          {
            "lookupID": 10880792,
            "registrantID": "01DVABPTWJ2MF7Y10VX",
            "racerLName": "Troi",
            "racerFName": "Deanna",
            "gender": "female",
            "isScholar": "N",
            "racer_dob": "4-9-2011",
            "age": 9,
            "race_name": "10K",
            "reg_choice_name": "10K Youth (18-under)",
            "bibNumber": 103,
            "raceShirt1": "NAVY Youth-Small",
            "designatedCampus": "ROS",
            "scholarLName": "Walker",
            "scholarFName": "Tammy",
            "grade": "6thGrade",
            "gradeNumber": 6,
            "couponCode": "VTEER100",
            "zipCode": 95678,
            "status": "CONF",
            "raceWaiver": true,
            "teacher": "rummelsburgSteven"
          }
        ]
      },
      {
        "id": 11621807,
        "collection": "Participants2020_test",
        "action": "patch",
        "data": [
          {
            "lookupID": 11621807,
            "registrantID": "01E0251NHMP6MY7AHRR",
            "racerLName": "Picard",
            "racerFName": "Jean-Luc",
            "gender": "male",
            "isScholar": "N",
            "racer_dob": "6-30-2009",
            "age": 11,
            "race_name": "1 Mile",
            "reg_choice_name": "1 Mile Youth (18-under)",
            "bibNumber": 1602,
            "raceShirt1": "NAVY Youth-Small",
            "designatedCampus": "ROS",
            "scholarLName": "Kliewer",
            "scholarFName": "Paul",
            "grade": "10thGrade",
            "gradeNumber": 10,
            "couponCode": "VTEER100",
            "zipCode": 95678,
            "status": "CONF",
            "raceWaiver": true,
            "teacher": "courage"
          }
        ]
      }
    ]
  }
}

If so, this was the formatting I used for the keys:

[akliewer99]

Hi Chris, This is not exactly what I was looking for, but close enough. I can make this work. I am a newbie when it comes to JSON files, so I didn’t know how to communicate what I was looking for.

I was trying to make the DATA an of array containing each item as an object. Which you did. But I wanted each object to contain a key for item name and a value for the value. Like: data[0].key=lookupID and data[0].value=10880791.

But like I said before I can make this work. Super BIG THANKS! Your prompt reply this has helped me a ton of time cleaning up some bad data.

— Respectfully, Arlene Kliewer Very happy in CA.

On Feb 14, 2020, at 1:27 PM, Chris notifications@github.com wrote:

Does this seem like the data format you were expecting?

{ "Sheet2": { "records": [ { "id": 10880791, "collection": "Participants2020_test", "action": "patch", "data": [ { "lookupID": 10880791, "registrantID": "01DVABPTWG12WN6JF5T", "racerLName": "Data", "racerFName": "Commander", "gender": "male", "isScholar": "N", "racer_dob": "8-6-1970", "age": 50, "race_name": "1 Mile", "reg_choice_name": "1 Mile Adult", "bibNumber": 1502, "raceShirt1": "NAVY Adult-Medium", "designatedCampus": "EDH", "scholarLName": "Oxford", "scholarFName": "Ashely", "grade": "3rdGrade", "gradeNumber": 3, "couponCode": "VTEER100", "zipCode": 95762, "status": "CONF", "raceWaiver": true, "teacher": "langtonKaralee" } ] }, { "id": 10880792, "collection": "Participants2020_test", "action": "patch", "data": [ { "lookupID": 10880792, "registrantID": "01DVABPTWJ2MF7Y10VX", "racerLName": "Troi", "racerFName": "Deanna", "gender": "female", "isScholar": "N", "racer_dob": "4-9-2011", "age": 9, "race_name": "10K", "reg_choice_name": "10K Youth (18-under)", "bibNumber": 103, "raceShirt1": "NAVY Youth-Small", "designatedCampus": "ROS", "scholarLName": "Walker", "scholarFName": "Tammy", "grade": "6thGrade", "gradeNumber": 6, "couponCode": "VTEER100", "zipCode": 95678, "status": "CONF", "raceWaiver": true, "teacher": "rummelsburgSteven" } ] }, { "id": 11621807, "collection": "Participants2020_test", "action": "patch", "data": [ { "lookupID": 11621807, "registrantID": "01E0251NHMP6MY7AHRR", "racerLName": "Picard", "racerFName": "Jean-Luc", "gender": "male", "isScholar": "N", "racer_dob": "6-30-2009", "age": 11, "race_name": "1 Mile", "reg_choice_name": "1 Mile Youth (18-under)", "bibNumber": 1602, "raceShirt1": "NAVY Youth-Small", "designatedCampus": "ROS", "scholarLName": "Kliewer", "scholarFName": "Paul", "grade": "10thGrade", "gradeNumber": 10, "couponCode": "VTEER100", "zipCode": 95678, "status": "CONF", "raceWaiver": true, "teacher": "courage" } ] } ] } } If so, this was the formatting I used for the keys:

https://user-images.githubusercontent.com/6645950/74568788-c8007680-4f46-11ea-86dc-397662fdb0d0.png — You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub https://github.com/Synthoid/ExportSheetData/issues/107?email_source=notifications&email_token=AAH2NO5IJ7JM3AEKYZBVB7LRC4EE5A5CNFSM4KVNM7VKYY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOEL2PP7A#issuecomment-586479612, or unsubscribe https://github.com/notifications/unsubscribe-auth/AAH2NO6WZHCW2VSCRQQLCK3RC4EE5ANCNFSM4KVNM7VA.

[Synthoid]

If you want each value in the data array to be its own object (similar to what you had originally) all you would need to do is change the index values to what you had originally. So instead of all your data keys starting with [records]{#id}[data]{#1}, you would number them sequentially with [records]{#id}[data]{#2}, [records]{#id}[data]{#3}, etc.

Keep in mind that this would make accessing the data look more like data[0].lookupID, data[1].registrantID, data[2].racerLName, and so on.

[Synthoid]

Upon closer reading of your comment, it sounds like you want you data elements to look something like this:

{
  "key" : "lookupID",
  "value" : 10880791
}

If so, you should be able to format your keys like this:

[records]{#id}[data]{#1}key	[records]{#id}[data]{#1}value	[records]{#id}[data]{#2}key	[records]{#id}[data]{#2}value
lookupID	10880791	registrantID	01DVABPTWG12WN6JF5T

[akliewer99]

Yay!!!! This is exactly what I am looking for. Thanks a bunch!!!

— Respectfully, Arlene Kliewer 916-847-1542 cell

On Feb 15, 2020, at 10:14 AM, Chris notifications@github.com wrote:

Upon closer reading of your comment, it sounds like you want you data elements to look something like this:

{ "key" : "lookupID", "value" : 10880791 } If so, you should be able to format your keys like this:

[records]{#id}[data]{#1}key [records]{#id}[data]{#1}value [records]{#id}[data]{#2}key [records]{#id}[data]{#2}value lookupID 10880791 registrantID 01DVABPTWG12WN6JF5T — You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub https://github.com/Synthoid/ExportSheetData/issues/107?email_source=notifications&email_token=AAH2NOYTM76ZGYZHAA5VO4TRDAWI5A5CNFSM4KVNM7VKYY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOEL3TPAA#issuecomment-586626944, or unsubscribe https://github.com/notifications/unsubscribe-auth/AAH2NOZCCYSYEPWSLHG3SMDRDAWI5ANCNFSM4KVNM7VA.