Sort JSON Data by Date Google Apps Script

Question

I'm running a basic API call which uses the following Google Apps Script:

var response = UrlFetchApp.fetch('https://url.com?startDate=2017-09-01&endDate=2018-04-01', options);
var dataAll = JSON.parse(response.getContentText());

This gives me the following JSON data

{
  "kind": "resultTable",
  "columnHeaders": [
    {
      "name": "month",
      "columnType": "DIMENSION",
      "dataType": "STRING"
    },
    {
      "name": "averageViewDuration",
      "columnType": "METRIC",
      "dataType": "INTEGER"
    },
    {
      "name": "averageViewPercentage",
      "columnType": "METRIC",
      "dataType": "FLOAT"
    },
    {
      "name": "subscribersGained",
      "columnType": "METRIC",
      "dataType": "INTEGER"
    },
    {
      "name": "estimatedMinutesWatched",
      "columnType": "METRIC",
      "dataType": "INTEGER"
    },
    {
      "name": "views",
      "columnType": "METRIC",
      "dataType": "INTEGER"
    },
    {
      "name": "likes",
      "columnType": "METRIC",
      "dataType": "INTEGER"
    },
    {
      "name": "subscribersGained",
      "columnType": "METRIC",
      "dataType": "INTEGER"
    },
    {
      "name": "shares",
      "columnType": "METRIC",
      "dataType": "INTEGER"
    }
  ],
  "rows": [
    [
      "2017-11",
      648,
      22.06,
      13,
      47298,
      4376,
      60,
      13,
      72
    ],
    [
      "2017-12",
      641,
      21.83,
      6,
      21284,
      1990,
      13,
      6,
      14
    ],
    [
      "2018-01",
      620,
      21.12,
      5,
      12790,
      1236,
      14,
      5,
      12
    ],
    [
      "2018-02",
      636,
      21.65,
      2,
      11374,
      1072,
      5,
      2,
      12
    ],
    [
      "2018-04",
      604,
      20.58,
      4,
      9760,
      968,
      14,
      4,
      9
    ],
    [
      "2018-03",
      615,
      20.94,
      8,
      8486,
      827,
      7,
      8,
      12
    ]
  ]
}

While the rows are mostly in date order, there are one or two instances where it isn't. How would I sort the rows to be by date in Google Apps Script so that it starts with 2017-11 first and 2018-04 last?

The rows appear to be a two dimensional array. You could use the sort method with a sort function that uses the full year and month-1 along with a Date() constructor to create a new Date() and then comparable the values via valueOf or getTime methods — Cooper
– Cooper, Commented Dec 17, 2020 at 21:48

Cooper · Accepted Answer · 2020-12-17 22:00:59Z

1

Something like this

function sortRows() {
  var response = UrlFetchApp.fetch(); 
  let data=JSON.parse(response.getContentText());//from what you provided
  data.rows.sort((a,b)=>{
    let A=a[0].split('-');
    let adv=new Date(A[0],A[1],0).valueOf();
    let B=b[0].split('-');
    let bdv=new Date(B[0],B[1],0).valueOf();
    return adv-bdv;
  });
}

answered Dec 17, 2020 at 22:00

Cooper

64.2k7 gold badges29 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Sort JSON Data by Date Google Apps Script

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related