[Linebot]-Google Forms+Google Sheet+Google Apps Script

ITTIRIT T.

4 min readOct 13, 2019

[Linebot]-Google Forms+Google Sheet+Google Apps Script

สร้าง form

Google Forms - create and analyze surveys, for free.

Create a new survey on your own or with others at the same time. Choose from a variety of survey types and analyze…

docs.google.com

เมื่อกดสร้างจะได้ sheet ใหม่

ลองบันทึกข้อมูล 3–5 ตัวอย่างเพื่อใช้ค้นหา

2.สร้าง webhook ด้วย Apps script

var channelToken = “dltp6XXXXXXyilFU=”; //แก้ไข channelToken
var ss = SpreadsheetApp.openByUrl(“https://docs.gooXXXXXXFI/edit");
function replyMsg(replyToken, Msg, channelToken) {
var url = ‘https://api.line.me/v2/bot/message/reply';
var opt = {
‘headers’: {
‘Content-Type’: ‘application/json; charset=UTF-8’,
‘Authorization’: ‘Bearer ‘ + channelToken,
},
‘method’: ‘post’,
‘payload’: JSON.stringify({
‘replyToken’: replyToken,
‘messages’: Msg
})
};
UrlFetchApp.fetch(url, opt);
}

function doPost(e) {

var value = JSON.parse(e.postData.contents);
var events = value.events;
var event = events[0];
var type = event.type;
var replyToken = event.replyToken;
var sourceType = event.source.type;
var userId = event.source.userId;
var groupId = event.source.groupId;
var timeStamp = event.timestamp;
var url = “https://api.line.me/v2/bot/profile/”+userId;
var headers = {
“contentType": “application/json”,
“headers”:{“Authorization”: “Bearer “+channelToken}
};
var getprofile = UrlFetchApp.fetch(url, headers);
var profiledata = JSON.parse(getprofile.getContentText());
var displayName = profiledata.displayName;
var statusMessage = profiledata.statusMessage;
var pictureUrl = profiledata.pictureUrl;

switch (type) {
case ‘postback’:
break;
case ‘message’:
var messageType = event.message.type;
var messageId = event.message.id;
var messageText = event.message.text;
var sheet = ss.getSheetByName(“การตอบแบบฟอร์ม 1”);
var word = sheet.getRange(2, 2, sheet.getLastRow(),sheet.getLastColumn()).getValues(); //
for(var i = 0;i<word.length; i++){
if(word[i][0] == messageText ){
var founded = true;
i=i+2;
var Data1 = sheet.getRange(i,2).getValue();
var Data2 = sheet.getRange(i,3).getValue();
var Data3 = sheet.getRange(i,4).getValue();
var Data4 = sheet.getRange(i,5).getValue();
var mess = [{‘type’: ‘text’, ‘text’: “ชื่อเล่น : “+Data1+”\nอายุ : “+Data2+”\nวดป.เกิด : “+Data3+”\nรูปภาพ : “+Data4}];
}
}
if(founded){
replyMsg(replyToken, mess, channelToken);
}
break;
default:
break;
}
}

นำ code ไปวางและ deploy

https://gist.github.com/itchampclub/780f28cd9c9dd03c4b1c124cf256f159

var channelToken = “dltp6rZJvyJ7fC9XXXXXXXXXXXXXO/w1cDnyilFU=”;
//แก้ไข channelToken (จาก line console)
var ss = SpreadsheetApp.openByUrl(“ใส่ sheet url”); // ใส่ url sheet

var sheet = ss.getSheetByName(“การตอบแบบฟอร์ม 1”); // ใส่ sheet name