Google スプレッドシートで始めるアプリ運用

payBackContact.js

var userName = ''
var gmailUserName = ''
var gmailAddress = ''

function reply() {
  var sheet = SpreadsheetApp.getActiveSheet()
  var active = sheet.getActiveCell()
  if (sheet.getName() != 'contact') {
    return
  }
  if (active.getColumn() == 5 && active.getValue() == 'ok') {
    var select = Browser.msgBox(sheet.getRange(active.getRow(), 2).getValue() + ' に返信します。', 'よろしいですか？', Browser.Buttons.OK_CANCEL)
    if (select == 'cancel') {
      Browser.msgBox('送信を取り止めました。')
      active.setValue('')
      return
    }
  }
  
  if (select == 'ok') {
    GmailApp.sendEmail(
      sheet.getRange(active.getRow(), 2).getValue(),
      'お問い合わせの内容につきまして',
      sheet.getRange(active.getRow(), 4).getValue() + '\n\nお問い合わせ内容\n---------------------------------------\n' + sheet.getRange(active.getRow(), 3).getValue() + '\n---------------------------------------\n\n' + userName,
      {
        name: gmailUserName,
        from: gmailAddress
      }
    )
    active.setValue('done')
    sheet.getRange(active.getRow(), 6).setValue(Utilities.formatDate(new Date(), 'Asia/Tokyo', 'yyyy/MM/dd HH:mm:ss'))
  }
}

release.gs

var appSheetIndex = 1
var serverSheetIndex = 2
var repoName = ''
var githubToken = ''
var appRepoName = ''
var serverRepoName = ''

function release() {
  var sheet = SpreadsheetApp.getActiveSheet()
  if (!(sheet.getIndex() == appSheetIndex || sheet.getIndex() == serverSheetIndex)) {
    return
  }
  if (isApp(sheet) && sheet.getRange(sheet.getLastRow(), 6).getValue() != 'release') {
    sheet.getRange(sheet.getLastRow(), 6).setValue('temp')
    return
  }
  if (!isApp(sheet) && sheet.getRange(sheet.getLastRow(), 4).getValue() != 'release') {
    sheet.getRange(sheet.getLastRow(), 4).setValue('temp')
    return
  }
  var relaeseSelect = Browser.msgBox(sheet.getName() + 'を行います。', 'よろしいですか？', Browser.Buttons.OK_CANCEL)
  if (relaeseSelect == 'cancel') {
    Browser.msgBox('リリースを取り止めました。')
    return
  }
  if (relaeseSelect == 'ok') {
    
    if (check(sheet)) {
      Browser.msgBox('記載内容が不足しています。')
      return
    }
    
    // リリースタグを打つ
    var releasePushUrl = 'https://api.github.com/repos/' + repoName + '/' + getRepoName(sheet) + '/releases'
    var headers = {
      'Authorization': 'token ' + githubToken
    }
    var data = []
    if (isApp(sheet)) {
      data = {
        tag_name: 'v' + sheet.getRange(sheet.getLastRow(), 2).getValue(),
        target_commitish: sheet.getRange(sheet.getLastRow(), 5).getValue(),
        name: 'v' + sheet.getRange(sheet.getLastRow(), 2).getValue(),
        body: sheet.getRange(sheet.getLastRow(), 4).getValue()
      }
    } else {
      data = {
        tag_name: 'v' + sheet.getRange(sheet.getLastRow(), 1).getValue(),
        target_commitish: sheet.getRange(sheet.getLastRow(), 2).getValue(),
        name: 'v' + sheet.getRange(sheet.getLastRow(), 1).getValue(),
        body: sheet.getRange(sheet.getLastRow(), 3).getValue()
      }
    }
    var options = {
     method : 'post',
     contentType: 'application/json',
     headers : headers,
     payload : JSON.stringify(data)
    }
    var response = UrlFetchApp.fetch(releasePushUrl, options)
    if (isApp(sheet)) {
      sheet.getRange(sheet.getLastRow(), 7).setValue(response)
    } else {
      sheet.getRange(sheet.getLastRow(), 5).setValue(response)
    }
  }
}

function check(sheet) {
  if (isApp(sheet)) {
    return !sheet.getRange(sheet.getLastRow(), 1).getValue()
        || !sheet.getRange(sheet.getLastRow(), 2).getValue()
        || !sheet.getRange(sheet.getLastRow(), 3).getValue()
        || !sheet.getRange(sheet.getLastRow(), 4).getValue()
        || !sheet.getRange(sheet.getLastRow(), 5).getValue()
  } else {
    return !sheet.getRange(sheet.getLastRow(), 1).getValue()
        || !sheet.getRange(sheet.getLastRow(), 2).getValue()
        || !sheet.getRange(sheet.getLastRow(), 3).getValue()
  }
}

function isApp(sheet) {
  return sheet.getIndex() == appSheetIndex
}

function getRepoName(sheet) {
  if (isApp(sheet)) {
    return appRepoName
  } else {
    return serverRepoName
  }
}

function doGet(e) {
  var ss = SpreadsheetApp.getActiveSpreadsheet()
  var sheet = ss.getSheets()[appSheetIndex]
 
  var data = {
    code: sheet.getRange(sheet.getLastRow(), 1).getValue(),
    name: sheet.getRange(sheet.getLastRow(), 2).getValue(),
    english: sheet.getRange(sheet.getLastRow(), 3).getValue(),
    japanese: sheet.getRange(sheet.getLastRow(), 4).getValue()
  }
  
  return ContentService.createTextOutput(JSON.stringify(data)).setMimeType(ContentService.MimeType.JSON)
}

saveContact.gs

var channelName = ''
var iconUrl = ''
var spreadsheetUrl = ''
var slackUrl = ''
var gmailUserName = ''
var gmailAddress = ''

var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();

function doPost(e) {
  // 値取得
  var datevalue = Utilities.formatDate(new Date(), 'Asia/Tokyo', 'yyyy/MM/dd HH:mm:ss')
  var data = JSON.parse(e.postData.contents)
  var email = data.email
  var text = data.text
  
  // 行挿入
  var sheet = spreadsheet.getSheets()[0]
  var lastRow = sheet.getLastRow()
  sheet.insertRowAfter(lastRow)
  
  // 値設定
  sheet.getRange(lastRow + 1, 1).setValue(datevalue)
  sheet.getRange(lastRow + 1, 2).setValue(email)
  sheet.getRange(lastRow + 1, 3).setValue(text)
  
  var json =
  {
    'channel': channelName,
    'username' : 'お問い合わせ',
    'icon_url': iconUrl,
    'text' : '以下のお問い合わせのがありました。\n' + spreadsheetUrl + '\n------------------\n' + text
  }
  var payload = JSON.stringify(json)

  var options =
  {
    'method' : 'post',
    'contentType' : 'application/json',
    'payload' : payload
  }

  UrlFetchApp.fetch(slackUrl, options)
  
  GmailApp.sendEmail(
    email,
    'ご意見ありがとうございます。',
    '以下の内容でご意見を承りました。\n\n---------------------------------------\n' + text + '\n---------------------------------------\n\n※ 頂いたご意見は必ず確認いたしますが、全てにお返事はできないこともございます。\n何卒ご了承下さい。',
    {
      name: gmailUserName,
      from: gmailAddress
    }
  )
  
  return ContentService.createTextOutput(JSON.stringify({ok: true})).setMimeType(ContentService.MimeType.JSON)
}

twitterSearch.gs

var searchWord = ''
var bearer = ''
var slackUrl = ''
var excludeSheetIndex = 3
var postSheetIndex = 4

var spreadsheet = SpreadsheetApp.getActiveSpreadsheet()

function search() {
  var excludeSheet = spreadsheet.getSheets()[excludeSheetIndex] // エゴサ除外
  var lastRow = excludeSheet.getLastRow()

  var excludeUsers = []
  var excludeWords = []
  var excludeIds = []

  for (var i = 1; i <= lastRow; i++) {
    var user = excludeSheet.getRange(i, 1).getValue()
    var word = excludeSheet.getRange(i, 2).getValue()
    if (user) {
      excludeUsers.push(user)
    }
    if (word) {
      excludeWords.push(word)
    }
  }

  var postSheet = spreadsheet.getSheets()[postSheetIndex] // 投稿済み
  var postLastRow = postSheet.getLastRow()

  var excludeIds = []
  
  for (var i = 1; i <= postLastRow; i++) {
    var id = postSheet.getRange(i, 1).getValue()
    if (id) {
      excludeIds.push(id)
    }
  }

  var url = 'https://api.twitter.com/1.1/search/tweets.json?q=' + encodeURI(searchWord);
  var options = {
    'method': 'get',
    'headers': {
      'authorization': 'Bearer ' + bearer
    },
  }
  var response = JSON.parse(UrlFetchApp.fetch(url, options))
  var statuses = response.statuses
  statuses.sort(function (first, second) {
    var firstDate = new Date(first.created_at).getTime()
    var secondDate = new Date(second.created_at).getTime()
    if (firstDate < secondDate) {
      return -1;
    } else if (firstDate > secondDate) {
      return 1;
    } else {
      return 0;
    }
  });
  statuses.forEach(element => {
    // 対象外ユーザーは除外
    if (excludeUsers.includes(element.user.name)) {
      return
    }

    // 対象外ワードが入っていたら除外
    if (excludeWords.filter(word => element.text.indexOf(word) > -1).length) {
      return
    }

    // URL 生成
    var twitterUrl = 'https://twitter.com/' + element.user.screen_name + '/status/' + element.id_str

    // 既に登録済みだったら除外
    if (excludeIds.includes(twitterUrl)) {
      return
    }

    // 登録
    var targetLastRow = postSheet.getLastRow()
    postSheet.insertRowAfter(1)
    postSheet.getRange(2, 1).setValue(twitterUrl)
    postSheet.getRange(2, 2).setValue(element.user.name)
    postSheet.getRange(2, 3).setValue(element.text)
    postSheet.getRange(2, 4).setValue(Utilities.formatDate(new Date(element.created_at), 'Asia/Tokyo', 'yyyy/MM/dd HH:mm:ss'))

    // Slack に投げる
    var param =
    {
      'method' : 'post',
      'contentType' : 'application/json',
      'payload' : JSON.stringify({ 'text' : twitterUrl })
    };

    UrlFetchApp.fetch(slackUrl, param);
  })
}