Automating Google Spreadsheets – Email Reminders

Question

I have a Google Spreadsheet and have modified the code below as indicated to send an email alert 7 days before the date condition is due. This works perfectly, however it only looks at the first sheet and the spreadsheet contains 23 sheets in total. I think I need to include an array and loop the code but cannot work this out so would appreciate some help! Thanks in advance.

function checkReminder() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  // set the first sheet as active
  SpreadsheetApp.setActiveSheet(spreadsheet.getSheets()[0]);
  // fetch this sheet
  var sheet = spreadsheet.getActiveSheet();

  // figure out what the last row is
  var lastRow = sheet.getLastRow();

  // the rows are indexed starting at 1, and the first row
  // is the headers, so start with row 2
  var startRow = 7;

  // grab column 14 (the 'days left' column) 
  var range = sheet.getRange(7, 14, lastRow - startRow + 1, 1);
  var numRows = range.getNumRows();
  var days_left_values = range.getValues();

  // Now, grab the reminder name column
  range = sheet.getRange(7, 15, lastRow - startRow + 1, 1);
  var reminder_info_values = range.getValues();

  // Now, grab the deceased name column
  range = sheet.getRange(3, 1);
  var name_of_sheet = range.getValue();

  var warning_count = 0;
  var msg = "";

  // Loop over the days left values
  for (var i = 0; i <= numRows - 1; i++) {
    var days_left = days_left_values[i][0];
    if (days_left == 7) {
      // if it's exactly 7, do something with the data.
      var reminder_name = reminder_info_values[i][0];

      msg = msg + "Reminder: " + name_of_sheet + " inscription work is due in " + days_left + " days.\n";
      warning_count++;
    }
  }

  if(warning_count) {
    MailApp.sendEmail("my@email.com", 
        "Reminder Inscription Schedule Message", msg);
  }
};

Your thought is correct. There is a class method that will return all sheets in the spreadsheet. You can then combine that array of `Sheet`s with a for loop. Note that your script uses it, but purposefully selects only the first sheet in an awkward and unnecessary activation. — tehhowch, Sep 25 '18 at 12:47
Thanks, I'm very new to this so could you provide an example? Much appreciated! — onthenoggin, Sep 25 '18 at 13:41
Use the Apps Script documentation (internet search or Help menu in Script Editor) and teach yourself the code you are already using. For every line, tell an inanimate object near you exactly what that line does, what types and values each parameter any functions take, and what (if any) those functions return back to the caller. Become familiar with JavaScript `for` loops (use internet search again). This is not a website for receiving tutorials or links to tutorials. — tehhowch, Sep 25 '18 at 14:02
I've already tried that, and still couldn't resolve it, which is why I ended up asking here. I will keep trying. Thanks anyway. — onthenoggin, Sep 25 '18 at 14:31
Ive sussed it - tough love works! var ss = SpreadsheetApp.getActive(); var allsheets = ss.getSheets(); for (var s in allsheets){ var sheet=allsheets[s] — onthenoggin, Sep 25 '18 at 14:58
Great! One caveat (for the future: https://stackoverflow.com/questions/500504/why-is-using-for-in-with-array-iteration-a-bad-idea) If you feel your solution will be useful to others, please [write a good answer](https://stackoverflow.com/help/how-to-answer) and accept it (when the self-answer limit expires) — tehhowch, Sep 25 '18 at 15:02

Automating Google Spreadsheets – Email Reminders

0 Answers0