@JPV helped me create this awesome app script that pulls specific data from a spreadsheet and e-mails it to me. I have been trying to add functionality where it would take the data that it is emailing and also add it to the next row of a specific spreadsheet Here it is. The new spreadsheet has the same headings for the columns that the email message has:
Assessment score, StudentId, Name, HR, Teacher, Grade, Race, G, Ed, AVG
I have been searching/messing with this for days and I know that it's easy for any of the guys on this forum to do that aren't beginners like me. I know that after the message portion of this code, I have to open the target spreadsheet, find the last row, add a row, and move identified cell values over to the new row, but it never works. :( I'd post the code I've been playing with, but it's not even worth it honestly. Any help or ideas that you guys have would be greatly appreciated. Thanks so much and happy holidays! Brandon
function assessmentAlert() {
SpreadsheetApp.getActive().getSheets()
.forEach( function (s) {
var val = s.getDataRange().getValues();
//check to see if correct cells where found ==> check the log when the script finishes
var count = 0;
//start looping through the values, first loop = rows, second loop = columns
for (var i = 0, ilen = val.length; i < ilen; i++) {
for (var j = 0, jlen = val[0].length; j < jlen; j++) {
//conditions: column > 8, row > 2, value should be numeric and < 0.6 and the headerrow should not have 'Sent'
if (j > 8 && i >2 && !isNaN(parseFloat(val[i][j])) && val[i][j] < 0.6 && val[0][j] != 'Sent') {
count += 1
//if all conditions are met, send the email
var message = 'Assessment score: ' + Math.round((val[i][j] * 100) * 10) / 10 + ' %' +
'\nStudentId: ' + val[i][0] +
'\nName: ' + val[i][1] +
'\nHR: ' + val[i][2] +
'\nTeacher: ' + val[i][3] +
'\nGrade: ' + val[i][4] +
'\nRace: ' + val[i][5] +
'\nG: ' + val[i][6] +
'\nEd: ' + val[i][7] +
'\nAVG: ' + Math.round((val[i][8] * 100) * 10) / 10 + ' %';
var emailAddress = 'email@email.com'; // email details
var subject = 'ALERT - Assessment score below 60% inputted.';
MailApp.sendEmail(emailAddress, subject, message);
s.getRange(1, j+1).setValue('Sent').setFontColor('Red');
}
}
}
Logger.log('number of values found:' + count);
});
}
Aucun commentaire:
Enregistrer un commentaire