0

I have a spreadsheet containing all of the annual leave balances for each staff member. I have a form that the staff will fill out, and I would like it to pull the their data from the spreadsheet and send them an email with the result.

I am having trouble with the matching of form data to the spreadsheet data, and extracting the related cells. Here is the code:

function SendGoogleForm(e) {  
  try {      

    var subject = "Leave Balance Request";  
    var s = SpreadsheetApp.openByUrl("url").getSheetByName("Check Leave");

    var email = e.namedValues["BCA National email address"];
    var sheet =  SpreadsheetApp.openByUrl("url").getSheetByName("Leave Summary");
    var cell = sheet.getRange("F3:F3");
    var getcell = cell.getCell(1,1);
    var pdate = getcell.setValue(e.namedValues['END date of leave requested']);
    var annualvalue = sheet.getRange("H6:H").getValues();
    var emaildata = sheet.getRange("A6:A").getValues();
    var username = e.namedValues["Username"];

    var message = "Your" + " " + e.namedValues["Type of leave requested"] + " " + "balance        is below" + "\n\n";   

    if (username == emaildata)  { message += "Annual Leave" + " : " +    annualvalue               + "\n\n"; }

    MailApp.sendEmail(email, subject, message);

  } 

  catch (e) {
    Logger.log(e.toString());
  }
}

I tried this: How do I search for and find the coordinates of a row in Google Spreadsheets

But couldn't get it to work...

Any suggestions would be greatly appreciated!

Community
  • 1
  • 1
Merchant
  • 1
  • 1

1 Answers1

0

When you write var emaildata = sheet.getRange("A6:A").getValues();, you are getting an array of arrays with strings. You will need to iterate in a for loop to test each value.

Something like that should work :

var message = "Your" + " " + e.namedValues["Type of leave requested"] + " " + "balance        is below" + "\n\n";   
for(var n in emaildata){
  if (username == emaildata[n][0])  { message += "Annual Leave" + " : " +    annualvalue               + "\n\n"; }
}
MailApp.sendEmail(email, subject, message);

}

Serge insas
  • 45,904
  • 7
  • 105
  • 131
  • Hmm I got this in the log: [14-08-11 09:29:37:152 EST] Sheet.getRange([A6:A]) [0.031 seconds] [14-08-11 09:29:37:276 EST] Range.getValues() [0.123 seconds] [14-08-11 09:29:37:483 EST] MailApp.sendEmail([daniel@bcanational.com, Leave Balance Request, Your Personal Leave balance is below Annual Leave : 140.5904109589041,106.1204109589041,185.98041095890412,33.1304109589.... it goes on... I would like to pinpoint the row that the matching name is in, then provide the balance of leave... I'm stumped! – Merchant Aug 10 '14 at 23:35