Meraki Dashboard Reports with Google Sheets

Easily import Meraki Network data into Google Sheets using the Meraki Dashboard API.

Get Started

Simply run this demo by opening the following Google Sheet.

Meraki-Reports Sheet

Then MAKE A COPY of the file to your own account by selecting File -> Make a copy. This will allow the script to run and ensure your credentials are kept within your account.

How it works

The Google Sheet has a Code.gs file attached to it. This script creates a menu of reports that run functions to call the Meraki Dashboard API.

Source code

You can find this in Tools –> Script editor

The beginning of the script includes variables that can be adjusted by the user. Enter your Meraki Dashboard API key here as well as the default organization ID. You can also adjust which sheet and cell these variables can be pulled from.

Script Details

If you would like to extend the reports you can modify the JavaScript file as needed. Here is a general overview of what each code section does.

Dashboard API Functions

These are the individual calls to the Meraki Dashboard API. To add additional Meraki endpoints copying a similar function and adjust the path and function name.

function getOrgs(apiKey) {
  var response = UrlFetchApp.fetch("https://api.meraki.com/api/v0/organizations", {headers:{'X-Cisco-Meraki-API-Key': apiKey}});
  var data = response.getContentText();
  var json = JSON.parse(data);
  return json;
}

Report Functions

These functions will make the request to the API functions and format the data as necessary. You will define what parameters of the object should be set as the column headers. These names should match the JSON object key for the respective value.

function callOrgs(){
  var data = getOrgs(settings.apiKey);
  displayJSON(data, ['id','name']);
}

If there are nested JSON objects, some additional logic will need to be used to flatten the data.

function callLicenseState(){
  var data = getLicenseState(settings.apiKey, settings.orgId);

  // flatten model names and values
  var models = Object.keys(data.licensedDeviceCounts);
  models.forEach(
    function (m){
      data[m] = data.licensedDeviceCounts[m];
    }
  );
  var keys = ['status', 'expirationDate'];
  var combinedKeys = keys.concat(models);
  displayJSON([data], combinedKeys);
}

Toolbar Menu

This is where we define what menu options are available to the Sheets page and which function should be called for each.

// Toolbar Menu Items
function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Meraki-Reports')
      .addItem('Organizations for API key','callOrgs')
      .addItem('Organization','callOrg')
      .addItem('Inventory','callInventory')
      .addItem('Networks','callNetworks')
      .addItem('Devices','callDevices')
      .addItem('License State','callLicenseState')
      .addItem('Configuration Templates','callConfigTemplates')
      .addItem('Group Policies','callGroupPoliciesOfOrg')
      .addItem('SSIDs','callSsidsOfOrg')
      .addItem('VLANS','callVlansOfOrg')
      .addToUi();

}

Display Functions

This function is responsible for converting the data arrays into the Google sheet format. It handles the ability to place the report data where the active cell is. You shouldn’t have to modify this.

function displayJSON(json, keys){
  //json = [{"id":"1234","name":"sample"},{"id":"9876","name":"sample 2", "extra":"more info"}];
  Logger.log('displayJSON'+ JSON.stringify(json));
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheets = ss.getSheets();
  var sheet = ss.getActiveSheet();
  ...

What can you do with this?

Share your ideas and feedback on our community!