How can you set the value of a target cell in google sheets within a forEach loop in google apps script?

Question

I've written a script that searches through a sheet, finds rows that meet 7 parameters, returns those rows' data, and sends an email to the address in each of the matching rows. As part of this process, I want to update the cell value for 'Date of last contact' to the date the email is sent (that part's not hard). I'm struggling to get the A1 notation of the cell containing the date of last contact, which I believe I need in order to use the setValue() method. Here's the relevant parts of the code:

function sendFollowUps () {
  // Gets data from defined range of active sheet
  var rows = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Test List').getRange(2,1,152,12).getValues()

  // User-defined cutoff date, everything on or before will be contacted
  var cutoffDate = new Date('11/10/2021')
  console.log('Cutoff date is: ' + cutoffDate)

  // selects and returns only firms that haven't been met, schedule, or declined and have a date of last contact on or before the cutoffDate
  var filteredRows = rows.filter(function(row) {
    if (row[10] && row[10] != 'Met' && row[10] != 'Responded' && row[10] != 'Warm' && row[10] != 'Scheduled' && row[10] != 'Declined' && row[11] <= cutoffDate) {
      return row
    }
  })

  // for each firm returned by filteredRows, pulls email, firstName of investor, and firmName
  filteredRows.forEach(function(row) {
    var email = row[6]
    var firstName = row[4].split(' ')[0]
    var firmName = row[0]
    var sendToEmails = ['...']

    // Customized email message with firstName, firmName updated per investor
    var messageSubject = '...'
    var messageBody = `...`

    // Adds investor email to recipient list
    sendToEmails.push(email)

    // Formats recipient list to CSV string
    sendToEmails.join(',')

    // Sends email with customized firstName and firmName to email in row
    GmailApp.sendEmail(sendToEmails, messageSubject, messageBody, {'from': 'me@email.com'})

    followUpEmails.push(email)
}

I've tried just setting row[11] = sendDate within the forEach loop, but that doesn't actually update the cell value in the sheet. I've also tried using SpreadsheetApp.getSheetByName('Test List').getRange(row[11]), SpreadsheetApp.getSheetByName('Test List').getActiveCell(), and SpreadsheetApp.getSheetByName('Test List').getCurrentCell(row[11]), no joy with those either.

How can I go about getting the A1 notation for the row[11] cell in order to use setValue method?

user_7215091 · Answer

I suspect that this is not working correctly because you cannot compare dates like that since var cutoffDate = new Date('11/10/2021')

var filteredRows = rows.filter(function(row) {
    if (row[10] && row[10] != 'Met' && row[10] != 'Responded' && row[10] != 'Warm' && row[10] != 'Scheduled' && row[10] != 'Declined' && row[11] <= cutoffDate) {
      return row
    }
  })

Also using the filter like you have now has broken the continuity between the index in the foreach and the indexing of the values in the dataset. I would not filter the data and instead perform the data search as part of the forEach

I think you will be better off to do it this way:

function sendFollowUps() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName('Test List');
  const vs = sh.getRange(2, 1, 152, 12).getValues()
  var cutoffDate = new Date('11/10/2021').valueOf();
  vs.forEach(row => {
    if (row[10] && row[10] != 'Met' && row[10] != 'Responded' && row[10] != 'Warm' && row[10] != 'Scheduled' && row[10] != 'Declined' && new Date(row[11]).valueOf() <= cutoffDate) {
      var email = row[6];
      var firstName = row[4].split(' ')[0];
      var firmName = row[0];
      var sendToEmails;
      var messageSubject;
      var messageBody;
      sendToEmails.push(email)
      sendToEmails.join(',')
      GmailApp.sendEmail(sendToEmails, messageSubject, messageBody, { 'from': 'me@email.com' });
      followUpEmails.push(email)
    }
  });
}

user_8404453 · Answer

The A1 notation will be almost impossible to get because you filter first. This is already mentioned in the previous answer. However, you can modify the array and then .setValues() the modified array on the entire range:

const range = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Test List').getRange(2,1,152,12);
const rows = range.getValues();

Then inside .forEach(),

row[11]=sentDate;

Outside forEach():

range.setValues(rows);

Note that this replaces all the formulas(if any) with values.

How can you set the value of a target cell in google sheets within a forEach loop in google apps script?

2 Answers