Create a BMI Calculator using Google Forms and Google Sheets

This tutorial explains how you can build a BMI calculator app with Google Sheets and Google Forms. When a user submits the form, their BMI score is calculated in Google Sheets and a personalized report is generated in Google Docs. The user then receives an email with their BMI score and the PDF report.

👋 Complete this quick Google Form to calculate your BMI and receive a personalized report in your inbox.

BMI Calculator with Google Forms

We have a Google Form that asks the user to enter their height and weight. The response is stored in a Google Sheet and the BMI score is calculated automatically using the BMI formula.

The PDF report is personalized and includes the user’s BMI score, BMI category, and suggestions on how to improve their BMI score. The user’s photo is also embedded in the report as shown in the screenshot above.

BMI Calculations in Google Sheets

We perform the following calculations in Google Sheets to calculate the user’s BMI score.

Age of the respondent

The form requires the user to enter their date of birth. We use the DATEDIF function to calculate the age of the user in years.

=MAP(C:C, LAMBDA(c, IF(ROW(c)=1, "Age", 
   IF(ISDATE(c), ROUND(DATEDIF(c, TODAY(), "Y"), 0),))))

BMI Score (kg/m²)

The user enters their height and weight in the form. We use the MAP function to calculate the BMI score for each form response in the sheet.

=MAP(E:E,F:F, LAMBDA(ht, wt, IF(ROW(wt)=1, "BMI", 
    IF(AND(ISNUMBER(wt),ISNUMBER(ht)), 
      ROUND(wt/(ht/100)^2,2),))))

BMI Category

The BMI category is calculated using the array function.

=ARRAYFORMULA(IF(ROW(J:J)=1, "BMI Category", IF(ISNUMBER(J:J), 
  IF(J:J < 18.5, "Underweight", 
  IF(J:J < 25, "Normal weight", 
  IF(J:J < 30, "Overweight", "Obese"))),)))

Prepare BMI Report

We’ve created a template in Google Docs that will be used to generate personalized BMI reports for each user with the help of Document Studio.

The report uses conditional content to display suggestions based upon the BMI score of the user. For instance, if the BMI score is less than 18.5, the user is underweight and the report suggests that they should eat more calories.

The image is inserted in the report using a special Embed Image tag to add thethe photo uploaded by the user in the Google Form in the document.

Create BMI Workflow

Launch Document Studio inside the form responses sheet and go to Extensions > Document Studio > Open to create a new BMI workflow.

We’ll only process the form responses that have a valid email address, the age of the user is numeric, and the calculated BMI score is at least 10.

On the next screen, add a Create File task and select the Google Docs template that we’ve created in the previous step. You may also want to change the name of the generated PDF file to include the name of the form respondent.

Click the Add Another Task button to add the Send Email task. This will send the generated PDF report to the user via email.

For the recipient’s email address, select the Email column from the Google Sheet. You can also customize the email subject and the body of the email.

The Attach Merged Files option should be enabled so that the generated PDF report is attached to the email. That’s it. Click the Save Workflow button and your BMI calculator is ready for use.

You can view the tutorial section for more ideas on Google Forms automation with Document Studio.

source:https://ift.tt/d5gekGw