Last active
March 29, 2022 22:39
-
-
Save venetucci/8ebc5b0f6f73354743c3307d2f38b25a to your computer and use it in GitHub Desktop.
🤓 A Google Apps Script to use with Slack webhooks - allows you to post to a spreadsheet from Slack, and receive a confirmation message in Slack. Full tutorial with Slack integrations: https://medium.com/p/a-bot-to-help-you-read-your-way-through-next-4-years-7ba9d80066fc
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
function testNewItemPost() { | |
var request = { | |
parameters: { | |
token: "[]", | |
user_name: "michelle", | |
text: "add: Rural America; Hillbilly Elegy; J.D. Vance; From a former marine and Yale Law School graduate, a powerful account of growing up in a poor Rust Belt town that offers a broader, probing look at the struggles of America’s white working class; https://www.amazon.com/gp/product/0062300547/ref=pd_bxgy_14_2?ie=UTF8&pd_rd_i=0062300547&pd_rd_r=MG2AV2PZZVEXNYZ1Y84V&pd_rd_w=xCKNl&pd_rd_wg=55Iya&psc=1&refRID=MG2AV2PZZVEXNYZ1Y84V;" | |
} | |
}; | |
doPost(request); | |
} | |
function testListTopics() { | |
var request = { | |
parameters: { | |
token: "[]", | |
user_name: "michelle", | |
text: "List topics" | |
} | |
}; | |
doPost(request); | |
} | |
function doPost(request) { | |
Logger.log("request" + request.parameters.text); | |
var params = request.parameters; | |
// Make sure that the request came from our slack integration. | |
if (params.token == "[]") { | |
// Get the spreadsheet | |
var sheets = SpreadsheetApp.openById('[]'); | |
// Check command name. If it starts with add, then add to sheet, if it's list topics, then respond with list of topics. | |
var text = String(params.text); | |
if (text.match(/^add:/i)) { | |
addNewItemToSheet(sheets, params); | |
} else if (text.match(/^list topics/i)) { | |
listSheetNames(sheets, params.channel_name) | |
} | |
// If the token does not match then do nothing | |
} else { | |
return; | |
} | |
} | |
function addNewItemToSheet(sheets, params) { | |
// PROCESS TEXT FROM MESSAGE | |
var textRaw = String(params.text).replace(/^\s*add\s*:*\s*/gi,''); | |
var text = textRaw.split(/\s*;\s*/g); | |
// FALL BACK TO DEFAULT TEXT IF NO UPDATE PROVIDED | |
var sheetName = text[0] || ""; | |
var title = text[1] || ""; | |
var author = text[2] || ""; | |
var description = text[3] || ""; | |
var link = (text[4] || "").replace("<", ""); | |
var sheet = sheets.getSheetByName(sheetName) | |
var nR = getNextRow(sheet) + 1; | |
// RECORD USER NAME IN SPREADSHEET | |
sheet.getRange('E:E').getCell(nR,1).setValue(params.user_name); | |
// RECORD UPDATE INFORMATION INTO SPREADSHEET | |
sheet.getRange('A:A').getCell(nR,1).setValue(title); | |
sheet.getRange('B:B').getCell(nR,1).setValue(author); | |
sheet.getRange('C:C').getCell(nR,1).setValue(description); | |
sheet.getRange('D:D').getCell(nR,1).setValue('=HYPERLINK("' + link + '", "Link")'); | |
var channel = "reading-list"; | |
postResponse(channel,params.user_name, sheetName, title, author, description, link); | |
} | |
function listSheetNames(sheets, channelName) { | |
var sheetNames = sheets.getSheets().map(function(sheet) { return sheet.getName() }); | |
postTopicNamesResponse(channelName, sheetNames.slice(1)); | |
} | |
function getNextRow(sheet) { | |
var titles = sheet.getRange("A:A").getValues(); | |
for (i in titles) { | |
if(titles[i][0] == "") { | |
return Number(i); | |
break; | |
} | |
} | |
} |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
function postResponse(channel, userName, topic, title, author, description, link) { | |
var payload = { | |
"channel": "#" + channel, | |
"username": "New item added to reading list", | |
"icon_emoji": ":grin:", | |
"link_names": 1, | |
"attachments":[ | |
{ | |
"fallback": "This is an update from a Slackbot integrated into your organization. Your client chose not to show the attachment.", | |
"pretext": "@" + userName + " added a new item to the reading list", | |
"mrkdwn_in": ["pretext"], | |
"color": "#76E9CD", | |
"fields":[ | |
{ | |
"title":"Topic", | |
"value": topic, | |
"short":false | |
}, | |
{ | |
"title":"Title", | |
"value": title, | |
"short":false | |
}, | |
{ | |
"title":"Author", | |
"value": author, | |
"short":false | |
}, | |
{ | |
"title":"Description", | |
"value": description, | |
"short": false | |
}, | |
{ | |
"title":"Link", | |
"value": link, | |
"short": false | |
} | |
] | |
} | |
] | |
}; | |
var url = '[]'; | |
var options = { | |
'method': 'post', | |
'payload': JSON.stringify(payload) | |
}; | |
return UrlFetchApp.fetch(url,options); | |
} |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
function postTopicNamesResponse(channel, topicNames) { | |
var payload = { | |
"channel": "#" + channel, | |
"username": "Topic List", | |
"icon_emoji": ":grin:", | |
"attachments":[ | |
{ | |
"fallback": "This is an update from a Slackbot integrated into your organization. Your client chose not to show the attachment.", | |
"pretext": "You can use the following topic names: " + topicNames.join(", "), | |
"mrkdwn_in": ["pretext"] | |
} | |
] | |
}; | |
var url = '[]'; | |
var options = { | |
'method': 'post', | |
'payload': JSON.stringify(payload) | |
}; | |
return UrlFetchApp.fetch(url,options); | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment