Google Script Json Nested Arrays To Cell

im trying to read parts of the following JSON into google sheets, there seems to be nested arrays which i am having difficulty in pushing into one cell... main JSON from floodlig

Solution 1:

I believe your goal as follows.

• You want to put the object of { "1fI": "72217193", "3PCdays": 30, "2PVdays": 30 },,, to a cell.

For this, how about this modification?

Modification point:

• In your script, data.conversionCounting.floodlightActivityConfigs is an array. In order to put this to a cell, please use JSON.stringify. I think that the reason of your issue is this.

When this is reflected to your script, please modify as follows.

Modified script:

From:

rows.push([
 data.campaignId, 
 data.conversionCounting.floodlightActivityConfigs
 ]);

To:

rows.push([
  data.campaignId, 
  JSON.stringify(data.conversionCounting.floodlightActivityConfigs)
]);

Reference:

• JSON.stringify()

Answer for the additional question:

• You want to sort the keys from { "1fI": "72217193", "3PCdays": 30, "2PVdays": 30 } to { "1fI": "72217193", "2PVdays": 30,"3PCdays": 30... }.

Unfortunately, the JSON object is not guaranteed the order. For example, this thread is useful for understanding about it.

But, in your case, you want to put the object to a cell as a string. I thought that this can be used for resolving your additional question. So as a workaround, when { "1fI": "72217193", "3PCdays": 30, "2PVdays": 30 } is not used as the JSON object, I think that the order can be set.

Modified script:

From:

rows.push([
 data.campaignId, 
 data.conversionCounting.floodlightActivityConfigs
 ]);

To:

rows.push([
  data.campaignId,
  data.conversionCounting.floodlightActivityConfigs ? `[${data.conversionCounting.floodlightActivityConfigs.map(e => `{${Object.entries(e).map(([k, v]) => typeof v == "number" ? `"${k}":${v}` : `"${k}":"${v}"`).sort().join(",")}}`).join(",")}]` : ""
]);

• In this modification, each object is converted to the array with the string and sort it, and then, the array is converted to the string. By this, your goal can be achieved.

• In this case, the following value is put to the cell.

  [{"1fI":"7517101","2PVdays":30,"3PCdays":30},{"1fI":"7541802","2PVdays":30,"3PCdays":30},{"1fI":"7552803","2PVdays":30,"3PCdays":30},{"1fI":"7517104","2PVdays":30,"3PCdays":30}]