harshsharma94/DisplayJSONFromSheet.gs

## DisplayJSONFromSheet.gs
var KEY_COLUMN = 0;
var VALUE_COLUMN = 5;
var DIALOG_TITLE = 'Exported JSON';
var MENU_TITLE = "Export JSON";
var MENU_ENTRY_ITEM_TITLE = "Export Key Values as JSON";

// Invoked when Script is invoked
function onOpen() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var menuEntries = [
    {name: MENU_ENTRY_ITEM_TITLE, functionName: "loadWindow"}
  ];
  ss.addMenu(MENU_TITLE, menuEntries);
}

// Callback function invoked on menu entry click
function loadWindow(e) {
  var output = HtmlService.createTemplateFromFile("index").evaluate();
  output.setWidth(400)
  output.setHeight(800);
  SpreadsheetApp.getUi()
      .showModalDialog(output, DIALOG_TITLE);
}

// Invoked from html script
function getData() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var rowsData = getRowsData_(sheet);
  var json = makeJSON_(rowsData);
  return json
}

function makeJSON_(object) {
  var jsonString = JSON.stringify(object, undefined, 4);
  return jsonString;
}

// getRowsData iterates row by row in the input range and returns an array of objects.
// Returns an Array of objects.
function getRowsData_(sheet) {
  var dataRange = sheet.getRange(sheet.getFrozenRows()+1, 1, sheet.getMaxRows(), sheet.getMaxColumns());
  var objects = getObjects_(dataRange.getValues());
  return objects;
}

// For every row of data in data, generates an object that contains the data.
// Arguments:
//   - data: JavaScript 2d array
function getObjects_(data) {
  var object = {};
  for (var i = 0; i < data.length; ++i) {
      var keyName = data[i][KEY_COLUMN];
      var valueName = data[i][VALUE_COLUMN];
      if (isCellEmpty_(keyName) || isCellEmpty_(valueName)) {
        continue;
      }
      object[keyName] = valueName;
    }
  return object;
}

// Returns true if the cell where cellData was read from is empty.
// Arguments:
//   - cellData: string
function isCellEmpty_(cellData) {
  return typeof(cellData) == "string" && cellData == "";
}

// Called from html to include js script file in html
function include(filename) {
  return HtmlService.createHtmlOutputFromFile(filename)
      .setSandboxMode(HtmlService.SandboxMode.IFRAME)
      .getContent();
}

## index.html
<?!= include('script_js'); ?>
<body>
 <pre id="json"></pre>
</body>

## script_js.html
<script>

    window.onload = function(){
      google.script.run.withSuccessHandler(showData).getData();
    }

    function showData(data){
      document.getElementById("json").innerHTML = data;
    }

</script>
	var KEY_COLUMN = 0;
	var VALUE_COLUMN = 5;
	var DIALOG_TITLE = 'Exported JSON';
	var MENU_TITLE = "Export JSON";
	var MENU_ENTRY_ITEM_TITLE = "Export Key Values as JSON";

	// Invoked when Script is invoked
	function onOpen() {
	var ss = SpreadsheetApp.getActiveSpreadsheet();
	var menuEntries = [
	{name: MENU_ENTRY_ITEM_TITLE, functionName: "loadWindow"}
	];
	ss.addMenu(MENU_TITLE, menuEntries);
	}

	// Callback function invoked on menu entry click
	function loadWindow(e) {
	var output = HtmlService.createTemplateFromFile("index").evaluate();
	output.setWidth(400)
	output.setHeight(800);
	SpreadsheetApp.getUi()
	.showModalDialog(output, DIALOG_TITLE);
	}

	// Invoked from html script
	function getData() {
	var ss = SpreadsheetApp.getActiveSpreadsheet();
	var sheet = ss.getActiveSheet();
	var rowsData = getRowsData_(sheet);
	var json = makeJSON_(rowsData);
	return json
	}

	function makeJSON_(object) {
	var jsonString = JSON.stringify(object, undefined, 4);
	return jsonString;
	}

	// getRowsData iterates row by row in the input range and returns an array of objects.
	// Returns an Array of objects.
	function getRowsData_(sheet) {
	var dataRange = sheet.getRange(sheet.getFrozenRows()+1, 1, sheet.getMaxRows(), sheet.getMaxColumns());
	var objects = getObjects_(dataRange.getValues());
	return objects;
	}

	// For every row of data in data, generates an object that contains the data.
	// Arguments:
	// - data: JavaScript 2d array
	function getObjects_(data) {
	var object = {};
	for (var i = 0; i < data.length; ++i) {
	var keyName = data[i][KEY_COLUMN];
	var valueName = data[i][VALUE_COLUMN];
	if (isCellEmpty_(keyName) \|\| isCellEmpty_(valueName)) {
	continue;
	}
	object[keyName] = valueName;
	}
	return object;
	}

	// Returns true if the cell where cellData was read from is empty.
	// Arguments:
	// - cellData: string
	function isCellEmpty_(cellData) {
	return typeof(cellData) == "string" && cellData == "";
	}

	// Called from html to include js script file in html
	function include(filename) {
	return HtmlService.createHtmlOutputFromFile(filename)
	.setSandboxMode(HtmlService.SandboxMode.IFRAME)
	.getContent();
	}
	<?!= include('script_js'); ?>
	<body>
	<pre id="json"></pre>
	</body>
	<script>

	window.onload = function(){
	google.script.run.withSuccessHandler(showData).getData();
	}

	function showData(data){
	document.getElementById("json").innerHTML = data;
	}

	</script>