Get a Google Sheet script to skip empty rows?

Question

I have a script that sends e-mails from a google sheet. The data in the sheet is populated with a google form. A few fields in the sheet use array formulas to add additional data to the sheet example: array formulas in the sheet to populate fields:

=ArrayFormula(IF(ROW(A:A)=1,"link",VLOOKUP($C:C,array!$A$2:$C$9000,3,FALSE)))

This causes "#N/A" to show in rows where no user has submitted data. I am ok with that but it is causing the script to take a long time to run. When the user submits data, a timestamp is placed in the first column. Can I use that to get the script to skip to stop if empty? Right now, I have the script checking 1000 rows but ideally it would check rows for timestamp and if no timestamp, stop.

/**
 * Add a menu item that calls this script
 */
function onOpen() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();

  var menu = [{
    name: "Send Email",
    functionName: "uiSendEmail"
  }];

  ss.addMenu("SCCCounseling", menu);

}
var EMAIL_SENT = "EMAIL_SENT";
/**
 * Send the e-mail to everyone listed in the spreadsheet
 */

function uiSendEmail() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var startRow = 2;  // First row of data to process
  var numRows = 1000;
  var dataRange = sheet.getRange(startRow, 1, numRows, 1000)
  // Fetch values for each row in the Range.
  var data = dataRange.getValues();
  for (var i = 0; i < data.length; ++i) {

    var row = data[i];

    var timestamp = row[0];

    var username = row[1];

    var counselor = row[2];

    var date = row[3];

    var sName = row[4];

    var wNum = row[5];

    var toemail = row[6];

    var sLName= row[7];

    var pin = row[8];

    var link = row[9];

    var cMail = row[10];

    var emailSent = row[11]; 

    var subject = "Online Counseling Appointment Confirmation - " + sName + " " + sLName + " " + wNum + " ";

    var emailBody = "Online Counseling Appointment " + " - " + sName + " " + sLName + " " + wNum + 

    "\n\nHello " + sName +

    "\n\n\nYou are scheduled for an online counseling appointment for Sacramento City College: " +

    "\n\n\nDate: " + date +

    "\n\nPlease follow the instructions below at least one day before your appointment to make " +

    "\nsure that your computer is ready. " +

    "\n\nIf your computer is not ready, you will not be able to " +

    "\nparticipate in online counseling. " +

    "\n\nTo cancel your appointment, please email: " +

    "\n\nCounselor: " + counselor + " at " + cMail +

    "\n\nYou will be required to show photo identification at the start of the appointment to verify " +

    "\nyour identity. " +

    "\n\n\nONE DAY BEFORE YOUR APPOINTMENT " +

    "\n\nGo to the following link to test your computer readiness one day before your " +

    "\nscheduled online counseling appointment: " +

    "\n\nhttp://www.cccconfer.org/support/supportReadiness.aspx " +

    "\n\n\nFIVE MINUTES BEFORE YOUR APPOINTMENT: " +

    "\n\nGo to the following link to connect to the online counseling appointment and the " +

    "\ncounselor will join you: " + link +

    "\n\n\nIf you want to use the phone for audio: " +

    "\n\nTelephone Conference Line: 719-785-4469, Toll-free: 1-888-450-4821 " +

    "\n\nStudent PIN: " + pin +

    "\n\n\nFOR ASSISTANCE CONNECTING" + 

    "\n\nContact CCC Confer Client Services, Monday - Friday 8am - 4pm  " +

    "\n\nPhone: 1-760-744-1150 ext 1537 or 1554 " +

    "\n\nEmail: [email protected] " +

    "\n\n\nThank you " + sName;





    var htmlBody = "<b>Online Counseling Appointment Confirmation" + " -</b> " + " - " + sName +  " " + sLName + " " + wNum +
      "<br/><br/><br/>" + "Hello " + sName + ", <br/><br/>" + "You are scheduled for an online counseling appointment for Sacramento City College: <i>" + 
      "<br/><br/><br/>" + "<b>Date: " + date + "<br/><br/>"  + "</b><br/>" + "</i><br/>Please follow the instructions below <b>at least one day before</b> your appointment to make sure that your computer is ready." +
      "<br/><br/><i>If your computer is not ready, you will not be able to participate in online counseling.</i> " + "<br/><br/> To cancel your appointment, please email: " + 
      "<br/><br/><font color=\"red\">Counselor:</font> " + counselor + " at " + cMail + "<br/><br/><br/>You will be required to show photo identification at the start of the appointment to verify your identity. " +
      "<br/><br/><br/><b>ONE DAY BEFORE YOUR APPOINTMENT:</b> " + "<br/><br/>➢  Go to the following link to test your computer readiness one day before your scheduled online counseling appointment: " +
      "<br/><br/>http://www.cccconfer.org/support/supportReadiness.aspx " +
      "<br/><br/><br/><b>FIVE MINUTES BEFORE YOUR APPOINTMENT:</b>" +
      "<br/><br/>➢  Go to the following link to connect to the online counseling appointment and the counselor will join you (link to counselor online room). " +
      "<br/><br/>  " + link +
      "<br/><br/>➢  If you want to use the phone for audio: " +
      "<br/><ul><li>Telephone Conference Line: 719-785-4469, Toll-free: 1-888-450-4821</li><li>Student PIN: " + pin + "</li></ul>" +
      "<br/><br/><br/><b>FOR ASSISTANCE CONNECTING</b>" + 
      "<br/><br/>Contact CCC Confer Client Services, Monday - Friday 8am - 4pm  " + 
      "<br/><br/>Phone: 1-760-744-1150 ext 1537 or 1554 " +
      "<br/><br/>Email: [email protected] " +
      "<br/><br/><br/>Thank You! " + sName;

      var optAdvancedArgs = {name: "SCC Counseling", htmlBody: htmlBody};


    if (emailSent != EMAIL_SENT) {  // Prevents sending duplicates
      var subject = "Online Counseling Appointment Confirmation - " + sName + " " + wNum + " ";
    MailApp.sendEmail(toemail, subject, emailBody, optAdvancedArgs);
  sheet.getRange(startRow + i, 12).setValue(EMAIL_SENT);
      // Make sure the cell is updated right away in case the script is interrupted
  SpreadsheetApp.flush();
  }
}
}

Your getRange(startRow, 1, numRows, 1000) is getting 1,000 columns. Is that really what you want? — Alan Wells

Alan Wells Alan Wells · Accepted Answer · 2015-03-10T04:47:01

Create a simple function to test for #NA!:

function checkForNa() {
  var theVal = SpreadsheetApp.getActiveSpreadsheet().getRange("A1").getValue();

  if (theVal === "#NA!") {
    Logger.log('it's NA');
    return;
  };

  Logger.log('There is data. The code continued');
};

Intentionally cause an #NA! error, and run the code.

If the code works, incorporate the conditional check into your code. The return; statement will stop your code.

If you don't want your code to stop, but to skip this row, a break; statement can be used, but the break; can only be used inside of a loop. So, you'll need a nested loop to check for the #NA! value, to skip over that one line.

function checkForNa() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var startRow = 2;  // First row of data to process
  var numRows = 1000;
  var dataRange = sheet.getRange(startRow, 2, numRows, 1000)
  var data = dataRange.getValues();

  for (var i = 0; i < data.length; ++i) {
    var theVal = data[i][0]; //Get first value out of this row

    for (var j = 0;j<1;j++) { //Only loops once
      if (theVal === "#NA!") {
        Logger.log('skipped this row: ' + i);
        break;
      };
      Logger.log('loop number: ' + i);
    };
  };
};

This is example code. You'll need to adapt it to your code.

Get a Google Sheet script to skip empty rows?

2 Answers