GSheets JSON String Splitter, with Dynamic Column Selection

Question

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

By guessing your goal from your question, I proposed a sample script as an answer. Could you please confirm it? If I misunderstood your question, I apologize. At that time, can you provide your expected input and output situations? By this, I would like to modify the script. — Tanaike
– Tanaike, Commented Mar 22, 2022 at 13:23

Tanaike · Accepted Answer · 2022-03-25 09:13:35Z

2

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:

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

edited Mar 25, 2022 at 9:13

answered Mar 22, 2022 at 13:22

Tanaike

203k12 gold badges126 silver badges222 bronze badges

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

15 Comments

Simon Hardham Over a year ago

Thanks for your response! This works except now it is not pulling in the data from the last row. Where the conditional logic was before to make it not dependent on finding the additional info tag first. Could you help add that part back in please?

Tanaike Over a year ago

@Simon Hardham Thank you for replying. From your showing script, I had thought that you wanted to retrieve the values from additionalInfo. And I have to apologize for my poor English skill. About

This works except now it is not pulling in the data from the last row. Where the conditional logic was before to make it not dependent on finding the additional info tag first. Could you help add that part back in please?

, in order to correctly understand your question, can you provide your expected input and output situations? By this, I would like to modify the script.

Tanaike Over a year ago

@Simon Hardham Unfortunately, I cannot see your input and output situations, is the following modification your expected result? From return obj.hasOwnProperty("additionalInfo") ? Object.keys(obj.additionalInfo) : []; to return obj.hasOwnProperty("additionalInfo") ? Object.keys(obj.additionalInfo) : Object.keys(obj);. And also, From

return obj.hasOwnProperty("additionalInfo") ? headers.map(h => obj.additionalInfo[h] || "") : Array(headers.length).fill("");

to

return obj.hasOwnProperty("additionalInfo") ? headers.map(h => obj.additionalInfo[h] || "") : headers.map(h => obj[h] || "");

.

Simon Hardham Over a year ago

Basically I need the script to work for either with additional info or without EG both of these: { "additionalInfo": { "UANNumber": "123", l } } AND: "{ ""isComplianceUploaded"": true, ""isOpsOnboardingComplete"": true }" Currently the second one returns a blank result.

Tanaike Over a year ago

@Simon Hardham Thank you for replying. I saw your updated Spreadsheet. Using it, I added one more sample script? Could you please confirm it? if that wat not useful, I apologize, again.

|

Collectives™ on Stack Overflow

GSheets JSON String Splitter, with Dynamic Column Selection

1 Answer 1

Sample script:

References:

Added:

Sample script:

15 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Sample script:

References:

Added:

Sample script:

15 Comments

Your Answer

Sign up or log in

Post as a guest

Related