Google Script JSON nested Arrays to cell

Question

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

{
  "lineItems": [
    {
      "name": "advertisers/1558261/lineItems/12317016",
      "advertiserId": "1238261",
      "campaignId": "1233305",
      "insertionOrderId": "13016372",
      "lineItemId": "12317016",
      "displayName": "All | Routes| All Users | ABC | ABC-LI1",
      "lineItemType": "LINE_ITEM_TYPE_DISPLAY_DEFAULT",
      "entityStatus": "ENTITY_STATUS_ACTIVE",
      "updateTime": "2020-04-15T12:51:42.929Z",
      "partnerCosts": [
        {
          "costType": "PARTNER_COST_TYPE_THIRD_PARTY_AD_SERVER",
          "feeType": "PARTNER_COST_FEE_TYPE_CPM_FEE",
          "feeAmount": "1000000",
          "invoiceType": "PARTNER_COST_INVOICE_TYPE_PARTNER"
        },
        {
          "costType": "PARTNER_COST_TYPE_DOUBLE_VERIFY_PREBID",
          "feeType": "PARTNER_COST_FEE_TYPE_CPM_FEE",
          "feeAmount": "0",
          "invoiceType": "PARTNER_COST_INVOICE_TYPE_DV360"
        },
        {
          "costType": "PARTNER_COST_TYPE_DV360_FEE",
          "feeType": "PARTNER_COST_FEE_TYPE_MEDIA_FEE",
          "feePercentageMillis": "0",
          "invoiceType": "PARTNER_COST_INVOICE_TYPE_DV360"
        },
        {
          "costType": "PARTNER_COST_TYPE_DEFAULT",
          "feeType": "PARTNER_COST_FEE_TYPE_MEDIA_FEE",
          "feePercentageMillis": "0",
          "invoiceType": "PARTNER_COST_INVOICE_TYPE_PARTNER"
        }
      ],
      "flight": {
        "flightDateType": "LINE_ITEM_FLIGHT_DATE_TYPE_CUSTOM",
        "dateRange": {
          "startDate": {
            "year": 2020,
            "month": 4,
            "day": 15
          },
          "endDate": {
            "year": 2020,
            "month": 4,
            "day": 30
          }
        }
      },
      "budget": {
        "budgetAllocationType": "LINE_ITEM_BUDGET_ALLOCATION_TYPE_UNLIMITED",
        "budgetUnit": "BUDGET_UNIT_CURRENCY"
      },
      "pacing": {
        "pacingPeriod": "PACING_PERIOD_DAILY",
        "pacingType": "PACING_TYPE_EVEN",
        "dailyMaxMicros": "40100000"
      },
      "frequencyCap": {
        "timeUnit": "TIME_UNIT_DAYS",
        "timeUnitCount": 1,
        "maxImpressions": 5
      },
      "partnerRevenueModel": {
        "markupType": "PARTNER_REVENUE_MODEL_MARKUP_TYPE_TOTAL_MEDIA_COST_MARKUP"
      },
      "conversionCounting": {
        "postViewCountPercentageMillis": "100000",
        "floodlightActivityConfigs": [
          {
            "1fI": "7517101",
            "3PCdays": 30,
            "2PVdays": 30
          },
          {
            "1fI": "7541802",
            "3PCdays": 30,
            "2PVdays": 30
          },
          {
            "1fI": "7552803",
            "3PCdays": 30,
            "2PVdays": 30
          },
          {
            "1fI": "7517104",
            "3PCdays": 30,
            "2PVdays": 30
          }
        ]
      },
      "bidStrategy": {
        "fixedBid": {
          "bidAmountMicros": "3610000"
        }
      },
      "integrationDetails": {}
    },
    {
      "name": "advertisers/1558261/lineItems/12317017",
      "advertiserId": "1238261",
      "campaignId": "1233305",
      "insertionOrderId": "13016372",
      "lineItemId": "12317017",
      "displayName": "All | Routes| All Users | ABC | ABC-LI2",
      "lineItemType": "LINE_ITEM_TYPE_DISPLAY_DEFAULT",
      "entityStatus": "ENTITY_STATUS_ACTIVE",
      "updateTime": "2020-04-01T16:06:19.831Z",
      "partnerCosts": [
        {
          "costType": "PARTNER_COST_TYPE_THIRD_PARTY_AD_SERVER",
          "feeType": "PARTNER_COST_FEE_TYPE_CPM_FEE",
          "feeAmount": "1000000",
          "invoiceType": "PARTNER_COST_INVOICE_TYPE_PARTNER"
        },
        {
          "costType": "PARTNER_COST_TYPE_DOUBLE_VERIFY_PREBID",
          "feeType": "PARTNER_COST_FEE_TYPE_CPM_FEE",
          "feeAmount": "0",
          "invoiceType": "PARTNER_COST_INVOICE_TYPE_DV360"
        },
        {
          "costType": "PARTNER_COST_TYPE_DV360_FEE",
          "feeType": "PARTNER_COST_FEE_TYPE_MEDIA_FEE",
          "feePercentageMillis": "0",
          "invoiceType": "PARTNER_COST_INVOICE_TYPE_DV360"
        },
        {
          "costType": "PARTNER_COST_TYPE_DEFAULT",
          "feeType": "PARTNER_COST_FEE_TYPE_MEDIA_FEE",
          "feePercentageMillis": "0",
          "invoiceType": "PARTNER_COST_INVOICE_TYPE_PARTNER"
        }
      ],
      "flight": {
        "flightDateType": "LINE_ITEM_FLIGHT_DATE_TYPE_CUSTOM",
        "dateRange": {
          "startDate": {
            "year": 2020,
            "month": 4,
            "day": 15
          },
          "endDate": {
            "year": 2020,
            "month": 4,
            "day": 30
          }
        }
      },
      "budget": {
        "budgetAllocationType": "LINE_ITEM_BUDGET_ALLOCATION_TYPE_UNLIMITED",
        "budgetUnit": "BUDGET_UNIT_CURRENCY"
      },
      "pacing": {
        "pacingPeriod": "PACING_PERIOD_DAILY",
        "pacingType": "PACING_TYPE_EVEN",
        "dailyMaxMicros": "26730000"
      },
      "frequencyCap": {
        "timeUnit": "TIME_UNIT_DAYS",
        "timeUnitCount": 1,
        "maxImpressions": 5
      },
      "partnerRevenueModel": {
        "markupType": "PARTNER_REVENUE_MODEL_MARKUP_TYPE_TOTAL_MEDIA_COST_MARKUP"
      },
      "conversionCounting": {
        "postViewCountPercentageMillis": "100000"
      },
      "bidStrategy": {
        "fixedBid": {
          "bidAmountMicros": "3610000"
        }
      },
      "integrationDetails": {}
    }
  ]
}

from floodlightActivityConfigs i need these arrays to be in one cell as

{ "1fI": "72217193", "3PCdays": 30, "2PVdays": 30 },{ "1fI": "75221840", "3PCdays": 30, "2PVdays": 30 }, { "1fI": "75222864", "3PCdays": 30, "2PVdays": 30 }, { "1fI": "75333184", "3PCdays": 30, "2PVdays": 30 }

however when i try to push them only the first part gets printed, from the JSON example the second object doesn't have floodlightActivityConfigs details and this is expected

My Code is below with the JSON embedded, just cant get my head around this

function testAPI2(){

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Mysheet');

  var response =  {"lineItems":[{"name":"advertisers/1558261/lineItems/12317016","advertiserId":"1238261","campaignId":"1233305","insertionOrderId":"13016372","lineItemId":"12317016","displayName":"All | Routes| All Users | ABC | ABC-LI1","lineItemType":"LINE_ITEM_TYPE_DISPLAY_DEFAULT","entityStatus":"ENTITY_STATUS_ACTIVE","updateTime":"2020-04-15T12:51:42.929Z","partnerCosts":[{"costType":"PARTNER_COST_TYPE_THIRD_PARTY_AD_SERVER","feeType":"PARTNER_COST_FEE_TYPE_CPM_FEE","feeAmount":"1000000","invoiceType":"PARTNER_COST_INVOICE_TYPE_PARTNER"},{"costType":"PARTNER_COST_TYPE_DOUBLE_VERIFY_PREBID","feeType":"PARTNER_COST_FEE_TYPE_CPM_FEE","feeAmount":"0","invoiceType":"PARTNER_COST_INVOICE_TYPE_DV360"},{"costType":"PARTNER_COST_TYPE_DV360_FEE","feeType":"PARTNER_COST_FEE_TYPE_MEDIA_FEE","feePercentageMillis":"0","invoiceType":"PARTNER_COST_INVOICE_TYPE_DV360"},{"costType":"PARTNER_COST_TYPE_DEFAULT","feeType":"PARTNER_COST_FEE_TYPE_MEDIA_FEE","feePercentageMillis":"0","invoiceType":"PARTNER_COST_INVOICE_TYPE_PARTNER"}],"flight":{"flightDateType":"LINE_ITEM_FLIGHT_DATE_TYPE_CUSTOM","dateRange":{"startDate":{"year":2020,"month":4,"day":15},"endDate":{"year":2020,"month":4,"day":30}}},"budget":{"budgetAllocationType":"LINE_ITEM_BUDGET_ALLOCATION_TYPE_UNLIMITED","budgetUnit":"BUDGET_UNIT_CURRENCY"},"pacing":{"pacingPeriod":"PACING_PERIOD_DAILY","pacingType":"PACING_TYPE_EVEN","dailyMaxMicros":"40100000"},"frequencyCap":{"timeUnit":"TIME_UNIT_DAYS","timeUnitCount":1,"maxImpressions":5},"partnerRevenueModel":{"markupType":"PARTNER_REVENUE_MODEL_MARKUP_TYPE_TOTAL_MEDIA_COST_MARKUP"},"conversionCounting":{"postViewCountPercentageMillis":"100000","floodlightActivityConfigs":[{"1fI":"7517101","3PCdays":30,"2PVdays":30},{"1fI":"7541802","3PCdays":30,"2PVdays":30},{"1fI":"7552803","3PCdays":30,"2PVdays":30},{"1fI":"7517104","3PCdays":30,"2PVdays":30}]},"bidStrategy":{"fixedBid":{"bidAmountMicros":"3610000"}},"integrationDetails":{}},{"name":"advertisers/1558261/lineItems/12317017","advertiserId":"1238261","campaignId":"1233305","insertionOrderId":"13016372","lineItemId":"12317017","displayName":"All | Routes| All Users | ABC | ABC-LI2","lineItemType":"LINE_ITEM_TYPE_DISPLAY_DEFAULT","entityStatus":"ENTITY_STATUS_ACTIVE","updateTime":"2020-04-01T16:06:19.831Z","partnerCosts":[{"costType":"PARTNER_COST_TYPE_THIRD_PARTY_AD_SERVER","feeType":"PARTNER_COST_FEE_TYPE_CPM_FEE","feeAmount":"1000000","invoiceType":"PARTNER_COST_INVOICE_TYPE_PARTNER"},{"costType":"PARTNER_COST_TYPE_DOUBLE_VERIFY_PREBID","feeType":"PARTNER_COST_FEE_TYPE_CPM_FEE","feeAmount":"0","invoiceType":"PARTNER_COST_INVOICE_TYPE_DV360"},{"costType":"PARTNER_COST_TYPE_DV360_FEE","feeType":"PARTNER_COST_FEE_TYPE_MEDIA_FEE","feePercentageMillis":"0","invoiceType":"PARTNER_COST_INVOICE_TYPE_DV360"},{"costType":"PARTNER_COST_TYPE_DEFAULT","feeType":"PARTNER_COST_FEE_TYPE_MEDIA_FEE","feePercentageMillis":"0","invoiceType":"PARTNER_COST_INVOICE_TYPE_PARTNER"}],"flight":{"flightDateType":"LINE_ITEM_FLIGHT_DATE_TYPE_CUSTOM","dateRange":{"startDate":{"year":2020,"month":4,"day":15},"endDate":{"year":2020,"month":4,"day":30}}},"budget":{"budgetAllocationType":"LINE_ITEM_BUDGET_ALLOCATION_TYPE_UNLIMITED","budgetUnit":"BUDGET_UNIT_CURRENCY"},"pacing":{"pacingPeriod":"PACING_PERIOD_DAILY","pacingType":"PACING_TYPE_EVEN","dailyMaxMicros":"26730000"},"frequencyCap":{"timeUnit":"TIME_UNIT_DAYS","timeUnitCount":1,"maxImpressions":5},"partnerRevenueModel":{"markupType":"PARTNER_REVENUE_MODEL_MARKUP_TYPE_TOTAL_MEDIA_COST_MARKUP"},"conversionCounting":{"postViewCountPercentageMillis":"100000"},"bidStrategy":{"fixedBid":{"bidAmountMicros":"3610000"}},"integrationDetails":{}}]};
  var data = JSON.parse(JSON.stringify(response));

  var LiData = data["lineItems"];

  
  
  var rows = [],
      data;
  for (i = 0; i < LiData.length; i++) {
      data = LiData[i];
      
     rows.push([
      data.campaignId, 
      data.conversionCounting.floodlightActivityConfigs
      ]);
    }

 dataRange = sheet.getRange(2, 1, rows.length,2).setValues(rows);

}

Tanaike · Accepted Answer · 2020-06-19 12:31:27Z

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}]

edited Jun 19, 2020 at 12:31

answered Jun 18, 2020 at 22:54

Tanaike

203k12 gold badges126 silver badges222 bronze badges

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

12 Comments

Imdad Rahman Over a year ago

Hey Tanaike, that worrks well, do you happen to know how i can sort the keys out before too as im trying to get the keys in order of name so like { "1fI": "72217193", "2PVdays": 30,"3PCdays": 30... }

Tanaike Over a year ago

@Imdad Rahman Thank you for replying. I'm glad your issue was resolved. About your additional question, I added one more sample script. Could you please confirm it? If that was not the direction you expect, I apologize.

Imdad Rahman Over a year ago

Hey Tanike, thats exactly the output i need, however im getting an error on the updated script "Illegal character."

Tanaike Over a year ago

@Imdad Rahman Thank you for replying. Unfortunately, in my environment, when I tested the script using your sample values, no error occurs. So I cannot replicate your situation. This is due to my poor skill. I apologize for this. In order to correctly replicate your situation, can you provide the detail information about the issue? If you can cooperate to resolve your issue, I'm glad.

Imdad Rahman Over a year ago

no need to apologize, im using a windows machine, im guessing its the quotes that may be the issue, but cant seem to figure which one or where

|

Collectives™ on Stack Overflow

Google Script JSON nested Arrays to cell

1 Answer 1

Modification point:

Modified script:

Reference:

Answer for the additional question:

Modified script:

12 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Modification point:

Modified script:

Reference:

Answer for the additional question:

Modified script:

12 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related