Star rating system in Google Sheets

Star rating systems are commonly used in websites where users rate products or services. For example, you've probably used or at least seen star ratings on sites like Amazon where users write reviews about products that they've purchased.

Star rating systems can also be used to collect and/or analyze user feedback in spreadsheets. It can be helpful to visualize user ratings using a star system versus just displaying the corresponding number. However, Google Sheets does not have a way to display stars corresponding to a numeric rating so we will have to create it ourselves.

In this tutorial, I will show you how to write a custom function in Google Sheets to display star ratings. We will use Google Apps Script to write the custom function.

We will use the 5-star rating system where 0 is the lowest rating and 5 is the highest rating.

Prerequisites

This tutorial will assume that you are familiar with:

• Google Sheets

• Familiarity with basic coding concepts and Google Apps Script

• How to create a custom function in Google Sheets using Apps Script

How will the custom function work?

In this tutorial, we will be writing a custom function called STAR(). This function will accept a numeric value val as input and it will return the corresponding star rating for that value. The star rating itself is just text containing a certain number of star (★) characters.

The function will also support an optional boolean parameter called fill that will be set to false by default. If fill is set to true, the function will always return 5 star characters with white stars (☆) used as needed to get to 5 characters. For example, if the rating is 3 stars, the function will return:

• ★★★☆☆ if fill is set to true

• ★★★ if fill is set to false (default)

Creating the custom function to display star ratings in Google Sheets

First, open the script editor by selecting Extensions —> Apps Script from the menu in Google Sheets.

Then, replace the code in the script editor with the function below. Save your script.

/**
 * Returns a star rating for a numeric value
 * @param {4} val The value to generate the star rating for
 * @param {true} fill optional - Fill with blank stars as needed
 * @returns {string} A star rating for the value
 * @customfunction
 */
function STAR(val, fill = false) {
  
  // If val is an array, it means that
  // an ARRAYFORMULA is being used.
  if(Array.isArray(val))
    return val.map(STAR);
  
  // We have to round since half stars are not supported
  val = Math.round(val);
  
  // Only 5-star ratings are supported so
  // val must be between 0 and 5.
  if(val < 0 || val > 5)
    return "N/A"
  
  let returnVal = "";
  
  // Add val number of stars
  for(let i = 0; i < val; i++)
    returnVal += "\u2605";
  
  // If fill is true, add empty stars to get to 5 stars.
  if(fill) {
    for(let i = 0; i < 5 - val; i++)
      returnVal += "\u2606";
  }
  return returnVal;
}

Once you save your script, you should be able to use the STAR() function from your spreadsheet just like any other function.

Conclusion

In this tutorial, you learned how to display star ratings in Google Sheets using Apps Script. We created a custom function called STAR() that accepts a numeric value as input and returns the corresponding 5-star rating.