CODEWITHSUNDEEP
google-apps-script
wes1337
wes1337

Reputation: 1

Apps Script to copy data up to last cell containing data in a single column

I am trying to copy the content of a single column and send this range of data in an email.

My users submit a for. Their email address are added in column C. I want to send a weekly email to a team member of all the email address in column C.

The number of emails in column C is increasing and decreasing daily. So I need to be able to find the number of non empty cells in column C. copy these cells and send via email using Apps Script.

Would love some help on this please!

Upvotes: 0

Views: 48

Answers (1)

Cooper
Cooper

Reputation: 64032

Checking for actual emails and getting a list and Sending Them

Looking at Form Response Sheet column C

Get and check the emails:

function getEmailAddresses() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("Form Response Sheet Name");
  const vs = sh.getRange(2,3,sh.getLastRow() - 1).getValues().flat().filter(e => e.match(/^([a-zA-Z0-9_\-\.]+)@([a-zA-Z0-9_\-\.]+)\.([a-zA-Z]{2,5})$/));
  Logger.log(vs.length);//number found
  Logger.log(JSON.stringify(vs)); //actual emails
  return vs.join('\n');
}

Sending them

function SendWeeklyEmailList() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("Team Member Emails")
  const teamlist = sh.getRange(2,1,sh.getLastRow() - 1).getValues().flat().filter(e => e.match(/^([a-zA-Z0-9_\-\.]+)@([a-zA-Z0-9_\-\.]+)\.([a-zA-Z]{2,5})$/)).join(',');
  GmailApp.sendEmail(teamlist,"EmailsList", getEmailAddresses())
}

Creating a trigger to send once every two weeks on monday near midnight

function createWeeklyTrigger() {
  if(ScriptApp.getProjectTriggers().filter(t => t.getHandlerFunction() == 'SendWeeklyEmailList').length == 0) {
    ScriptApp.newTrigger('SendWeeklyEmailList').timeBased().everyWeeks(2).onWeekDay(1).atHour(0).create()
  }
}

Upvotes: 1

Related Questions