Javascript 编码谷歌表格脚本

发布于07月01日

我在保险公司工作，我有一个谷歌表格给我所有的同事，在那里我记录下我们与外部合作制定的所有政策.

function alertSender() {
  var today = new Date(); 
  var values = SpreadsheetApp.getActiveSheet().getDataRange().getValues();
  for(n=1;n<values.length;++n){
    var cell_date = values[n][5];
    var expired = today > cell_date;
    if (expired) {
      MailApp.sendEmail(values[n][6], 'Mail automatica', 'Servizio scaduto per il sig. ' + values[n][0]);
      Logger.log('Mail inviata all\'indirizzo ' + values[n][6] + ' del sig. ' + values[n][0]);
    }
  }
}

这是我使用的代码，其中日期(5)是F列，邮件(6)是G列.

Just execution started and execution completed, no errors but no emails, and yes i put in and expired date for test purposes

https://docs.google.com/spreadsheets/d/1JXgpU5QkV3XlhXh6VMVy_sTeDkXr_Egr9lzhfyno8O4/edit?usp=sharing

推荐答案

try

function alertSender() {
  var today = new Date();
  SpreadsheetApp.getActiveSpreadsheet().getSheets().forEach(sheet => {
    var values = sheet.getDataRange().getValues().filter(r => r[0] != '');
    for (n = 1; n < values.length; ++n) {
      var cell_date = values[n][5];
      var expired = today > cell_date;
      if (expired) {
        MailApp.sendEmail(values[n][6], 'Mail automatica', 'Servizio scaduto per il sig. ' + values[n][0]);
        Logger.log('Mail inviata all\'indirizzo ' + values[n][6] + ' del sig. ' + values[n][0]);
      }
    }
  })
}

参考文献