appsscript.json

{
  "timeZone": "Asia/Hong_Kong",
  "dependencies": {
  },
  "webapp": {
    "access": "ANYONE_ANONYMOUS",
    "executeAs": "USER_DEPLOYING"
  },
  "exceptionLogging": "STACKDRIVER",
  "oauthScopes": ["https://www.googleapis.com/auth/script.send_mail", "https://www.googleapis.com/auth/script.external_request", "https://www.googleapis.com/auth/script.scriptapp", "https://www.googleapis.com/auth/spreadsheets", "https://www.googleapis.com/auth/userinfo.email"],
  "executionApi": {
    "access": "DOMAIN"
  }
}

main.gs

function getYourSheetID() {
 var sheetID = {
   spreadsheetID: "",  //"{輸入你的GoogleSheetID}",
 };
 return sheetID;
}

// Initialize
function initialize(e) {
  var spreadsheet = SpreadsheetApp.openById(getYourSheetID().spreadsheetID);
   ScriptApp.newTrigger("setTime")
     .forSpreadsheet(spreadsheet)
     .onChange()
     .create();
}

function returnDataInJson(sheetID) {
 var ss = SpreadsheetApp.openById(sheetID);
 SpreadsheetApp.setActiveSpreadsheet(ss);
 var sheets = ss.getSheets();
  var mainData = [];
 for (var i = 1; i < sheets.length; i++) {
   SpreadsheetApp.setActiveSheet(sheets[i]);
   importSheet(sheets[i],mainData);
 }
  var dataFormat = JSON.stringify(mainData);
  return ContentService.createTextOutput(dataFormat).setMimeType(ContentService.MimeType.JSON);
}


function assign(obj, keyPath, value) {
 if (!(keyPath in obj)) obj[keyPath] = {};
 obj[keyPath] = value;
}

//data[行][欄]
function importSheet(s,mainData){
  var sheet = SpreadsheetApp.getActiveSheet();
  var name = sheet.getName();
  var data = sheet.getDataRange().getValues();
  
  var dataToImport = {};
  dataToImport[data[3][0]] = {};
  dataToImport[data[7][0]] = {};
  dataToImport[data[12][0]] = [];
  //(行,欄): (0,0...5) 藍區活動表格
  //assign(dataToImport,) 
  for (var i = 0; i < data[0].length; i++){
    assign(dataToImport, data[0][i],data[1][i]);
  }
    
   for (var j = 0; j < data[4].length; j++){
     var value = data[5][j].toString();
    assign( dataToImport[data[3][0]], data[4][j],value);
  }
  
  //橘區活動表格 
   for (var i = 0; i < data[8].length; i++){
     var value = data[9][i].toString();
    assign(dataToImport[data[7][0]], data[8][i],value);
  }
  
  if (data.length > 14) {
    var arr = [];
   for (var i = 14; i < data.length; i++){
      var obj = {};
     for (var j = 0; j < data[13].length; j++){ 
       var value = data[i][j];
       if (value != true && value != false && j != 0){
         value = data[i][j].toString();
       }
      assign(obj,data[13][j],value)
     }
     arr.push(obj);
    } 
    dataToImport[data[12][0]] = arr;
  }
  
  mainData.push(dataToImport);
}
  
function newSheetLast() {
  var environment = getYourSheetID();
  var id = environment.spreadsheetID ; // 前一段取得的 Sheet id
  var spreadsheet = SpreadsheetApp.openById(id);
  var templateSheet = spreadsheet.getSheetByName("Default");
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var i = ss.getSheets().length;
  var sheetName = i - 1
  ss.insertSheet(sheetName, ss.getSheets().length, {template: templateSheet});
  var sheets = ss.getSheets();
  var sheet = sheets[i];
  return sheet
}

function updateOldSheet(index){
  var environment = getYourSheetID();
  var id = environment.spreadsheetID ; // 前一段取得的 Sheet id
  var spreadsheet = SpreadsheetApp.openById(id);
  SpreadsheetApp.setActiveSpreadsheet(spreadsheet);
  var sheets = spreadsheet.getSheets();
  var realIndex = index + 1;
  var sheet = sheets[realIndex];
  return sheet
}

function doPost(request){ //添加新活動
  var params = request.parameter;
  var type = params.type;
  console.log(type);
  
  if (type == "updateAll") {
    var result = updateAllSheets(request);
    if (result) {
      setTime();
      return ContentService.createTextOutput(true);
    } else {
      return ContentService.createTextOutput(false);
    }
  }
  
  var postJson = request.postData.getDataAsString(); 
  var jsonObj = JSON.parse(postJson); 
  var contractee = jsonObj.contractee;
  var contact = jsonObj.contact;
  var personLists = jsonObj.participantList;
  var sheet = {};
  
  if (type == "email"){
    sendMail(jsonObj);
    return ContentService.createTextOutput(true);
  }
  
  
  
  if (type == "update"){
    var indexSt = params.index;
    var index = parseInt(indexSt);
      sheet = updateOldSheet(index); 
  } else if (type == "createNew") {
    sheet = newSheetLast();
  }
  
  
  
  configureEvent(sheet,jsonObj);
  confiureContractee(sheet,contractee);
  confiureContact(sheet,contact);
  confiurePerson(sheet,personLists);
  
  console.log(jsonObj.count);
  console.log(jsonObj.result);
  reviseIndex(index,15)
  setTime();
  return ContentService.createTextOutput(true);
}


function configureEvent(sheet,event){
  sheet.getRange(2, 1).setValue(event.eventName);
  sheet.getRange(2, 2).setValue(event.photoURL);
  sheet.getRange(2, 3).setValue(event.webSite);
  sheet.getRange(2, 4).setValue(event.eventLocation);
  sheet.getRange(2, 5).setValue(event.description);
  sheet.getRange(2, 6).setValue(event.beginTime);
  sheet.getRange(2, 7).setValue(event.endTime);
  sheet.getRange(2, 8).setValue(event.creatAt);
}

function confiureContractee(sheet,contractee){
  sheet.getRange(6, 1).setValue(contractee.name);
  sheet.getRange(6, 2).setValue(contractee.taxIDNumber);
  sheet.getRange(6, 3).setValue(contractee.phone);
  sheet.getRange(6, 4).setValue(contractee.address);
  sheet.getRange(6, 5).setValue(contractee.comment);
}

function confiureContact(sheet,contact){
  sheet.getRange(10, 1).setValue(contact.name);
  sheet.getRange(10, 2).setValue(contact.phone);
  sheet.getRange(10, 3).setValue(contact.email);
  sheet.getRange(10, 4).setValue(contact.comment);
}

function confiurePerson(sheet,persons){
  for (var i = 1; i <= persons.length; i++){
  var a = 14 + i;
  var index = i - 1;
  sheet.getRange(a, 1).setValue(persons[index].index);
  sheet.getRange(a, 2).setValue(persons[index].name);
  sheet.getRange(a, 3).setValue(persons[index].gender);
  sheet.getRange(a, 4).setValue(persons[index].section);
  sheet.getRange(a, 5).setValue(persons[index].phone);
  sheet.getRange(a, 6).setValue(persons[index].email);
  sheet.getRange(a, 7).setValue(persons[index].checkID);
  sheet.getRange(a, 8).setValue(persons[index].checkIn);
  }
}

function updateAllSheets(request){
  var environment = getYourSheetID();
  var id = environment.spreadsheetID ; 
  var spreadsheet = SpreadsheetApp.openById(id);
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var a = ss.getSheets().length;
  var sheets = ss.getSheets();
  for (i = 1; i < a; i ++ ){ //deleteAll
    var realIndex = a - i;
    var sheet =  sheets[realIndex];
    ss.deleteSheet(sheet);
  }
  
  var postJson = request.postData.getDataAsString(); 
  var jsonObjs = JSON.parse(postJson); 
  var max = jsonObjs.length;
  var index = 1;
  while (index <= max){
    var s = index - 1
    var sheet = newSheetLast();
    var jsonObj = jsonObjs[s];
    var contractee = jsonObj.contractee;
    var contact = jsonObj.contact;
    var participantList = jsonObj.participantList
    configureEvent(sheet,jsonObj);
    confiureContractee(sheet,contractee);
    confiureContact(sheet,contact);
    confiurePerson(sheet,participantList);
    index = index + 1;
  }
  return true
}





function doGet(request) { 
  var params = request.parameter;
  var type = params.type;
  var range = params.range;           
  var sheetInSt = params.sheetIndex;
  var sheetIn = parseInt(sheetInSt);
  console.log(type);
  if (type == "time") {
    return importTime();
  }
  
  if (type == "get") {
   return returnDataInJson(getYourSheetID().spreadsheetID);
  }
  if (type == "delete") { 
      if (range == "person"){
        var personIndexSt = params.personIndex;
        var personIn = parseInt(personIndexSt);
        var del = personDelete(sheetIn,personIn);
            if (del) { 
              setTime();
              return ContentService.createTextOutput(true) ; } }
  else if (range == "event") {
    var del = eventDelete(sheetIn);
    if (del) { 
      setTime();
      return ContentService.createTextOutput(true); } }
}
  
  return ContentService.createTextOutput(false);
}



function eventDelete(index){
  var environment = getYourSheetID();
  var id = environment.spreadsheetID ; // 前一段取得的 Sheet id
  var spreadsheet = SpreadsheetApp.openById(id);
  SpreadsheetApp.setActiveSpreadsheet(spreadsheet);
  var sheets = spreadsheet.getSheets();
  var realindex = index + 1;
  var sheet = sheets[realindex];
  spreadsheet.deleteSheet(sheet);
  return true
}

function personDelete(sheetIn,personIn){
  var environment = getYourSheetID();
  var id = environment.spreadsheetID ; 
  var spreadsheet = SpreadsheetApp.openById(id);
  SpreadsheetApp.setActiveSpreadsheet(spreadsheet);
  var sheets = spreadsheet.getSheets();
  var realSheetIn = sheetIn + 1;
  var realPersonIn = personIn + 15;
  var sheet = sheets[realSheetIn];
  sheet.deleteRow(realPersonIn); 
  
  reviseIndex(sheetIn,realPersonIn); 
  setTime();
  return true 
}

function reviseIndex(sheetIn,realPersonIn){
  var environment = getYourSheetID();
  var id = environment.spreadsheetID ; // 前一段取得的 Sheet id
  var spreadsheet = SpreadsheetApp.openById(id);
  SpreadsheetApp.setActiveSpreadsheet(spreadsheet);
  var sheets = spreadsheet.getSheets();
  var realSheetIn = sheetIn + 1;
  var sheet = sheets[realSheetIn];
  var lastRow = sheet.getLastRow();
  
  for (var i = realPersonIn; i <= lastRow; i++){
    var a = i - 15;
    sheet.getRange(i, 1).setValue(a);
  }
}


function importTime(){ 
  var environment = getYourSheetID();
  var id = environment.spreadsheetID ;
  var spreadsheet = SpreadsheetApp.openById(id);
  SpreadsheetApp.setActiveSpreadsheet(spreadsheet);
  var sheet = spreadsheet.getSheetByName("Default");
  var data = sheet.getDataRange().getValues();
  
  var updateTime = {};
  updateTime["time"] = {};
  updateTime["time"] = data[1][12] ;
  var dataFormat = JSON.stringify(updateTime);
  return ContentService.createTextOutput(dataFormat).setMimeType(ContentService.MimeType.JSON);
}

function setTime(){
 var environment = getYourSheetID();
  var id = environment.spreadsheetID ;
  var spreadsheet = SpreadsheetApp.openById(id);
  SpreadsheetApp.setActiveSpreadsheet(spreadsheet);
  var sheet = spreadsheet.getSheetByName("Default");
  var now = new Date();
  var timeZone = Session.getScriptTimeZone();
  var noonString = Utilities.formatDate(now, timeZone,"yyyy-MM-dd hh:mm:ss");
  
  var time = noonString;
  sheet.getRange(2, 13).setValue(time);
}

function sendMail(data){
  var email = data.email;
  var body = data.stringBody;
  var subject = data.subject;
  var name = data.personName;
  var photoString = data.qrCode;
  var photoDecode = Utilities.base64Decode(photoString, Utilities.Charset.UTF_8)
  var photoBlob = Utilities.newBlob(photoDecode, "image/jpeg", "QRCode.jpg");
  
  MailApp.sendEmail({
    to:email,
    subject:subject,
    htmlBody: name +"您好,"+ "<br><br>" + body + "<br><br><br>" + "<img src='cid:qRCode'> <br>",
    inlineImages:
      {
        qRCode: photoBlob
      }
  });
}