Question

0

149

Views

Split multiple JSON string into strucutred table using Google App Script

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

about 4 years ago · Juan Pablo Isaza

1 answers

Answer question

0

Answer question

Find remote jobs

Accepted Answer · 2022-07-03T09:59:14.273Z

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.

0

149