I have the below script that I am using to split out a JSON string.
Currently it has a unique ID in Col A, and the Data in Col B. I want to be able to have the data in any column, provided the column is called DATA. I want the script to look for column titled data and keep all columns left of that in the out put tab.
I have tried a few things but none of them are working.
I have created a sample workbook here to show what I'm after 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：

I believe your goal is as follows.

• From Currently it has a unique ID in Col A, and the Data in Col B. I want to be able to have the data in any column, provided the column is called DATA. I want the script to look for column titled data and keep all columns left of that in the out put tab., for example, when your "Sample Data 1" sheet is used, you want to achieve the following situation.

  • From: This is the header row of the input sheet.

      Contract Oid,Name,Age,DATA
    

  • To: This is the header row of the output sheet. In your situation, it is required to search DATA column for each input sheet.

      Contract Oid,Name,Age,{keys parsed from JSON data of "DATA"}
    

When I saw your script, it seems that "DATA" column is not searched. If my understanding is correct, how about the following sample script?

Sample script:

function JSON_SPLITTER2() {
  var ss = SpreadsheetApp.getActive();
  var inputsheet = ss.getSheetByName("Sample Data 1");
  var outputsheet = ss.getSheetByName("Current Output");

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

  // 2. Check "DATA" column.
  var dataIdx = head.indexOf("DATA");
  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) : [];
  }))];

  // 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] || "") : Array(headers.length).fill("");
  });

  // 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);
}

• In this script, when "DATA" sheet is not found, an error occurs. If you want to finish instead of the error, please modify if (dataIdx == -1) throw new Error("No DATA column."); to if (dataIdx == -1) return;

References:

• map()
• splice()

Added:

From your reply, how about the following sample script?

Sample script:

function JSON_SPLITTER3() {
  var ss = SpreadsheetApp.getActive();
  var inputsheet = ss.getSheetByName("Sample Data 1");
  var outputsheet = ss.getSheetByName("Current Output");

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

  // 2. Check "DATA" column.
  var dataIdx = head.indexOf("DATA");
  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);
}