Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Google スプレッドシートで始めるアプリ運用
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'))
}
}
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)
}
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)
}
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);
})
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment