Question

0

65

Vistas

setValue function skipping rows instead of choosing current in for loop

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.

7 months ago · Juan Pablo Isaza

2 Respuestas

Responde la pregunta

0

Try it this way:

function sendReminders() {
  const ss = SpreadsheetApp.openById('1IvXctYLXMKrSYPPSuMg_4_AQopemHPSnLHmQQgBDqH8');
  var sh = ss.getSheetByName('ÉVALUATION');
  var startRow = 2;
  var dataRange = sh.getRange(startRow, 1, sh.getLastRow() - 1, sh.getLastColumn());
  var vs = dataRange.getValues();
  vs.forEach((r, i) => {
    var email = r[4];
    var ccEmail = r[5];
    var customId = r[1];
    var dueDate = Utilities.formatDate(new Date(r[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 = r[12];
    var value = Utilities.formatDate(new Date(r[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 });
      sh.getRange(startRow + i, 13).setValue(1);
    }
  });
}

7 months ago · Juan Pablo Isaza Denunciar

0

Responde la pregunta

Encuentra empleos remotos

Accepted Answer · 2022-06-30T07:52:58.874Z

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 are 22, 23, 24...etc. It just appends the value of startRow and i 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 variable i in sheet.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:

Reference:

parseInt()