I’m building a Slack Bot for my internship, and I can get it to send direct messages to a private channel, and connect through the API. But my current problem is getting the algorithm to take the information in a spreadsheet and form a message with that data in the channel. I found a tutorial on youtube, and their code looks like this, I need to understand what this code is doing, please help!
JavaScript
x
76
76
1
function getLatestMetrics(){
2
3
const ss = SpreadsheetApp.openById(YOUR_SPREADSHEET_ID)
4
const sheet = ss.getSheetByName(YOUR_SHEET_NAME)
5
const latest_metrics = sheet.getRange(6,1,8,7).getValues();
6
const metric_deltas = sheet.getRange(2,4,2,4).getValues();
7
const metric_array_positions = {
8
9
queries: 3,
10
first_page: 4,
11
impressions: 5,
12
clicks: 6
13
}
14
15
const metrics = {
16
period: {
17
week: {
18
start: Utilities.formatDate(latest_metrics[0][1],"EST", "yyyy-MM-dd"),
19
end: Utilities.formatDate(latest_metrics[0][2],"EST", "yyyy-MM-dd")
20
},
21
22
month: {
23
start: Utilities.formatDate(latest_metrics[3][1],"EST", "yyyy-MM-dd"),
24
end: Utilities.formatDate(latest_metrics[0][2],"EST", "yyyy-MM-dd")
25
}
26
},
27
28
queries: {
29
this_week: latest_metrics[0][metric_array_positions.queries],
30
last_week: latest_metrics[1][metric_array_positions.queries],
31
this_month: sumSheetValues(latest_metrics, 0, 3, metric_array_positions.queries),
32
last_month: sumSheetValues(latest_metrics, 4, 7, metric_array_positions.queries),
33
wow_delta: Math.round(metric_deltas[0][0]*100) + '%',
34
mom_delta: Math.round(metric_deltas[1][0]*100) + '%'
35
},
36
37
38
first_page: {
39
this_week: latest_metrics[0][metric_array_positions.first_page],
40
last_week: latest_metrics[1][metric_array_positions.first_page],
41
this_month: sumSheetValues(latest_metrics, 0, 3, metric_array_positions.first_page),
42
last_month: sumSheetValues(latest_metrics, 4, 7, metric_array_positions.first_page),
43
wow_delta: Math.round(metric_deltas[0][1]*100) + '%',
44
mom_delta: Math.round(metric_deltas[1][1]*100) + '%'
45
},
46
47
48
impressions: {
49
this_week: latest_metrics[0][metric_array_positions.impressions],
50
last_week: latest_metrics[1][metric_array_positions.impressions],
51
this_month: sumSheetValues(latest_metrics, 0, 3, metric_array_positions.impressions),
52
last_month: sumSheetValues(latest_metrics, 4, 7, metric_array_positions.impressions),
53
wow_delta: Math.round(metric_deltas[0][2]*100) + '%',
54
mom_delta: Math.round(metric_deltas[1][2]*100) + '%'
55
},
56
57
58
clicks: {
59
this_week: latest_metrics[0][metric_array_positions.clicks],
60
last_week: latest_metrics[1][metric_array_positions.clicks],
61
this_month: sumSheetValues(latest_metrics, 0, 3, metric_array_positions.clicks),
62
last_month: sumSheetValues(latest_metrics, 4, 7, metric_array_positions.clicks),
63
wow_delta: Math.round(metric_deltas[0][3]*100) + '%',
64
mom_delta: Math.round(metric_deltas[1][3]*100) + '%'
65
}
66
}
67
return metrics
68
}
69
function sumSheetValues(data, array_row_start, array_row_end, array_column) {
70
let result = 0
71
for(let i = array_row_start; i <= array_row_end; i++) {
72
result += data[i][array_column]
73
}
74
return result
75
}
76
Advertisement
Answer
Getting data from Spreadsheet and iterating over it
JavaScript
1
13
13
1
function getSpreadsheetData() {
2
const ss = SpreadsheetApp.getActive();
3
const sh = ss.getSheetByName("Sheet0");
4
const vs = sh.getRange(2,1,sh.getLastRow()-1,sh.getLastColumn()).getValues();
5
Logger.log(JSON.stringify(vs));
6
vs.forEach((row,i) => {
7
//iterate through rows
8
row.forEach((col,j) => {
9
//iterate through columns
10
})
11
})
12
}
13
Data:
COL1 | COL2 | COL3 | COL4 | COL5 |
---|---|---|---|---|
12 | 3 | 19 | 14 | 5 |
9 | 6 | 15 | 4 | 15 |
13 | 7 | 9 | 14 | 6 |
17 | 17 | 7 | 11 | 0 |
16 | 4 | 18 | 14 | 17 |
JavaScript
1
5
1
Execution log
2
7:44:48 AM Notice Execution started
3
7:44:49 AM Info [[12,3,19,14,5],[9,6,15,4,15],[13,7,9,14,6],[17,17,7,11,0],[16,4,18,14,17]]
4
7:44:50 AM Notice Execution completed
5