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:
- Calls the Dad Joke API
- Automatically translates the joke into a chosen language (or, if we have a corrected version, returns that one)
- Allows users to provide a better translation for the joke
- 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.
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
MATCHfunction to query our database - An
INDEXandMATCHcombination 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 
|
| 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:
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.
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!
- Go to the dadJoke view.
- Enter a translation language (preferably a language you know).
- Click Get New Joke.
- Submit a correction, if you have one, and check the box.
- Type in a valid email address to send the joke to.
- 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!