Send Tasks to Remember the Milk Using Google Sheets and Google Apps Script
Remember The Milk users can use this Google Sheet and the following Google Apps Script to email task to your RTM account.
Updated 20Jan15:
-fixed the problem with adding recurring task
-fixed problem of adding tasks before the subject line was generated
// 20Jan15 // This app takes task information entered into a Google spreadsheet and // emails it to your Remember the Milk account // Intro to Google Apps Scrips here: // https://developers.google.com/apps-script/overview // The base of the code and a great Google spreadsheet/email tutorial here: // https://developers.google.com/apps-script/articles/sending_emails var EMAIL_SENT = "TASK_SENT_TO_RTM"; function sendTaskstoRTM() { var sheet = SpreadsheetApp.getActiveSheet(); var startRow = 4; // First row of data to process // var numRows = 2; // Number of rows to process // Determine how many rows are in the ss // http://stackoverflow.com/questions/17632165/determining-the-last-row-in-a-single-column-google-apps-script var Avals = sheet.getRange("A3:A").getValues(); var numRows = (Avals.filter(String).length - 1); // Number of rows to process Logger.log(numRows); var emailAddress = sheet.getRange("B1").getValue(); // First column/Column A Logger.log(emailAddress); // Fetch the range of cells A2:last row/last column var dataRange = sheet.getRange(startRow, 1, numRows, 10) // Fetch values for each row in the Range. var data = dataRange.getValues(); // Before sending the tasks to RTM create the RTM Smart Add email subject for (var i = 0; i < data.length; ++i) { currentRow = startRow + i sheet.getRange(currentRow, 9).setFormula('=CONCATENATE(A'+currentRow+'," ", IF(ISBLANK(B'+currentRow+'),"",CONCATENATE("^",(TEXT(B'+currentRow+',"mm/dd/yyyy"))))," ",IF(ISBLANK(C'+currentRow+'),"",CONCATENATE("#",C'+currentRow+'))," ",IF(ISBLANK(D'+currentRow+'),"",CONCATENATE("#",D'+currentRow+'))," ",IF(ISBLANK(E'+currentRow+'),"",CONCATENATE("!",E'+currentRow+'))," ",IF(ISBLANK(F'+currentRow+'),"",CONCATENATE("=",F'+currentRow+'))," ",IF(ISBLANK(G'+currentRow+'),"",CONCATENATE("*",G'+currentRow+')))'); } // pause for 3 seconds (2000 milliseconds) // I added this because the task was being sent before the subject line was generated Utilities.sleep(3000); // Read through the rows and send the tasks to RTM if they haven't been sent already for (var i = 0; i < data.length; ++i) { var row = data[i]; var subject = row[8]; // 9th column/Column I Logger.log(subject); var message = row[7]; // 8th column/Column H Logger.log(message); var emailSent = row[9]; // 10th column/Column J Logger.log(emailSent); if (emailSent != EMAIL_SENT) { // Prevents sending duplicates MailApp.sendEmail(emailAddress, subject, message); sheet.getRange(startRow + i, 10).setValue(EMAIL_SENT); // Make sure the cell is updated right away in case the script is interrupted SpreadsheetApp.flush(); // pause for 2 seconds (2000 milliseconds) Utilities.sleep(2000); } } } /** * Adds a custom menu to the active spreadsheet, containing a single menu item * for invoking the sendTaskstoRTM() function specified above. * The onOpen() function, when defined, is automatically invoked whenever the * spreadsheet is opened. * For more information on using the Spreadsheet API, see * https://developers.google.com/apps-script/service_spreadsheet */ function onOpen() { var spreadsheet = SpreadsheetApp.getActiveSpreadsheet(); var entries = [{ name : "Send Tasks to RTM", functionName : "sendTaskstoRTM" }]; spreadsheet.addMenu("RTM", entries); };