How to fetch the row number of a Google Spread Sheet via Google Apps Script? -
i have google script using , wanting insert current row working value re added formula set in spreadsheet. want in case resubmits same data, emailed changes. @ moment script proof of concept me, can go on , code entire process.
the problem having script value currentrow. seems value of first pass , not change it, problematic when have more 1 entry process.
i use value currentrow in variable resend_email, can generate formula looks see if 2 columns alike (i going use date time stamp , copy in first time column d (also known var resend[4]).
this way can set script run automatically , check if column set "yes", in case send , email again , change value (shown here email address - use timestamp instead) formula resend_email changes resend "no".
how ger correct row number in value currentrow?
// constant written in column c rows email // has been sent successfully. var email_sent = "email_sent"; function sendemails2() { var sheet = spreadsheetapp.getactivesheet(); var startrow = 2; // first row of data process var numrows = 2; // number of rows process // fetch range of cells a2:b3 var datarange = sheet.getrange(startrow, 1, numrows, 6) // fetch values each row in range. var data = datarange.getvalues(); (var = 0; < 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 var resend = row[4]; var extension = row [5]; var currentrow = sheet.getactiverange().getrow(); var resend_email = "=if(a"+ currentrow +"=d" + currentrow +",\"no\",\"yes\")"; if (emailsent != email_sent) { // prevents sending duplicates var subject = "sending emails spreadsheet"; mailapp.sendemail(emailaddress, subject, message); sheet.getrange(startrow + i, 3).setvalue(email_sent); sheet.getrange(startrow + i, 5).setformula(resend_email); // make sure cell updated right away in case script interrupted spreadsheetapp.flush(); } if (resend == "yes") { // prevents sending duplicates var subject = "sending emails spreadsheet"; message = message + " " + extension; mailapp.sendemail(emailaddress, subject, message); sheet.getrange(startrow + i, 4).setvalue(emailaddress); // make sure cell updated right away in case script interrupted spreadsheetapp.flush(); } } }
as shown in code i variable value becomes current row.
... var currentrow = startrow + i; ...
Comments
Post a Comment