|
// SPDX-License-Identifier: CC0-1.0 |
|
|
|
const EMAIL = 3 /* C */, TUTOR = 5 /* E */, PROBLEMS = 7 /* G: */; |
|
const TOKEN = 'SLACK_TOKEN'; |
|
|
|
function slack(url, payload) { |
|
var token = PropertiesService.getDocumentProperties().getProperty(TOKEN); |
|
if (token === null) |
|
throw new Error('Slack bot token not set'); |
|
var req = {headers: {'Authorization': `Bearer ${token}`}}; |
|
if (typeof payload !== 'undefined') { |
|
req.method = 'POST'; |
|
req.contentType = 'application/json;charset=utf-8'; |
|
req.payload = JSON.stringify(payload); |
|
} |
|
var res = UrlFetchApp.fetch(`https://slack.com/api/${url}`, req); |
|
return JSON.parse(res.getContentText()); |
|
} |
|
|
|
var users = new Map(); |
|
function findUser(email) { |
|
var user = users.get(email); |
|
if (typeof user === 'undefined') { |
|
var result = slack(`users.lookupByEmail?email=${encodeURIComponent(email)}`); |
|
if (!result.ok) throw new Error(`user ${email} not found`); |
|
users.set(email, user = result.user.id); |
|
} |
|
return user; |
|
} |
|
|
|
function sendToSlack() { |
|
var ui = SpreadsheetApp.getUi(), sheet; |
|
|
|
// Map short tutor names to Slack user IDs |
|
sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Семинаристы'); |
|
var tutors = new Map(); |
|
for (var row = sheet.getFrozenRows() + 1; row <= sheet.getLastRow(); row++) { |
|
tutors.set(sheet.getRange(row, 1).getValue(), |
|
findUser(sheet.getRange(row, 2).getValue())); |
|
} |
|
|
|
// Get problem set and problem names |
|
sheet = SpreadsheetApp.getActiveSheet(); |
|
var prset = sheet.getName().trim(), problems = []; |
|
for (var col = PROBLEMS; col <= sheet.getLastColumn(); col++) |
|
problems.push(sheet.getRange(1, col).getValue()); |
|
|
|
// Walk through selected rows and collect the grades |
|
var data = []; |
|
for (var range of sheet.getActiveRangeList().getRanges()) { |
|
if (range.getColumn() != 1 || range.getLastColumn() < sheet.getLastColumn()) |
|
throw new Error('selection does not extend all the way to the right'); |
|
for (var row = range.getRow(); row <= range.getLastRow(); row++) { |
|
if (row <= sheet.getFrozenRows() || sheet.isRowHiddenByFilter(row)) continue; |
|
if (row > sheet.getLastRow()) break; |
|
var entry = { |
|
student: findUser(sheet.getRange(row, EMAIL).getValue()), |
|
tutor: tutors.get(sheet.getRange(row, TUTOR).getValue()), |
|
grades: problems.map((_, i) => sheet.getRange(row, PROBLEMS+i).getValue()), |
|
}; |
|
if (typeof entry.tutor === 'undefined') |
|
throw new Error('unknown or unspecified tutor'); |
|
if (entry.grades.every(grade => grade === '')) |
|
continue; |
|
if (entry.grades.some(grade => grade === '')) |
|
throw new Error('there are ungraded problems'); |
|
data.push(entry); |
|
} |
|
} |
|
|
|
var confirmation = `Send grades for ${prset} to ${data.length} student(s)?`; |
|
if (ui.alert(confirmation, ui.ButtonSet.OK_CANCEL) !== ui.Button.OK) |
|
return; |
|
|
|
// From this point on, we are committed to sending messages. Slack can still |
|
// return an error to us and the user will have to resend manually, risking |
|
// duplicates, but this can't be fixed without remembering additional state. |
|
|
|
// Format and send the grades |
|
for ({student, tutor, grades} of data) { |
|
var message, result; |
|
grades = grades.map((grade, i) => `*${problems[i]}* ${grade}`).join(' '); |
|
message = { |
|
channel: student, |
|
text: `<@${tutor}> проверил(а) ваше решение ${prset} ` + |
|
`и поставил(а) оценки:\n${grades}`, |
|
}; |
|
result = slack('chat.postMessage', message); |
|
if (!result.ok) throw new Error(`Slack returned ${result.error}`); |
|
message = { |
|
channel: tutor, |
|
text: `<@${student}> получил(а) поставленные вами оценки ` + |
|
`за ${prset}:\n${grades}`, |
|
} |
|
result = slack('chat.postMessage', message); |
|
if (!result.ok) throw new Error(`Slack returned ${result.error}`); |
|
} |
|
} |
|
|
|
function setSlackToken() { |
|
var ui = SpreadsheetApp.getUi(), |
|
res = ui.prompt('Enter Slack bot token', ui.ButtonSet.OK_CANCEL); |
|
if (res.getSelectedButton() !== ui.Button.OK) |
|
return; |
|
var token = res.getResponseText(); |
|
if (!token.startsWith('xoxb-')) |
|
throw new Error('not a valid Slack bot token'); |
|
// token must have chat:write, im:write, user:read, user:read.email scopes |
|
PropertiesService.getDocumentProperties().setProperty(TOKEN, token); |
|
} |
|
|
|
function onOpen() { |
|
SpreadsheetApp.getUi().createAddonMenu() |
|
.addItem('Send to Slack', 'sendToSlack') |
|
.addItem('Set Slack bot token', 'setSlackToken') |
|
.addToUi(); |
|
} |