to_json.ts

// Includes functions for exporting active sheet or all sheets as JSON object (also Python object syntax compatible).
// Tweak the makePrettyJSON_ function to customize what kind of JSON to export.
// adding download json

var FORMAT_ONELINE = 'One-line';
var FORMAT_MULTILINE = 'Multi-line';
var FORMAT_PRETTY = 'Pretty';

var LANGUAGE_JS = 'JavaScript';
var LANGUAGE_PYTHON = 'Python';

var STRUCTURE_LIST = 'List';
var STRUCTURE_HASH = 'Hash (keyed by "id" column)';

/* Defaults for this particular spreadsheet, change as desired */
var DEFAULT_FORMAT = FORMAT_PRETTY;
var DEFAULT_LANGUAGE = LANGUAGE_JS;
var DEFAULT_STRUCTURE = STRUCTURE_LIST;

function onOpen() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var menuEntries = [
    { name: "Export JSON for this sheet", functionName: "exportSheet" },
    { name: "Export JSON for all sheets", functionName: "exportAllSheets" }
  ];
  ss.addMenu("Export JSON", menuEntries);
}

function makeLabel(app, text, id) {
  var lb = app.createLabel(text);
  if (id) lb.setId(id);
  return lb;
}

function makeListBox(app, name, items) {
  var listBox = app.createListBox().setId(name).setName(name);
  listBox.setVisibleItemCount(1);
  
  var cache = CacheService.getPublicCache();
  var selectedValue = cache.get(name);
  Logger.log(selectedValue);
  for (var i = 0; i < items.length; i++) {
    listBox.addItem(items[i]);
    if (items[1] == selectedValue) {
      listBox.setSelectedIndex(i);
    }
  }
  return listBox;
}

function makeButton(app, parent, name, callback) {
  var button = app.createButton(name);
  app.add(button);
  var handler = app.createServerClickHandler(callback).addCallbackElement(parent);
  button.addClickHandler(handler);
  return button;
}

function makeTextBox(app, name) { 
  var textArea = app.createTextArea().setWidth('100%').setHeight('200px').setId(name).setName(name);
  return textArea;
}

function exportAllSheets(e) {
  try {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheets = ss.getSheets();
    var sheetsData = {};
    for (var i = 0; i < sheets.length; i++) {
      var sheet = sheets[i];
      var rowsData = getRowsData_(sheet, getExportOptions(e));
      var sheetName = sheet.getName();
      sheetsData[sheetName] = rowsData;
    }
    var json = makeJSON_(sheetsData, getExportOptions(e));
    displayTextWithDownload(json, "all_sheets.json");
  } catch (error) {
    var errorMessage = "Error: " + error.toString() + "\n\nStack Trace:\n" + error.stack;
    displayText(errorMessage);
  }
}

function exportSheet(e) {
  try {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getActiveSheet();
    var rowsData = getRowsData_(sheet, getExportOptions(e));
    var json = makeJSON_(rowsData, getExportOptions(e));
    var sheetName = sheet.getName();
    displayTextWithDownload(json, sheetName + ".json");
  } catch (error) {
    var errorMessage = "Error: " + error.toString() + "\n\nStack Trace:\n" + error.stack;
    displayText(errorMessage);
  }
}

function displayText(text) {
  var output = HtmlService.createHtmlOutput("<textarea style='width:100%; height: 100%;' rows='20'>" + text + "</textarea>");
  output.setWidth(400)
  output.setHeight(300);
  SpreadsheetApp.getUi()
      .showModalDialog(output, 'Exported JSON');
}

function displayTextWithDownload(text, filename) {
  var html = `
    <textarea id="jsonData" style="width:100%; height:200px; margin-bottom:10px;">${text}</textarea>
    <br/>
    <button onclick="downloadJson()">Download JSON File</button>
    <script>
      function downloadJson() {
        var text = document.getElementById("jsonData").value;
        var blob = new Blob([text], {type: "application/json"});
        var url = URL.createObjectURL(blob);
        var a = document.createElement("a");
        a.href = url;
        a.download = "${filename}";
        document.body.appendChild(a);
        a.click();
        document.body.removeChild(a);
        URL.revokeObjectURL(url);
      }
    </script>
  `;
  
  var output = HtmlService.createHtmlOutput(html);
  output.setWidth(400)
  output.setHeight(300);
  SpreadsheetApp.getUi().showModalDialog(output, 'Exported JSON - Download Available');
}

function getExportOptions(e) {
  var options = {};
  
  options.language = e && e.parameter.language || DEFAULT_LANGUAGE;
  options.format = e && e.parameter.format || DEFAULT_FORMAT;
  options.structure = e && e.parameter.structure || DEFAULT_STRUCTURE;
  
  var cache = CacheService.getPublicCache();
  cache.put('language', options.language);
  cache.put('format', options.format);
  cache.put('structure', options.structure);
  
  Logger.log(options);
  return options;
}

function makeJSON_(object, options) {
  if (options.format == FORMAT_PRETTY) {
    var jsonString = JSON.stringify(object, null, 4);
  } else if (options.format == FORMAT_MULTILINE) {
    var jsonString = JSON.stringify(object);
    jsonString = jsonString.replace(/},/gi, '},\n');
    jsonString = jsonString.replace(/":\[{"/gi, '":\n[{"');
    jsonString = jsonString.replace(/}\],/gi, '}],\n');
  } else {
    var jsonString = JSON.stringify(object);
  }
  if (options.language == LANGUAGE_PYTHON) {
    // add unicode markers
    jsonString = jsonString.replace(/"([a-zA-Z]*)":\s+"/gi, '"$1": u"');
  }
  return jsonString;
}

function getRowsData_(sheet, options) {
  var lastRow = sheet.getLastRow();
  var lastColumn = sheet.getLastColumn();
  
  // Jika tidak ada data di sheet
  if (lastRow <= sheet.getFrozenRows() || lastColumn === 0) {
    return options.structure === STRUCTURE_HASH ? {} : [];
  }

  var headersRange = sheet.getRange(1, 1, 1, lastColumn);
  var headers = headersRange.getValues()[0];
  
  // Pastikan ada data setelah header
  if (lastRow <= sheet.getFrozenRows()) {
    return options.structure === STRUCTURE_HASH ? {} : [];
  }

  var dataRange = sheet.getRange(
    sheet.getFrozenRows() + 1,
    1,
    lastRow - sheet.getFrozenRows(),
    lastColumn
  );

  var objects = getObjects_(dataRange.getValues(), normalizeHeaders_(headers));
  
  if (options.structure == STRUCTURE_HASH) {
    var objectsById = {};
    objects.forEach(function(object) {
      if (object.id) {
        objectsById[object.id] = object;
      }
    });
    return objectsById;
  } else {
    return objects;
  }
}

function getColumnsData_(sheet, range, rowHeadersColumnIndex) {
  rowHeadersColumnIndex = rowHeadersColumnIndex || range.getColumnIndex() - 1;
  var headersTmp = sheet.getRange(range.getRow(), rowHeadersColumnIndex, range.getNumRows(), 1).getValues();
  var headers = normalizeHeaders_(arrayTranspose_(headersTmp)[0]);
  return getObjects_(arrayTranspose_(range.getValues()), headers);
}

function getObjects_(data, keys) {
  var objects = [];
  for (var i = 0; i < data.length; ++i) {
    var object = {};
    var hasData = false;
    for (var j = 0; j < data[i].length; ++j) {
      var cellData = data[i][j];
      if (isCellEmpty_(cellData)) {
        continue;
      }
      object[keys[j]] = cellData;
      hasData = true;
    }
    if (hasData) {
      objects.push(object);
    }
  }
  return objects;
}

function normalizeHeaders_(headers) {
  var keys = [];
  for (var i = 0; i < headers.length; ++i) {
    var key = normalizeHeader_(headers[i]);
    if (key.length > 0) {
      keys.push(key);
    }
  }
  return keys;
}

function normalizeHeader_(header) {
  var key = "";
  var upperCase = false;
  for (var i = 0; i < header.length; ++i) {
    var letter = header[i];
    if (letter == " " && key.length > 0) {
      upperCase = true;
      continue;
    }
    if (!isAlnum_(letter)) {
      continue;
    }
    if (key.length == 0 && isDigit_(letter)) {
      continue; // first character must be a letter
    }
    if (upperCase) {
      upperCase = false;
      key += letter.toUpperCase();
    } else {
      key += letter.toLowerCase();
    }
  }
  return key;
}

function isCellEmpty_(cellData) {
  return typeof(cellData) == "string" && cellData == "";
}

function isAlnum_(char) {
  return char >= 'A' && char <= 'Z' ||
    char >= 'a' && char <= 'z' ||
    isDigit_(char);
}

function isDigit_(char) {
  return char >= '0' && char <= '9';
}

function arrayTranspose_(data) {
  if (data.length == 0 || data[0].length == 0) {
    return null;
  }

  var ret = [];
  for (var i = 0; i < data[0].length; ++i) {
    ret.push([]);
  }

  for (var i = 0; i < data.length; ++i) {
    for (var j = 0; j < data[i].length; ++j) {
      ret[j][i] = data[i][j];
    }
  }

  return ret;
}