Google Script JSON nested Arrays to cell

user_12326548 0
Viewed 84

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

}

google-apps-script json javascript
1 Answers

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