Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
325 views
in Technique[技术] by (71.8m points)

google apps script - Need to get variable from static to dynamic

How can I get line 4 to be dynamic here? I want it to change based off how many rows are filled out. I have a cell in the spreadsheet (a52), that has this formula =COUNTA(A4:A50). The formula correctly calculates how many rows are filled out. Thanks for help.

   function sendEmails() {
      var sheet = SpreadsheetApp.getActiveSheet();
      var startRow = 4;
      var numRows = 40; // How can I make this number dynamic, based on how many rows are filled out?
      var dataRange = sheet.getRange(startRow, 1, numRows, 20);
      var data = dataRange.getValues();
      for (var i = 0; i < data.length; ++i) {
        var row = data[i];
        var emailAddress = row[0]; // First column
        var message = 'Where you are at:'+ '
' + '
'+
    'Data1 : ' + row[8]  + '
'+
    'Data2 : ' + row[3]  + '
'+
    'Data3 : ' + row[4]  + '
'+
    'Data4 : ' + row[5]  + '
'+
    'Data5 : ' + row[6]  + '
'+
    'Data6 : ' + row[7]+ '
' + '
'+ '
';
        var emailSent = row[2];
        if (emailSent !== EMAIL_SENT) { 
          var subject = 'MTD Numbers for ' + row[2];
          MailApp.sendEmail(emailAddress, subject, message,{cc: ''});
          sheet.getRange(startRow + i, 21).setValue(EMAIL_SENT);
          SpreadsheetApp.flush();
        }
      }
    }

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

const numRows=sheet.getLastRow()-startRow+1

if you wanted numCols

numCols=sheet.getLastColumn()-startColumn+1


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...