· 6 years ago · Jan 02, 2020, 07:52 PM
1function onOpen() {
2 var ss = SpreadsheetApp.getActiveSpreadsheet();
3 var menuEntries = [ {name: "Update from Trello", functionName: "loadFromTrello"},
4 {name: "Send summary email", functionName: "sendEmail"}];
5 ss.addMenu("Custom Functions", menuEntries);
6
7}
8
9// trello variables
10var api_key = "xxxxxxxxxxxxxxxxxxxxx";
11var api_token = "xxxxxxxxxxxxxxxxxxxxxxx";
12var board_id = "5665ca9e832a5e8de402213e"; //https://trello.com/b/cPC4jGzZ/project-manager-sample-board
13var sheetName = "Trello"
14var enableStackdriverLogging = true;
15var logingName = "Demo Trello";
16// Sheet for Email Options (which sheets to send via email and email headers)
17var emailOptionsSheet = "Email Options";
18
19
20/**
21 * Loads the details from a Trello board using the Trello API v1, into a google sheet.
22 *
23 * Each time it is run the sheet gets cleared and the following data are downloaded from the Trello board
24 *"Date", "Task", "Desc", "Who", "List", "Link", "Labels","Label Colors","Checklists", "Due Date", "Due Complete"
25 *
26 * @param {string} api_key The Trello API Key (Get it from https://trello.com/app-key)
27 * @param {string} api_token The Trello API token (Get it from https://trello.com/app-key)
28 * @param {string} board_id The Trello Board ID who's cards will be downloaded (Get it from adding ".json" at the end of the board url)
29 * @param {string} sheetName The name of the sheet in the active spreadsheet to update it's rows
30 * @param {boolean} enableStackdriverLogging True to enable Stackdriver Logging. Default is false
31 * @param {string} logingName logging name to be appended in the message. Default is ""
32 * @return {void} Not applicable.
33 */
34function loadFromTrello() {
35try {
36 if (enableStackdriverLogging) console.time(logingName + " - loadTrello");
37 if (enableStackdriverLogging) console.log(logingName + " - Loading from Trello STARTED");
38
39 var url = "https://api.trello.com/1/";
40 var key_and_token = "key="+api_key+"&token="+api_token;
41 var cr = 2;
42
43 // get sheet with name Trello, clear all contents, add titles
44 var ss = SpreadsheetApp.getActive().getSheetByName(sheetName).clear();
45 ss.appendRow(["Date", "Task", "Desc", "Who", "List", "Link", "Labels","Label Colors","Checklists", "Due Date", "Due Complete"]);
46 ss.getRange(1,1,1,11).setFontWeight("Bold");
47
48 //Get all lists from Trello API
49 var response = UrlFetchApp.fetch(url + "boards/" + board_id + "/lists?cards=all&" + key_and_token);
50 var lists = JSON.parse((response.getContentText()));
51
52 // for all lists
53 for (var i=0; i < lists.length; i++) {
54 var list = lists[i];
55 // Get all cards from Trello API
56 var response = UrlFetchApp.fetch(url + "list/" + list.id + "/cards?" + key_and_token);
57 var cards = JSON.parse(response.getContentText());
58 if(!cards) continue;
59
60 // for all cards
61 for (var j=0; j < cards.length; j++) {
62 var card = cards[j];
63 //Get all details of card from Trello API
64 var response = UrlFetchApp.fetch(url + "cards/" + card.id + "/?actions=all&" + key_and_token);
65 var carddetails = JSON.parse(response.getContentText()).actions;
66 if(!carddetails) continue;
67
68 //Get all checklists of card from Trello API
69 var response = UrlFetchApp.fetch(url + "cards/" + card.id + "/checklists?action=all&" + key_and_token);
70 var cardchecklists = JSON.parse(response.getContentText());
71
72 var checkliststr = "";
73 // For all checklists get Name
74 for (var m=0; m < cardchecklists.length; m++) {
75 checkliststr = checkliststr + (checkliststr == "" ? "" : "\n\n") + cardchecklists[m].name + "\n --------- \n";
76 // For all checklists get Items
77 for (var n=0; n < cardchecklists[m].checkItems.length; n++) {
78 checkliststr = checkliststr + (checkliststr == "" ? "" : "\n") + (cardchecklists[m].checkItems[n].state == 'complete' ? "[x] ":"[ ] " ) + cardchecklists[m].checkItems[n].name;
79 }
80 }
81 for (var k=0; k < carddetails.length; k++) {
82 // Get the rest of the card data
83 var dato = carddetails[k].date;
84 var fullname = carddetails[k].memberCreator.fullName;
85 var name = card.name;
86 var link = card.shortUrl;
87 var listname = list.name;
88 var desc = card.desc;
89 var duedate = card.due;
90 var duecomplete = (card.dueComplete == true ? 'YES' : 'NO');
91 var labels = "";
92 var labelsColors = "";
93 for (var l=0; l < card.labels.length; l++) {
94 labels = labels + (labels == "" ? "" : "\n") + card.labels[l].name;
95 labelsColors = labelsColors + (labelsColors == "" ? "" : "\n") + card.labels[l].color;
96 }
97 }
98 //Append row with data
99 ss.appendRow([dato, name, desc, fullname, listname, link, labels, labelsColors, checkliststr, duedate, duecomplete ]);
100
101 //change labels color ---
102 var labelsColor = labelsColors.split('\n');
103 if (labelsColor[0] == "sky") {
104 ss.getRange(cr, 8).setBackground("#87CEFA");
105 } else {
106 ss.getRange(cr, 8).setBackground(labelsColor[0]);
107 if ((labelsColor[0] == "red") || (labelsColor[0] == "black") || (labelsColor[0] == "purple") || (labelsColor[0] == "green") || (labelsColor[0] == "blue")) {
108 ss.getRange(cr, 8).setFontColor("white");
109 }
110 }
111 //change labels color END ---
112
113 cr++;
114 }
115 }
116 } catch (e) {
117 if (enableStackdriverLogging) console.error(logingName + " ERROR: " + e);
118 } finally {
119 if (enableStackdriverLogging) console.log(logingName + " - Loading from Trello ENDED");
120 if (enableStackdriverLogging) console.timeEnd(logingName + " - loadTrello");
121 }
122}
123
124/**
125 * Send email of the sheet contents based on the options defined in the "Email Options" sheet.
126 *
127 * The "Email Options" sheet, must have the following values in row 6
128 * |name | title | headersAtRow | display | columnsToSend | Empty | Current Date | Emails addresses | Message Prefix | Message PostFix | Subject
129 *
130 * The first 5 columns concern Information about which sheets to send the email and what content. Can have more that 1 row for each sheet to send. 1 row for each sheet.
131 * Then there is an empty column
132 * The next 5 columns concern Information about the notification email header. Only 1 row of options is valid
133 *
134 * More details about the columns of "Email Options" sheet:
135 * - name: The name of the sheet (per sheet)
136 * - title: The Title of the sheet to be displayed in the email (per sheet)
137 * - headersAtRow: Where is the header row located in the sheet. The header will contain the titles of the columns and the next row will contain the data (Row 1 is 0) (per sheet)
138 * - display: The display format of the content in the email. Can be 1 of the following formats: table, list, numbered, lines,title (Not linked to a sheet) ,paragraph (Not linked to a sheet) (per sheet)
139 * - columnsToSend: Which columns to include in the contents of the email, separated by comma. (Column A is 0). Example 0,1,2 (per sheet)
140 * - Current Date: The current date (No need to change this)
141 * - Emails addresses: Email addresses to send the notifications. Separate emails with a comma
142 * - Message Prefix: The notification message header (in HTML Format)
143 * - Message PostFix: The notification email footer (in HTML Format)
144 * - Subject: The subject of the enail
145 *
146 * @param {string} emailOptionsSheet the sheet name for the Email Options. Default is "Email Options"
147 * @param {boolean} enableStackdriverLogging True to enable Stackdriver Logging. Default is false
148 * @param {string} logingName logging name to be appended in the message. Default is ""
149 * @return {number} Not applicable.
150 */
151function sendEmail() {
152 try {
153 //default values
154 if (emailOptionsSheet == null) emailOptionsSheet="Email Options";
155 if (enableStackdriverLogging == null) enableStackdriverLogging=false;
156 if (logingName == null) logingName = "";
157
158 if (enableStackdriverLogging) console.time(logingName + " - emailSheetContent");
159 if (enableStackdriverLogging) console.log(logingName + " - emailSheetContent STARTED");
160
161 var sheetsToEmail = [];
162
163 // get active spreadsheet
164 var ss = SpreadsheetApp.getActiveSpreadsheet();
165 var sheetUrl = ss.getUrl();
166
167 //get Options start---------------------------
168 var sOptions2=ss.getSheetByName(emailOptionsSheet);
169 var sOptions2range = sOptions2.getDataRange();
170 var sOptions2values = sOptions2range.getDisplayValues();
171 var sOptions2lastRow = sOptions2range.getLastRow();
172
173 //for each row in body options sheet
174 for (var f = 5; f < sOptions2lastRow; f++) {
175 sheetsToEmail[f-5] = {name: "" + sOptions2values[f][0], title: sOptions2values[f][1], headersAtRow : parseInt(sOptions2values[f][2],10), display: sOptions2values[f][3], columnsToSend: sOptions2values[f][4].split(",")};
176 }
177
178 //get header options
179 var email = sOptions2values[5][7];
180 var messagePreFix = sOptions2values[5][8];
181 var messagePostFix = sOptions2values[5][9] + "<br />" + ss.getUrl();
182 var messageSubject = sOptions2values[5][10];
183 var message = messagePreFix + "<br /> ";
184 //get Options end---------------------------
185
186 //write message start ----------------------
187 // fop each Sheet defined in body options
188 for (var h=0; h<sheetsToEmail.length; h++) {
189 // Start message with header with the title of each sheet ---- MESSAGE ---
190 if (sheetsToEmail[h].display == "title") {
191 message = message + "<h2>" + sheetsToEmail[h].title + "</h2>";
192 } else if (sheetsToEmail[h].display == "paragraph") {
193 message = message + sheetsToEmail[h].title;
194 }else {
195 message = message + "<h3>" + sheetsToEmail[h].title + "</h3>";
196 }//---------------
197
198 if ((sheetsToEmail[h].display != "title") && (sheetsToEmail[h].display != "paragraph")) {
199 //Get sheet
200 var sheet = ss.getSheetByName(sheetsToEmail[h].name);
201 var range = sheet.getDataRange();
202 var values = range.getDisplayValues();
203 var lastRow = range.getLastRow();
204
205 //begin table, list, numbered ---- MESSAGE ---
206 if (sheetsToEmail[h].display == "table") {
207 message = message + "<table><tr>";
208 for (var g = 0; g < sheetsToEmail[h].columnsToSend.length; g++) {
209 message = message + "<td><b>" +values[sheetsToEmail[h].headersAtRow][sheetsToEmail[h].columnsToSend[g]] + "</b></td>";
210 }
211 message = message + "</tr>";
212 } else if (sheetsToEmail[h].display == "list") {
213 message = message + "<ul>";
214 } else if (sheetsToEmail[h].display == "numbered" ) {
215 message = message + "<ol>";
216 }//---------------
217
218 //for each row in sheet
219 for (var i = sheetsToEmail[h].headersAtRow + 1; i < lastRow; i++) {
220 //beginning each line ---- MESSAGE ---
221 if (sheetsToEmail[h].display == "table") {
222 message = message + "<tr>";
223 } else if ((sheetsToEmail[h].display == "list") ||(sheetsToEmail[h].display == "numbered" )) {
224 message = message + "<li>";
225 }
226 //for each columnsToSend
227 for (var j = 0; j < sheetsToEmail[h].columnsToSend.length; j++) {
228 //Write values ---- MESSAGE ---
229 if ((sheetsToEmail[h].display == "lines") ||(sheetsToEmail[h].display == "list") ||(sheetsToEmail[h].display == "numbered" )) {
230 message = message
231 + (j > 0?"<br />":"")
232 + "<b>" + values[sheetsToEmail[h].headersAtRow][sheetsToEmail[h].columnsToSend[j]]
233 + ":</b> " + values[i][sheetsToEmail[h].columnsToSend[j]];
234 } else if (sheetsToEmail[h].display == "table") {
235 message = message
236 + "<td>" + values[i][sheetsToEmail[h].columnsToSend[j]] + "</td>";
237 }
238 }
239
240 //ending each line ---- MESSAGE ---
241 if (sheetsToEmail[h].display == "lines") {
242 message = message + " <hr>";
243 } else if (sheetsToEmail[h].display == "table") {
244 message = message + "</tr>";
245 } else if ((sheetsToEmail[h].display == "list") || (sheetsToEmail[h].display == "numbered" )) {
246 message = message + "</li>";
247 }
248 }
249 }
250 // END IF HERE
251
252 //edning Table, List for each sheet ---- MESSAGE ---
253 if (sheetsToEmail[h].display == "table") {
254 message = message + "</table>";
255 } else if (sheetsToEmail[h].display == "list") {
256 message = message + "</ul>";
257 } else if (sheetsToEmail[h].display == "numbered" ) {
258 message = message + "</ol>";
259 }
260 //message = message + " <br />";
261 }
262
263 // footer for message ---- MESSAGE ---
264 message = message + "<br /><br /> " + messagePostFix;
265 //write message end ----------------------
266
267 if (enableStackdriverLogging) console.info(logingName + " Message: " + message);
268 //return false;
269 MailApp.sendEmail({
270 to: email,
271 subject: messageSubject,
272 htmlBody: message});
273
274 } catch (e) {
275 if (enableStackdriverLogging) console.error(logingName + " ERROR: " + e);
276 } finally {
277 if (enableStackdriverLogging) console.log(logingName + " - emailSheetContent ENDED");
278 if (enableStackdriverLogging) console.timeEnd(logingName + " - emailSheetContent");
279 }
280}