Approaches and options to pulling District Dashboard stats

[gcor71]

Hey, I'm looking at how to adapt your project to pull Section Size information into a sheet. It's not part of OSM many folks will see/use.

Would you take a look at the output below and suggest which of your existing .gs components would be closest to what I'd need to do to pull the "sections" elements per group? I am happy to work on it myself and share back, but I struggle to interpret the function calls necessary to navigate across the array. It's probably pretty simple - but I can't get my head round it.

Thanks in advance.

The POST is: /ext/discounts/?action=getSizes&code=221&sectionid=68972 The returned BODY (sample) is:

{
    "Portsmouth": {
        "idealsize": 1451,
        "numscouts": 1084,
        "waiting": 272,
        "byGroup": {
            "104th Portsmouth": {
                "sections": [
                    {
                        "numscouts": 15,
                        "sectionid": "30931",
                        "type": "beavers",
                        "idealsize": 50,
                        "idealleaders": "5",
                        "numleaders": "5",
                        "name": "Beavers",
                        "waiting": 3
                    },
                    { ... etc ... }
                ],
                "idealsize": 88,
                "numscouts": 45,
                "waiting": 7
            },
            "1st Portsmouth Sea Scouts": {
                "sections": [
                    {
                        "numscouts": 15,
                        "sectionid": "50569",
                        "type": "beavers",
                        "idealsize": 18,
                        "idealleaders": "4",
                        "numleaders": "2",
                        "name": "Beavers",
                        "waiting": 0
                    },
                    { ... etc ... }
                ],
                "idealsize": 63,
                "numscouts": 70,
                "waiting": 0
            },
            ... etc ...
            }
        }
    },
    "categories": [
        "Portsmouth"
    ]
}

[gcor71]

After a lot of trial and error, and some help from ChatGPT, I got it to work as follows:

in OSM.gs

  /////////////////
  // 
  //  Section size statistics
  //
  /////////////////

  fetch_stats() {
    var result = this.fetch("ext/discounts/?action=getSizes&code=221&sectionid=68972");

    return result;
  }

in testcode.gs

function action_fetch_stats() {
  var service = getOSMService();
  var osm = OSM(service).init();

  var jsonData = osm.fetch_stats();

  // Replace SHEET_NAME with the name of the sheet where you want to create the table
  var sheetName = 'Section Size Stats';

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);

  if (!sheet) {
    // If the sheet doesn't exist, create a new sheet with the specified name
    sheet = SpreadsheetApp.getActiveSpreadsheet().insertSheet(sheetName);
  } else {
    // If the sheet already exists, clear its contents
    sheet.clear();
  }

  // Write headers to the sheet
  var headers = [
    'Group Name',
    'Section Name',
    'Type',
    'Ideal Size',
    'Ideal Leaders',
    'Num Leaders',
    'Num Scouts',
    'Waiting'
  ];

  sheet.appendRow(headers);

  // Loop through the data and write it to the sheet
  var categories = jsonData.categories;

  for (var i = 0; i < categories.length; i++) {
    var category = categories[i];
    var groups = jsonData[category].byGroup;

    for (var groupName in groups) {
      var group = groups[groupName];

      for (var j = 0; j < group.sections.length; j++) {
        var section = group.sections[j];
        var rowData = [
          groupName,
          section.name,
          section.type,
          section.idealsize,
          section.idealleaders,
          section.numleaders,
          section.numscouts,
          section.waiting
        ];

        sheet.appendRow(rowData);
      }
    }
  }
}