Skip to content

Instantly share code, notes, and snippets.

@venetucci
Last active March 29, 2022 22:39
Show Gist options
  • Save venetucci/8ebc5b0f6f73354743c3307d2f38b25a to your computer and use it in GitHub Desktop.
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
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;
}
}
}
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);
}
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