Dad Joke Translation Project

Dad Joke Translator Project

Estimated Time to Complete: 30-45 minutes

The Dad Joke Translator is an excellent project for users to learn key dashdash ideas while having fun at the same time.
After you complete the project, you’ll have a cool app that:

  1. Calls the Dad Joke API
  2. Automatically translates the joke into a chosen language (or, if we have a corrected version, returns that one)
  3. Allows users to provide a better translation for the joke
  4. Send the translated joke (along with a correction, if there is any) to a valid email address

In this project, you’ll learn quite a lot of useful skills:

  • Communicating with an API
  • Automatically translating with the Microsoft Translator
  • Verifying email addresses using ZeroBounce
  • Sending emails with conditional content using dashdash
  • A whole bunch of formulas and logic

Note: Please make sure you have done all the dashdash tutorials before doing this project. Some concepts will be easier to understand.

Setting up your workspace

Because we want to make this look like an app, a lot of the calculations have to be “hidden” from the user. The way to do that in dashdash is to create different views that do the calculations in the background.

For this project, you’ll need to create the following views:

View Description
dadJokes The interface our users will interact with.
apiCall Contains the details required to make the API call.
translationCheck Translating the dad joke while also checking if our database doesn’t have a “human-verified” translation
emailConstruction Validation and construction of a conditionalized email.
userSuggestions Database of user-submitted translation suggestions.
verifiedTranslations Database of “human-verified” translation suggestions.

dadJokes view

The dadJokes view is going to function as the interface that our users will interact with. As such, it will just reference the final calculations in different views. Let’s set it up!

Setting up the dadJokes user interface

Enter the following content into the dadJokes view:

Cell Content
A1 Dad Joke
A3 Get New Joke
A5 Want to send this joke to make someone’s day? Type their email below.
A7 Send Email
B1 Language
C1 Translation
D1 It didn’t make sense, here’s my suggestion!
D2 Just to make sure you’re not a robot, check the box:

Lastly, let’s just add the following action elements to let our users provide input.

Cell Action Element
A3 Button
A7 Send Email
B2 Input Field
D2 Input Field
E3 Checkbox

Resize the cells a little so that everything fits nicely and add some formatting. When your view looks like the one below, you’re good to go to the next stage!

apiCall

Right, so now let’s do our first tricky bit - setting up the apiCall view so that we can call an API.

Setting up the apiCall view

To set up the translationCheck view, create the following table:

Cell Content
A1 URL
A5 Call the API and Retrieve the Joke
B1 Header Part 1
C1 Header Part 2
D1 Complete Header
E1 Entity to Retrieve

Resize the cells a little so that everything fits nicely and add some formatting. When your view looks like the one below, you’re good to go to the next stage!

Connecting to the dad jokes API

We’re going to use the icanhazdadjoke api to get a random dad joke. This is a free API that doesn’t require any authentication, which makes things a lot easier for you if this is your first time using an API.

When you look at the icanhazdadjoke documentation, under Fetch a random dad joke we have this example:

$ curl -H "Accept: application/json" https://icanhazdadjoke.com/
{
  "id": "R7UfaahVfFd",
  "joke": "My dog used to chase people on a bike a lot. It got so bad I had to take his bike away.",
  "status": 200
}

So, we know that when we call https://icanhazdadjoke.com/ with the header "Accept: application/json", we will get back a JSON object with an id, joke, and status.

The information we need for our GET request are:

  • URL: https://icanhazdadjoke.com/
  • Header: "Accept: application/json"

Now let’s fill in our table

Cell Content
A2 https://icanhazdadjoke.com/
B2 Accept
C2 application/json
D2 =PAIR2JSON(B2,C2)

Did you notice that we broke up the header into two parts? Instead of one cell with "Accept: application/json", we put Accept in B2 and application/json in C2. Why?

When you work with more complex APIs, the headers may get a bit complicated, leading to simple mistakes that will leave you scratching your head for hours. Instead, a good practice is to put each element of the header in a separate cell and then use the PAIR2JSON function to combine the elements (and it’ll do all annoying punctuation for you too!). Another friendly tip is to reference your cells in formulas as often as possible, instead of typing them. Why? Have a look at the example below and see just how much simpler it is.

If you reference the cells, dashdash automatically figures out what it is and adds the required punctuation for you. So fewer headaches in figuring out if it’s a typo or not.

Calling the dad jokes API

In A5, enter =GET(A2,D2). You should get {data} in the cell. Clicking on it, you’ll see a contextual menu pop out on the right, with responseid, joke, and status. Now, the only thing that interests us is the joke right? This is the entity that we want to retrieve automatically when we call the API. So now, type in joke into cell E2.

Next, we’re going to parse the API response to automatically retrieve the joke.

Parsing the API

We’re going to use the PARSE function, in combination with GET to retrieve the joke automatically.

In A5, enter =PARSE(GET(A2,D2),E2) and press Enter.

How does it work? Have a look at the image below and it’ll all make sense.

dd-parse-example

Now we have just one last part to do before we finish with this view: have the joke appear in the dadJokes view.

Adding the joke to the dadJokes view

We want a new joke to appear whenever a user clicks Get New Joke in the dadJokes view. To do that, we simply need an IF function.

In the apiCall view, enter =IF('dadJokes'!A3="Get New Joke",PARSE(GET(A2,D2),E2),"Click the button for a new joke") in A5.

In the dadJokes view, enter ='apiCall'!A5 in A2. Then click Get New Joke. Did we get a new joke? Perfect!

If you’re wondering how the IF function works, just have a look at the image below.

Summary

Great job! You’ve just:

  • Called the API
  • Parsed the response to get the value you want
  • And made it interactive!

Next, let’s get to translating the joke.

verifiedTranslations and translationCheck

In this part of the project, we’re going to:

  • Set up the translationCheck view.
  • Set up the verifiedTranslations view to store our “human-verified” user-submitted translations.
  • Translate the original dad joke in the translationsCheck view.
  • Check if we have a better translation in our database. If not, we’re just going to use the automatic translation.

Setting up the translationCheck view

To set up the translationCheck view, create the following table:

Cell Content
A1 Automatic Translation
B1 Final Translation

Resize the cells a little so that everything fits nicely and add some formatting. When your view looks like the one below, you’re good to go to the next stage!

Setting up the verifiedTranslations view

To set up the verifiedTranslation view, create the following table:

Cell Content
A1 Dad Joke
B1 Language
C1 Microsoft Translation
D1 Verified Translation

Resize the cells a little so that everything fits nicely and add some formatting. When your view looks like the one below, you’re good to go to the next stage!

Translating the dad joke

Note: To connect the Microsoft Translate Text integration, you will have to connect the integration with an API key. Just follow the steps in Microsoft Azure: Getting an API key to use Bing search API, adjusting for the Microsoft Translate resource.

To translate the dad joke that appears in the dadJokes view, enter the following formula in A2 of translationCheck.

=IF('dadJokes'!A3="Get New Joke",TRANSLATE_MICROSOFT('dadJokes'!A2,,'dadJokes'!B2)," ")

What’s going on here? Check out the image below.

You can test out if the formula works by providing a two-letter language code in the dadJoke view.

The next step is a bit trickier, as we want to check if there’s a better translation in our database and if there is, display it to the user.

Checking if we have a better translation

In B2 of translationCheck, enter the following formula:

=IFERROR(IF(MATCH('dadJokes'!A2,'verifiedTranslations'!A:A,0),INDEX(A2,(MATCH(A2,'verifiedTranslations'!C:C,0))),A2),A2)

Now, this is a slightly more complicated formula as we have:

  • Error handling with IFERROR
  • A MATCH function to query our database
  • An INDEX and MATCH combination to return a verified translation

Have a look at the image below to understand how the whole formula works.

The last thing to do is to return the Final Translation to the dadJokes view.

Adding the translation to the dadJokes view

For the Final Translation to appear in the dadJokes view, enter ='translationCheck'!B2 in C2. Then click Get New Joke. Did we get a new joke and a translation? Brilliant job!

Summary

That was a lot of work - well done. You’ve just used the Microsoft Translate Text integration, in combination with IFERROR, IF, MATCH, and INDEX functions to translate a text and return a verified translation if we have any.

Now go have a coffee, because next we are creating our userSuggestions view.

userSuggestions

The userSuggestions view contains all the user-submitted suggestions that could make the joke better.

Setting up the userSuggestions view

To set up the userSuggestions view, create the following table:

Cell Content
A2 Dad Joke
B2 Language
C2 Translation
D2 It didn’t make sense. Here’s my suggestion!

Resize the cells a little so that everything fits nicely and add some formatting. Have a look at the screenshot for some guidance.

And now, we need a way to automatically populate this table every time a user enters a suggestion in the dadJoke view.

Updating the userSuggestions table

Now, in order to populate this table, we need to create a function that will update the table when you check the I’m not a robot checkbox in dadJokes. So, in A1 enter this formula:

=IF('dadJokes'!E8=TRUE, UPDATE('dadJokes'!A6:D6,4,'dadJokes'!A7:D7,A2:D2), "Nothing to update")

Know what’s happening? Well, just in case, check out the image below.

Now, as a little side note, we should let the user know that we use their suggestion when they check the box.

To do this, enter the following formula into D4 of the dadJokes view:

=IF('userSuggestions'!C1="Nothing to update"," ","We'll use your suggestion to make our service better")

It’s a nice and straightforward IF formula. If in C1 of the userSuggestions view we have “Nothing to update”, we don’t display anything. If there is, we display to the user “We’ll use your suggestion to make our service better”.

Summary

Wow, that was quick. So now, using the UPDATE formula, we automatically update our table in the userSuggestions view when they check the box. Plus, we give them some feedback about what we do with the suggestion! Nice job.

Next up is the final view: emailConstruction.

emailConstruction

The last piece of the Dad Jokes Translator is sending the translation to a verified email address using dashdash. The emailConstruction view is where we do all that. Let’s set it up!

Setting up the emailConstruction view

To set up the emailConstruction view, create the following table:

Cell Content
A1 Check Email Validity
A4 From
A8 If checkbox = FALSE
A9 If checkbox = True
A12 No Email Entered
A13 Email Not Verified
A15 AND Condition
A17 Send Email on Button Click
B1 Entity
B4 To
B7 Subject
B8 Here’s a random dad joke I found and translated
B9 Here’s a random dad joke I found, translated, and improved
B12 Please enter a valid email.
B13 Hmmm, that email address isn’t valid. Please check it :slight_smile:
B11 Display to User
C1 valid (make sure it’s lowercase!)
C7 Intro Sentence
C8 I used dashdash’s Dad Jokes app to find a random dad joke and translated it automatically:
C9 I used dashdash’s Dad Jokes app to find a random dad joke and translated it automatically. But I think my suggestion is better:
D7 Body Content

Play around with the formatting and cell sizes. When it looks similar to the screenshot below, let’s keep going!

Validating an email address

Note: To validate the email address, connect the ZeroBounce integration.

First, to make sure that you don’t get an error, enter a valid email address into A7 of the dadJokes view.

Now, in the emailConstruction view, enter the following formula in A2:

=VERIFY_EMAIL_ZEROBOUNCE('dadJokes'!A7)

This will return a JSON object. But we just need to know if the status entity is valid. So, write status in B2.

Now replace the content in A2 with the following formula:

=IFERROR(PARSE(VERIFY_EMAIL_ZEROBOUNCE('dadJokes'!A7),B2),"No Email Entered")

What’s happening here? Let’s have a look!

Now, the last part that we need is to simply know if the email is valid or not. To do that, enter the following formula in C2:

=IF(A2=C1,"Yes","No")

OK, nearly there! Let’s set up the email content now.

Configuring the email content

The body of the email will change depending on if the user submitted a correction or not. If they haven’t, we’ll just create an email with the original and the Microsoft translation. If they have, then we add the suggestion as well.

The formulas for this are very easy. If you remember the dashdash email tutorial, then you know how to use the RANGE2HTML function.

Simply enter the following formulas into A2 and A3:

Formula for A2
=RANGE2HTML(C8,'dadJokes'!A1:C2)
Formula for A3
=RANGE2HTML(C8,'dadJokes'!A1:D2)

And just in case you’ve forgotten what’s going on, check out the image below:

dd-range2html

Done! Now, depending on what the user selects, we will send a different introductory sentence and table.

We’re almost there! Next up, actually sending the emails.

Sending emails from dashdash

Note: To send an email, connect dashdash’s Email integration. If you don’t know how to do that, check out dashdash’s email tutorial.

To send the email, we want the user to click Send Email in the dadJokes view, verify that the email is correct, and if it is, send an email with the subject and body changing depending on if there’s a user suggestion.

So let’s start with an AND function.

Creating an AND function

The AND function is useful when you want two conditions to be TRUE before doing anything else. In our case, we want the user to both click Send Email and for the email to be valid before we do anything else.

In B15, enter the following formula:

=AND('dadJokes'!A8="Send Email",C2="Yes")

Check the image below to make sure that you understand the formula.

dd-AND

OK, now that we have the condition, it’s time to actually send the email.

Sending the email

Before we write any formulas, we’re just missing two bits of information. Let’s fill them in now.

Information Cell Content
The sender’s address A5 Your email address
The recipient’s address B5 =‘dadJokes’!A7

Now we have all the information to create the email function.

In B17, enter the following function:

=IFERROR(IF(B15,IF('dadJokes'!E3=FALSE,SEND_EMAIL(A5,B5,B8,D8),SEND_EMAIL(A5,B5,B9,D9)),B12),B13)

Now, this may seem a bit complicated, but once you look at the image below, it will all make sense!

Lastly, let’s just connect this view to the dadJokes view.

Connecting emailConstruction to dadJokes

For the user to get some feedback when they click the button, enter the following formula in C7 of the dadJokes view:

='emailConstruction'!B17

And that’s it! We’ve just connected the final view to main interface. There’s nothing left to do but to test the entire project.

Use the app and test it

Let’s test to see if our project works!

  1. Go to the dadJoke view.
  2. Enter a translation language (preferably a language you know).
  3. Click Get New Joke.
  4. Submit a correction, if you have one, and check the box.
  5. Type in a valid email address to send the joke to.
  6. Click Send Email.

Did it send? Great! If not, go through the project to see where there might be an error.

Project Summary

Well, that was a long project - but you learn so much! You created a pretty advanced app, just by using some formulas, three integrations, and a bit of creativity!

If you’re up for an additional challenge, try the following tasks:

  • Publish the app so that only the dadJokes view is visible.
  • Limit the number of languages the user can input to three.
  • Customize the body of the email to contain a personal signature.
  • Send emails once a day with the newest user submissions to review.

When you think you have an answer, post it in the comments!

3 Likes