Set Value on a DIFFERENT Sheet in Google Apps Script

Question

I want to do a variation on the "Sending emails from a Spreadsheet" tutorial on the Google Developers page.

I want to do exactly what they walk me through EXCEPT that when it comes to marking the cell in each row every time the e-mail is sent, I want to mark that on a SEPARATE sheet.

So, on the sheet "NeedReminders", my script finds the e-mail addresses of members to whom the e-mail should be sent in column A (which then becomes variable "emailAddress"). It sends the e-mail to that address.

THEN--and here's where I need help--I want the script to go to the "Master" sheet, find the row where emailAddress is in column X, and--in that same row--set the value of column BT.

Shouldn't be difficult. I just don't necessarily know how to do the "find the row where columm X equals emailAddress and set value of BT to today's date" part.

Who can help? Thanks in advance!

Here's my code so far (I'm always embarrassed to show my code because I'm sure it's terrible juvenile, so be gentle, please!):

function sendDuesReminder() {
  var sheet = SpreadsheetApp.openById('___').getSheetByName('NeedReminders');
  var startRow = 4; // There are a bunch of headers and other nonsense.
  var valueSheet = SpreadsheetApp.openById('___').getSheetByName('Master');

  // Determine how many rows to process
  var rowRange = sheet.getRange(3,4); // On my sheet, cell D3 counts how many members are on the list of people who need a reminder e-mail.
  var rowData = rowRange.getValue();
  // End Determine how many rows to process

  // Send e-mail
  var dataRange = sheet.getRange(startRow, 1, rowData, 2);
  var data = dataRange.getValues();
  for (i in data) {
    var row = data[i];
    // var emailAddress = row[0]; //Column A: E-mail Address
    var name = row[1]; //Column B: First Name
    var subject = "Reminder About Dues for Newcomers & Neighbors";
    MailApp.sendEmail("[email protected]", subject, "Hi, " + name + ". It's been some time since we received your online registration for Newcomers & Neighbors. But we don't seem to have received your dues payment, yet. So we wanted to drop you a quick line and remind you about it." + "\n\n" + "If you think we should have received your payment by now or there's some kind of a mistake, please let us know by responding to this message." + "\n\n" + "Otherwise, please send a check for __ made payable to ___ to:" + "\n\n" + "___" + "\n" + "___" + "\n" + "___" + "\n" + "___" + "\n\n" + "Thanks! Please don't hesitate to reach out if you have any questions." + "\n" + "___" + "\n" + "___" + "\n\n" + "Best wishes," + "\n\n" + "Kati" + "\n" + "Membership Director",
                     {name:"___"});

  // End Send E-Mail

  // Set that an a-(Experimental)
  var valueRange = valueSheet.getRange // Here's where I kind of break down . . . I need *something* here that searches the sheet for emailAddress
  setValue(today()) //I'm also not sure that this is the right way to set the value as today's date
  SpreadsheetApp.flush();
    // End Set value
  }
  Browser.msgBox("OK. Reminder messages have been sent. doebtown rocks the house!")
}

This is your third question asking pieces of code and it appears that you never answer nor check as answered any answers... do you read them at least ? People that take time to help generally like to know it has been useful... Well at least I do :-) — Serge insas
Oh my gosh, I TOTALLY read them. And I'm so grateful for all the help everybody's been giving me. I didn't realize that I was supposed to mark things as answered. I'll go back and do that. Thanks for the clue-in! — doebtown
OK. I checked as answered a couple of my old posts. Is that all I'm supposed to do? — doebtown
@Serge: I totally agree with you. This is what contributors expect and keep them motivated to answer the queries — Waqar Ahmad
Gotcha. Will COMMIT to being better about checking questions as answered. I ALWAYS appreciate everybody's contributions; I would be so lost without everyone's contributions. That said, does anybody have any thoughts about THIS little predicament? — doebtown

fullOfGAS fullOfGAS · Accepted Answer · 2012-08-08T17:22:16

If you're still struggling, try this:

var emails = valueSheet.getRange('X:X').getValues();
var targetRange = valueSheet.getRange('BT:BT');
var dates = targetRange.getValues();
for (var j=0; j<emails.length; ++j) {
    if (emails[j][0] == emailAddress) {
        dates[j][0] = new Date();
        break;
    }
}
targetRange.setValues(dates);

Set Value on a DIFFERENT Sheet in Google Apps Script

1 Answers