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

Tanaike · Accepted Answer · 2021-12-22 09:47:24Z

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

edited Dec 22, 2021 at 9:47

answered Dec 22, 2021 at 8:15

Tanaike

203k12 gold badges126 silver badges222 bronze badges

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

14 Comments

JHP Over a year ago

Thanks a lot, Tanaike. But in Google Apps Script, it says there is a Syntax error in the line of json.block1 =~~~. I don't know why.

JHP Over a year ago

Another goal I'd like to achieve is to upload it into a google sheet. thanks a lot!

Tanaike Over a year ago

@JHP Thank you for replying. I apologize for the inconvenience. About But in Google Apps Script, it says there is a Syntax error in the line of json.block1 =~~~. I don't know why., I thought that you might disable V8 runtime. If my understanding is correct, please enable V8 runtime with the script editor and test it again. About Another goal I'd like to achieve is to upload it into a google sheet., I couldn't notice this. I apologize for this. Please wait for it.

Tanaike Over a year ago

@JHP About Another goal I'd like to achieve is to upload it into a google sheet., I added one more sample script. Could you please confirm it? Unfortunately, I cannot understand your expected situation on the Spreadsheet. So when I misunderstood your other goal and that was not useful, I apologize, again.

JHP Over a year ago

You are amazing! I enabled V8 runtime, but now it says TypeError: Cannot read property 'map' of undefined. Sorry, I'm a very beginner in Apps script.

|

Collectives™ on Stack Overflow

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

1 Answer 1

Sample script:

Reference:

Added 1:

Sample script:

Added 2:

Sample script:

14 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Sample script:

Reference:

Added 1:

Sample script:

Added 2:

Sample script:

14 Comments

Your Answer

Sign up or log in

Post as a guest

Related