1

Background: I use Google Sheets to track issues/progress and I log daily progress by inserting comments in the cells of the A column.

Question: I would like to extract all the comments that were logged in a particular time period, say in a day and then send them to a defined mail account. Any way to do that?

1

This cannot be done because comments in spreadsheets cannot be accessed by Apps Script. They are really ephemeral and are meant for human use only.

You could use notes instead. A script can access notes with getNotes method but notes do not have a timestamp. A workaround would be to put the current date in the note in some specific format, e.g., 2017-12-20. Then the script could do something like

function notesToday() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var notes = sheet.getRange("A:A").getNotes();
  var today = Utilities.formatDate(new Date(), "GMT-5", "yyyy-MM-dd"); 
       // adjust the timezone and the format you want on the previous line, per https://developers.google.com/apps-script/reference/utilities/utilities
  var todayNotes = notes.map(function (row) {
    return row[0];
  }).filter(function (text) {
    return text.indexOf(today) != -1;
  });
  if (todayNotes.length) {
    MailApp.sendEmail("user@gmail.com", "Notes for " + today, todayNotes.join("\n"));
  }
}

Create a daily trigger to run the above at midnight.


Another version, which does not require entering dates: it mails any notes that do not end with the word "logged", and appends "logged" at the end of those notes. This way, you can run it at any intervals you want and it will only send fresh notes.

function notesToday() {
  var logString = "logged";   // the string to designate logged notes
  var logRegex = new RegExp(logString + "$");   // $ means it must be at the end
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getRange("A:A");
  var notes = range.getNotes();
  var todayNotes = notes.map(function (row) {
    return row[0];
  }).filter(function (str) {
    return !logRegex.test(str);
  });
  if (todayNotes.length) {
    MailApp.sendEmail("user@gmail.com", "Notes for today", todayNotes.join("\n"));
    var updatedNotes = notes.map(function (row) {
      return (!row[0] ? row : (logRegex.test(row[0]) ? row : [row[0] + "\n" + logString]));
    });
    range.setNotes(updatedNotes);
  }
}

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.