Monitor a cell in a Google Sheet

The following Google Apps Script is designed to monitor a cell within a Google Sheet to see if any text has been entered into it (such as a name for a signature) upon which it will then send an email to a specified account. The email contains a link to the Google Sheet and the value that has been entered into the cell.

It was developed as part of searching for a solution to allow a large number of student spreadsheets to be created each of which then required sign-off at some stage. Rather than administrators constantly checking a large number of files regularly however this function would allow passive monitoring so they only needed to take action once an email has been received.

Screenshot of example cell to monitor

monitorCell.gs
We start by getting the usual spreadsheet data including the Url - as we will be using this in the email to provide a link back to the relevant spreadsheet. We get the value of the cell we want to monitor so that we can determine its status (whether it is blank or contains text).

There is also a cell that will signify when the automatic email has been sent - which for the purposes of this example is on the same spreadsheet but in reality may be tucked away on another sheet. This cell is important to prevent repeat emails from being sent as we will be using a project trigger which will fire once a day (though this could be more/less frequent if you decide).

Using an if statement we ensure we only run the sendEmail Function if the cell we are monitoring is not blank/empty (so it contains a value) and if the automatic email cell is blank (so an email has not already been sent).

if ((monitorCell != '') && (sentCellValue == ' ')) {
    sendEmail(monitorCell, spreadsheetUrl);

After running the sendEmail Function we can then set the automatic email cell to the current date/time.

sentCell.setValue(new Date());

sendEmail.gs
This Function takes 2 input parameters - the value of the cell we are monitoring and the Url of the spreadsheet so that they can be incorporated into the email body. We begin by constructing the various elements of the email as required by the 'MailApp'. A try/catch is then used to prevent a script error from an invalid email address for example.

try {
    MailApp.sendEmail(recipient, subject, body, options);
}
catch(e) {
    Logger.log('Error with email: ' + e);
}

Project Trigger
The final step is to create a project trigger which runs our monitorCell Function based on a Time-driven event source that for this example I selected Day timer and to run between 8am to 9am though this could be more/less frequent as you require. In this instance it will run our Function once a day which will check the status of the cells and determine if an email needs to be sent.

	function monitorCell() {
	// get spreadsheet details
	var ss = SpreadsheetApp.getActiveSpreadsheet();
	var sheet = ss.getActiveSheet();
	var spreadsheetUrl = ss.getUrl();
	// get cell value to monitor
	var monitorCell = sheet.getRange(2, 3).getValue();
	Logger.log('Monitor cell value is: ' + monitorCell);
	// get email sent cell for writing confirmation too after emailing
	var sentCell = sheet.getRange(4, 3);
	var sentCellValue = sentCell.getValue();
	Logger.log('Sent cell value is: ' + sentCellValue);
	// check status of cells before performing next action
	if ((monitorCell != '') && (sentCellValue == '')) {
	// run Function to send email
	sendEmail(monitorCell, spreadsheetUrl);
	// update status of email sent cell
	sentCell.setValue(new Date());
	}
	else {
	Logger.log('No email will be sent');
	}
	}
	function sendEmail(monitorCell, spreadsheetUrl) {
	// construct email elements ******************************************************
	var recipient = 'noreply@example.com';
	var subject = 'Signature cell has been filled';
	var body = 'Dear Joe Bloggs' + '\n\n';
	body+= 'The following name has been added: ' + monitorCell + '\n\n';
	body+= 'Here is a link to the relevant spreadsheet: ' + spreadsheetUrl + '\n\n';
	body+= 'Thank you'
	var options = {noReply:true};
	// end of construct email elements ***********************************************
	// send email
	try {
	MailApp.sendEmail(recipient, subject, body, options);
	}
	catch(e) {
	Logger.log('Error with email: ' + e);
	}
	}

view raw Monitor a cell in a Google Sheet.gs hosted with ❤ by GitHub

Monitor a cell in a Google Sheet.xlsx