asked on

Google Apps Script to test for X and mail IF

I have a Google Form where I'm taking reservations for an speaking event. I have 50 seats that I can fill and setup a script to warn me just before I reach capacity for one of the 12 days the event is being held. I have a field in the sheet that is FALSE until it sees 48 reservations for a day then it turns TRUE.

I am having problems with the Google Apps Script that is supposed to email me.

Here is the code.

function capacityAlert() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("counts");
  var currentValue = sheet.getRange("D2:D13").getValues();
  Logger.log('currentValue = '+currentValue);
  var nRows = currentValue.length;
  for(var i=0;i<nRows;i++)
  Logger.log(currentValue[i][0]);
  {
    Logger.log('row '+i+' col[0] = '+currentValue[i][0]);
    if (currentValue[i][0]=="true");
    
  {
   MailApp.sendEmail("dave@mydomain.com","Talks Room Capacity Reached","A date within the next round of Talks has reached capacity, " +
                     "\nplease check the sheet and remove the date.");
  }  
}
}

Open in new window

I am getting an error that says:

TypeError: Cannot read property "0" from undefined. (line 9)

and here is the log output.

currentValue = false,false,false,false,false,false,false,false,false,false,false,false
false
false
false
false
false
false
false
false
false
false
false
false

What am I missing?

leakim971

you put the log after the for loop so the bracket is useless (not attached to the loop)
try this :

function capacityAlert() {
	var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("counts");
	var currentValue = sheet.getRange("D2:D13").getValues();
	Logger.log('currentValue = '+currentValue);
	var nRows = currentValue.length;
	for(var i=0;i<nRows;i++)
	{
		//Logger.log(currentValue[i][0]);
		Logger.log('row '+i+' col[0] = '+currentValue[i][0]);
		if(currentValue[i][0]=="true")
		{
			MailApp.sendEmail("dave@mydomain.com","Talks Room Capacity Reached","A date within the next round of Talks has reached capacity, \nplease check the sheet and remove the date.");
		}  
	}
}

Open in new window

hrhs

ASKER

For some reason this script isn't working, even moving the bracket? It's a by the book example of loop and the logs show

currentValue = false,false,false,false,false,false,false,false,false,false,false,true
row 0 col[0] = false
row 1 col[0] = false
row 2 col[0] = false
row 3 col[0] = false
row 4 col[0] = false
row 5 col[0] = false
row 6 col[0] = false
row 7 col[0] = false
row 8 col[0] = false
row 9 col[0] = false
row 10 col[0] = false
row 11 col[0] = true

I have a correct email in my script on my end and I've tried creating a popup instead of emailing. I've tested popups with a 3 line script and they're working.

Any idea?

leakim971

Line 10 you've == 'true,
But all your cell return 'false,

Wrong column? You're using [0],the first one
Wrong sheet?

ASKER CERTIFIED SOLUTION

hrhs

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

leakim971

Replace :