gas-google-home.gs

function getSheet(name) {
  return SpreadsheetApp.getActiveSpreadsheet().getSheetByName(name);
}

function getEndRow(sheet, col) {
  var endRow = 0;
  var lastRow = sheet.getLastRow()
  for(var i = 1; i <= lastRow; i++) {
    if(sheet.getRange(i, col).getValue() === "") break;
    endRow++;
  }
  return endRow; 
}

function notifyGoogleHome(text) {
  var url = getSheet('main').getRange(1, 5).getValue();
  var res = UrlFetchApp.fetch(url + "?text=" + text);
  Logger.log(res.getContentText());
}

function exec() {
  var mainSheet = getSheet('main');
  var ruijiSheet = getSheet('ruiji_sample');
  var words = mainSheet.getRange(getEndRow(mainSheet, 2), 2).getValue().replace("おいしい", "美味しい").split(' ');
  var maxScore = -100;
  var maxScoreIndices = [];
  var endRow = getEndRow(ruijiSheet, 1)
  for(var i = 4; i <= endRow; i++) {
    var matchWord = ruijiSheet.getRange(i, 12).getValue();
    var score = -matchWord.split(' ').length;
    for(var j = 0; j < words.length; j++) {
      if(words[j].length <= 1) continue;
      if(matchWord.indexOf(words[j]) != -1) {
        score += 2;
      } 
    }
    if(score > maxScore) {
      maxScore = score;
      maxScoreIndices = [i];
    } else if(score === maxScore) {
      maxScoreIndices.push(i);
    }
  }
  notifyGoogleHome(ruijiSheet.getRange(maxScoreIndices[Math.floor(Math.random() * maxScoreIndices.length)], 10).getValue());
}