Google表單 + Line通知 (10802-讓雲端工具提昇工作效率)

tags: 整合運用

---

Google表單

1. 輕鬆建立以所有人為對象的問卷調查和表單
2. 建立自訂的問卷表單不需要額外支付任何費用。受訪者的答案會自動填入 Google 試算表，讓您立即就能分析資料。
3. 企業帳號，可以限定同網域帳號才能填表單
4. 自動回覆，填報內容，請開「設定/一般/收集電子郵件地址」

Line通知

1. Line Notify

2. 只要與其他網路服務完成連動設定，您即可透過「LINE Notify」官方帳號接收該網路服務的通知訊息。

3. 請先登入「Line Notify」

4. 至「個人頁面」
   

5. 畫面捲至最下方
   

6. 發行權杖，只會出現一次，請自行記住，方便日後使用

Google表單+Line通知

1. 請先建立表單

2. 設定「收集電子郵件地址」

3. 民宿管理範例

4. 表單填寫

5. 權限設定
   

6. 指令碼編輯器
   

檔名：專案檔名建議與表單名稱一樣
時區：檔案/專案屬性/時區 => GMT +08:00 台北 (擁有者才能修改)

function getFormResponse() {  
  //由表單 Id 開啟表單
  var form = FormApp.openById("XXXXXX-Google表單ID");
  
  //取得表單名稱
  var formTitle = form.getTitle();
  
  //取得表單回覆內容
  var formResponse = form.getResponses();   
  
  //處理表單上問答內容
  for(var i = 0; i < formResponse.length ; i++){
    
    //取得表單上的項目
    var itemResponses = formResponse[i].getItemResponses();
    
    //取得表單上「收集電子郵件地址」
    var itemRespondentEmail = formResponse[i].getRespondentEmail();
    //Logger.log(itemRespondentEmail);
    
    //傳送訊息設定
    var itemContext = formTitle + "\n 填報者電子郵件 : " + itemRespondentEmail + "\n";
        
    //將表單每一項問答組成訊息
    for(var j = 0; j < itemResponses.length ; j++)
    {       
      //取得問題標題
      itemContext += itemResponses[j].getItem().getTitle();    
      
      //取得問題回覆內容
      itemContext += " : " + itemResponses[j].getResponse() + "\n";
    }    
    
    //取得填寫表單時間
    itemContext += "\n填寫時間 : " + formResponse[i].getTimestamp() + "\n\n";
    
    //送出表單到Line
    sendToLine(itemContext);
  }
  
  //刪除回應問題
  form.deleteAllResponses();
}

function sendToLine(message){
  //填入Line Notify 權杖 繳費通知單
  var token = "XXXXXX-LineNotify權杖";
  
  var options =
  {
      method  : "post",
      payload : "message=" + message,
      headers : {"Authorization" : "Bearer "+ token},
      muteHttpExceptions : true
  };  

  UrlFetchApp.fetch("https://notify-api.line.me/api/notify",options);
}

6. 更換Google表單ID
7. 更換Line Notify 權杖
8. 若Line Notify 權杖，指定群組非個人，請把「LineNotify」加入該群組
9. 設定「現有專案的啟動程序」
   

10.權限設定

10. 儲存，測試
    試算表結果

備註：

從表單的響應存儲中刪除所有提交的響應。此方法不會刪除存儲在外部響應目標（如電子表格）中的響應副本，但會清除表單的摘要視圖

Google App Script (寫入日曆)

1. 日曆函數

createAllDayEvent(title, startDate, endDate, options)

日曆整日事件(標題,開始日期,結束日期,參數)

名稱	類型	描述
title	String	活動的標題
startDate	Date	事件開始的日期（僅使用日期；時間被忽略）
endDate	Date	事件結束的日期（僅使用日期；時間被忽略）
options	Object	指定高級參數的JavaScript對象，如下所示

進階參數

名稱	類型	描述
description	String	事件的描述
location	String	活動地點
guests	String	以逗號分隔的電子郵件地址列表，應將其添加為來賓
sendInvites	Boolean	是否發送邀請電子郵件（默認：false）

2. 完整程式碼

function getFormResponse() {  
  //由表單-Id-開啟表單
  var form = FormApp.openById("表單-Id");
  
  //取得表單名稱
  var formTitle = form.getTitle();
  
  //取得表單回覆內容
  var formResponse = form.getResponses();   
  
  //處理表單上問答內容
  for(var i = 0; i < formResponse.length ; i++){
    
    //取得表單上的項目
    var itemResponses = formResponse[i].getItemResponses();
    
    //取得表單上「收集電子郵件地址」
    var itemRespondentEmail = formResponse[i].getRespondentEmail();
    //Logger.log(itemRespondentEmail);
    
    //傳送訊息設定
    var itemContext = formTitle + "\n 填報者電子郵件 : " + itemRespondentEmail + "\n";        
      
    //----傳給日曆變數----
    var calendar = [];
    
    //將表單每一項問答組成訊息
    for(var j = 0; j < itemResponses.length ; j++)
    {  
      //----日曆----
      var v = itemResponses[j].getItem().getTitle();
      calendar[v] = itemResponses[j].getResponse();      
      //----日曆end----
      
      //取得問題標題
      itemContext += itemResponses[j].getItem().getTitle();    
      
      //取得問題回覆內容
      itemContext += " : " + itemResponses[j].getResponse() + "\n";
    }    
    
    //取得填寫表單時間
    itemContext += "\n填寫時間 : " + formResponse[i].getTimestamp() + "\n\n";
    
    //送出表單到Line
    sendToLine(itemContext);    
    
    //----寫入日曆----
    setCalendar(calendar);
    
  }
  
  //刪除回應問題
  form.deleteAllResponses();
}

function sendToLine(message){
  //填入Line-Notify-權杖
  var token = "Line-Notify-權杖";
  
  var options =
  {
      method  : "post",
      payload : "message=" + message,
      headers : {"Authorization" : "Bearer "+ token},
      muteHttpExceptions : true
  };  

  UrlFetchApp.fetch("https://notify-api.line.me/api/notify",options);
}


function setCalendar(calendar) {
  Logger.log(calendar);
  
  //日曆ID
  var calId = "日曆ID";    
  
  //設定新增的行程標題
  var title = calendar['房間編號'] + " - " + calendar['客戶姓名'];
   
  //開始日期
  var startDate = calendar['入住日期'];
  startDate = new Date(startDate.replace(/-/g, "/"));//轉換日期格式 
  
  //結束日期(必需多一天)
  var endDate = calendar['退房日期'];
  endDate = new Date(endDate.replace(/-/g, "/"));//轉換日期格式   
  //endDate=endDate.setDate(endDate.getDate()+1);//假如沒有結束日期
  endDate=new Date(endDate);
  
  var description = "房間編號: " + calendar['房間編號'] + "\n";
  description += "客戶姓名: " + calendar['客戶姓名'] + "\n";  
  description += "客戶電話: " + calendar['客戶電話'] + "\n";    
  description += "停車券: " + calendar['停車券'] + "\n";   
  description += "訂金: " + calendar['訂金'] + "\n";
  description += "尾款: " + calendar['尾款'] + "\n";
  description += "經手人員: " + calendar['經手人員'] + "\n";
  
  //顏色
  var color = 11;
  if(calendar['房間編號'] == "601"){
    var color = 1;
  }
  if(calendar['房間編號'] == "602"){
    var color = 2;
  }
  if(calendar['房間編號'] == "603"){
    var color = 3;
  }
  
  //----------------------//
  
  //透過ID取得日曆
  var cal = CalendarApp.getCalendarById(calId);
  
  //建立新的日曆行程
  var even = cal.createAllDayEvent(title, startDate, endDate, {description:description});
  
  //沒有結束日期的全天事件
  //var even = cal.createAllDayEvent(title, startDate, {description:description});

  //設定形成顏色
  even.setColor(color);
  
  //取得形成的ID
  //Logger.log(even.getId());
}

繳費通知

表單：https://docs.google.com/forms/d/1lhOoaKltMIiowq6f-MFl2JutVYjx7goSWqjnTfcH6Bw/edit?usp=sharing

function getFormResponse() {  
  //由表單-Id-開啟表單
  var form = FormApp.openById("Google表單-Id");
  
  //取得表單名稱
  var formTitle = form.getTitle();
  
  //取得表單回覆內容
  var formResponse = form.getResponses();   
  
  //處理表單上問答內容
  for(var i = 0; i < formResponse.length ; i++){
    
    //取得表單上的項目
    var itemResponses = formResponse[i].getItemResponses();
    
    //取得表單上「收集電子郵件地址」
    var itemRespondentEmail = formResponse[i].getRespondentEmail();
    //Logger.log(itemRespondentEmail);
    
    //傳送訊息設定
    var itemContext = formTitle + "\n 填報者電子郵件 : " + itemRespondentEmail + "\n";        
      
    //----傳給日曆變數----
    var calendar = [];
    
    //將表單每一項問答組成訊息
    for(var j = 0; j < itemResponses.length ; j++)
    {  
      //----日曆----
      var v = itemResponses[j].getItem().getTitle();
      calendar[v] = itemResponses[j].getResponse();      
      //----日曆end----
      
      //取得問題標題
      itemContext += itemResponses[j].getItem().getTitle();    
      
      //取得問題回覆內容
      itemContext += " : " + itemResponses[j].getResponse() + "\n";
    }    
    
    //取得填寫表單時間
    itemContext += "\n填寫時間 : " + formResponse[i].getTimestamp() + "\n\n";
    
    //送出表單到Line
    sendToLine(itemContext);    
    
    //----寫入日曆----
    setCalendar(calendar);
    
  }
  
  //刪除回應問題
  form.deleteAllResponses();
}

function sendToLine(message){
  //填入Line-Notify-權杖
  var token = "Line-Notify-權杖";
  
  var options =
  {
      method  : "post",
      payload : "message=" + message,
      headers : {"Authorization" : "Bearer "+ token},
      muteHttpExceptions : true
  };  

  UrlFetchApp.fetch("https://notify-api.line.me/api/notify",options);
}


function setCalendar(calendar) {
  Logger.log(calendar);
  
  //日曆ID
  var calId = "Google日曆ID";    
  
  //設定新增的行程標題
  var title = calendar['繳費單位'] + calendar['其他'] + " - " + calendar['繳費金額'];
   
  //開始日期
  var startDate = calendar['繳費日期'];
  startDate = new Date(startDate.replace(/-/g, "/"));//轉換日期格式 
  
  //結束日期(必需多一天)
  var endDate = calendar['繳費日期'];
  endDate = new Date(endDate.replace(/-/g, "/"));//轉換日期格式
   
  endDate=endDate.setDate(endDate.getDate()+1);//假如沒有結束日期 
  endDate=new Date(endDate);
  
  //Logger.log(endDate);
  
  var description = "繳費單位: " + calendar['繳費單位'] + "\n";
  description += "其他: " + calendar['其他'] + "\n";  
  description += "繳費金額: " + calendar['繳費金額'] + "\n";    
  description += "備註: " + calendar['備註'] + "\n"; 
  
  //顏色
  var color = 11;
  //  if(calendar['房間編號'] == "601"){
  //    var color = 1;
  //  }
  //  if(calendar['房間編號'] == "602"){
  //    var color = 2;
  //  }
  //  if(calendar['房間編號'] == "603"){
  //    var color = 3;
  //  }
  //  
  //----------------------//
  
  //透過ID取得日曆
  var cal = CalendarApp.getCalendarById(calId);
  
  //建立新的日曆行程
  var even = cal.createAllDayEvent(title, startDate, endDate, {description:description});
  
  //沒有結束日期的全天事件
  //var even = cal.createAllDayEvent(title, startDate, {description:description});

  //設定形成顏色
  even.setColor(color);
  
  //取得形成的ID
  //Logger.log(even.getId());
}

訂便當流程

1. 請先建立一個Google表單

2. 設定「收集電子郵件地址」與「作答回條」

3. 執行「更多選項/指令碼編輯器」

function FormResponse(){
  // Open a form by ID and log the responses to each question.
  var form = FormApp.openById('表單-ID');
  var formResponses = form.getResponses();//得到所有回應
  
  //---- 所有回應
  for (var i = 0; i < formResponses.length; i++) {
    var formResponse = formResponses[i];//每一筆回應
    var itemResponses = formResponse.getItemResponses();//回應內容
    var formResponseTime = formResponse.getTimestamp();//時間戳記    
    var formRespondentEmail = formResponse.getRespondentEmail();//取得表單上「電子郵件地址」
      
    //----日曆變數----
    var calendar = {};
    calendar["時間戳記"] = formResponseTime;//表單回應時間    
    calendar['電子郵件地址'] = formRespondentEmail;//電子郵件地址
    
    //----取得單筆回應資料
    for (var j = 0; j < itemResponses.length; j++) {
      var itemResponse = itemResponses[j];
      
      var title = itemResponse.getItem().getTitle();//問題名稱
      var value = itemResponse.getResponse();//填報內容
      calendar[title] = value;

    }
    //----單筆回應資料end
    
    //----單筆要做的事情     
        
    //----寫入試算表(作帳用)
    appendRow(calendar);
    //----單筆要做的事情
    
    
  }
  
  //刪除回應問題
  form.deleteAllResponses();
}

function appendRow(calendar){
  //用id取得試算表
  var ss = SpreadsheetApp.openById('EXCEL-ID');
  //設定工作表 0 1 2 ....
  var Sheet = ss.getSheets()[1];//第1個工作表
  
  //插入第2行空白
  var row = 2;
  Sheet.insertRowBefore(row);
  //				
  
  //寫入記錄
  var NewArray = new Array();
  var NewArray = calendar['便當種類'].split("-");
  calendar['電話'] = calendar['電話'].toString();
  
  var data = [calendar['時間戳記'],calendar['電子郵件地址'],calendar['姓名'],calendar['電話'],NewArray[0],NewArray[1]];
  //var data = ["2019/11/23 上午 9:22:19","[e-mail]","俊良郭","0921560421","魚排便當-80"];
  data.forEach(function(item,index){
    Sheet.getRange(row, index+1).setValue(item);
  });
}

取得物件回圈

var obj = {};
obj["時間戳記"] = "2019/11/23 上午 8:00:40";
obj["姓名"] = "XXX";
obj["電話"] = "XXXX";

for (var k in obj){
    console.log("Key is " + k + ", value is" + obj[k]);
}

Google App Script Excel

函數	描述
SpreadsheetApp.openById('Id')	用id取得試算表
getSheetByName(sheetName)	用工作表名稱設定工作表
getSheets()[0]	用工作表序設定工作表
insertRowBefore(2)	在第2列插入空白列
getRange(2, 1)	得到第2列第1欄的儲存格
setValue(值)	把值寫入儲存格
getValues()	取得儲存格的值

取得Google Excel 並設定工作表

  var googleExcelID = "ID";
  //用id取得試算表
  var ss = SpreadsheetApp.openById(googleExcelID);
  //用工作表序 設定工作表 0 1 2 ....
  var Sheet = ss.getSheets()[0];//第1個工作表


  var googleExcelID = "ID";
  var sheetName = '工作表1'  
  //用id取得試算表
  var ss = SpreadsheetApp.openById(googleExcelID);  
  //用工作表名稱設定工作表
  var Sheet = ss.getSheetByName(sheetName);

得到儲存格 並設定其值

https://developers.google.com/apps-script/reference/spreadsheet/sheet#getrangerow,-column

  //插入第2行空白
  var row = 2;
  Sheet.insertRowBefore(row);
  //				
  
  //寫入記錄
  //data[calendar['時間戳記'],calendar['電子郵件地址'],calendar['姓名'],calendar['電話'],calendar['便當種類']];
  var data = ["2019/11/23 上午 9:22:19","[e-mail]","郭xxx","0921xxxxxx","魚排便當-80"];
  data.forEach(function(item,index){
    Sheet.getRange(row, index+1).setValue(item);
  });

將資料寫入儲存格

1. 建立一個Googel Excel

2. 第1列標題：時間戳記 電子郵件地址 姓名 電話 便當種類

3. 設定A欄為日期時間格式，設定D欄為文字格式

4. 「工具/指令碼編輯器」
   

5. 設定指令碼檔名
   

6. 設定檔案 時區

7. 將程式碼貼上，將試算表ID替換，並執行

  var googleExcelID = "ID";
  var sheetName = '工作表1'  
  //用id取得試算表
  var ss = SpreadsheetApp.openById(googleExcelID);  
  //用工作表名稱設定工作表
  var Sheet = ss.getSheetByName(sheetName);
  
  //插入第2行空白
  var row = 2;
  Sheet.insertRowBefore(row);
  //				
  
  //寫入記錄
  //data[calendar['時間戳記'],calendar['電子郵件地址'],calendar['姓名'],calendar['電話'],calendar['便當種類']];
  
  var x0 = "2019/11/23 上午 9:22:19";
  var x1 = "[e-mail]";
  var x2 = "路人甲";
  var x3 = "0921123456";
  var x4 = "魚排便當-80";
  
  Sheet.getRange(row, 1).setValue(x0);//寫入A欄
  Sheet.getRange(row, 2).setValue(x1);//寫入B欄
  Sheet.getRange(row, 3).setValue(x2);//寫入C欄
  Sheet.getRange(row, 4).setValue(x3);//寫入D欄
  Sheet.getRange(row, 5).setValue(x4);//寫入E欄

8. 核對權限
   

9. 選擇gmail
   

10. 選擇進階
    

11. 選擇「前往…」
    

12. 允許
    

13. 將變數 改用 陣列

  //  var x0 = "2019/11/23 上午 9:22:19";
  //  var x1 = "[e-mail]";
  //  var x2 = "路人甲";
  //  var x3 = "0921123456";
  //  var x4 = "魚排便當-80";
  var x = [];
  x[0] = "2019/11/23 上午 9:22:19";
  x[1] = "[e-mail]";
  x[2] = "路人甲";
  x[3] = "0921123456";
  x[4] = "魚排便當-80";
  
  Sheet.getRange(row, 1).setValue(x[0]);//寫入A欄
  Sheet.getRange(row, 2).setValue(x[1]);//寫入B欄
  Sheet.getRange(row, 3).setValue(x[2]);//寫入C欄
  Sheet.getRange(row, 4).setValue(x[3]);//寫入D欄
  Sheet.getRange(row, 5).setValue(x[4]);//寫入E欄

14. 寫入儲存格，改用迴圈

  //  var x0 = "2019/11/23 上午 9:22:19";
  //  var x1 = "[e-mail]";
  //  var x2 = "路人甲";
  //  var x3 = "0921123456";
  //  var x4 = "魚排便當-80";
  var x = [];
  x[0] = "2019/11/23 上午 9:22:19";
  x[1] = "[e-mail]";
  x[2] = "路人甲";
  x[3] = "0921123456";
  x[4] = "魚排便當-80";
  
  //x = ["2019/11/23 上午 9:22:19","[e-mail]","路人甲","0921123456","魚排便當-80"]
  
  //  Sheet.getRange(row, 1).setValue(x[0]);//寫入A欄
  //  Sheet.getRange(row, 2).setValue(x[1]);//寫入B欄
  //  Sheet.getRange(row, 3).setValue(x[2]);//寫入C欄
  //  Sheet.getRange(row, 4).setValue(x[3]);//寫入D欄
  //  Sheet.getRange(row, 5).setValue(x[4]);//寫入E欄
  
  x.forEach(function(item,index){
    Sheet.getRange(row, index+1).setValue(item);
  });

寫入Google Excel 的函數

function appendRow(calendar){
  //用id取得試算表
  var googleExcelID = "ID";
  var sheetName = '作帳用'
  
  var ss = SpreadsheetApp.openById(googleExcelID);  
  //用工作表名稱設定工作表
  var Sheet = ss.getSheetByName(sheetName);
  //設定工作表 0 1 2 ....
  //var Sheet = ss.getSheets()[0];//第1個工作表
  
  //插入第2行空白
  var row = 2;
  Sheet.insertRowBefore(row);
  //				
  
  //寫入記錄
  //data[calendar['時間戳記'],calendar['電子郵件地址'],calendar['姓名'],calendar['電話'],calendar['便當種類']];
  var data = ["2019/11/23 上午 9:22:19","[e-mail]","俊良郭","0921560421","魚排便當-80"];
  data.forEach(function(item,index){
    Sheet.getRange(row, index+1).setValue(item);
  });
}

APP程式位置

1. Google 表單
2. Google 試算表
3. Google 文件
4. Google 日曆
5. Google App Script
6. Line Notify