I'm using google app script to parse this json response.
Issue
On sheets I get only the values of the first metric not the second one
But I want the values of the second metric as well.
This is what i'm tryng to achieve
Any help?
Thanks
7
-
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– Tedinoz2020-03-12 22:53:54 +00:00Commented Mar 12, 2020 at 22:53
-
@Tedinoz I added the json as text. Unfortunally I can't share the spreadsheet because there are sensitive information. ThanksMauro Bros– Mauro Bros2020-03-12 23:01:00 +00:00Commented Mar 12, 2020 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– Tedinoz2020-03-12 23:09:12 +00:00Commented Mar 12, 2020 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– Tedinoz2020-03-12 23:23:14 +00:00Commented Mar 12, 2020 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 a while back.TheMaster– TheMaster2020-03-13 05:45:02 +00:00Commented Mar 13, 2020 at 5:45
|
Show 2 more comments
1 Answer
How about this sample script?
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);
jsonis 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 about this.
