时间戳

首页 > 解决方案 > 谷歌脚本 JSON 嵌套数组到单元格

javascript - 谷歌脚本 JSON 嵌套数组到单元格

问题描述

我试图将以下 JSON 的部分内容读入谷歌表格，似乎有嵌套数组，我难以将其推入一个单元格......主 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": {}
    }
  ]
}

从 FloodlightActivityConfigs 我需要将这些数组放在一个单元格中

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

但是，当我尝试推送它们时，仅打印第一部分，从 JSON 示例中，第二个对象没有 FloodlightActivityConfigs 详细信息，这是预期的

我的代码在下面嵌入了 JSON，只是无法理解

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

}

标签： javascriptjsongoogle-apps-script

解决方案

我相信你的目标如下。

  • 你想把对象{ "1fI": "72217193", "3PCdays": 30, "2PVdays": 30 },,,放到一个单元格中。

对于这个，这个修改怎么样？

修改点：

  • 在您的脚本中，data.conversionCounting.floodlightActivityConfigs是一个数组。要将其放入单元格，请使用JSON.stringify. 我认为你的问题的原因是这个。

当这反映到您的脚本中时，请进行如下修改。

修改后的脚本：

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

参考：

回答附加问题：

  • 您想对从{ "1fI": "72217193", "3PCdays": 30, "2PVdays": 30 }到的键进行排序{ "1fI": "72217193", "2PVdays": 30,"3PCdays": 30... }。

不幸的是，JSON 对象不能保证顺序。例如，这个线程有助于理解它。

但是，在您的情况下，您希望将对象作为字符串放入单元格中。我认为这可用于解决您的其他问题。所以作为一种解决方法，当{ "1fI": "72217193", "3PCdays": 30, "2PVdays": 30 }不用作JSON对象时，我认为可以设置顺序。

修改后的脚本：

从： 
rows.push([
 data.campaignId, 
 data.conversionCounting.floodlightActivityConfigs
 ]);
至： 
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(",")}]` : ""
]);
  • 在此修改中，将每个对象转换为带有字符串的数组并对其进行排序，然后将数组转换为字符串。这样，您的目标就可以实现。

  • 在这种情况下，将以下值放入单元格中。

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

推荐阅读