How to get A2:A length using google scripts

0
votes

I need to send an email from my sheets based on the first Column inputs (email ID's) and (which is dynamic row and gets updated based on the time). how can I return only A2:A length using the google script. Also, how can I do it in excel as well?

    var formattedDate = Utilities.formatDate(new Date(), "GMT-6", "'Date: ' yyyy-MM-dd ' Time: ' HH:mm:ss ' CDT'");

var EMAIL_SENT = 'Email Success ! '+ "\n\n" + formattedDate; 



/**
 * Sends non-duplicate emails with data from the current spreadsheet.
 */function sendEmails2() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Script (Beta)").activate();


  var startRow = 2; // First row of data to process
  var numRows = 120; // Number of rows to process

  // Fetch the range of cells 'A' columns

  var dataRange = sheet.getRange(startRow, 1, numRows, 3);
  // 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 emailAddress = row[0]; // First column
    var message = row[1]; // Second column
    var emailSent = row[2]; // Third column


    if (emailSent !== EMAIL_SENT) { // Prevents sending duplicates

      var subject = '[Auto] The Process Has Not Yet Been Started';

      if(emailSent =='')
      break;

      MailApp.sendEmail(emailAddress, subject, message, {htmlBody: message,
                                                         cc: '[email protected]',
                                                         bcc:'[email protected]'});

      sheet.getRange(startRow + i, 3).setValue(EMAIL_SENT);

      // Make sure the cell is updated right away in case the script is interrupted
      Logger.log(sendEmails2);
      SpreadsheetApp.flush();
    }}}
2
google-apps-scriptgoogle-sheets

2 Answers

1
votes
function A2A(col,sh,ss) {
  var ss=ss || SpreadsheetApp.getActive();
  var sh=sh || ss.getActiveSheet();
  var col=col || 1;
  return sh.getRange(2,col,sh.getLastRow()-1,1).getValues().filter(String).length;
}
0
votes

To get the last row of the 'A' column you can get first all the values of the row:

var dataRange = sheet.getRange('A:A').getValues();

getValues() returns a two-dimensional array of values, indexed by row, then by column

Since we have already set the column to be A we will need only one for to loop through the array:

  var lastDataPosition = 0; // Create variable to set the last row of the column that has data

  for (var i = 0; i < dataRange.length; ++i) {
    if (dataRange[i] != '') { // Check if the array position has data
      lastDataPosition = i + 1 // If the position has data in it -> assign that position to 'lastDataPosition' as the last one
    }
  }

*We add +1 to the array position since the first cell of the sheet is 1 and the first position of the array is 0

This is how you get the last row with data even if there are some blank cells in the middle.

For excel, I recommend you to create a new question with the proper tags.