2

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

}
Imdad Rahman
  • 27
  • 5

1 Answers1

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:

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}]
Tanaike
  • 181,128
  • 11
  • 97
  • 165
  • 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... } – Imdad Rahman Jun 19 '20 at 09:56
  • @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. – Tanaike Jun 19 '20 at 12:31
  • Hey Tanike, thats exactly the output i need, however im getting an error on the updated script "Illegal character." – Imdad Rahman Jun 19 '20 at 13:18
  • @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. – Tanaike Jun 19 '20 at 23:31
  • 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 – Imdad Rahman Jun 20 '20 at 10:28
  • i just get an error when trying to save the code, "illegal Character" – Imdad Rahman Jun 20 '20 at 10:29
  • @Imdad Rahman Thank you for replying. I would like to support you and resolve your new issue. But, I have to apologize for my poor skill. Unfortunately, I cannot understand about your situation from `illegal Character`. So in order to correctly understand about your current situation, can you post it as new question by including more information for replicating your new issue? By this, I would like to confirm it. And also, it will also help other users think of your new issue. When you can cooperate to resolve your issue, I'm glad. – Tanaike Jun 20 '20 at 11:44
  • thanks for the advise, i just copied the code to a new sheet and itsworking as expected, just strange im getting an error on the existing sheet, ill play around with it a little more, im guessing its something silly on my side. – Imdad Rahman Jun 21 '20 at 10:01
  • Thanks for all the help @Tanaike.. your a star!! – Imdad Rahman Jun 21 '20 at 10:01
  • @Imdad Rahman Thank you for replying and adding more information. I'm glad your issue was resolved. Thank you, too. – Tanaike Jun 21 '20 at 22:31
  • For those that may have a similar issue... please refer to https://stackoverflow.com/questions/62513309/google-script-sort-json-nested-arrays/62514828?noredirect=1#comment110557053_62514828 @tanaike, this solved my issue – Imdad Rahman Jun 22 '20 at 14:02
  • @Imdad Rahman Thank you for replying. Now I saw your new question. I noticed that your issue has already been resolved. I'm glad for it. – Tanaike Jun 23 '20 at 00:09