在Google表格脚本编辑器中比较日期 [英] Comparing Dates in Google Sheets Script Editor

问题描述

我对此很陌生，所以请多多包涵.我正在尝试让我的代码查看列中的每个单元格，然后将该日期与当前日期进行比较，如果它们匹配，则发送电子邮件.我知道我需要某种循环才能使它遍历该专栏，但我还没走那么远.我尝试了所有可以在网上找到的方法，只是将其与日期比较一个单元格并发送电子邮件.我在调整电子邮件功能以比较日期之前对其进行了测试，因此我知道它可以正常工作.放东西肯定不起作用...

I am very new to this, so please bear with me. I am trying to get my code to look at each cell in a column, then compare that date to the current date, and if they match send an email. I am aware I will need a loop of some sort to get it to look through the column, but I haven't even gotten that far. I've tried every method I can find online to just get it to compare one cell to the date and send the email. I tested the email function prior to adjusting it to compare the date, so I know that is working. Put something definitely isn't working...

function sendEmail() {
  //Fetch the date
  var removalDateRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Expirations").getRange("E2");
  var removalDate = removalDateRange.getValue();
 
  var currentDateRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Alerts").getRange("C2");
  var currentDate = new Date();
  
  var ui = SpreadsheetApp.getUi();
  
      //Check Date
      if (removalDate == currentDate) {
        
          //Fetch the email address
          var emailRange =
           SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Alerts").getRange("B2");
           var emailAddress = emailRange.getValue();
        
         //Fetch Item Brand
         var itemBrandRange =
          SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Expirations").getRange("B2");
          var itemBrand = itemBrandRange.getValue();
        
         //Fetch Item Name
         var itemNameRange =
          SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Expirations").getRange("A2");
          var itemName = itemNameRange.getValue();
        
         //Fetch Item Location
         var itemLocationRange =
          SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Expirations").getRange("H2");
          var itemLocation = itemLocationRange.getValue();
        
         // Send Alert Email
         var message = 'The ' + itemBrand + ' ' + itemName + ' in ' + itemLocation + ' will expire in 2 months. Please use and replace item.';
         
         // Second Column
         var subject = 'Pantry Alert';
            MailApp.sendEmail(emailAddress, subject, message);
    }
  }

编辑

好吧，我昨晚让它工作了，甚至让它循环播放，不知何故我又把它弄坏了.数小时来，我一直在寻找答案，以尝试调整其功能以使其正常工作.我在做什么错了?

Okay, I had it working late last night and even got it to loop through, and somehow I've broken it again. I've been looking at answers for hours trying to adjust things to make it work. What am I doing wrong?

function emailAlert() {
  
  // today's date information
  var today = new Date();
  var todayMonth = today.getMonth() + 1;
  var todayDay = today.getDate();
  var todayYear = today.getFullYear();

  // 2 months from now
  var oneMonthFromToday = new Date(todayYear, todayMonth, todayDay);
  var oneMonthMonth = oneMonthFromToday.getMonth() + 2;
  var oneMonthDay = oneMonthFromToday.getDate();
  var oneMonthYear = oneMonthFromToday.getYear();

  // getting data from spreadsheet
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Exp");
  var startRow = 2; // First row of data to process
  var numRows = 500; // Number of rows to process

  var dataRange = sheet.getRange(startRow, 1, numRows, 999);
  var data = dataRange.getValues();

  //looping through all of the rows
  for (var i = 0; i < data.length; ++i) {
    var row = data[i];    
    
    
    var expireDateFormat = Utilities.formatDate(new Date(row[5]),
      'ET',
      'MM/dd/yyyy'
    );

    //email Information
    var subject = 'Pantry Item Needs Attention!';
    var message1 =
      row[6] + ' ' + row[3] + ' of ' + row[2] + ' ' + row[1] + ' will expire on ' + expireDateFormat + '. Item can be found in ' + row[7]
       + '. Please Remove and Replace Item.' + 
      '\n' + 'Thanks Steve!';
      
    var message2 =
      row[6] + ' ' + row[3] + ' of ' + row[2] + ' ' + row[1] + ' will expire on ' + expireDateFormat + '. Item can be found in ' + row[7] + 
      '. Please ensure item has been replaced, removed from the pantry, and deleted from inventory.' +
      '\n' + 'Thanks Steve!'

    //expiration date information
    var expireDateMonth = new Date(row[5]).getMonth() + 1;
    var expireDateDay = new Date(row[5]).getDate();
    var expireDateYear = new Date(row[5]).getYear();

    //checking for today
    if (
      expireDateMonth === todayMonth &&
      expireDateDay === todayDay &&
      expireDateYear === todayYear
    ) {
     
       ui.alert(message1);
    }
   }
  }

推荐答案

使用日期可能会令人沮丧，因此请考虑首先使用整数完成整个操作(循环，如果则声明...)，然后返回到整数.如果遇到麻烦，请约会.也就是说，请尝试将代码的顶部调整为如下所示:

Dates can be frustrating to work with, so consider doing the whole thing (loop, if then statement...) with an integer first and then returning to the date part if you're having trouble. That said, try adjusting the top of your code to look like the following:

var ss =SpreadsheetApp.getActiveSpreadsheet();
var removalDateVal = ss.getSheetByName("Expirations").getRange("E2").getValue();
var removalDate = new Date(removalDateVal);

var currentDateVal = ss.getSheetByName("Alerts").getRange("C2").getValue();
var currentDate = new Date(currentDateVal);

这将为您提供两个日期对象.但是要当心！这些日期既包含时间，又包含日历日期，因此即使它们看起来一样也可能彼此不相等.将 setHours ()设置为零列出如下所示的日期.

That will give you two date objects. But BEWARE! These dates contain time as well as calendar date so they may not equal each other even when they appear to. Use setHours() to zero out the date as seen below.

currentDate.setHours(0,0,0);
removalDate.setHours(0,0,0);

其他说明，最佳做法是为电子表格和工作表设置变量，如

Other notes, it's best practice to set a variable for a spreadsheet and worksheet as shown by Google here. It makes the code much more readable.

这篇关于在Google表格脚本编辑器中比较日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！