-
Notifications
You must be signed in to change notification settings - Fork 1
/
sheetbot.js
255 lines (241 loc) · 7.84 KB
/
sheetbot.js
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
const {JWT} = require('google-auth-library');
const {google} = require('googleapis');
const gapi = async (googleObj, call, args) => {
/**
* Google API availability is not very reliable. This function retries
* connecting to the API up to 5 times following a similar strategy posted
* here:
* https://stackoverflow.com/questions/42552925/the-service-is-currently-unavailable-google-api
*/
let apiCallNumOfAttempts = 5;
let apiCallAttempts = 0;
let apiCallSleep = 1;
return new Promise(
(resolve, reject) => {
getGoogleObj();
function getGoogleObj() {
googleObj[call](args, (err, res) => {
if (err) {
if(apiCallAttempts < apiCallNumOfAttempts) {
console.log('The API returned an error: ' + err + '. Retrying...');
setTimeout(getGoogleObj, apiCallSleep * 1000);
apiCallAttempts++;
apiCallSleep *= 2;
} else {
console.log('The API returned an error: ' + err);
reject('The API returned an error: ' + err);
}
return;
}
resolve(res);
});
}
}
);
}
const algorithms = {
'first_row': (rows, filter, date) => {
// first non-blank
for (i=0; i < rows.length; i++) {
if (rows[i].length && filter(rows[i])) {
return rows[i];
}
}
},
'date_match': (rows, filter, date) => {
// epoch time in days: same as "SERIAL_NUMBER" from google date format
const today = (date ? new Date(date) : new Date()).toDateString();
for (i=0; i < rows.length; i++) {
if (filter(rows[i])) {
const date = (new Date(rows[i][0])).toDateString();
if (date == today) {
return rows[i];
}
}
}
return null;
},
'date_match_with_row_contents': (rows, filter, date) => {
// epoch time in days: same as "SERIAL_NUMBER" from google date format
const today = (date ? new Date(date) : new Date()).toDateString();
for (i=0; i < rows.length; i++) {
if (filter(rows[i])) {
const date = (new Date(rows[i][0])).toDateString();
if (date == today && rows[i].length > 1 && rows[i][1]) {
return rows[i];
}
}
}
return null;
},
'date_most_recent': (rows, filter, date) => {
// assuming the first column is full of dates in ascending order,
// get the most recent row
const today = (date ? new Date(date) : new Date()).toDateString();
for (i=0; i < rows.length; i++) {
if (rows[i][0]) {
const date = (new Date(rows[i][0])).toDateString();
if (date == today && filter(rows[i])) {
return rows[i];
} else if (new Date(date) > new Date(today)) {
if (i && filter(rows[i-1])) {
return rows[i-1];
} else {
return null;
}
}
}
}
return null;
},
'tomorrow_reminder': (rows, filter, date) => {
const tomorrow = (date ? new Date(date) : new Date());
tomorrow.setDate(tomorrow.getDate() + 1);
for (i=0; i < rows.length; i++) {
if (filter(rows[i])) {
const date = (new Date(rows[i][0])).toDateString();
if (date == tomorrow.toDateString()) {
return rows[i];
}
}
}
return null;
},
'weekdays_after_topdate': (rows, filter, date) => {
// get the date from the first row, then just count each weekday
let firstDate = new Date(rows[0][0]);
let today = date ? new Date(date) : new Date();
// Convert to UTC and zero time to do calculations correctly
firstDate = Date.UTC(firstDate.getFullYear(), firstDate.getMonth(), firstDate.getDate(), 0, 0, 0, 0);
today = Date.UTC(today.getFullYear(), today.getMonth(), today.getDate(), 0, 0, 0, 0);
const daysSince = Math.ceil((today - firstDate) / (1000 * 60 * 60 * 24));
const weeksSince = parseInt(daysSince / 7);
const rowIndex = (weeksSince * 5) + (daysSince % 7);
if (rowIndex < rows.length) {
return rows[rowIndex];
}
return null;
}
};
class SheetBot {
constructor({
clientAuth,
spreadsheetUrl,
clientEmail,
clientPrivateKey,
userMap,
sheetData,
shareEmail
}) {
const auth = clientAuth || new JWT(
clientEmail,
null,
clientPrivateKey,
['https://www.googleapis.com/auth/spreadsheets.readonly', 'https://www.googleapis.com/auth/drive'],
shareEmail
);
this.c = google.sheets({version: 'v4', auth});
this.userMap = userMap || {};
this.sheetData = sheetData;
this.bColumnFilter = sheetData?.bColumnFilter;
this.spreadsheetUrl = spreadsheetUrl || sheetData?.spreadsheetUrl;
this.spreadsheetId = this.spreadsheetUrl.match(/\/d\/([^/]+)/)[1];
const subsheet = this.spreadsheetUrl.match(/gid=(\d+)/);
if (subsheet) {
this.gid = subsheet[1];
}
}
async maybeMessage({ algorithm, fakedate }) {
const data = await this.getData();
const message = this.customCellMessage || data[0][1];
const HEADERROWS = 2;
const rows = data.slice(HEADERROWS);
const bColumnFilter = this.bColumnFilter;
const filter = bColumnFilter
? (row => row[0] && row[1] == bColumnFilter)
: (row => row[0]);
if (data.length > HEADERROWS) {
const row = (algorithms[algorithm] ||
algorithms["date_most_recent"])(
rows,
filter || (row => row[0]),
fakedate ? new Date(fakedate) : new Date()
);
if (row?.length) {
console.log('maybeMessage', row, this.spreadsheetId, this.gid);
return this.formatMessage(message, row);
}
}
return null
}
getDisplayName(name) {
// Allow multiple names to be specified, separated by the words
// "and", "or", or any of the following characters: , ; & +
const names = name.split(/[,;&+]|\sand\s|\sor\s/);
let displayNames = [];
let nameColumn = false;
if (names.length) {
for (i=0; i < names.length; i++) {
const trimmedName = names[i].trim();
if (trimmedName) {
const slackId = this.userMap[String(trimmedName).replace(/^@/,'').toLowerCase()];
if (slackId) {
nameColumn = true;
displayNames.push("<@" + slackId + ">");
}
}
}
}
else {
return "_";
}
// If this column isn't used for names, return the original value.
return nameColumn ? displayNames.join(', ') : name;
}
formatMessage(text, row) {
return text
.replace(/\$\w+/g, (item) => {
const index = item.toUpperCase().charCodeAt(1) - 65;
if (index < row.length) {
return this.getDisplayName(row[index]);
}
return "__";
});
}
async getData() {
// we use batchGetByDataFilter instead of get so we can use the gid= spreadsheet id
// that people share in the urls (rather than the sheet name)
const resp = await gapi(this.c.spreadsheets.values, 'batchGetByDataFilter', {
spreadsheetId: this.spreadsheetId,
majorDimension: 'ROWS',
resource: {
dataFilters: [
{
gridRange: {
sheetId: this.gid || 0,
startRowIndex: 0,
endRowIndex: 1000,
startColumnIndex: 0,
endColumnIndex: 10,
},
},
],
}
});
if (this.sheetData?.customMessageCell) {
const messageCellData = await gapi(this.c.spreadsheets.values, 'get', {
spreadsheetId: this.spreadsheetId,
majorDimension: 'ROWS',
range: this.sheetData.customMessageCell
});
if (messageCellData.data.values) {
this.customCellMessage = messageCellData.data.values[0][0];
}
}
// console.log('getData', JSON.stringify(resp, null, 2));
// talk about burying the data!
return (resp.data?.valueRanges.length &&
resp.data.valueRanges[0].valueRange.values);
}
}
exports.SheetBot = SheetBot