0

im trying to sort a nested array before copying back to Gsheet, i was helped on a previous part here, however when i save the script i get an error "illegal Character", i have copied the script to a new sheet and it saves and works, however on my current script i can seem to save the script without that error, is there another way i can re-order that set of arrays.

I have to use my current sheet as there is a lot more code which i cannot just leave so hence i have to use my current sheet/project

my desired output is

{ "1fI": "72217193", "3PCdays": 30, "2PVdays": 30 } to { "1fI": "72217193", "2PVdays": 30,"3PCdays": 30... }

function testAPI(){

  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
  • did you use that same exact code when saving the script? there's a lot of people getting the same error using the wrong type of quotes in their code because they used a word processor to copy and paste their code. If you copy and pasted your JSON data from a table cell in docs, the wrong quotes is most likely oyur problem – user120242 Jun 22 '20 at 11:57
  • yep exact same code, the weired thing is, i copied the same code to a new sheet and it was working, im not sure what settings might be different on my current project that may be causing this. – Imdad Rahman Jun 22 '20 at 12:28
  • Also to note, i have to use my current project as there a lot more things that is being done apart from this API, so i cannot just use a new sheet – Imdad Rahman Jun 22 '20 at 12:29
  • it should give a line number error. keep deleting lines in your script until it stops complaining – user120242 Jun 22 '20 at 12:31
  • ive tried this code in my project under a new script file, still no luck – Imdad Rahman Jun 22 '20 at 12:35
  • what line number does it complain about. find the line. and keep editing and saving the script removing one line at a time, until it stops complaining about the illegal character. then you've found the code that causes a problem – user120242 Jun 22 '20 at 12:36
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/216428/discussion-between-imdad-rahman-and-user120242). – Imdad Rahman Jun 22 '20 at 12:38

1 Answers1

2

See if this works for you. I've transpiled it using Babel and added polyfills for Object.keys and Object.entries. For some reason Google Scripts sometimes choke on code that uses newer syntax:

if (!Object.keys) {
  Object.keys = (function() {
    'use strict';
    var hasOwnProperty = Object.prototype.hasOwnProperty,
        hasDontEnumBug = !({ toString: null }).propertyIsEnumerable('toString'),
        dontEnums = [
          'toString',
          'toLocaleString',
          'valueOf',
          'hasOwnProperty',
          'isPrototypeOf',
          'propertyIsEnumerable',
          'constructor'
        ],
        dontEnumsLength = dontEnums.length;

    return function(obj) {
      if (typeof obj !== 'function' && (typeof obj !== 'object' || obj === null)) {
        throw new TypeError('Object.keys called on non-object');
      }

      var result = [], prop, i;

      for (prop in obj) {
        if (hasOwnProperty.call(obj, prop)) {
          result.push(prop);
        }
      }

      if (hasDontEnumBug) {
        for (i = 0; i < dontEnumsLength; i++) {
          if (hasOwnProperty.call(obj, dontEnums[i])) {
            result.push(dontEnums[i]);
          }
        }
      }
      return result;
    };
  }());
}
if (!Object.entries) {
  Object.entries = function( obj ){
    var ownProps = Object.keys( obj ),
        i = ownProps.length,
        resArray = new Array(i); // preallocate the Array
    while (i--)
        resArray[i] = [ownProps[i], obj[ownProps[i]]];

    return resArray;
  };
}

rows.push([data.campaignId, data.conversionCounting.floodlightActivityConfigs ? "[" + data.conversionCounting.floodlightActivityConfigs.map(function (e) {
  return "{" + Object.entries(e).map(function (_ref) {
    var k = _ref[0],
        v = _ref[1];
    return typeof v == "number" ? "\"" + k + "\":" + v : "\"" + k + "\":\"" + v + "\"";
  }).sort().join(",") + "}";
}).join(",") + "]" : ""]);
user120242
  • 14,918
  • 3
  • 38
  • 52