Adding a button to an email-CodePudding

I have a Google Apps Script that is contained in a Sheet (which is tied to a Form). When a person fills in the Form, it triggers a series of events within the Sheet. The details of that aren't relevant (I don't think).

I would like to create a script that sends a follow-up email to everyone who filled out the form with a "Yes" or "No" question. To keep it simple, I would like to have two buttons in the email. When they click the button, it logs their response onto a cell in the Sheet.

I am okay with the apps script (.gs) coding, but I'm not very good with html. I can put together the email (shown below) and send it to the recipient, but I don't know how to get the response back from their click. Right now I have the 'myFunction()' script tied to onclick and I have that script ready to go, I just don't know how to get the 'myFunction()' to actually trigger when they click the button in the email.

If you have any solutions for getting the responses from the email, I'd appreciate the help.

Here is the email file (checkIN.html):

    <!DOCTYPE html>
    <html>
    
    <head>
        <base target="_top">
        <style>
            button {
                color: white;
          width: 250 px;
                padding: 15px 32px;
                text-align: center;
                text-decoration: none;
                display: inline-block;
                font-size: 16px;
            }
        </style>
    </head>
    <body>
        <p style="font-size:150%;">Good afternoon,<br><br>
        Yesterday you assigned <?= info.name ?> to academic recovery for <?= info.subject?>.<br>
        Did the student complete the assignment?</p>
            <button style = "background-color:#4CAF50;" id='yesButton' onclick='myFunction(True)'> Yes </button>
        <button style = "background-color:#f44336;" id='noButton' onclick='myFunction(False)'> No </button>
    </body>
    </html>

Here are the scripts that send the email and the function ready for a response:

    function emailTest() {
      var infoDict =
      {
        name: "Teacher",
        subject: "Computer Science",
      };
      sendEmail(infoDict);
    }
    
    function sendEmail(info) {
      var templ = HtmlService.createTemplateFromFile('checkIn.html');
      templ.info = info;
      var ssMessage = templ.evaluate().getContent();
    
      MailApp.sendEmail({
        to: "[email protected]",
        subject: "Academic Recovery",
        htmlBody: ssMessage,
        noReply: true
      });
    }
    
    function myFunction(response) {
      var ss = SpreadsheetApp.openById("ID goes here");
      var tracking = ss.getSheetByName('Tracking');
      if (response) {
        tracking.getRange("I12").setValue("Yes!");
      }
      else {
        tracking.getRange("I12").setValue("No");
      }
    }

CodePudding user response：

You can publish the app as web app (accessible to anyone with link) and change the buttons to <a> tags linked to the web app with the query string

The doGet function can accept query parameters. You can embed the row number or other identification method in the URL through some encryption and then decrypt in the doGet. So you will have to rename myFunction to doGet and e.parameter should give you the query params in doGet.

Workflow: Link generated e.g. http://appurl?complete=yes&row=20 for email. User clicks the link and goes to the App URL, script gets both params values and updates the range in the row.

CodePudding user response：

The first thing regarding sending buttons on email is that each email client has it's own limitations and that most modern email-services do not allow certain type of content including JavaScript, so it's not possible to use the following type of buttons on the email HTML body:

<button style = "background-color:#4CAF50;" id='yesButton' onclick='myFunction(True)'>

If you fill confortable with Google Apps Script server-side code (.gs) but not with HTML you might be more confortable by sending a Google Form by email with an on form submit trigger to pass the collected response to the corresponding place in your spreadsheet.

Another option, if the email recipients are using Gmail you might use one-click actions. Also you might opt to embed an html form, or make the buttons to open a link on the email HTML body.

Related (from oldest to newest)