Google apps script: Parsing JSON and write them to google spreadsheets

Question

I've managed to download a JSON file using the Google Apps Script. Soon, I've found that my JSON is somewhat complex than examples on the internet (including here).

var json = {"block1":[{"TRD_DD":"2021/12/22","MKTCAP":"2,208,300,470,743,887","FORN_HD_MKTCAP":"740,740,079,190,175","MKTCAP_RTO":"33.54","LIST_SHRS":"61,701,475,584","FORN_HD_SHRS":"11,191,374,601","LIST_SHRS_RTO":"18.14"},{"TRD_DD":"2021/12/21","MKTCAP":"2,200,896,109,564,335","FORN_HD_MKTCAP":"736,382,905,931,787","MKTCAP_RTO":"33.46","LIST_SHRS":"61,701,475,584","FORN_HD_SHRS":"11,198,948,564","LIST_SHRS_RTO":"18.15"},{"TRD_DD":"2021/12/20","MKTCAP":"2,191,456,183,748,063","FORN_HD_MKTCAP":"731,379,899,525,089","MKTCAP_RTO":"33.37","LIST_SHRS":"61,698,916,232","FORN_HD_SHRS":"11,215,916,966","LIST_SHRS_RTO":"18.18"}],"CURRENT_DATETIME":"2021.12.22 PM 04:24:06"}

There are more arrays in "Block1" but I shortened the length. please consider multiple more rows (about 500 rows, I think)

I'd like to give different column names like "AAA", "BBB", "CCC", "DDD", "EEE", "FFF", "GGG" instead of "TRD_DD", "MKTCAP", "FORN_HD_MKTCAP", "MKTCAP_RTO", "LIST_SHRS", "FORN_HD_SHRS", "LIST_SHRS_RTO". Each value for the same name should be inserted in each row (sorry, StackOverflow does not allow me to insert images. It is very difficult to explain).

and if possible, I'd like to change all numbers to numeric not string.

I am a beginner in handling javascript, so it is very difficult!!! Is there anyone to help me? If any, it will be greatly appreciated.

***Addendum

Here is my original script

function foreign_daily(){
  
  var url = 'http://data.krx.co.kr/comm/bldAttendant/getJsonData.cmd';
  var formData = {
      "bld": "dbms/MDC/STAT/standard/MDCSTAT03601",
      "mktId": "STK",
      "strtDd": "20211220",
      "endDd": "20211222",
      "share": "2",
      "money": "3",
      "csvxls_isNo": "false"};
  

  var headers = { 
      "Accept": "application/json, text/javascript, */*; q=0.01",
      "Accept-Encoding": "gzip, deflate",
      "Accept-Language": "ko-KR,ko;q=0.9,en-US;q=0.8,en;q=0.7",
      "Connection": "keep-alive",
      "Content-Type": "application/x-www-form-urlencoded; charset=UTF-8",
                 
      "Origin": "http://data.krx.co.kr",
      "Referer": "http://data.krx.co.kr/contents/MDC/MDI/mdiLoader/index.cmd?menuId=MDC0201020503",
      "User-Agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/94.0.4606.118 Whale/2.11.126.23 Safari/537.36",
      "X-Requested-With": "XMLHttpRequest"
    };
  Logger.log(JSON.stringify(formData));

  var options = { 
    'method' : 'post',
    'contentType': 'application/x-www-form-urlencoded; charset=UTF-8',
    'headers': headers,
    'payload': formData
  };

  var json = UrlFetchApp.fetch(url, options);
  

// Object for converting the keys.
  var obj = {"TRD_DD": "AAA", "MKTCAP": "BBB", "FORN_HD_MKTCAP": "CCC", "MKTCAP_RTO": "DDD", "LIST_SHRS": "EEE", "FORN_HD_SHRS": "FFF", "LIST_SHRS_RTO": "GGG"};

// Converting keys.
  json.block1 = json.block1.map(o => Object.fromEntries(Object.entries(o).map(([k, v]) => [obj[k], v.includes(",") ? v.split(",").map(e => Number(e)) : v])));

  console.log(json)

// Put the values to Spreadsheet.
  var header = ["AAA", "BBB", "CCC", "DDD", "EEE", "FFF", "GGG"];
  var values = json.block1.flatMap(o => {
    var temp = header.map(h => o[h]);
    var max = Math.max(...temp.map(r => Array.isArray(r) ? r.length : 1));
    temp = temp.map(r => {
      if (Array.isArray(r)) {
        return r.length == max ? r : [...r, ...Array(max - r.length).fill("")];
      }
      return [r, ...Array(max - 1).fill("")]; // or return Array(max).fill(r);
    })
    return temp[0].map((_, c) => temp.map(r => r[c]));
  });
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("sheet126"); // Please set the sheet name.
  sheet.getRange(sheet.getLastRow() + 1, 1, values.length, values[0].length).setValues(values);

and now it says

TypeError: Cannot read property 'map' of undefined
foreign_daily   @ Foreign_daily.gs:42

Thanks again, Tanaike!

enter image description here

Answer 1

I believe your goal is as follows.

• You want to change the keys from "TRD_DD", "MKTCAP", "FORN_HD_MKTCAP", "MKTCAP_RTO", "LIST_SHRS", "FORN_HD_SHRS", "LIST_SHRS_RTO" to "AAA", "BBB", "CCC", "DDD", "EEE", "FFF", "GGG".
• You want to convert the values like "2,208,300,470,743,887" to the numbers like [2,208,300,470,743,887].
• You want to achieve this using Google Apps Script.

In this case, how about the following sample script?

Sample script:

// This is from your question.
var json = {"block1":[{"TRD_DD":"2021/12/22","MKTCAP":"2,208,300,470,743,887","FORN_HD_MKTCAP":"740,740,079,190,175","MKTCAP_RTO":"33.54","LIST_SHRS":"61,701,475,584","FORN_HD_SHRS":"11,191,374,601","LIST_SHRS_RTO":"18.14"},{"TRD_DD":"2021/12/21","MKTCAP":"2,200,896,109,564,335","FORN_HD_MKTCAP":"736,382,905,931,787","MKTCAP_RTO":"33.46","LIST_SHRS":"61,701,475,584","FORN_HD_SHRS":"11,198,948,564","LIST_SHRS_RTO":"18.15"},{"TRD_DD":"2021/12/20","MKTCAP":"2,191,456,183,748,063","FORN_HD_MKTCAP":"731,379,899,525,089","MKTCAP_RTO":"33.37","LIST_SHRS":"61,698,916,232","FORN_HD_SHRS":"11,215,916,966","LIST_SHRS_RTO":"18.18"}],"CURRENT_DATETIME":"2021.12.22 PM 04:24:06"};

// Object for converting the keys.
var obj = {"TRD_DD": "AAA", "MKTCAP": "BBB", "FORN_HD_MKTCAP": "CCC", "MKTCAP_RTO": "DDD", "LIST_SHRS": "EEE", "FORN_HD_SHRS": "FFF", "LIST_SHRS_RTO": "GGG"};

// Converting keys.
json.block1 = json.block1.map(o => Object.fromEntries(Object.entries(o).map(([k, v]) => [obj[k], v.includes(",") ? v.split(",").map(e => Number(e)) : v])));

console.log(json)

Reference:

• map()

Added 1:

About Another goal I'd like to achieve is to upload it into a google sheet. in your comment, how about the following sample script?

Sample script:

// This is from your question.
var json = {"block1":[{"TRD_DD":"2021/12/22","MKTCAP":"2,208,300,470,743,887","FORN_HD_MKTCAP":"740,740,079,190,175","MKTCAP_RTO":"33.54","LIST_SHRS":"61,701,475,584","FORN_HD_SHRS":"11,191,374,601","LIST_SHRS_RTO":"18.14"},{"TRD_DD":"2021/12/21","MKTCAP":"2,200,896,109,564,335","FORN_HD_MKTCAP":"736,382,905,931,787","MKTCAP_RTO":"33.46","LIST_SHRS":"61,701,475,584","FORN_HD_SHRS":"11,198,948,564","LIST_SHRS_RTO":"18.15"},{"TRD_DD":"2021/12/20","MKTCAP":"2,191,456,183,748,063","FORN_HD_MKTCAP":"731,379,899,525,089","MKTCAP_RTO":"33.37","LIST_SHRS":"61,698,916,232","FORN_HD_SHRS":"11,215,916,966","LIST_SHRS_RTO":"18.18"}],"CURRENT_DATETIME":"2021.12.22 PM 04:24:06"};

// Object for converting the keys.
var obj = {"TRD_DD": "AAA", "MKTCAP": "BBB", "FORN_HD_MKTCAP": "CCC", "MKTCAP_RTO": "DDD", "LIST_SHRS": "EEE", "FORN_HD_SHRS": "FFF", "LIST_SHRS_RTO": "GGG"};

// Converting keys.
json.block1 = json.block1.map(o => Object.fromEntries(Object.entries(o).map(([k, v]) => [obj[k], v.includes(",") ? v.split(",").map(e => Number(e)) : v])));

console.log(json)

// Put the values to Spreadsheet.
var header = ["AAA", "BBB", "CCC", "DDD", "EEE", "FFF", "GGG"];
var values = json.block1.flatMap(o => {
  var temp = header.map(h => o[h]);
  var max = Math.max(...temp.map(r => Array.isArray(r) ? r.length : 1));
  temp = temp.map(r => {
    if (Array.isArray(r)) {
      return r.length == max ? r : [...r, ...Array(max - r.length).fill("")];
    }
    return [r, ...Array(max - 1).fill("")]; // or return Array(max).fill(r);
  })
  return temp[0].map((_, c) => temp.map(r => r[c]));
});
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1"); // Please set the sheet name.
sheet.getRange(sheet.getLastRow() + 1, 1, values.length, values[0].length).setValues(values);

• In the above script, if you want to embed the same values for only one value, please replace return [r, ...Array(max - 1).fill("")]; with return Array(max).fill(r);.

Added 2:

From your following replying,

One thing is, however, converting to numeric is not what I want. the comma "," does not separate different numbers, but is just a separator for money count, like 1 million dollars (1,000,000$). I want "1,000,000" to "1000000", but not 1 and 000 and 000.

How about the following sample script?

Sample script:

// Object for converting the keys.
var obj = {"TRD_DD": "AAA", "MKTCAP": "BBB", "FORN_HD_MKTCAP": "CCC", "MKTCAP_RTO": "DDD", "LIST_SHRS": "EEE", "FORN_HD_SHRS": "FFF", "LIST_SHRS_RTO": "GGG"};

json.block1 = json.block1.map(o => Object.fromEntries(Object.entries(o).map(([k, v]) => [obj[k], v.includes(",") ? v.replace(/,/g, "") : v])));
// or json.block1 = json.block1.map(o => Object.fromEntries(Object.entries(o).map(([k, v]) => [obj[k], v.includes(",") ? Number(v.replace(/,/g, "")) : v])));

var header = ["AAA", "BBB", "CCC", "DDD", "EEE", "FFF", "GGG"];
var values = json.block1.map(o => header.map(h => o[h]));
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1"); // Please set the sheet name.
sheet.getRange(sheet.getLastRow() + 1, 1, values.length, values[0].length).setValues(values);