google sheets - Send Automatic Follow Up Emails a certain number of days after a Dropdown Selection Status - Stack Overflow

admin管理员组

文章数量:1122832

I have an appscript code for custom emails that send to a specified person when I select a status dropdown for an item on the spreadsheet. When I select "Request Sent" it fires off an email to the owner of the item with the details from the spreadsheet using the template I typed.

I would like to be able to add in addition to the trigger-based status emails, automatic reminder/follow up emails which would go out, for example, 7 days after the initial "Request Sent" status is selected on the spreadsheet if the status hasn't been changed for that item yet. And/or an email that would go out automatically, for example, 3 days before the due date I entered on the spreadsheet (Column H).

Here is my current code:

function checkMySheet(e) {
  let range = e.range;
  let source = e.source.getActiveSheet();
  let row = range.getRow();
  let col = range.getColumn();
  let val = range.getValue();

  if (source.getName() == "Individual Licenses" && col == 6 && val == 'Request Sent') {
    let ss = SpreadsheetApp.getActiveSpreadsheet();
    let sheet = ss.getSheetByName(source.getName());
    let data = sheet.getRange(row, 1, 1, 12).getValues().flat();
   
      MailApp.sendEmail({
        to: data[4],
        subject: `ACTION REQUIRED: License Item Needed for ${data[0]}`,
        htmlBody: `
       Hey ${data[3]},<br />
       <br />
       Please see the below details for an individual license item that requires your attention so we can clear this with the regulator. It is important that you please review this as soon as possible and let me know if you have any questions on what may be needed to address and clear the item.<br />
      <br />
       <strong>STATE</strong>: ${data[0]}<br />
       <strong>LICENSE ITEM</strong>: ${data[1]}<br />
       <strong>PRIORITY</strong>: ${data[2]}<br />
       <strong>ASSIGNED TO</strong>: ${data[3]}<br />
       <strong>DUE DATE</strong>: ${Utilities.formatDate(new Date(data[7]), ss.getSpreadsheetTimeZone(),"M/d/yy")}<br />
       <strong>NOTES</strong>: ${data[11]}<br />
       <br />
       If you need any help or additional clarification then please don't hesitate to reach out and let me know. Also please keep me updated on the status and your expected time to have this completed.<br />
       <br />
       Thanks!<br />
       <br />
       `
      })

  ss.toast("Email successfully sent!", "STATUS", 5);
  }

Mark down example:

State	License Item	Priority	Owner	Email Addresses	Status	Date Requested	Due Date	Date Completed	Milestone	Documents	Notes
California	Continuing Education Required (2024)	Medium	Kerry	[email protected]	Request Sent	11/9/2024	12/6/2024		Active

I have an appscript code for custom emails that send to a specified person when I select a status dropdown for an item on the spreadsheet. When I select "Request Sent" it fires off an email to the owner of the item with the details from the spreadsheet using the template I typed.

I would like to be able to add in addition to the trigger-based status emails, automatic reminder/follow up emails which would go out, for example, 7 days after the initial "Request Sent" status is selected on the spreadsheet if the status hasn't been changed for that item yet. And/or an email that would go out automatically, for example, 3 days before the due date I entered on the spreadsheet (Column H).

Here is my current code:

function checkMySheet(e) {
  let range = e.range;
  let source = e.source.getActiveSheet();
  let row = range.getRow();
  let col = range.getColumn();
  let val = range.getValue();

  if (source.getName() == "Individual Licenses" && col == 6 && val == 'Request Sent') {
    let ss = SpreadsheetApp.getActiveSpreadsheet();
    let sheet = ss.getSheetByName(source.getName());
    let data = sheet.getRange(row, 1, 1, 12).getValues().flat();
   
      MailApp.sendEmail({
        to: data[4],
        subject: `ACTION REQUIRED: License Item Needed for ${data[0]}`,
        htmlBody: `
       Hey ${data[3]},<br />
       <br />
       Please see the below details for an individual license item that requires your attention so we can clear this with the regulator. It is important that you please review this as soon as possible and let me know if you have any questions on what may be needed to address and clear the item.<br />
      <br />
       <strong>STATE</strong>: ${data[0]}<br />
       <strong>LICENSE ITEM</strong>: ${data[1]}<br />
       <strong>PRIORITY</strong>: ${data[2]}<br />
       <strong>ASSIGNED TO</strong>: ${data[3]}<br />
       <strong>DUE DATE</strong>: ${Utilities.formatDate(new Date(data[7]), ss.getSpreadsheetTimeZone(),"M/d/yy")}<br />
       <strong>NOTES</strong>: ${data[11]}<br />
       <br />
       If you need any help or additional clarification then please don't hesitate to reach out and let me know. Also please keep me updated on the status and your expected time to have this completed.<br />
       <br />
       Thanks!<br />
       <br />
       `
      })

  ss.toast("Email successfully sent!", "STATUS", 5);
  }

Mark down example:

State	License Item	Priority	Owner	Email Addresses	Status	Date Requested	Due Date	Date Completed	Milestone	Documents	Notes
California	Continuing Education Required (2024)	Medium	Kerry	[email protected]	Request Sent	11/9/2024	12/6/2024		Active

• google-sheets
• google-apps-script

Share Improve this question Follow edited Nov 21, 2024 at 21:51 Kamron Kimbrough asked Nov 21, 2024 at 17:05 Kamron KimbroughKamron Kimbrough 2133 bronze badges 10

• Kindly share a minimal, reproducible example—no sensitive data with your desired output—that the community can use to replicate and see what you'd like to do. – Saddles Commented Nov 21, 2024 at 18:26
• You may use Tables to create one, with tools like Table to Markdown to help you easily copy and paste the data from the spreadsheet into the question, and as a last resort (not recommended since access may be revoked after the question has been answered), you can also Make an anonymous sample document. – Saddles Commented Nov 21, 2024 at 18:28
• 1 @Saddles edited with the markdown sample. Thank you for your help. – Kamron Kimbrough Commented Nov 21, 2024 at 21:52
• 2 I've read the existing scenario and script - sounds good. But then "I would like to be able to add": what I do not understand is "What is the problem?" The scenario describes a script triggered by a (daily?) time-driven trigger that responds to the "Status" and/or "Due Date". Have you tried to write code for this, have you researched for other scripts that do this? It is an interesting scenario but not too complicated, and the existing script suggests a competent developer. Again, "what is the problem?", because StackOverflow is not a bespoke code-writing service. – Tedinoz Commented Nov 21, 2024 at 23:55
• 1 I think you just need a daily trigger and store the date that the drop down is changed using an onEdit trigger then when the daily trigger finds the date that's 3 days old it sends the email. Don't use onSelectionChange at all. – Cooper Commented Nov 22, 2024 at 0:37

 |  Show 5 more comments

1 Answer 1

Sorted by: Reset to default 1

The goal is to either send a follow-up email 7 days after the initial "Request Sent" status is set in Column F and is still not changed, send an email 3 days before the due date set in Column H, or both.

Both options are possible; however, for the first one, I suggest using Column M as a timestamp for the Request Sent in Column F, which would be a week from that date. I think this is also @Cooper's point in his last comment.

You may add the following to the checkMySheet(e) function after the email has been sent to achieve that:

sheet.getRange(row, 13).setValue(new Date(new Date().setDate(new Date().getDate() + 7)));

The full code would become:

function checkMySheet(e) {
  let range = e.range;
  let source = e.source.getActiveSheet();
  let row = range.getRow();
  let col = range.getColumn();
  let val = range.getValue();

  if (source.getName() == "Individual Licenses" && col == 6 && val == 'Request Sent') {
    let ss = SpreadsheetApp.getActiveSpreadsheet();
    let sheet = ss.getSheetByName(source.getName());
    let data = sheet.getRange(row, 1, 1, 12).getValues().flat();

    MailApp.sendEmail({
      to: data[4],
      subject: `ACTION REQUIRED: License Item Needed for ${data[0]}`,
      htmlBody: `
       Hey ${data[3]},<br />
       <br />
       Please see the below details for an individual license item that requires your attention so we can clear this with the regulator. It is important that you please review this as soon as possible and let me know if you have any questions on what may be needed to address and clear the item.<br />
      <br />
       <strong>STATE</strong>: ${data[0]}<br />
       <strong>LICENSE ITEM</strong>: ${data[1]}<br />
       <strong>PRIORITY</strong>: ${data[2]}<br />
       <strong>ASSIGNED TO</strong>: ${data[3]}<br />
       <strong>DUE DATE</strong>: ${Utilities.formatDate(new Date(data[7]), ss.getSpreadsheetTimeZone(), "M/d/yy")}<br />
       <strong>NOTES</strong>: ${data[11]}<br />
       <br />
       If you need any help or additional clarification then please don't hesitate to reach out and let me know. Also please keep me updated on the status and your expected time to have this completed.<br />
       <br />
       Thanks!<br />
       <br />
       `
    });
    sheet.getRange(row, 13).setValue(new Date(new Date().setDate(new Date().getDate() + 7)));

    ss.toast("Email successfully sent!", "STATUS", 5);
  }
}

Note: Assuming that you're using an Installable Triggers for onEdit() to run the script, what it should do now is that every time Request Sent is set in Column F, an email will be sent to the user in Column E, and a date will show up in Column M, which would be today's date + 7 days.

To send the emails, here's a sample code that you may use with a daily Time-driven triggers:

function myFunction() {
  // Variable names from the other function
  let ss = SpreadsheetApp.getActiveSpreadsheet();
  let sheet = ss.getSheetByName("Individual Licenses");
  let data = sheet.getRange(2, 1, 1, 13).getValues();
  // Newly declared variable names
  let today = new Date();
  today.setHours(0, 0, 0, 0);
  data.forEach(r => {
    let colH = new Date(r[7]);
    let colH3D = new Date(colH);
    colH3D.setDate(colH.getDate() - 3);
    colH3D.setHours(0, 0, 0, 0);
    let colM = new Date(r[12]);
    colM.setHours(0, 0, 0, 0);
    if (today.getTime() == colH3D.getTime()) {
      MailApp.sendEmail({
        to: r[4],
        subject: `${r[0]} is due in 3 days`,
        htmlBody: `Kindly finish ${r[0]} immediately!`
      });
    }
    if (today.getTime() == colM.getTime()) {
      MailApp.sendEmail({
        to: r[4],
        subject: `${r[0]} — 7 days in Request Sent`,
        htmlBody: `Kindly update ${r[0]} immediately!`
      });
    }
  });
}

I've opted to create another function so that its sole purpose is to check what you'd like and send emails based on it.

OUTPUT

UPDATE

To make the script work only for Individual Licenses, Branch Licenses, and Company Licenses, it can be modified to:

function myFunction() {
  let ss = SpreadsheetApp.getActiveSpreadsheet();
  let today = new Date();
  today.setHours(0, 0, 0, 0);
  ["Individual Licenses", "Branch Licenses", "Company Licenses"].forEach(sheet => {
    let sh = ss.getSheetByName(sheet);
    let data = sh.getRange(2, 1, 1, 13).getValues();
    data.forEach(r => {
      let colH = new Date(r[7]);
      let colH3D = new Date(colH);
      colH3D.setDate(colH.getDate() - 3);
      colH3D.setHours(0, 0, 0, 0);
      let colM = new Date(r[12]);
      colM.setHours(0, 0, 0, 0);
      if (today.getTime() == colH3D.getTime()) {
        MailApp.sendEmail({
          to: r[4],
          subject: `${r[0]} is due in 3 days`,
          htmlBody: `Kindly finish ${r[0]} immediately!`
        });
      }
      if (today.getTime() == colM.getTime()) {
        MailApp.sendEmail({
          to: r[4],
          subject: `${r[0]} — 7 days in Request Sent`,
          htmlBody: `Kindly update ${r[0]} immediately!`
        });
      }
    });
  });
}

Note: Don't forget to create a time-driven trigger in Google Apps Script to allow the code to run automatically, so that you won't have to manually execute it in the editor.

本文标签：