In this example, we’ll create a custom formula that helps you analyze survey data by calculating the average rating for a set of responses.
Scenario: You want to create a custom formula that calculates the average rating from a set of survey responses, where each response is rated on a scale from 1 to 5.
🌟 Simplify Your Data Analysis with Custom Google Apps Script for Average Ratings! 📊
Are you overwhelmed by the task of calculating average ratings from a multitude of responses to various questions? We’ve got you covered! In our latest video, we’re introducing a powerful Google Apps Script that automates the process, making it a breeze to determine average ratings for each question.
📋 In this video, we’ll cover:
✅ The CALC_AVERAGE_RATING function: Learn how to harness this custom script to effortlessly compute average ratings for multiple questions and responses. ✅ Real-life use cases: We’ll walk you through practical examples, such as survey analysis, customer feedback assessments, and more. ✅ Streamlined insights: Discover how this script transforms raw data into meaningful statistics.
You don’t need to be a coding expert to get started. We’ll guide you step by step through the setup process, ensuring you can easily incorporate this automation into your data analysis workflow.
Say goodbye to manual number crunching and hello to efficient data analysis! Join us for this essential tutorial.
#GoogleAppsScript #DataAnalysis #Automation #AverageRatings #YouTubeTutorials #DataInsights #SurveyAnalysis #CustomerFeedback #Productivity #CodingTutorial
function CALC_AVERAGE_RATING(questions,responses){
const averageRatings = [];
for(let i=0;i<questions.length;i++){
let totalRating = 0;
for(let j=0;j<responses[i].length;j++){
totalRating += responses[i][j];
}
const averageRating = totalRating / responses[i].length;
averageRatings.push([averageRating]);
}
return averageRatings;
}
The provided code defines a Google Apps Script function named CALC_AVERAGE_RATING. This function is designed to calculate the average ratings for a list of questions based on corresponding responses. Here’s a detailed explanation of how the code works:
- Function Name: CALC_AVERAGE_RATING(questions, responses)
This function takes two parameters as input:- questions: An array that contains the questions for which you want to calculate average ratings.
- responses: A 2D array where each sub-array represents the responses to a specific question. The outer array contains all the questions, and each sub-array contains the responses for a particular question.
- Initialization:
Inside the function, an empty array named averageRatings is initialized. This array will store the calculated average ratings for each question. - Loop Through Questions:
The function uses a for loop to iterate through each question in the questions array. The loop index i represents the current question being processed. - Calculate Total Rating:
Within the outer loop, there is an inner loop that iterates through the responses for the current question (retrieved using responses[i]). The loop index j represents the current response being considered. The totalRating variable is used to accumulate the sum of all the responses for the current question. - Calculate Average Rating:
After iterating through all the responses for the current question, the code calculates the average rating for that question by dividing the totalRating by the number of responses (which is the length of the responses[i] array). The average rating is then stored in an array within the averageRatings array. - Return the Results:
After processing all questions and their responses, the function returns the averageRatings array, which contains the average rating for each question.
In summary, this script is helpful for conducting surveys or assessments where multiple respondents provide ratings for various questions. It calculates the average rating for each question, allowing you to analyze the overall response for each item on your list of questions. The function provides a simple way to obtain insights into how questions are rated on average based on collected responses.
Data Table:
| A | B | C | D | E |
| Question | Response 1 | Response 2 | Response 3 | Average Rating |
| Question 1 | 4 | 5 | 3 | |
| Question 2 | 2 | 4 | 5 | |
| Question 3 | 3 | 3 | 4 | |
| Total |
Step 1: Setting up the Spreadsheet
- Create a new Google Sheets document.
- Enter the survey questions in column A starting from row 2.
- Enter the responses for each question in columns B to D.
- Leave cells in column E 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:
// Custom formula to calculate average rating
function CALC_AVERAGE_RATING(questions, responses) {
var averageRatings = [];
for (var i = 0; i < questions.length; i++) {
var totalRating = 0;
for (var j = 0; j < responses[i].length; j++) {
totalRating += responses[i][j];
}
var averageRating = totalRating / responses[i].length;
averageRatings.push([averageRating]);
}
return averageRatings;
}
Step 3: Using the Custom Formula in Google Sheets
- Go back to your Google Sheets document.
- In cell E2, enter the following formula:
=CALC_AVERAGE_RATING(A2:A, B2:D)
Explanation of the Code:
- The function CALC_AVERAGE_RATING calculates the average rating for a set of survey responses.
- It takes two parameters: questions and responses.
- questions: The range of cells containing the survey questions.
- responses: The range of cells containing the responses for each question.
- Inside the function, a loop iterates through each question.
- Within the question loop, another loop iterates through each response for the current question.
- The total rating for each question is calculated by summing up all the responses.
- The average rating for each question is calculated by dividing the total rating by the number of responses.
- The calculated average ratings are stored in the averageRatings array.
- The function returns the array of calculated average ratings.
Step 4: Testing the Custom Formula
- Enter survey questions in column A starting from row 2.
- Enter responses for each question in columns B to D.
- Use the custom formula in cell E2 to calculate the average rating for each question.
For example, if you have entered the survey responses as shown in the data table, the calculated average ratings for each question should appear in column E.
Remember to enable the “Google Apps Script” extension and use the exact function name (CALC_AVERAGE_RATING) in your formula.