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