How do I convert a couple of Google Sheets columns into a JSON string?

Question

I'm working on a piece of code that will convert two columns (A, B) into key/value pairs with JSON formatting. I can't figure out how to make it work. I currently have a function that will read the top row (keys) and the bottom row of the values, but I can't get it to show the rows in between. I'm not an engineer or a developer, but I do enjoy coding.

The sheet looks like this:

+-------+-------------+
| A     | B           |
+-------+-------------+
| code  | description |
+-------+-------------+
| brand | microsoft   |
+-------+-------------+
| size  | large       |
+-------+-------------+
| color | green       |
+-------+-------------+

And I'm expecting to get a JSON string like this:

[{"code":"brand","description":"microsoft"}, {"code":"color","description":"green"}, {"code":"size","description":"large"}]

So far my Google Apps Script code looks like this:

function tableJSON(arr) {
  var i, j, obj = {};
  for (j = 0; j < arr[0].length; j++) {
    obj[arr[0][j]] = {};
  }
  
  for (i = 0; i < arr.length; i++) {
    for (j = 0; j < arr[0].length; j++) {
      obj[arr[0][j]] = arr[i][j];
    }
  }
  
  return JSON.stringify(obj);
}

And the result when I type

="[" & tableJSON(A1:B4) & "]"

is:

[{"code":"size","description":"large"}]

I'm sure the solution is simple, but I haven't been able to figure it out.

Answer 1

Issue:

The code only creates 1 object and rewrites the same object each time in iteration.

Solution:

• Shift the 2D array to get headers

• Map the array to a object

/*<ignore>*/console.config({maximize:true,timeStamps:false,autoScroll:false});/*</ignore>*/
/**
 * @param{Array<Array>} arr 2D array
 * @customfunction
 * @OnlyCurrentDoc
 */
function tableJSONFixed(arr) {
  'use strict';
  const [code, desc] = arr.shift();
  return JSON.stringify(arr.map(([v1, v2]) => ({ [code]: v1, [desc]: v2 })));
}

console.info(
  tableJSONFixed([
    ['code', 'description'],
    ['brand', 'google'],
    ['size', 'large'],
    ['color', 'green'],
  ])
);

<!-- https://meta.stackoverflow.com/a/375985/ -->    <script src="https://gh-canon.github.io/stack-snippet-console/console.min.js"></script>

Answer 2

https://github.com/jsoma/tabletop

I was using tabletop to convert sheets to JSON but if you have an organisational google account this isn't going to work for much longer