code บอตตอบจากชีต

code.gs

var LINE_ACCESS_TOKEN =
"xxx";

var ss = SpreadsheetApp.openByUrl(
"xxx"
);

var sh = ss.getSheetByName("แผ่น1");

function doPost(e) {
if (typeof e === "undefined") {
return;
} else {
var json = JSON.parse(e.postData.contents);

replyFromSheet(json);
}
}

function replyFromSheet(data) {
var replyUrl = "https://api.line.me/v2/bot/message/reply";

var lastRow = sh.getLastRow();

var wordList = sh.getRange(1, 1, lastRow, 2).getDisplayValues();
var number = Math.floor(Math.random() * (5 - 2 + 1)) + 2;
var NoList = sh.getRange(number, 3).getValues();
var reply_token = data.events[0].replyToken;
var text = data.events[0].message.text;

var replyTextList = [];

for (var i = 1; i < wordList.length; i++) {
if (wordList[i][0] == text) {
var already = true;
replyTextList.push(wordList[i][1]);
}
}
if (!already) {
replyTextList = NoList;
}

if (replyTextList.length < 1) {
return;
} else if (replyTextList.length > 5) {
var messageLength = 5;
} else {
var messageLength = replyTextList.length;
}
var messageArray = [];
for (var j = 0; j < messageLength; j++) {
messageArray.push({

  "type": "text",
  "text": "" + replyTextList[j],
  
  "quickReply": {
    "items": [
     ///--start
      {
        "type": "action",
        "action": {
          "type": "uri",
          "label": "อธิบายแจ้งเกรด",
          "uri": "https://www.youtube.com/watch?v=DkNybvuWrQM"
      }},
      {
        "type": "action",
        "action": {
          "type": "message",
          "label": "13",
          "text": "14"
      }},
      {
        "type": "action",
        "action": {
          "type": "message",
          "label": "14",
          "text": "14"
      }},
      {
        "type": "action",
        "action": {
          "type": "message",
          "label": "15",
          "text": "15"
      }},
      {
        "type": "action",
        "action": {
          "type": "message",
          "label": "16",
          "text": "16"
      }},
      {
        "type": "action",
        "action": {
          "type": "message",
          "label": "17",
          "text": "17"
      }},
      {
        "type": "action",
        "action": {
          "type": "message",
          "label": "18",
          "text": "18"
      }},
      {
        "type": "action",
        "action": {
          "type": "message",
          "label": "19",
          "text": "19"
      }},
      {
        "type": "action",
        "action": {
          "type": "message",
          "label": "20",
          "text": "20"
      }},
      {
        "type": "action",
        "action": {
          "type": "message",
          "label": "21",
          "text": "21"
      }},
      {
        "type": "action",
        "action": {
          "type": "message",
          "label": "24-28",
          "text": "24-28"
      }},
      {
        "type": "action",
        "action": {
          "type": "message",
          "label": "1-4 มิ.ย",
          "text": "1-4 มิ.ย"
        }
      },
      {
        "type": "action",
        "action": {
          "type": "message",
          "label": "7 มิ.ย",
          "text": "7 มิ.ย"
      }}
               
  ] 
      }
 ///--end quick reply       

});
  
}


var headers = {
"Content-Type": "application/json; charset=UTF-8",
Authorization: "Bearer " + LINE_ACCESS_TOKEN
};

var postData = {
replyToken: reply_token,
messages: messageArray
};

var options = {
method: "post",
headers: headers,
payload: JSON.stringify(postData)
};

UrlFetchApp.fetch(replyUrl, options);
}