鹿児島.mk #7 GASの体験ワークメモ

var postChannel = "{チャンネル名}";
var url = "{webhook}";
// 好きな名前で大丈夫です
var userName = "{参加者名}";

function sendToSlack(body) {
  var data = { "channel" : postChannel, "username" : userName, "text" : body, "icon_emoji" : ":sushi:" };
  var payload = JSON.stringify(data);
  var options = {
    "method" : "POST",
    "contentType" : "application/json",
    "payload" : payload
  };
  var response = UrlFetchApp.fetch(url, options);
}

function test() {
　FormApp.getActiveForm();
  sendToSlack("テスト通知確認です");
}

function onFormSubmit(e){
  var body = "=============================="; 
  var applicant = "";
  var itemResponse = e.response ? e.response.getItemResponses(): [];
  
  var email = e.response ? e.response.getRespondentEmail(): 'test@hoge.com';
  var name = '名無し';
  var jobs = '無職';
  var age = '無回答';

  for (var j = 0; j < itemResponse.length; j++){    
    var formData = itemResponse[j];
    var title = formData.getItem().getTitle();
    var response = formData.getResponse();
    
    switch (title) {
      case "メールアドレス":
        email = response;
        break;
      case "お名前":
        name = response;
        break;
      case "所属":
        jobs = response;
        break;  
      case "年齢":
        age = response;
        break;
      default:
        break;
    }
  }
  var text =  body + " \nお名前:" + name + "\nメールアドレス: " + email + "\n職業:" + jobs + "\n年齢" + age + "\n\n==============================";
  sendToSlack(text);
}

var ssId = '{スプレッドシートのID}';
var dbSheetName = '{DBとして使うシート名}';

//JSONのURL
var jsonUrl = "{APIのURL}";
var inputSheetName = "{データを書き込むシート名}";

// 以下の項目は編集しない
var spUrl = "https://docs.google.com/spreadsheets/d/"+ssId+"/";
var spreadsheet = SpreadsheetApp.openByUrl(spUrl);
var keys = {
  'title' : "",
  'started_at': "",
  'event_url' : "",
};

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  var menu = ui.createMenu('メニュー');
  menu.addItem('APIからJSON取得', 'getJson');
  menu.addToUi();
}

function getData(id, sheetName) {
  var sheet = SpreadsheetApp.openById(id).getSheetByName(sheetName);
  var rows = sheet.getDataRange().getValues();
  var keys = rows.splice(0, 1)[0];
  return rows.map(function(row) {
    var obj = {}
    row.map(function(item, index) {
      obj[keys[index]] = item;
    });
    return obj;
  });
}

function doGet(e) {
  var func = 'items';
  var data = getData(ssId, dbSheetName);
  return ContentService.createTextOutput(func + '(' + JSON.stringify(data, null, 2) + ')')
  .setMimeType(ContentService.MimeType.JAVASCRIPT);
}


function getJson() {
  var sheet = spreadsheet.getSheetByName(inputSheetName);
  var json = UrlFetchApp.fetch(jsonUrl).getContentText();
  var jsonData = JSON.parse(json);
  var eventData = jsonData.events;
  
  var k = 1;
  for(var key in keys) {
    sheet.getRange(1, k).setValue(key);  
    k++;
  }

  var i=2;
  for (var idx in eventData) {
    var j=1;
    for(var key in keys) {
      var type = typeof eventData[idx][key];
      sheet.getRange(i, j).setValue(eventData[idx][key]);  
      j++;
    }
    i++;
  }
}