Title: Building a Google Apps Script for Age Calculation

Description:

In this tutorial, we’ll dive into Google Apps Script and build a custom function that calculates a person’s age based on their date of birth. Whether you’re a beginner looking to explore Apps Script or you’re interested in creating practical scripts for everyday tasks, this video is for you!

Code Overview:

javascriptCopy code

function CALC_AGE(dateOfBirth) { var dob = new Date(dateOfBirth); var today = new Date(); var age = calculateAge(dob, today); return age; } function calculateAge(dob, today) { var yearsDiff = today.getFullYear() - dob.getFullYear(); var monthsDiff = today.getMonth() - dob.getMonth(); var daysDiff = today.getDate() - dob.getDate(); if (monthsDiff < 0 || (monthsDiff === 0 && daysDiff < 0)) { yearsDiff--; } return yearsDiff; }

This script takes a person’s date of birth as input and calculates their age accurately, accounting for leap years and varying month lengths.

Key Points Covered:

• Creating custom functions in Google Apps Script.
• Working with dates in JavaScript.
• Accurate age calculation logic.

Practical Uses:

• Age calculation for forms and surveys.
• Automating age-related tasks in Google Workspace.

Join me in this step-by-step guide to building this handy age calculation script. Be sure to like, share, and subscribe for more Google Apps Script tutorials and coding insights!

#GoogleAppsScript #JavaScript #AgeCalculation #CodingTutorial #GoogleWorkspace #Scripting

CALC_AGE

In this example, we’ll create a custom formula that calculates the age of a person based on their date of birth.

Scenario: You want to create a custom formula that calculates the age of a person based on their date of birth.

Data Table:

A	B	C
Name	Date of Birth	Age
John	1990-05-15
Jane	1985-09-20
Alex	2002-03-10
Total

Step 1: Setting up the Spreadsheet

• Create a new Google Sheets document.
• Enter the names and date of birth in columns A and B starting from row 2.
• Leave cells in column C empty for now.

Step 2: Writing the Google Apps Script Code

• Click on “Extensions” in the top menu, then select “Apps Script”.
• Delete any default code and replace it with the following script:

function CALC_AGE(dateOfBirth) {

var dob = new Date(dateOfBirth);

var today = new Date();

var age = calculateAge(dob, today);

return age;

}

function calculateAge(dob, today) {

var yearsDiff = today.getFullYear() – dob.getFullYear();

var monthsDiff = today.getMonth() – dob.getMonth();

var daysDiff = today.getDate() – dob.getDate();

if (monthsDiff < 0 || (monthsDiff === 0 && daysDiff < 0)) {

yearsDiff–;

}

return yearsDiff;

}

Step 3: Using the Custom Formula in Google Sheets

• Go back to your Google Sheets document.
• In cell C2, enter the following formula:

=CALC_AGE(B2:B)

Step 3: Testing the Custom Formula

• Enter names and dates of birth in columns A and B starting from row 2.
• Use the custom formula in cell C2 to calculate the ages based on the provided dates of birth.

For example, if you have entered the names and dates of birth as shown in the data table, the calculated ages should appear in column C.

Remember to enable the “Google Apps Script” extension and use the exact function name (CALC_AGE) in your formula.

Code Explained

This Google Apps Script code defines two functions, CALC_AGE and calculateAge, that work together to calculate a person’s age based on their date of birth.

CALC_AGE(dateOfBirth) Function:

This function takes one argument, dateOfBirth, which is expected to be a string representing a date in a specific format (e.g., “YYYY-MM-DD”).

Inside the function, it first converts the dateOfBirth string into a JavaScript Date object using the new Date(dateOfBirth) constructor. This allows for easier date manipulation and comparison.

It then creates another Date object called today, representing the current date.

Next, it calls the calculateAge function and passes both the dob (date of birth) and today as arguments.

Finally, it returns the calculated age, which is the result of the calculateAge function.

calculateAge(dob, today) Function:

1. This function is called by the CALC_AGE function and takes two Date objects as arguments: dob (date of birth) and today (current date).
2. It calculates the age by performing the following steps:
3. It calculates the difference in years between the current year (today.getFullYear()) and the year of birth (dob.getFullYear()).
4. It calculates the difference in months between the current month (today.getMonth()) and the month of birth (dob.getMonth()).
5. It calculates the difference in days between the current day (today.getDate()) and the day of birth (dob.getDate()).
6. It then checks if the months difference is negative (indicating the birthday hasn’t occurred yet in the current year) or if the months difference is zero but the days difference is negative (indicating the birthday is yet to come in the current month). If either of these conditions is true, it subtracts 1 from the calculated years difference.
7. Finally, it returns the calculated age, which represents the number of full years between the date of birth and the current date.

Here’s an example of how you can use the CALC_AGE function:

var dateOfBirth = “1990-05-15”; // Replace with the person’s actual date of birth in the “YYYY-MM-DD” format.

var age = CALC_AGE(dateOfBirth);

console.log(“Age: ” + age + ” years”);

In this example, dateOfBirth is set to a specific date, and then the CALC_AGE function is called to calculate the age. The result is displayed in the console.

function CALC_AGE(dobval){

const dob = new Date(dobval);

const today = new Date();

const age = calculateAge(dob,today);

return age;

}

function calculateAge(dob,today){

let yearsDiff = today.getFullYear() – dob.getFullYear();

const monthDiff = today.getMonth() – dob.getMonth();

const daysDiff = today.getDate() – dob.getDate();

if(monthDiff < 0 || (monthDiff === 0 && daysDiff <0)){

yearsDiff–;

}

return yearsDiff;

}