An add-on custom function to call a Meraki API and shape the data to fit a Google Sheet.

merakiFetchReport.gs

/**
 * Fetches Meraki API data and formats the JSON data into Google Sheet format
 * @param url url to pull json data from
 * @param apiKey API key for authorization
 * @param title The title text to display in the cell
 * @param refresh Triggers a refresh of report when changed. Cannot use now() functions, so a helper "Refresh Reports" button will update a hidden _meraki_tools settings sheet to set a time, triggering the function to re-evaluate. You can optionaly set a Trigger to call the refreshReports function on a schedule. 
 * @return Google Sheet data
 * @customfunction
 */
async function merakiFetchReport(url,apiKey, title, refresh){
  refresh = refresh; // this does nothing, but triggers a change event for google sheets to re-evaluate the function
  const userProperties = PropertiesService.getUserProperties();
  apiKey = apiKey || userProperties.getProperty("MERAKI_API_KEY")
  // set default tile
  const date = new Date()

  title = title || `Meraki API`;
  title = `${title} , ${url}, Updated: ${date.toISOString()} \n` 

  const baseUrl = "https://api.meraki.com/api/v1" // consider sourcing this from a global namespace
  url = url.includes("https://") ? url : baseUrl+url
  const options = {
    "async": true,
     "crossDomain": true,
     "method" : "GET",
     "headers" : {'X-Cisco-Meraki-API-Key': apiKey}
  }
  // get api result
  let res = {}
  res = await fetch(url,options)
  let result = {}
  try{
    result = JSON.parse(res["body"])

    // report helpers
    if(url.includes("openapiSpec")){
      result = parseSwaggerPaths(result)
    }else if(url.includes("/events")) {   
      result = parseNetworkEvents(result)   
    }
  }catch(e){
    result = e
  }

 
  
  // build results array
  let data = []
  let keys = []
  let results = []
  // find keys
  if(Array.isArray(result)){
    results = [...[],...result]
    }else{results.push(result)}  
  results.forEach(function(obj){ 
    let flat  = {};
    flat = flattenObject(obj) || {};
    data.push(flat);
    // set keys
    Object.keys(flat).forEach(function(value){
      if (keys.indexOf(value)==-1) keys.push(value);
    });
  });

  // convert to csv
  let csvData= await parseJsonToCsv(data,keys).toString()
  Logger.log(`csvData results: %s`, csvData)

  // add title
  csvData = title + csvData;
  // convert to sheet multi-dimensional array
  const arr = await Utilities.parseCsv(csvData);
  
  Logger.log(` csvToArray arr : %s`, arr)
  SpreadsheetApp.flush();
  return arr
}


// Utilities

// Helper functions

function flattenObject(ob) {
    var toReturn = {};
     
     for (var i in ob) {
         if (!ob.hasOwnProperty(i)) continue;
         
         if ((typeof ob[i]) == 'object') {
             var flatObject = flattenObject(ob[i]);
             for (var x in flatObject) {
                 if (!flatObject.hasOwnProperty(x)) continue;
                 
                 toReturn[i + '.' + x] = flatObject[x];
             }
         } else {
             toReturn[i] = ob[i];
         }
     }
     return toReturn;
 };
 
function parseJsonToCsv(json, keys){
   var values = [];
 
   // Parse JSON Object
   if(!Array.isArray(json)){
        
     // Get Values
     var v = [];    
     keys.forEach(
       function (k){
         v.push(json[k]);
       }
     );
     values.push(keys.toString())
     values.push("\n"+v)
     //Logger.log('Parse Object values %s',values);
     
   } else {
   
   // Parse JSON Array of Objects
     for (let i = 0; i < json.length; i++) { 
       
       var data = json[i];
       
       // Get Values
       var v = [];
       keys.forEach(
         function (k){
           v.push(data[k]);
         }
       );
       // Create a new line
       if(i > 0){
         values.push("\n"+v)
       }else{
         values.push(keys.toString())
         values.push("\n"+v);       
       }     
  //     Logger.log('Parse Array of Object values '+i + " : " +v);
     } 
   }
   return values;
 }
 

 // REPORT Helpers

 // Extracts report of paths and relavent information of a Swagger / OAS v2 JSON object.
function parseSwaggerPaths(swagger) {
  if(!swagger){return}
  let paths = swagger["paths"];
  //console.log("organization Open API paths", paths);
  let report = [];
  try {
    // get paths
    Object.keys(paths).forEach(function(path, index) {
      // get details for each path resource
      Object.keys(paths[path]).forEach((p, i) => {
        let tag = paths[path][p]["tags"][0];
        let summary = paths[path][p]["summary"];
        let description = paths[path][p]["description"];
        let operationId = paths[path][p]["operationId"];
        let params = paths[path][p]["parameters"] || [];
        let method = Object.keys(paths[path])[i];

        let responses = "";
        try{
          JSON.stringify(paths[path][p]["responses"]["200"]["example"])
        }
          catch(e){

        }

        // pathParams
        let pathParams = [];
        let filteredPathParams = params.filter(p => p.in.includes("path"));
        filteredPathParams.forEach(p => pathParams.push(p.name));
        //pathParams = JSON.stringify(pathParams);
        pathParams = pathParams.join("; ").toString();

        // queryParams
        let queryParams = [];
        let filteredQueryParams = params.filter(p => p.in.includes("query"));
        filteredQueryParams.forEach(p => queryParams.push(p.name));
        //queryParams = JSON.stringify(queryParams);
        queryParams = queryParams.join("; ");

        // bodyModel
        let bodyModel  = [];
        let filteredBodyModel = params.filter(p => p.in.includes("body"));
        filteredBodyModel.forEach(p => bodyModel.push(p.name));
        //bodyModel = JSON.stringify(bodyModel);
        bodyModel = bodyModel.join(", ");

        // create report
        report.push({
          //tag,
          operationId,
          summary: summary.toString(),
          "product tags.0": paths[path][p]["tags"][0],    // product  
          "category tags.1": paths[path][p]["tags"][1],    // category
          "service tags.2": paths[path][p]["tags"][1],    // service
          method,
          path,
          pathParams,
          queryParams,
         // responses
         //j bodyModel
          //description //this data has chararcter conflicts with the sheet
        });
      });
      // sort order based on group tag name.
      report = report.sort((a, b) => {
        if (a.tag < b.tag) return -1;
        if (a.tag > b.tag) return 1;
        return 0;
      });
    });
    return report;
  } catch (error) {
    return error
    //throw (error, "parseSwaggerPaths");
  }
}

function parseNetworkEvents(events) {
  return events.events;
}


/**
 * Fetch an API request
 *
 * @param {path} URL API path .
 * @param {options} request options.
 * @return JSON data
 * @customfunction
 */
function fetch(path, options={}) {
  options = {...{followRedirects:true}, ...options}
  options["muteHttpExceptions"] = true; // passes error on to client for processing / display

  try {
    let res = UrlFetchApp.fetch(path, options);
    let responseCode = res.getResponseCode();
    let responseBody = res.getContentText();

    return {
      body: responseBody,
      headers: res.getHeaders(),
      statusCode: responseCode
    };
  } catch (e) {
    Logger.log("fetch error: " + e);
    return e;
  }
}