빵개발연구소

function onOpen() {
    SpreadsheetApp.getUi().createMenu("Export JSON").addItem("Export Say JSON", "exportSheet").addToUi();
}

function exportSheet(e) {  
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getActiveSheet();
    var Values = arrayTranspose_(sheet.getDataRange().getValues());

    var objects = [];  

    for (var i = 0; i < Values.length; ++i) {
        var object = {};
        object["Situation"] = Values[i][1];
        var say = [];
        for (var j = 2; j < Values[i].length; ++j) {
            if (isCellEmpty_(Values[i][j]))
                continue;

            say.push(Values[i][j])
        }
        object["Content"] = say;
        objects.push(object);
    }

    var json = JSON.stringify(objects, null, 4);
    return displayText_(json);
}

function displayText_(text) { 
  
  var html = HtmlService.createTemplateFromFile("Index");
  html.json_data = text;
  var htmlout = html.evaluate();  
  SpreadsheetApp.getUi().showModalDialog(htmlout, "JSON으로 내보내기");
}

function UploadToServer(json_data, pwd)
{
   var payload = {
     "password":pwd,
    "json_data":json_data
  };
  
  var options =
   {
     "method" : "post",
     "payload" : payload
   };
  var response = UrlFetchApp.fetch("http://www.myURL.com/my.php", options);
  var ui = SpreadsheetApp.getUi();
  ui.alert(response.getContentText());
  
}


// Given a JavaScript 2d Array, this function returns the transposed table.
// Arguments:
//   - data: JavaScript 2d Array
// Returns a JavaScript 2d Array
// Example: arrayTranspose([[1,2,3],[4,5,6]]) returns [[1,4],[2,5],[3,6]].
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;
}

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

구글스크립트에서는 UI를 HTML로 작성한다.

Index.html

레퍼런스 : https://developers.google.com/apps-script/