I have the below script that I am using to split out a JSON string.
Currently the script is working for some but not for others, there is one edge case that breaks the script, this is in Row 2 and Highlighted RED
I want the script to work for any JSON string, not have the dependency on the additional info tag
I have created a sample workbook here to highlight the JSON string breaking the splitter Splitter Example
Any help here would be amazing!

function JSON_SPLITTER() {
  var ss = SpreadsheetApp.getActive();
  var inputsheet = ss.getSheetByName("Input");
  var outputsheet = ss.getSheetByName("Current Output");

  // 0. 
  var response = SpreadsheetApp.getUi().prompt('JSON String Heading', 'Enter Column Heading for the JSON String in Row 1 Exactly as it appears', SpreadsheetApp.getUi().ButtonSet.OK_CANCEL)
    var JSONcolumnname = response.getResponseText()

  // 1. Retrieve values from the input sheet.
  var [head, ...additionalinfo] = inputsheet.getDataRange().getValues();

  // 2. Check "DATA" column.
  var dataIdx = head.indexOf(JSONcolumnname);
  if (dataIdx == -1) throw new Error("No DATA column.");

  // 3. Retrieve all keys from JSON data of the "DATA" column.
  var headers = [... new Set(additionalinfo.flatMap(r => {
    var obj = JSON.parse(r[dataIdx]);
    return obj.hasOwnProperty("additionalInfo") ? Object.keys(obj.additionalInfo) : Object.keys(obj);
  }))];

  // 4. Create values of JSON data.
  var objValues = additionalinfo.map(r => {
    var obj = JSON.parse(r[dataIdx]);
    return obj.hasOwnProperty("additionalInfo") ? headers.map(h => obj.additionalInfo[h] || "") : headers.map(h => obj[h] || "");
  });

  // 5. Create output values.
  additionalinfo.forEach((r, i) => r.splice(dataIdx, 1, ...objValues[i]));
  head.splice(dataIdx, 1, ...headers)
  var res = [head, ...additionalinfo];

  // 6. Put the values to the output sheet.
  outputsheet.getRange(1, 1, res.length, res[0].length).setValues(res);
}

CodePudding user response：

Although I'm not sure whether I could correctly understand your goal, how about the following modification?

From:

// 3. Retrieve all keys from JSON data of the "DATA" column.
var headers = [... new Set(additionalinfo.flatMap(r => {
  var obj = JSON.parse(r[dataIdx]);
  return obj.hasOwnProperty("additionalInfo") ? Object.keys(obj.additionalInfo) : Object.keys(obj);
}))];

// 4. Create values of JSON data.
var objValues = additionalinfo.map(r => {
  var obj = JSON.parse(r[dataIdx]);
  return obj.hasOwnProperty("additionalInfo") ? headers.map(h => obj.additionalInfo[h] || "") : headers.map(h => obj[h] || "");
});

To:

// 3. Retrieve all keys from JSON data of the "DATA" column.
var headers = [... new Set(additionalinfo.flatMap(r => {
  var obj = JSON.parse(r[dataIdx]);
  return obj.hasOwnProperty("additionalInfo") && obj.additionalInfo ? Object.keys(obj.additionalInfo) : Object.keys(obj); // Modified
}))];

// 4. Create values of JSON data.
var objValues = additionalinfo.map(r => {
  var obj = JSON.parse(r[dataIdx]);
  return obj.hasOwnProperty("additionalInfo") && obj.additionalInfo ? headers.map(h => obj.additionalInfo[h] || "") : headers.map(h => obj[h] || ""); // Modified
});

• When I saw your sample Spreadsheet, in your sample JSON data, I found that while the property of additionalInfo is existing, the value of additionalInfo is null. So I proposed the above modification.

Note:

• From your script, I guessed that the inputted value of the prompt is DATA.
• This modification uses your provided sample sheet of Sample Input 1. Please be careful about this.