Guest User

Google Sheets email completed tasks

a guest
Jan 19th, 2018
269
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 2.13 KB | None | 0 0
  1. function emailCompletedTasks() {
  2.  
  3. // emailCompletedTasks - Send daily summary of ToDoist completed tasks
  4. // Prereqs:
  5. // - Todoist account linked with IFTTT
  6. // - IFTTT recipe to capture every completed task in a Google sheet
  7. // - https://ifttt.com/applets/67297169d-put-all-your-completed-tasks-in-a-google-spreadsheet
  8.  
  9. // Email address to send the data to
  10.   var to = "myemailaddress@gmail.com";
  11.  
  12. // Today's date in the same format as IFTTT ToDoist recipe uses. E.g. "December 12, 2017"
  13. var today = Utilities.formatDate(new Date(), "GMT-8", ("MMMMM d, yyyy"));
  14.  
  15. // Subject line for the email with today's date appended
  16. var subject = "ToDone on " + today;
  17.  
  18. // Open the sheet by name with the task data in it. By default, it will be "Sheet1"
  19. var s = SpreadsheetApp.getActive().getSheetByName('Sheet1');
  20.  
  21. // Set up the regular expression to be todays date
  22. // - This will match the form of IFTTT's ToDoist date colume (e.g. "December 12, 2017")
  23. var regexp = new RegExp(today, "i");
  24.  
  25. // Pull the first for columns of every row into an array
  26. var data = s.getRange('A:D').getValues();
  27.  
  28. // Set up the default table format
  29. var TABLEFORMAT = 'cellspacing="2" cellpadding="2" dir="ltr" border="1" style="width:100%;table-layout:auto;font-size:10pt;font-family:arial,sans,sans-serif;border-collapse:collapse;border:1px solid #ccc;font-weight:normal;color:black;background-color:white;text-align:left;text-decoration:none;font-style:normal;'
  30. var body = '<table ' + TABLEFORMAT +' ">';
  31.  
  32. body += '<tr>';
  33.  
  34. body += '<th>' + "Task" + '</th><th>' + "Project" + '</th><th>' + "Tags" + '</th></tr>';
  35.  
  36. // For every row in the spreadsheet, try to match today's date
  37. // - If matched, add Task, Project, and Tags cells to the table
  38.   for( var row in data ) {
  39. if (regexp.test(data[row][2])) {
  40.       body += '<td>' + data[row][0] + '</td>' + '<td>' + data[row][1] + '</td><td>' + data[row][3] + '</td>';
  41.     body += '</tr>';
  42. }
  43. }
  44. body += '</table>';
  45.  
  46. // Mail the table to the address specified
  47. MailApp.sendEmail(to, subject, '', {htmlBody: body});
  48. }
Add Comment
Please, Sign In to add comment