Executing a DeepLink action from an Email Silently

Hello Everyone,

Anyone interested with executing a deeplink action from the inside of an workflow email body without launching the app, can see it in action below. Possible cases might cover subscribe/unsubscribe, approve/reject etc. without the need for the user to interact with the app or with any user set who are not authorized to use the app but users action might be needed. The solution covers:
**1. **Use of Google Apps Scripting
**2. **Use of AppSheet API
For sure, rather than using AppSheet API to edit a data, direct access to the gSheet is possible via Google Sheet API v4 as an alternative. However, I find the AppSheet API more direct and intact with the solution, as you won’t be needing to loop thru the whole respective gSheet tab to find the exact matching record first before altering it, which will consume script execution time.

This is a very basic work and it’s possible to execute more complex process i.e. invoking an action, deleting a record, creating an additional record in another table etc.

Your comments and feedback are always welcome.


[HOW TO USE]


Just click the top menu item in the Home Deck View saying Executing DeepLink from Email. Add a user record with any name but a valid email address as this address will be receiving a notification email. When you open that record, you will notice an UNSUBSCRIBE action button. Clicking that action button will trigger the email workflow and it will send an email containing an unsubscribe button in the body. Pressing that button will record unsubscribed value to the [STATUS] column of that particular record and you will see an HTML formatted result page.


[APPLICATION]


3 Likes

[STRUCTURE AND THE APP BUILD]


I will try to explain briefly but as detailed as possible on how to do/provide such a functionality in your app. Please keep in mind that, as mine was just a public sample, I have provided/triggered the functionality with an action button on the Detail View of the record. So as far as the “structure” is preserved, you can add the same functionality in any part of your app build.

I will go step by step and part by part for you ease of following and understanding


[STEP-1 | INTEGRATING GOOGLE APPS SCRIPT APP TO GDRIVE]


The core of this feature is a standalone Google Apps Script project which is deployed as a web-app. To create this; you need to add Google Apps Script as a part of your Google Drive. Follow the below steps:
1. Open your Google Drive
2. Click on NEW >> More >> Connect more apps


3. Write script in the search box on the upper right corner and hit enter
4. Locate Google Apps Script and click on CONNECT button

5. You can close this window now by pressing “X” on the upper right corner.


[STEP-2| CREATING THE STANDALONE WEB-APP/WEBHOOK]


1. Click on NEW >> More >> GOOGLE APPS SCRIPT
2. This will launch the Google Apps Script editor. Give your project a name you want.
3. Locate the Code.gs pane. Clear out the any code in this pane.
4. Copy and paste the below script code to this pane and SAVE

function doGet(e) {
  try {
    var key = e.parameter.ID;
    var option = e.parameter.Option;
    var status = (option == "unsubscribe") ? "unsubscribed" : "subscribed";
    
    var appURL = "https://api.appsheet.com/api/v2/apps/pasteYourAppIDHere/tables/YourTableName/Action?applicationAccessKey=pasteYourApplicationAccessCodeHere"
    
    var options = {
      "Action" : "Edit",
      "Properties" : {
        "Locale" : "en-US",
        "Location" : "47.623098, -122.330184",
        "Timezone" : "Pacific Standard Time",
      },
      "Rows": [
      {
        "ID" : key,
        "STATUS" : status
      }
      ]
    }
    
    var payload = {
      "method" : "post",
      "headers" : {
        "content-type" : "application/json"
      },
      "payload" : JSON.stringify(options),
      "muteHttpExceptions" : true
    }
    
    var call = UrlFetchApp.fetch(appURL, payload);
    var HTMLOutput

    if (option === "unsubscribe") {
      HTMLOutput = HtmlService.createHtmlOutput("<table cellspacing='0' cellpadding='0'; style='width: 100%'><tr align='center'><td colspan='2'><img src='https://drive.google.com/uc?export=view&id=12iDQdcJ51JmuaIezQi8By9htqBZyCEiT'/></td></tr><tr align='center'><td colspan='2'><strong><h2>You have successfully unsubscribed. We are sorry to see you go. You can subscribe back by clicking the button below.</h2></strong></td></tr><tr align='center'><td colspan='2'><a href='https://script.google.com/macros/s/AKfycbyYZbEA6V3vf4zKJNrsVxq7vj-Aeqw3mLT_n3vf-gf-2cgEp2E/exec?ID="+key+"&Option=subscribe' target='_blank'><img src='https://drive.google.com/uc?export=view&id=1avrBUyF9OiV6N0SizTG0ANoCi_uxT9i3' width='150'/></a></td></tr></table>");
    } else if (option === "subscribe") {
      HTMLOutput = HtmlService.createHtmlOutput("<table cellspacing='0' cellpadding='0'; style='width: 100%'><tr align='center'><td colspan='2'><img src='https://drive.google.com/uc?export=view&id=12iDQdcJ51JmuaIezQi8By9htqBZyCEiT'/></td></tr><tr align='center'><td colspan='2'><strong><h2>You have successfully subscribed. We are happy to see you back. You can unsubscribe anytime by clicking the button below.</h2></strong></td></tr><tr align='center'><td colspan='2'><a href='https://script.google.com/macros/s/AKfycbyYZbEA6V3vf4zKJNrsVxq7vj-Aeqw3mLT_n3vf-gf-2cgEp2E/exec?ID="+key+"&Option=unsubscribe' target='_blank'><img src='https://drive.google.com/uc?export=view&id=1vqG4747gg7-gIfR3cYcw4QSMqpXvYqcz' width='150'/></a></td></tr></table>");
    }
    return HTMLOutput
  } catch (e) {
    MailApp.sendEmail("email@yourdomain.com", "Webhok Error Occured!", 
      "\r\nMessage: " + e.message
      + "\r\nFile: " + e.fileName
      + "\r\nLine: " + e.lineNumber);
    e = (typeof e === 'string') ? new Error(e) : e;
    Logger.severe('%s: %s (line %s, file "%s"). Stack: "%s" . While processing %s.',e.name||'', 
               e.message||'', e.lineNumber||'', e.fileName||'', e.stack||'', processingMessage||'');
    throw e;
  }
}

5. Locate the Publish menu on the top tier of the script editor. Choose Deploy as web app… from this menu.
6. Set these as defaults:

  • Project version: New
  • Execute as: Me (your email)
  • Who has access to the app: Anyone, even anonymous

    7. Click on Publish/Update
    8. The app script manifest will require your authorization to read/write/access some namespaces/scopes in your Google Drive. Consent them all. Provided you face with a warning, don’t take it into account and choose Proceed unsafe. This warning comes up provided you are first time deploying a web-app and you are not authenticated as a developer. It’s not a big deal, so you can by-pass that warning.
    9. Copy and paste the Current web app URL to a notepad and save it, as you will be needing it later on.

Now you have deployed your script code as a web-app and it’s ready to be used as an endpoint.

2 Likes

@LeventK. This is an awesome share! Thanks!

@Henry_Scott
I will continue under this thread…It’s not finished yet

… I know :slight_smile:


[STEP-3 | THE ESSENCE]


As you have deployed your web-app, you should be having an endpoint URL similar to this:
Web-app URL> https://script.google.com/macros/s/AKfycbyYZb6AEV3vf4zKJNrsVxq7vj-Aeqw3mLT_n3vf-gf-2cgEp2E/exec

By design of my sample app, I have planned that this URL shall receive 2 parameters/query keys in the URL string. These are:

  • ID: Which is the key column of my sample table
  • Option: Which is the status that I define to pass both to the AppSheet API and the Subscribe Now and Unsubscribe buttons in the HTML response page

Basically; this will not be different than executing this URL in the browser:
https://script.google.com/macros/s/AKfycbyYZb6AEV3vf4zKJNrsVxq7vj-Aeqw3mLT_n3vf-gf-2cgEp2E/exec?ID=aaxx11bb&Option=unsubscribe

OR

https://script.google.com/macros/s/AKfycbyYZb6AEV3vf4zKJNrsVxq7vj-Aeqw3mLT_n3vf-gf-2cgEp2E/exec?ID=aaxx11bb&Option=subscribe

When this URL is executed; the doGet(e) function is triggered and I read and pass the query parameters to 2 variables that I have set:

  • key
  • option
    I have also set a 3rd variable to pass to the AppSheet API (status) depending on the value of the option key parameter.
    var key = e.parameter.ID;
    var option = e.parameter.Option;
    var status = (option == "unsubscribe") ? "unsubscribed" : "subscribed";

You can change the query parameter names with a meaningful name or the name that will match with your app/needs. You should not use any spaces but you can use underscore (_) character for spaces if you need.

The next step is to define our App URL for calling AppSheet API. We do this with the line below and remember to replace your table name, applicationAccessKey and your actual App Id.

var appURL = "https://api.appsheet.com/api/v2/apps/pasteYourAppIDHere/tables/YourTableName/Action?applicationAccessKey=pasteYourApplicationAccessCodeHere"

The next step is to structure the JSON payload that we will be passing to the AppSheet API. For this section I advise you to refer to the below page if you are not familiar with the JSON structure of AppSheet API. The structure will totally be the same except that we append that to a JS variable i.e. options

  var options = {
      "Action" : "Edit",
      "Properties" : {
        "Locale" : "en-US",
        "Location" : "47.623098, -122.330184",
        "Timezone" : "Pacific Standard Time",
      },
      "Rows": [
      {
        "ID" : key,
        "STATUS" : status
      }
      ]
    }

As you can follow from the above code snippet, we just structured a JSON body, and appended our variables to the columns in our table.

The next step is to make a Call to this endpoint and let it execute with the JSON body that we have structured. We do this with a built-in JS function called: UrlFetchApp.fetch( URL, payload )**

1. First we need to define the payload

   var payload = {
      "method" : "post",
      "headers" : {
        "content-type" : "application/json"
      },
      "payload" : JSON.stringify(options),
      "muteHttpExceptions" : true
    }

2. Then we make the call

var call = UrlFetchApp.fetch(appURL, payload);

The last step is to prepare an HTML output to show the user, when he presses the UNSUBSCRIBE button inside email body. For sure, the style that I have prepared with this sample app is pretty basic and you can structure your own HTML output as long as it’s a valid code.

When the user unsubscribes, I show an HTML page that he is successfully unsubscribed and there is an HTML button to re-subscribe. When the user clicks that button, it definitely executes the same web-app but the status is now changed and the AppSheet app will be updated as such. As the web-app is re-executed, now the HTML output will change and show that he is successfully subscribed and there will be an HTML button to unsubscribe now :slight_smile:

Here is the fiddle that you can preview these HTML pages:
HTML fiddle for UNSUBSCRIBE > https://jsfiddle.net/LeventK/8gjw0d34/2/
HTML fiddle for SUBSRIBE > https://jsfiddle.net/LeventK/8eznj2wg/1/

2 Likes

[STEP-4 | WORKFLOW EMAIL & BODY]


The final part of this post is how to prepare the Workflow Email body. Just create workflow email in the AppSheet editor and use the below or similar Email Body content. Please consider that the below content is HTML formatted which also AppSheet accepts.

<<[NAME]>>,<br>We are so sorry that you have unsubscribed. In order to complete your unsubscription status, please click <b>UNSUBSCRIBE</b> button below.<p>Thank you,<br>ABLE3 Team<p><p><table align="center"><tr><td><a href='<<CONCATENATE('https://script.google.com/macros/s/AKfycbyYZb6AEV3vf4zKJNrsVxq7vj-Aeqw3mLT_n3vf-gf-2cgEp2E/exec?ID=',[ID],'&Option=unsubscribe')>>'><img src="https://drive.google.com/uc?export=view&id=1vqG4747gg7-gIfR3cYcw4QSMqpXvYqcz" width="200"></a></td><td></table>

As a result, you will be receiving an email like below:

The HTML button; contains <a href=" "></a> and <img src=" "/> tags to control the HTML button. The href points out to our web-app’s endpoint URL with the query parameters (ID and Option) appended to it. The ID parameter reads the current rows key column value and Option parameter is set to a static text, which is: unsubscribe

Clicking the unsubscribe button in this email body, will create the HTML output explained in the prior post.

Hope I was in detail and explanatory enough. I thank you for your patience for reading it whole through and hope this can add a value to your app development. You can always PM me in the community or send an email to levent@able3ventures.com should you require any help and/or assistance.

Kindest regards,
Levent KULACOGLU

6 Likes

Thank you @LeventK Great post.

@LeventK nice one!