Linebotの返信結果によって、スプレットシートを色分けしたい

Question

ユーザーからlinebotに送られてきた単語をGoogleスプレッドシート のA列目から探し、一致する単語があった場合B列の内容を返すBotを作成しています。
その際、ユーザーから送られてきた単語を「toupdate」というシートにappendrowで書き込んでいます。

ユーザーから送られてきた単語がA列にあった場合、「toupdate」シートに書き込まれる単語のシートを青、単語がなかった場合赤にしたいのですが、現状できません。。

どうすればいいか教えていただけると助かります。
よろしくお願い致します

// 利用しているシート
var SHEET_ID = '1xc7II9n4B9Ag0cDIDkSpfCGF2AGXcYfOI2SzhV0tNGE';
// 利用しているSSのシート名（※変えるとみえなくなる）
var SHEET_NAME = 'faq';
// 利用しているもしかしてSSのシート名（※変えるとみえなくなる）
var SHEET_NAME_MAYBE = 'maybe';

var SHEET_UPDATE = 'toupdate';


// LINE Message API アクセストークン
var ACCESS_TOKEN = 'UNGO6V0ZGwxr8fMlnPwWfGW7ib+pt92V6ylh8zfc06XVJ0lVtGHyvpGO+tieHf/PPMuHT0SmmWH2I3Vml221UwV/+GIoEuO5D22bZCCXukXXzlHwP5MPMcK/ARyIAAnN6Xtr8lDBMru4BnrDb50WdwdB04t89/1O/w1cDnyilFU=';
// 通知URL
var PUSH = "https://api.line.me/v2/bot/message/push";
// リプライ時URL
var REPLY = "https://api.line.me/v2/bot/message/reply";
// プロフィール取得URL
var PROFILE = "https://api.line.me/v2/profile";

/**
 * doPOST
 * POSTリクエストのハンドリング
 */
function doPost(e) {
  var json = JSON.parse(e.postData.contents);
  reply(json);
}

/** 
 * doGet
 * GETリクエストのハンドリング
 */
function doGet(e) {
    return ContentService.createTextOutput("SUCCESS");
}

/** 
 * reply
 * ユーザからのアクションに返信する
 */
function reply(data) {
  // POST情報から必要データを抽出
  var lineUserId = data.events[0].source.userId;
  var postMsg    = data.events[0].message.text;
  var replyToken = data.events[0].replyToken;
  var action    = data.events[0].message.action;
  // 記録用に検索語とuserIdを記録
  debug(postMsg, lineUserId);
  //debug(action, lineUserId);

  // 検索語に対しての回答をSSから取得
  var answers = findResponseArray(postMsg);

  // 回答メッセージを作成
  var replyText = '「' + postMsg + '」ですね。かしこまりました。以下、回答です。';
  // 回答の有無に応じて分岐
  if (answers.length === 0) {
    // 「類似の検索キーワード」がないかチェック
    var mayBeWord = findMaybe(postMsg);
    if (typeof mayBeWord === "undefined") {
      // 回答がない場合の定型文
      sendMessage(replyToken, '答えが見つかりませんでした。別のキーワードで質問してみてください。');        
    } else {
      sendMayBe(replyToken, mayBeWord);
    }
  } else {
    // 回答がある場合のメッセージ生成
    answers.forEach(function(answer) {
      replyText = replyText + "\n\n＝＝＝＝＝＝＝＝＝＝＝＝＝\n\nQ：" + answer.key + "\n\nA：" + answer.value;
    });

    // 1000文字を超える場合は途中で切る
    if (replyText.length > 1000) {
      replyText = replyText.slice(0,1000) + "……\n\n＝＝＝＝＝＝＝＝＝＝＝＝＝\n\n回答文字数オーバーです。詳細に検索キーワードを絞ってください。";
    }
    // メッセージAPI送信
    sendMessage(replyToken, replyText);
  }
}

// SSからデータを取得
function getData() {
  var sheet = SpreadsheetApp.openById(SHEET_ID).getSheetByName(SHEET_NAME);
  var data = sheet.getDataRange().getValues();

  return data.map(function(row) { return {key: row[0], value: row[1], type: row[2]}; });
}

// SSから「もしかして」データを取得
function getMayBeData() {
  var sheet = SpreadsheetApp.openById(SHEET_ID).getSheetByName(SHEET_NAME_MAYBE);
  var data = sheet.getDataRange().getValues();
  return data.map(function(row) { return {key: row[0], value: row[1], type: row[2]}; });
}

// 単語が一致したセルの回答を配列で返す
function findResponseArray(word) {
  // スペース検索用のスペースを半角に統一
  word = word.replace('　',' ');
  // 単語ごとに配列に分割
  var wordArray = word.split(' ');
  return getData().reduce(function(memo, row) {
    // 値が入っているか
    if (row.value) {
      // AND検索ですべての単語を含んでいるか
      var matchCnt = 0;
      wordArray.forEach(function(wordUnit) {
        // 単語を含んでいればtrue
        if (row.key.indexOf(wordUnit) > -1) {
          matchCnt = matchCnt + 1;
        }
      });
      if (wordArray.length === matchCnt) {
        memo.push(row);
      }
    }
    return memo;
  }, []) || [];
}

// 単語が一致したセルの回答を「もしかして」を返す
function findMaybe(word) {
  return getMayBeData().reduce(function(memo, row) { return memo || (row.key === word && row.value); }, false) || undefined;
}

// 画像形式でAPI送信
function sendMessageImage(replyToken, imageUrl) {
  // replyするメッセージの定義
  var postData = {
    "replyToken" : replyToken,
    "messages" : [
      {
        "type": "image",
        "originalContentUrl": imageUrl
      }
    ]
  };
  return postMessage(postData);
}

// LINE messaging apiにJSON形式でデータをPOST
function sendMessage(replyToken, replyText) {  
  // replyするメッセージの定義
  var postData = {
    "replyToken" : replyToken,
    "messages" : [
      {
        "type" : "text",
        "text" : replyText
      }
    ]
  };
  return postMessage(postData);
}

// LINE messaging apiにJSON形式で確認をPOST
function sendMayBe(replyToken, mayBeWord) {  
  // replyするメッセージの定義
  var postData = {
    "replyToken" : replyToken,
    "messages" : [
      {
        "type" : "template",
        "altText" : "もしかして検索キーワードは「" + mayBeWord + "」ですか？",
        "template": {
          "type": "confirm",
          "actions": [
            {
                "type":"postback",
                "label":"はい",
                "data":"action=detail",
            },
            {
                "type": "message",
                "label": "いいえ",
                "text": "いいえ、違います。"
            }
          ],
          "text": "答えが見つかりませんでした。もしかして検索キーワードは「" + mayBeWord + "」ですか？"
        }

      }
    ]
  };
  return postMessage(postData);
}

// LINE messaging apiにJSON形式でデータをPOST
function postMessage(postData) {  
  // リクエストヘッダ
  var headers = {
    "Content-Type" : "application/json; charset=UTF-8",
    "Authorization" : "Bearer " + ACCESS_TOKEN
  };
  // POSTオプション作成
  var options = {
    "method" : "POST",
    "headers" : headers,
    "payload" : JSON.stringify(postData)
  };
  return UrlFetchApp.fetch(REPLY, options);      
}

/** ユーザーのアカウント名を取得
 */
function getUserDisplayName(userId) {
  var url = 'https://api.line.me/v2/bot/profile/' + userId;
  var userProfile = UrlFetchApp.fetch(url,{
    'headers': {
      'Authorization' :  'Bearer ' + ACCESS_TOKEN,
    },
  })
  return JSON.parse(userProfile).displayName;
}

// userIdシートに記載
function lineUserId(userId) {
  var sheet = SpreadsheetApp.openById(SHEET_ID).getSheetByName('userId');
  sheet.appendRow([userId]);
}

// debugシートに値を記載
function debug(text, userId) {
  var sheet1 = SpreadsheetApp.openById(SHEET_ID).getSheetByName('debug');
  var sheet2 = SpreadsheetApp.openById(SHEET_ID).getSheetByName('toupdate');
  var date = new Date();
  var userName = getUserDisplayName(userId);
  sheet1.appendRow([text, Utilities.formatDate( date, 'Asia/Tokyo', 'yyyy-MM-dd HH:mm:ss')]);
  sheet2.appendRow([text]);
 
  var sh2lastrow = sheet2.getLastRow();  // 追加した行の行番号を取得
  var sh2lastcolumn = sheet2.getLastColumn();  // 一番右端の列番号を取得
  // 追加した行のrangeを取得。
  var changeRange = sheet2.getRange(sh2lastrow, 1, 1, sh2lastcolumn);
  // 背景色を変える。
  reply(data);
  if (answers.length === 0) {
    // 「類似の検索キーワード」がないかチェック
    var mayBeWord = findMaybe(postMsg);
    if (typeof mayBeWord === "undefined") {
      // 回答がない場合の定型文
    changeRange.setBackground("blue");        
    } else { 
    changeRange.setBackground("red");
    }
  }

  

Answer 1

debug()関数の中で定義されていない変数（answers）が使われています。
質問文に記載の要件とプログラム全体から推測するに、findResponseArray()関数の返値をanswerに設定すればよいと思われます。

// debugシートに値を記載
function debug(text, userId) {
  var sheet1 = SpreadsheetApp.openById(SHEET_ID).getSheetByName('debug');
  var sheet2 = SpreadsheetApp.openById(SHEET_ID).getSheetByName('toupdate');
  var date = new Date();
  var userName = getUserDisplayName(userId);
  sheet1.appendRow([text, Utilities.formatDate( date, 'Asia/Tokyo', 'yyyy-MM-dd HH:mm:ss')]);
  sheet2.appendRow([text]);

  var sh2lastrow = sheet2.getLastRow();  // 追加した行の行番号を取得
  var sh2lastcolumn = sheet2.getLastColumn();  // 一番右端の列番号を取得
  // 追加した行のrangeを取得。
  var changeRange = sheet2.getRange(sh2lastrow, 1, 1, sh2lastcolumn);
  // 背景色を変える。
- reply(data);　　<---これは不要なので削除。
+ //answers定義追加
+ var answers = findResponseArray(text);
  if (answers.length === 0) {
    // 「類似の検索キーワード」がないかチェック
-   var mayBeWord = findMaybe(postMsg); <---postMsgは定義されていない。ここはtextが正しい。
+   var mayBeWord = findMaybe(text);
    if (typeof mayBeWord === "undefined") {
      // 回答がない場合の定型文
    changeRange.setBackground("blue");        
    } else { 
    changeRange.setBackground("red");
    }
  }
}

Comments

[naoko_]

解決しました！ありがとうございました！
無事、回答できなかった場合のセルの色を青にできました。
しかし回答ができている場合セルがif elseで赤になるですが、背景色に色がつきません（文字は入力されるのですが）
これってなんででしょうか。。

[退会済みユーザー]

なぜ回答ができている場合セルに色がつかないか：それは、「質問文に記載されている元々のコードが、そういう処理をするようになっているから」です。

というのは回答になっていないので、下記に詳しく説明します。

まずdebug()関数内では、LINEから投稿されてきた単語を、findResponseArray()関数に通した後
toupdateシートの末尾に一旦記録します。

この時点では、toupdateシートの末尾に記録された単語は、faqシートやmaybeシートに該当するものがあろうがなかろうが、背景色はついていません。

そしてdebug関数の後半
「 if (answers.length === 0) {
// 「類似の検索キーワード」がないかチェック
...」
以降の部分で以下のような処理をしています。

・answers.length === 0 、すなわち、「lineから送られてきた単語が、faqシートのA列のどの単語にも該当しない場合」
→findMaybe関数を使って、maybeシートに該当する単語がないかさらに調べる
→→存在する場合は、背景色を赤にする。
→→存在しない場合は、背景色を青にする。

したがって、「回答ができている場合」（すなわち「lineから送られてきた単語がfaqシートの中に存在する場合」）は
そもそもanswers.length === 0 にならないので 背景色を付ける処理は実行されません。

toupdateの単語に背景色がつくのは、「faqシートには存在しないがmaybeシートに存在する」または「faqシートにもmaybeシートにも存在しない」場合だけです。

debug関数及びtoupdateシートの目的は、「ユーザーが似たような単語を送ってきたときに補足する役割を果たすmaybeシート」を拡充させることであると思われますので
上記の動作で問題ないと思います。