0

I'm trying to make my code sending an email by referring to my google sheet data. Im using Apps Script and here is the code. However, as I run my function "sendEmail()", I got "typeError: "cannot read property "1" of undefined(line 17)".

Code line 17

var currentEmail = rows[i][1];

Here is the full code.

var ss = "1kuTkOuCd-wKTS2564oHdxALFbFo-IeyjzToYYhB6NrQ";
var SheetName = "FormResp";

function getRows(){
  var rangeName = 'FormResp!A2:E'; 
  var rows = Sheets.Spreadsheets.Values.get(ss, rangeName).values;
  return rows;
}

function sendEmails() {
  var ss_1 = SpreadsheetApp.openById(ss);
  var sheet = ss_1.getSheetByName(SheetName);
  var lr = sheet.getLastRow();
  
  for (var i = 0;i<=lr;i++){
    rows=getRows();
    var currentEmail = rows[i][1];
    var startingdate = rows[i][3];
    var endingdate = rows[i][4];
    MailApp.sendEmail(currentEmail,"Thank You for Applying Leave via Leave form: your request leave starting" + startingdate + "until" + endingdate,"Hello");
    
  }
  
}


function testgetrow(){
  var nama = getRows();
  var x = ""; 
}

I do make a test function "testgetrow()" to check my data, and I do manage to run the function without any error and I do confirm that there is values in my getRows() function.

my getRows() function working, and there is a value in the array as shown in the picture below.

there us value in 0

aiman khalid
  • 147
  • 10
  • 1
    This `for (var i = 0;i<=lr;i++)` should be `for (var i = 0;i – Cooper Jan 06 '21 at 05:30
  • Using ranges like `A2:E` is a problem because you end up with a lot of null lines at the bottom which have to be filtered out. – Cooper Jan 06 '21 at 05:48

2 Answers2

1

I suppose you can do it any way you wish but this seems a lot simpler to me.

function sendEmails() {
  const ss = SpreadsheetApp.openById("1kuTkOuCd-wKTS2564oHdxALFbFo-IeyjzToYYhB6NrQ");
  const sh = ss.getSheetByName('FormResp');
  const rg = sh.getRange(2,1,sh.getLastRow()-1,5);
  const vs=rg.getValues(); 
  vs.forEach(r=>{  
    var currentEmail = r[1];
    var startingdate = r[3];
    var endingdate = r[4];
    MailApp.sendEmail(currentEmail,"Thank You for Applying Leave via Leave form: your request leave starting" + startingdate + "until" + endingdate,"Hello");
  });
}
Cooper
  • 59,616
  • 6
  • 23
  • 54
0

Answer

The main problem is that you have not declared properly the variable rows in your line 16 rows=getRows(); because you forgot to use the keyword var. Change that line with var rows = getRows() and try it again.

Take a look

  • You are mixing SpreadsheetApp with Sheets API. I recommend you to pick one and stay there to have a clear and less confusing code.
  • Try use less functions if they are not really necessary, as @Cooper suggested, you can define the variable rows with getRange that can handle A1 notation ranges and getValues.
  • Define properly your range in A1 notation: if you write A2:E, your range goes from column A, row 2 until column E, row 1000. You have to add the number of the last row in your range, for example A2:E10.
  • With the last change, you do not have to calculate the last row, you can simply use rows.length.
  • It is not necessary to have the id of the spreadsheet if your script is a Container-bound Scripts and not a Standalone Script with getActiveSpreadsheet

I attach you a snippet of the code:

var sheetName = "FormResp";
var spreadsheet_id = "1kuTkOuCd-wKTS2564oHdxALFbFo-IeyjzToYYhB6NrQ";
var ss = SpreadsheetApp.openById(spreadsheet_id).getSheetByName(sheetName)

function sendEmails() {
  var rangeName = 'A1:B9'; 
  var rows = ss.getRange(rangeName).getValues()
  for (var i = 0; i <= rows.length; i++){
    var currentEmail = rows[i][1];
    var startingdate = rows[i][3];
    var endingdate = rows[i][4];
    MailApp.sendEmail(currentEmail,"Thank You for Applying Leave via Leave form: your request leave starting" + startingdate + "until" + endingdate,"Hello");
  }
}

I hope that it helps you!

References

fullfine
  • 1,371
  • 1
  • 4
  • 11