1

I'm using google app script to parse this json response.

enter image description here

Issue

On sheets I get only the values of the first metric not the second one

enter image description here

But I want the values of the second metric as well.

This is what i'm tryng to achieve

enter image description here

Any help?

Thanks

Mauro Bros
  • 91
  • 6
  • Would you please share a copy of your spreadsheet. Would you please edit your question to include the json as text, not as an image. – Tedinoz Mar 12 '20 at 22:53
  • @Tedinoz I added the json as text. Unfortunally I can't share the spreadsheet because there are sensitive information. Thanks – Mauro Bros Mar 12 '20 at 23:01
  • Sorry, I should have said... "exclude any sensitive or confidential information". But you really do need to share a spreadsheet. BTW, you have only shared a snippet of your code but haven't shown where/when/how you parse the json. – Tedinoz Mar 12 '20 at 23:09
  • The json lint tools are reporting that the json is not valid. What is the actual json that you are importing? – Tedinoz Mar 12 '20 at 23:23
  • 1
    @Tedinoz The question should be contained. External links are supplementary, but not necessary. In fact, any question that requires a external spreadsheet is not a good question. You don't have to agree with me. You might be interested in a [discussion I started with the community](https://meta.stackoverflow.com/questions/394304/) a while back. – TheMaster Mar 13 '20 at 05:45
  • @TheMaster Thank you for pointing this out; I was ignorant of the privacy issue. You make a sound point and I will give it more consideration. Speaking for myself, I see very few questions that could be considered "contained", and personally I feel that using an image to display spreadsheet information is the kiss of death because it forces one to develop a model using one;s own test data - and often that is just not worth the bother. Perhaps there is a happy medium that we are yet to discover. – Tedinoz Mar 15 '20 at 01:33
  • @Tedinoz Thank you for your consideration. I believe csv or just [written table](https://stackoverflow.com/questions/60466903/can-i-merge-a-multi-dimensional-array-into-a-single-dimensional-array-in-google) should be a viable alternative. – TheMaster Mar 15 '20 at 06:25

1 Answers1

4

How about this sample script? Please think of this as just one of several possible answers.

Sample script:

var json = {locationMetrics:[{metricValues:[{metric:"ACTIONS_DRIVING_DIRECTIONS", dimensionalValues:[{timeDimension:{timeRange:{startTime:"2020-02-01T00:00:00Z"}}, value:3, metricOption:"AGGREGATED_DAILY"}, {value:0, metricOption:"AGGREGATED_DAILY", timeDimension:{timeRange:{startTime:"2020-02-02T00:00:00Z"}}}]}, {metric:"ACTIONS_PHONE", dimensionalValues:[{metricOption:"AGGREGATED_DAILY", timeDimension:{timeRange:{startTime:"2020-02-01T00:00:00Z"}}, value:0}, {timeDimension:{timeRange:{startTime:"2020-02-02T00:00:00Z"}}, value:0, metricOption:"AGGREGATED_DAILY"}]}], timeZone:"Europe/London", locationName:"accounts/xxx/locations/xxx"}]};

// Create an array from "json".
var locationMetrics = json.locationMetrics;
var rows = locationMetrics.reduce(function(ar1, obj1) {
  return ar1.concat(obj1.metricValues.reduce(function(ar2, obj2) {
    return ar2.concat(obj2.dimensionalValues.map(function(obj3) {return [obj2.metric, obj3.metricOption, obj3.timeDimension.timeRange.startTime, obj3.value]}));
  }, []));
}, []);

// Put values to Spreadsheet.
var sheet = SpreadsheetApp.getActive().getSheetByName('sheet1')
dataRange = sheet.getRange(2, 1, rows.length, 4); 
dataRange.setValues(rows);
  • json is from your question.

Result:

[
    ["ACTIONS_DRIVING_DIRECTIONS","AGGREGATED_DAILY","2020-02-01T00:00:00Z",3],
    ["ACTIONS_DRIVING_DIRECTIONS","AGGREGATED_DAILY","2020-02-02T00:00:00Z",0],
    ["ACTIONS_PHONE","AGGREGATED_DAILY","2020-02-01T00:00:00Z",0],
    ["ACTIONS_PHONE","AGGREGATED_DAILY","2020-02-02T00:00:00Z",0]
]

Note:

  • In this case, the script can work with and without V8.
  • In this sample script, your sample value is used. So it supposes that all keys of "metric", "metricOption", "startTime", "value" are existing. So please be careful this.

References:

If I misunderstood your question and this was not the direction you want, I apologize.

Tanaike
  • 181,128
  • 11
  • 97
  • 165