Making User Data Range Dynamics



  • I'm a teacher with a spreadsheet of students. Each class period is it's own sheet (tab).
    The students earn points and I've used codingislove's javascript to create a script to send the students the points they've earned. As instructed in this post:

    https://codingislove.com/send-email-google-sheets/

    I got it to work with a specific cell range but I can't get it to work dynamically. Which is very much needed as students get added and dropped from the course on a daily basis.

    //function CustomEmail() {
    2
    var sheet = SpreadsheetApp.getActiveSheet();
    3
    var range = sheet.getRange("A2:G28");
    4
    var UserData = range.getValues();
    5
    for (i in UserData) {
    6
    var row = UserData[i];
    7
    var name = row[1];
    8
    var email = row[5];
    9
    var total = row[6];
    10
    if( total > 0 )
    11
    MailApp.sendEmail(row[5], "Custom mail", "Hello " + name + ", Congratulations you have earned the following professional and citizenship points in Graphic Design I" + total);
    12
    }
    13
    }//

    According to the codingislove I "can add more functionality like making user data range dynamic using getLastRow() method of spreadsheetApp so that when a new user row is added, email will be sent to that user also without changing any code."

    This is very handy since each sheet/tab has a different number of students. But I can't seem to figure out where in the code I need to put getLastRow() it seems wherever I put it, I get a code error. I've tried numerous places. I've tried every logical variable I could think of to make it work. I went to codeacademy to try and learn Javascript but I fear it will take me 40 hours before I can find what I'm doing wrong. Any help would be appreciated.

    //function CustomEmail() {
    2
    var sheet = SpreadsheetApp.getActiveSheet().getLastRow();
    3
    var range = sheet.getRange();
    4
    var UserData = range.getValues();
    5
    for (i in UserData) {
    6
    var row = UserData[i];
    7
    var name = row[1];
    8
    var email = row[5];
    9
    var total = row[6];
    10
    if( total > 0 )
    11
    MailApp.sendEmail(row[5], "Custom mail", "Hello " + name + ", Congratulations you have earned the following professional and citizenship points in Graphic Design I" + total);
    12
    }
    13
    }//


Log in to reply
 

Looks like your connection to Codingislove Forum was lost, please wait while we try to reconnect.