dimanche 17 mai 2020

ERROR : Unexpected error while getting the method or property > getFileById on object DriveApp

Hello i'm trying to send emails with attachments with data from a google sheet. Although the code does its job i get this error.

Exception: Unexpected error while getting the method or property getFileById on object DriveApp. (line 79, file "Code")Dismiss

The strange thing is that when i change the if statement

if (num !="" && IsEmailsent != EmailSent && fileID != "")

to

if (num == 300 && IsEmailsent != EmailSent && fileID != "")

i don't get the above error which refers to this line of code

var attachment1 = DriveApp.getFileById(fileID);

i assume its got something to do with a wrong value of fileID passing through or something to do with the loop? i tried to convert the number from the sheets DesNumb to a Number with

var num =(+DesNumb);

but i still get the error

What can i do to eliminate this error?

Thankyou

 function getDataSheet() {
sheet = SpreadsheetApp.getActiveSheet();

var startRow = 2; // First row of data to process
var numRows = 500; // Number of rows to process
var startCol = 1; //First column of data to process
var numCols = 33; // 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 getMessage(DesNumb, CaseNumb, CCaseDate) {
return "this " + CaseNumb +  "this " + CCaseDate + " this " + DesNumb + "/2020";
;} 

function sendEmail() {

    var EmailSent = 'YES' ;
   // var Dnumber = 0 ;
    var emailCol1 = 32;
  // var emailCol2 = 27;  
   // var prop2 = 'TRUE';


var data = getDataSheet();

  for (var i = 0; i < data.length; i++) {

    var row = data[i];

    var DesNumb = row[1];
    var CaseNumb = row[3];
    var DesDate = row[4];
    var CaseDate = row[5];
    var name1 = row[6];
    var name2 = row[7];
    var CaseType = row[12];
    var Desision = row[23];
    var Judge = row[24];
    var email1 = row[26];
    var email2 = row[27];
    var prop1 = row[29];
    var prop2 = row[30];
    var IsEmailsent = row[31];
    var fileID = row[32];

    /*
    To ensure date objects are written to a spreadsheet as you expect,
    set the spreadsheet's timezone to match your Google Ads account's timezone:

    spreadsheet.setSpreadsheetTimeZone(AdsApp.currentAccount().getTimeZone());
    */

   //convert time stamp format date to ex. 15/01/2007
    var DateConv = new Date(CaseDate);
    var CCaseDate = Utilities.formatDate(DateConv,'Europe/Athens', 'dd/MM/yyyy');
    /*
    var substring1 = s.getRange("A1").getValue();
    if (!isNaN(parseFloat(substring1)) && isFinite(substring1)) {
        s.getRange("B1").setValue("Is a number");
    } else {
        s.getRange("B1").setValue("Not a number");
    }
    */
    //var numAsNumber = Number(num);

    var num =(+DesNumb);


 if (num !="" && IsEmailsent != EmailSent &&  fileID != "")  {

      //send 1st email

      var subject = "this # " + DesNumb + "/2020";
      var message = getMessage(DesNumb, CaseNumb, CCaseDate);
      var recipientEmail1 = row[26];
      var attachment1 = DriveApp.getFileById(fileID);
      MailApp.sendEmail(recipientEmail1, subject, message,{
                        attachments:[attachment1.getAs(MimeType.PDF)],
                        name: 'Automatic Emailer Script',
                        });




      //Sheet range starts from index 1 and data range starts from index 0
        sheet.getRange(2 + i, emailCol1).setValue(EmailSent);
        SpreadsheetApp.flush();
      /*
      //send 2nd email
      if((DesNumb >0)  && (IsEmailsent != EmailSent ) && (prop2 = 'TRUE'))
      var subject = "This # " + DesNumb + "/2020";
      var message = getMessage(DesNumb, CaseNumb, CCasedate);
      var recipientEmail2 = row[27];
      var attachment2 = DriveApp.getFileById(fileID);
      MailApp.sendEmail(recipientEmail2, subject, message,
                        {name: 'Automatic Emailer Script',
                         attachments: [attachment2.getAs(MimeType.PDF)]
                        });

      //Sheet range starts from index 1 and data range starts from index 0
        sheet.getRange(2 + i, emailCol1).setValue(EmailSent);
        SpreadsheetApp.flush();
      */

    }
  }
}

Aucun commentaire:

Enregistrer un commentaire