CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
Stephen J Skrumf
Stephen J Skrumf

Reputation: 1

Automatically detect date when auto send emails in sheets

I'm currently trying to update the sample code that comes with the google auto email, which is here: Sending emails from a Spreadsheet. I can't share the details of my spreadsheet, but I would like it to automatically detect the date, rather than having to go into the script and change the week number each time. Here is the sample script: 

/**
 * Sends emails with data from the current spreadsheet.
 */
function sendEmails() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var startRow = 2; // First row of data to process
  var numRows = 2; // Number of rows to process
  // Fetch the range of cells A2:B3
  var dataRange = sheet.getRange(startRow, 1, numRows, 2);
  // Fetch values for each row in the Range.
  var data = dataRange.getValues();
  for (var i in data) {
    var row = data[i];
    var emailAddress = row[0]; // First column
    var message = row[1]; // Second column
    var subject = 'Sending emails from a Spreadsheet';
    MailApp.sendEmail(emailAddress, subject, message);
  }
}

I have a row in my sheets spreadsheet that is the week number called Week1 which is in F1 and it goes up to week 13 which is R1. In the rows that go down, I have the days of the week. In my code current form, I have to go in manually and change this line that has the week number in it below: 

var upcomingWeekNumber = Week13;  // EDIT THIS PARAMETER BEFORE SENDING
var upcomingEquipmentList = Week13;  //  EDIT THIS PARAMETER BEFORE SENDING

I would like the code to automatically know what the date is when I send the email, is there a way to do this by just editing the original sample code from the tutorial?

Upvotes: 0

Views: 61

Answers (1)

ziganotschka
ziganotschka

Reputation: 26796

You can get get todays date with the javacsript method new Date()

If you want to know the current calendar week, you can perform a simple calculation, sample:

function myFunction() {
  var now = new Date();
  var start = new Date(now.getFullYear(), 0, 1);
  var calendarweek = Math.ceil( (((now - start) / 86400000) + start.getDay() + 1) / 7 );
  Logger.log(calendarweek);
}

Upvotes: 1

Related Questions