CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Spear
Spear

Reputation: 15

Time trigger in Google Script gives an error

I'm trying to setup a time trigger for the google script to run on a schedule, but I get the following error. I'm trying to run a single script, which has 3 other scripts combined.

The script works fine when I manually run it.

Just to note, the date column in the sheet is populated using a formula and the date format is "m/dd/yyyy"

Error:

Exception: The parameters (String,String,String) don't match the method signature for Utilities.formatDate.
    at sendEmail(IntCode:54:32)
    at combine(Combine:6:3)

Line of Code (there are 3 of these, with few changes in row no., which I combine with "combine" script) - IntCode.gs

function getDataSheet() {

  sheet = SpreadsheetApp.getActiveSheet();

  startRow = 2;  // First row of data to process
  numRows = 200;   // Number of rows to process
  startCol = 1;  //First column of data to process
  numCols = 21;    // Number of columns to process 
  
  var dataRange = sheet.getRange(startRow, startCol, numRows, numCols);

  // Fetch values for each row in the Range.
  var data = dataRange.getValues();  

  return data;  
}

function sendFirstEmail() {
  var complete = "";
  var first = "Yes";
        
  var data = getDataSheet();
  
  for (var i = 0; i < data.length; i++) {
    
    var row = data[i];
    
    var isFirst = row[18];
    var isComplete = row[11];
    var user = row[0];
    var detail = row[1];
    var description = row[2];
    var period = row[3];
    var name_1 = row[4];
    var to_email_1 = row[5];
    var to_email_2 = row[6];
    var cc_email_1 = row[7];
    var cc_email_2 = row[8];
    var cc_email_3 = row[9];
    var email_1 = "email ID";
    var date = row[10];
                
    if(isFirst == first && isComplete == complete ) {
  

      var to_email_1 = row[5];
      var to_email_2 = row[6];
      var cc_email_1 = row[7];
      var cc_email_2 = row[8];
      var cc_email_3 = row[9];
      var email_1 = "email id";
      var period = row[3];
      var message = HtmlService.createHtmlOutputFromFile('FirstMail').getContent();
      var date = Utilities.formatDate(row[10], "GMT" , "dd/MMM/yyyy" );
      var file = row[12];
      
      message = message.replace("%user", user);
      message = message.replace("%detail", detail);
      message = message.replace("%description", description);
      message = message.replace("%period", period);
      message = message.replace("%name_1", name_1);
      message = message.replace("%date", date);
      message = message.replace("%file", file);
      
    MailApp.sendEmail({
      name: 'display name',
      to: to_email_1 + ", " + to_email_2,
      replyTo: email_1,
      cc: email_1 + ", " + cc_email_1 + ", " + cc_email_2 + ", " + cc_email_3,
      subject: "[TEST] " + detail + " on " + date,
      htmlBody: message
    });
      
      
    }
  }
}

The combine script (combine.gs) which I'm trying to setup with the time trigger.

function combine() {
 var ss = SpreadsheetApp.openById("google sheet ID");
// https://docs.google.com/spreadsheets/d/id_is_here/
var sh = ss.getSheetByName("Sheet1"); // name of the actual sheet ("Sheet 1" for example)  
  Logger.log('combine ran!');
  sendFirstEmail();
  sendSecondEmail();
  sendFinalEmail();
}

Thanks in advance.

Upvotes: 0

Views: 80

Answers (1)

Marios
Marios

Reputation: 27348

According to the error message you are getting, it seems that row[10] is a string while it should be a date object.

Try to replace that line with the following:

var date = Utilities.formatDate( new Date(row[10]), "GMT" , "dd/MMM/yyyy" );

Read here more about date objects.

Upvotes: 1

Related Questions