Executing a DeepLink action from an Email Silently

LeventK
Participant V

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]


16 33 5,602
33 REPLIES 33

LeventK
Participant V

[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.

Hi @LeventK. Another 3 questions

  • How can I define the font in the HtmlService.createHtmlOutput? Letโ€™s say Arial?
  • And how do you define the font in the Email Body?
  • How do you handle a table with Start Expressions in HTML? Like you have it here.

@LeventK. This is an awesome share! Thanks!

@Henry_Scott
I will continue under this threadโ€ฆItโ€™s not finished yet

โ€ฆ I know

LeventK
Participant V

[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

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/


[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

Hi @LeventK Could this be done also in a google doc workflow template?
I mean, is it possible in a template to define an image and to link it with an URL?
Or is this possible only with HTML?

Hello @Fabian
I have never tested such thing but I believe it might be possible. You just need to use the gDoc as a container for your Google Apps Scripting, write a lookup code to find your image inside the doc and then assign a URL to that image. That URL will be clickable as well after itโ€™s converted to PDF. However, you wonโ€™t be able to do this with AppSheetโ€™s PDF workflow rule, because you canโ€™t run the script before AppSheet converts the doc to PDF. The best option might be using your own standalone webhook endpoint and create that PDF with scripting.

Thank vou @LeventK
Sorry - I should have told you that my question was about the Email Body Template. Instead of writing your HTML Code in the Email Body section, we could also use a google doc in the Email Body Template section.

How can we convert your HTML code

<<[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>

into โ€œAppSheet-Languageโ€ to use it in a google doc Email Body Template?

@Fabian
Unfortuntaly HTML code inside a gDoc template wonโ€™t work.

Thank you @LeventK for confirming.

Leo, according to this page, you can have a HTML template as attachment or as Email body in a workflow rule.

Are you sure a HTML template specified as the Email body Template in the workflow rule โ€œEmail Contentโ€ section wonโ€™t work?

Jay, the refโ€™d page is explaining how you can create a HTML file type workflow template, which is the resulting file for an email workflow rule or scheduled report. Using HTML tags inside a gDoc workflow template wonโ€™t be working in any way. @Fabian mentioned that he is using a template for the email body and asked if itโ€™s possible to use the same HTML tagging inside that email body template rather than the resulting file.

Ok, I got it. It would be nice if we could chain such operations from multiple workflow rules - i.e., generate the HTML using a template in one workflow rule and specify that as input to the Email Body in another workflow rule.

@Phil, is this something that we can look forward to in the future ?

Thanks for this wonderful method. One question: in may case, I need to identify who actually approved a request in email. How to dynamically capture the email address so to send to the Webapp with the senderโ€™s email address, then the Webapp will add it to a new record via appsheet in one of the tables? I am struggling on this for quite a while.

Lynn
Participant V

Thank you @LeventK Great post.

@LeventK nice one!

Thank you very much @LeventK
Your Post was so helpful.
One thing I like to add:

The Current web app URL has to be copied in 3 places:
2 times in the Google Apps Script (in the HTMLOutput)
And one time in the Workflow Email body

Three other things I learned:

  • You should never use DateTime as a key. The API will not find the row. Perhaps this has to do with different formatting?
    But I think Praveen posted once, that when using the API, DateTime is never a good idea for a key. So better use UNIQUEID().
  • In HTML you should not use double quotes but only single quote.
  • When you donโ€™t get it to work: Disable web app and Deploy it again. Use the new URL. For some reason this worked for me.

And I can confirm that this is working with PRO plan. No need for business subscription.

It may be working in PRO plan NOW but not guaranteed it will stay that way. Use of REST API (sometime) in future will require a business subscription.

@Bellave_Jayaram that is NOT good news that it will require a Business Plan in future.
We are building solutions now based on the Appsheet pricing we communicate to our customers. If it changes then we will loose customers and potential lawsuits

@Henry_Scott, you should be talking to someone at AppSheet regarding your apps/customers and their requirements.
Tagging @Santiago

The most you should ever commit to is what appsheet holds you to. Read your contract with Appsheet, they can change anything at any time, even revoke your license and choose not to serve you. I feel like youโ€™ve made bad business choices if you can get sued over actions appsheet takesโ€ฆ

I like to share another way to do this: With Integromat.


In your E-Mail you can create the Link:
CONCATENATE("https://hook.integromat.com/YOUR_INTEGROMAT_WEBHOOK?Key=",[Key],"&Status=TRUE")
Integromat is recieving this Webhook. The Router divides it into 2 Routes: If Status=TRUE, or If Status=False.
In the Webhook response you can define your Response Message in HTML. For me it looks like this:
2X_2_2b116c939b4a640a9061d415e210420ee7bf2aa6.png
The Button โ€œBestellung offenโ€ sends the same Webhook, but with Status=FALSE.

At the End, the HTTP request sends the Data to the AppSheet API.

Would you be willing to please explain this a little more please?

how do you format the HTTP step in integromat to generate the email with the button you have in the picture in the last step in integromat? Is that custom HTML code to create that?

Hi @Ethan_U yes this is HTML code.

<!DOCTYPE html>
<html lang="de">
<meta charset="utf-8"/>
<font size="3" face="Arial">
<table style="width: 100%;" cellspacing="0" cellpadding="0">
<tbody>
<tr align="center">
<td colspan="2"><img src="https://i2.wp.com/leeesposito.de/wp-content/uploads/2019/03/shopping-cart.png?fit=1200%2C630&ssl=1" width="200" height="100" /></td>
</tr>
<tr align="center">
<td colspan="2">
<h1>{{8.Kommission}}</h1>
<h2>Die Bestellung wurde als <font color="#ffffff"><span style="background-color: #008f33">erledigt</span></font> markiert.</h2>
<h3>Du kannst das wieder rรผckgรคngig machen.</h3>
</td>
</tr>
<tr align="center">
<td colspan="2"><a href="https://hook.integromat.com/MY_INTEGROMAT_WEBHOOK?Key={{8.Key}}&Kommission={{8.Kommission}}&Status=FALSE"><img src="https://placehold.jp/ff0000/ffffff/300x50.png?text=Bestellung%20offen" /></a></td>
</tr>
</tbody>
</table>
</font>
</html>

Jonathon
Participant V

Revisiting this thread 2 years later for a clients use-case.

Thanks @LeventK @Fabian

Another solution:

@LeventK

Can you please share the sample app developed by you?

Thanks.

The last part in Intergromat is the HTTP request that sends the data to your AppSheet App. Here is how to configure it:

ntrejo
Participant III

@Tim_Mao : you could add a parameter such us useremail to the WebApp, that you will fill in the email sent to the user. In the sample app, @LeventK used just 2 parameters


@LeventK : awesome solution, thanks for sharing!!

The downside I see are the security issues, because anyone malicious may call the WebApp without you being able to avoid it. Maybe a stronger ID and/or adding an additional column that changes every X period may work as a pseudo token that expires. That could mitigate this issue and make you able to create expirable links.

Top Labels in this Space