Drip campaigns (aka email sequences) are a powerful way to re-engage users using a linear fashion. Many email marketers use this technique…
How to Auto-send Slackbot DMs using Google Sheets
Save yourself time (and save your sanity) by automating weekly metric update messages to your co-workers. In this video, you'll learn how to make a Slackbot that pulls data from your Google Sheet and sends it out as a curated and stylized message to anyone of your choice through Slack, as a direct message or group message. After this video, you'll be able to take time off with peace of mind.
Make sure to watch the video to follow along with the code: https://youtu.be/3Qswza8wQOU
The first set of code is our requestSlack() function:
function requestSlack(method, endpoint, params) {
const base_url = "https://slack.com/api/"
const headers = {
'Authorization': "Bearer " + YOUR_BOT_OAUTH_TOKEN
'Content-Type': 'application/json'
}
const options = {
headers: headers,
method: method
}
let request_url
if (method == "POST") {
request_url = base_url + endpoint
options.payload = JSON.stringify(params)
} else {
request_url = base_url + endpoint + params
}
const response = UrlFetchApp.fetch(request_url, options).getContentText();
const json = JSON.parse(response);
return {
response_code: json.ok,
response_data: json
}
}
From there, we start writing our weeklyMetricsSlackNotificationJob() function:
function weeklyMetricsSlackNotificationJob() {
let user_request = requestSlack("GET", "users.lookupByEmail", "?email=email@email.com")
console.log(`user: ${user_request.response_data}`)
let user_id = user_request.response_data.user.id
let conversationsOpen_payload = {
users: user_id
}
let conversation_request = requestSlack("POST", "conversations.open", conversationsOpen_payload)
let conversation_id = conversation_request.response_data.channel.id
let postMessage_payload = {
text: `TEST`,
channel: conversation_id
}
let post_message_request = requestSlack("POST", "chat.postMessage", postMessage_payload)
}
To grab the metrics from our Google Sheet, we'll need the getLatestMetrics() function and also the sumSheetValues() function.
function getLatestMetrics(){
const ss = SpreadsheetApp.openById(YOUR_SPREADSHEET_ID)
const sheet = ss.getSheetByName(YOUR_SHEET_NAME)
const latest_metrics = sheet.getRange(6,1,8,7).getValues();
const metric_deltas = sheet.getRange(2,4,2,4).getValues();
const metric_array_positions = {
queries: 3,
first_page: 4,
impressions: 5,
clicks: 6
}
const metrics = {
period: {
week: {
start: Utilities.formatDate(latest_metrics[0][1],"EST", "yyyy-MM-dd"),
end: Utilities.formatDate(latest_metrics[0][2],"EST", "yyyy-MM-dd")
},
month: {
start: Utilities.formatDate(latest_metrics[3][1],"EST", "yyyy-MM-dd"),
end: Utilities.formatDate(latest_metrics[0][2],"EST", "yyyy-MM-dd")
}
},
queries: {
this_week: latest_metrics[0][metric_array_positions.queries],
last_week: latest_metrics[1][metric_array_positions.queries],
this_month: sumSheetValues(latest_metrics, 0, 3, metric_array_positions.queries),
last_month: sumSheetValues(latest_metrics, 4, 7, metric_array_positions.queries),
wow_delta: Math.round(metric_deltas[0][0]*100) + '%',
mom_delta: Math.round(metric_deltas[1][0]*100) + '%'
},
first_page: {
this_week: latest_metrics[0][metric_array_positions.first_page],
last_week: latest_metrics[1][metric_array_positions.first_page],
this_month: sumSheetValues(latest_metrics, 0, 3, metric_array_positions.first_page),
last_month: sumSheetValues(latest_metrics, 4, 7, metric_array_positions.first_page),
wow_delta: Math.round(metric_deltas[0][1]*100) + '%',
mom_delta: Math.round(metric_deltas[1][1]*100) + '%'
},
impressions: {
this_week: latest_metrics[0][metric_array_positions.impressions],
last_week: latest_metrics[1][metric_array_positions.impressions],
this_month: sumSheetValues(latest_metrics, 0, 3, metric_array_positions.impressions),
last_month: sumSheetValues(latest_metrics, 4, 7, metric_array_positions.impressions),
wow_delta: Math.round(metric_deltas[0][2]*100) + '%',
mom_delta: Math.round(metric_deltas[1][2]*100) + '%'
},
clicks: {
this_week: latest_metrics[0][metric_array_positions.clicks],
last_week: latest_metrics[1][metric_array_positions.clicks],
this_month: sumSheetValues(latest_metrics, 0, 3, metric_array_positions.clicks),
last_month: sumSheetValues(latest_metrics, 4, 7, metric_array_positions.clicks),
wow_delta: Math.round(metric_deltas[0][3]*100) + '%',
mom_delta: Math.round(metric_deltas[1][3]*100) + '%'
}
}
return metrics
}
function sumSheetValues(data, array_row_start, array_row_end, array_column) {
let result = 0
for(let i = array_row_start; i <= array_row_end; i++) {
result += data[i][array_column]
}
return result
}
Here's a sample Spreadsheet with data in the right cells that'll let you test out the script: Link to Google Sheet
Now that we have both of those, the final version of your weeklyMetricsSlackNotificationJob() function should look like this:
function weeklyMetricsSlackNotificationJob() {
let user_request = requestSlack("GET", "users.lookupByEmail", "?email=email@email.com")
console.log(`user: ${user_request.response_data}`)
let user_id = user_request.response_data.user.id
let conversationsOpen_payload = {
users: [user_id, "user_id2", "user_id3"]
}
let conversation_request = requestSlack("POST", "conversations.open", conversationsOpen_payload)
let conversation_id = conversation_request.response_data.channel.id
let metrics = getLatestMetrics();
let postMessage_payload = {
text: `*Metrics Update* \n *_Weekly_* _(${metrics.period.week.start} - ${metrics.period.week.end})_` +
`\n- Queries: ${metrics.queries.this_week} (${metrics.queries.wow_delta})` +
`\n- First Page: ${metrics.first_page.this_week} (${metrics.first_page.wow_delta})` +
`\n- Impressions: ${metrics.impressions.this_week} (${metrics.impressions.wow_delta})` +
`\n- Clicks: ${metrics.clicks.this_week} (${metrics.clicks.wow_delta})` +
`\n \n *_Monthly_* _(${metrics.period.month.start} - ${metrics.period.month.end})_` +
`\n- Queries: ${metrics.queries.this_month} (${metrics.queries.mom_delta})` +
`\n- First Page: ${metrics.first_page.this_month} (${metrics.first_page.mom_delta})` +
`\n- Impressions: ${metrics.impressions.this_month} (${metrics.impressions.mom_delta})` +
`\n- Clicks: ${metrics.clicks.this_month} (${metrics.clicks.mom_delta})`
,
channel: conversation_id
}
let post_message_request = requestSlack("POST", "chat.postMessage", postMessage_payload)
}
======= KEEP LEARNING WITH Bootstrapping Tools =======
🎬 MOST RECENT VIDEOS: https://www.youtube.com/c/bootstrappingtools/videos
🤖 Automate with Google Apps Script: https://bit.ly/BootstrappingTools-GoogleAppsScript
💻 Build-Through Videos: https://bit.ly/BootstrappingTools-Lets-Build
🚑 Community Support Videos: https://bit.ly/BootstrappingTools-CommunitySupport
======================================================
👥 Looking for something specific? feedback@bootstrapping.tools
💝 Support us by buying us a coffee
======================================================
