I have this code in Google Apps Script that is linked to a Google Sheet:
function sendReminders() { var sheet = SpreadsheetApp.openById('1IvXctYLXMKrSYPPSuMg_4_AQopemHPSnLHmQQgBDqH8').getSheetByName('ÉVALUATION'); var startRow = 2; var numRows = sheet.getLastRow(); var numColumns = sheet.getLastColumn(); var dataRange = sheet.getRange(startRow, 1, numRows, numColumns); var data = dataRange.getValues(); for (var i in data) { var row = data[i]; var email = row[4]; var ccEmail = row[5]; var customId = row[1]; var dueDate = Utilities.formatDate(new Date(row[9]), Session.getScriptTimeZone(), "yyyy-MM-dd"); var message = 'Votre E&A est dûe pour la date suivante : ' + dueDate + '. Merci de la compléter en cliquant sur le lien suivant : https://form.jotform.com/221156384847058?customId3=' + customId; var emailSent = row[12]; var value = Utilities.formatDate(new Date(row[10]), Session.getScriptTimeZone(), "yyyy-MM-dd"); var today = Utilities.formatDate(new Date(), Session.getScriptTimeZone(), "yyyy-MM-dd"); if (value == today && emailSent != 1) { var subject = 'Rappel - Enquête & Analyse'; MailApp.sendEmail(email, subject, message, { cc: ccEmail }); sheet.getRange((startRow + i), 13).setValue(1); SpreadsheetApp.flush(); } } }
The script is supposed to send reminder emails. Everything works well, except this one line:
sheet.getRange((startRow + i), 13).setValue(1);
This line should add the value of 1 to a specific cell in the Google Sheet, in the current row the for loop is at.
However, instead of adding the value to the current row, it skips rows and adds the value to some other row. In some cases, there’s a difference of 18 rows, and in others, 198 rows.
Here is the Google Sheet link for reference.
Advertisement
Answer
I noticed some issues in your sheet and script.
In your sample sheet, you have values on M22, M27 and M210. The function
sheet.getLastRow()
will include those values and your last row will be 210.If you print the value of data, there is an extra empty array.
If you try to print
startRow + i
, the values are22, 23, 24...etc
. It just appends the value ofstartRow
andi
and not doing the arithmetic operation sum.
Solution:
Remove the unnecessary data or clean your sheet so that the value of
numRows
will become 12.Since you skip 1 row, you should also deduct 1 row to numRow. Simple add -1 to numRows in your
getRange()
.Use the
parseInt()
method to variablei
insheet.getRange((startRow + i), 13).setValue(1);
.
Your code should look like this:
function sendReminders() { var sheet = SpreadsheetApp.openById('1IvXctYLXMKrSYPPSuMg_4_AQopemHPSnLHmQQgBDqH8').getSheetByName('ÉVALUATION'); var startRow = 2; var numRows = sheet.getLastRow(); var numColumns = sheet.getLastColumn(); var dataRange = sheet.getRange(startRow, 1, numRows-1, numColumns); var data = dataRange.getValues(); for (var i in data) { var row = data[i]; var email = row[4]; var ccEmail = row[5]; var customId = row[1]; var dueDate = Utilities.formatDate(new Date(row[9]), Session.getScriptTimeZone(), "yyyy-MM-dd"); var message = 'Votre E&A est dûe pour la date suivante : ' + dueDate + '. Merci de la compléter en cliquant sur le lien suivant : https://form.jotform.com/221156384847058?customId3=' + customId; var emailSent = row[12]; var value = Utilities.formatDate(new Date(row[10]), Session.getScriptTimeZone(), "yyyy-MM-dd"); var today = Utilities.formatDate(new Date(), Session.getScriptTimeZone(), "yyyy-MM-dd"); if (value == today && emailSent != 1) { var subject = 'Rappel - Enquête & Analyse'; MailApp.sendEmail(email, subject, message, { cc: ccEmail }); sheet.getRange((startRow + parseInt(i)), 13).setValue(1); SpreadsheetApp.flush(); } } }
Sample:
Note: I adjusted the Reminder Date values to my current date so that the script will work.
Output: