Create a BMI Calculator using Google Forms and Google Sheets
Build your own BMI calculator app with Google Forms. The BMI report is generated in Google Sheets and the user receives an email with the BMI score.
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.
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.
We perform the following calculations in Google Sheets to calculate the user’s BMI score.
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),))))
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),))))
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"))),)))
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.
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.
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
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.
Google Developer Expert, Google Cloud Champion
Amit Agarwal is a Google Developer Expert in Google Workspace and Google Apps Script. He holds an engineering degree in Computer Science (I.I.T.) and is the first professional blogger in India.