Split multiple JSON string into strucutred table using Google App Script

Question

I am trying to split a data set with an ID and JSON string into a structured table.

The difficult part is I need it to be dynamic, the JSON string varies often and I want headings to be determined by the unique values in the input column at that time. I need the script to be able to create headings if the string changes without needed to recode the script.

We have about 150 different JSON strings we are hoping to use this script on, without recoding it for each one. Each string has lots of data points.

I have a script working but it splits them one by one, need to build something that will do bulk in one go, by looping through all outputs in B and creating a column for each unique field in all the strings, then populating them.

The script works if I paste the additional info straight in, however I am having trouble reading from the sheet


  var inputsheet = SpreadsheetApp.getActive().getSheetByName("Input");
  var outputsheet = SpreadsheetApp.getActive().getSheetByName("Current Output");

  var additionalinfo = inputsheet.getRange(1,1).getValue()
  Logger.log(additionalinfo)

  var rows = [],
      data;

    for (i = 0; i < additionalinfo.length; i++) {
        for (j in additionalinfo[i]) {   

          dataq = additionalinfo[i][j];

          Logger.log(dataq);

          rows.push([j, dataq]);
    }
      dataRange = outputsheet.getRange(1, 1, rows.length, 2);
      dataRange.setValues(rows);    
  }
}

Here is a link to the sample data. Note that in Sample 1 & 2 there are different headings, we need the script to identify this and create headings for both

https://docs.google.com/spreadsheets/d/1BMiVuAgDbibLw6yUG3IZ9iw4MZTaVVegkw_k3ItQ4mU/edit#gid=0

I have to apologize for my poor English skill. I understood that I couldn't correctly understand your question because I couldn't understand your changed question, and also, my answer was not useful for your situation. In this case, I have to delete my answer. Because I don't want to confuse other users. This is due to my poor English skill. I deeply apologize for this again. I think that I have to study more and more. — Tanaike
– Tanaike, Commented Mar 17, 2022 at 8:33

NightEye · Accepted Answer · 2022-03-17 07:41:16Z

1

Try this script that produces dynamic headers based on the json that has been read. It collects all json data, get its keys, and remove the duplicates.

Script:

function JSON_SPLITTER() {
  var spreadsheet = SpreadsheetApp.getActive();
  var inputsheet = spreadsheet .getSheetByName("Input");
  var outputsheet = spreadsheet .getSheetByName("Current Output");
  var additionalinfo = inputsheet.getDataRange().getValues();
  var keys = [];

  // prepare the additionalInfo  data to be parsed for later
  var data = additionalinfo.slice(1).map(row => {
    // collect all keys in an array
    if (JSON.parse(row[1]).additionalInfo) {
      keys.push(Object.keys(JSON.parse(row[1]).additionalInfo));
      return JSON.parse(row[1]).additionalInfo;
    }
    else {
      keys.push(Object.keys(JSON.parse(row[1])));
      return JSON.parse(row[1]);
    }
  });
  
  // unique values of keys, modified to form header
  var headers = [...new Set(keys.flat())]
  // Add A1 as the header for the ids
  headers.unshift(additionalinfo[0][0]);
  // set A1 and keys as headers
  var output = [headers]

  // build output array
  additionalinfo.slice(1).forEach((row, index) => {
    var outputRow = [];
    headers.forEach(column => {
      if(column == 'Contract Oid')
        outputRow.push(row[0]);
      else
        outputRow.push(data[index][column]);
    });
    output.push(outputRow)
  });
  outputsheet.getRange(1, 1, output.length, output[0].length).setValues(output);
}

Output:

Update:

Modified script for no-additionalInfo key objects.

edited Mar 17, 2022 at 7:41

answered Mar 17, 2022 at 6:53

NightEye

11.3k2 gold badges8 silver badges25 bronze badges

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

12 Comments

Simon Hardham Over a year ago

Thank you so much @AsyntuBu This looks like it could work perfectly... I'm just getting this error: SyntaxError: Unexpected token u in JSON at position 0 (anonymous) @ Copy of Code.gs:11 JSON_SPLITTER1234 @ Copy of Code.gs:9

NightEye Over a year ago

in your sheet sample, what sheet are you trying to process so I can replicate it on my end @SimonHardham

Simon Hardham Over a year ago

It is working now, thank you! This answers my Q

NightEye Over a year ago

Note that the logic for getting dynamic headers can be also applied to Tanaike's answer @SimonHardham. You might need to adjust it but it should work after some modification if needs be.

Simon Hardham Over a year ago

It works for my India data but not Singapore, I get this issue, any ideas why? TypeError: Cannot convert undefined or null to object (anonymous) @ Copy of Code.gs:11 JSON_SPLITTER @ Copy of Code.gs:9

|

Collectives™ on Stack Overflow

Split multiple JSON string into strucutred table using Google App Script

1 Answer 1

Script:

Output:

Update:

12 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Script:

Output:

Update:

12 Comments

Your Answer

Sign up or log in

Post as a guest

Related