10+ Google Sheets Named Functions To Boost Productivity

Unlocking Efficiency with Google Sheets Named Functions

In the realm of spreadsheet management, Google Sheets stands out for its robust features and seamless collaboration capabilities. One of the most powerful tools within Google Sheets is the ability to create named functions, which can significantly boost productivity by simplifying complex calculations, making formulas more readable, and reducing the effort required to perform repetitive tasks. Named functions in Google Sheets are essentially custom formulas that you can define and use like any other Google Sheets function. They are particularly useful for automating tasks, enhancing data analysis, and streamlining workflow processes.

1. SUMBYCOLOR

The SUMBYCOLOR function is a custom function that sums all the cells in a range that have a specific background color. This can be incredibly useful for quickly calculating totals based on visual categorizations. To create this function, you’ll need to use Google Apps Script, writing a function that iterates through the range, checks the background color of each cell, and sums the values of cells with the matching color.

Example: =SUMBYCOLOR(range, color) where range is the area you want to check and color is the color you’re looking for, specified either by its hex code or the actual color.

function SUMBYCOLOR(range, color) {
  var sum = 0;
  for (var i = 0; i < range.length; i++) {
    if (range[i].getBackground() === color) {
      sum += range[i].getValue();
    }
  }
  return sum;
}

2. AVERAGEIFTEXTCONTAINS

This named function averages all the cells in a specified range that contain specific text within their values. It’s a versatile tool for data analysis, especially when dealing with large datasets where filtering by text is necessary.

Example: =AVERAGEIFTEXTCONTAINS(range, text) where range is the area to average and text is what you’re looking for within the cells.

function AVERAGEIFTEXTCONTAINS(range, text) {
  var sum = 0;
  var count = 0;
  for (var i = 0; i < range.length; i++) {
    if (range[i].getValue().toString().indexOf(text)!== -1) {
      sum += range[i].getValue();
      count++;
    }
  }
  return sum / count;
}

3. TOUPPERCASEFIRST

A simple yet useful function, TOUPPERCASEFIRST capitalizes the first letter of each word in a cell, improving text readability and formatting.

Example: =TOUPPERCASEFIRST(text) where text is the string you want to capitalize.

function TOUPPERCASEFIRST(text) {
  return text.charAt(0).toUpperCase() + text.slice(1);
}

4. EXTRACTUNIQUEDATES

This function extracts and returns a list of unique dates from a specified range, ignoring any non-date values. It’s particularly useful for summarizing date-related data.

Example: =EXTRACTUNIQUEDATES(range) where range is the area containing dates.

function EXTRACTUNIQUEDATES(range) {
  var dates = [];
  for (var i = 0; i < range.length; i++) {
    if (!isNaN(range[i].getValue())) {
      var date = new Date(range[i].getValue());
      if (dates.indexOf(date.toLocaleDateString()) === -1) {
        dates.push(date.toLocaleDateString());
      }
    }
  }
  return dates;
}

5. CALCULATEWORKDAYS

Calculates the number of working days between two dates, excluding weekends and optionally, specific holidays. This function is indispensable for project planning and timeline management.

Example: =CALCULATEWORKDAYS(startdate, enddate, holidays) where startdate and enddate define your period, and holidays is an optional range of dates to exclude.

function CALCULATEWORKDAYS(startdate, enddate, holidays) {
  var count = 0;
  for (var date = startdate; date <= enddate; date.setDate(date.getDate() + 1)) {
    if (date.getDay()!== 0 && date.getDay()!== 6 &&!holidays.includes(date.toLocaleDateString())) {
      count++;
    }
  }
  return count;
}

6. REMOVECHARACTERS

Removes specified characters from a string, useful for cleaning up imported data or preparing text for analysis.

Example: =REMOVECHARACTERS(text, characters) where text is the string to clean and characters are the ones to remove.

function REMOVECHARACTERS(text, characters) {
  return text.replace(new RegExp('[' + characters + ']', 'g'), '');
}

**7.)“.station函數”

A named function that converts a date to a specific format, such as converting a serial number to a date or changing the format of a date string.

Example: =DATEFORMAT(date, format) where date is the date to format and format is the desired output format.

function DATEFORMAT(date, format) {
  var d = new Date(date);
  switch (format) {
    case "YYYY-MM-DD":
      return d.getFullYear() + "-" + pad(d.getMonth() + 1) + "-" + pad(d.getDate());
    // Add more formats as needed
  }
  
  function pad(n) {
    return n < 10? '0' + n : n;
  }
}

8. SELECTIONSTATS

Provides statistical analysis of a selected range, including mean, median, mode, and standard deviation, directly in the formula bar, making it a quick reference for data insights.

Example: =SELECTIONSTATS(range, metric) where range is the data to analyze and metric specifies which statistic to return.

function SELECTIONSTATS(range, metric) {
  var data = range.map(function(val) { return val; });
  switch (metric) {
    case "mean":
      return data.reduce(function(a, b) { return a + b; }, 0) / data.length;
    case "median":
      var sorted = data.sort(function(a, b) { return a - b; });
      var mid = Math.floor(sorted.length / 2);
      return sorted.length % 2!== 0? sorted[mid] : (sorted[mid - 1] + sorted[mid]) / 2;
    // Implement more metrics as needed
  }
}

9. RANDOMIZECELLS

Randomizes the order of cells in a specified range, useful for creating randomized lists or for statistical sampling.

Example: =RANDOMIZECELLS(range) where range is the area to randomize.

function RANDOMIZECELLS(range) {
  var cells = range.map(function(val) { return val; });
  for (var i = cells.length - 1; i > 0; i--) {
    var j = Math.floor(Math.random() * (i + 1));
    var temp = cells[i];
    cells[i] = cells[j];
    cells[j] = temp;
  }
  return cells;
}

10. CHARTDATAEXTRACTOR

Extracts data directly from a chart in Google Sheets, allowing for further analysis or use of the data in other parts of your spreadsheet.

Example: =CHARTDATAEXTRACTOR(chart) where chart is the chart object from which to extract data.

function CHARTDATAEXTRACTOR(chart) {
  // This example requires accessing the chart object, which is more complex and may involve using SpreadsheetApp services
  // The logic here would depend on how the chart data is structured and accessed
}

Creating and Implementing Named Functions

To create a named function in Google Sheets, you typically use Google Apps Script. Here’s a general outline: 1. Open your Google Sheet. 2. Click on “Tools” > “Script editor”. This opens the Google Apps Script editor. 3. In the script editor, write your function using JavaScript. For example, if you wanted to create a simple function that adds two numbers, it might look like this:

function ADDTWONUMBERS(a, b) {
  return a + b;
}

1. Save the script. You can then use this function directly in your Google Sheet like any other function, for example, =ADDTWONUMBERS(2, 3).

Conclusion

Named functions in Google Sheets offer a powerful way to extend the capabilities of the platform, making complex tasks simpler and repetitive tasks automated. By leveraging these custom functions, users can significantly boost their productivity, enhance data analysis, and streamline their workflow processes. Whether you’re dealing with date calculations, text manipulation, or statistical analysis, there’s likely a named function that can help. Remember, the key to getting the most out of named functions is to understand your needs and then tailor your functions to meet those specific requirements. Experiment with different scripts, share functions with colleagues to collaborate on complex tasks, and don’t hesitate to explore the vast library of community-developed functions available online. With practice and creativity, named functions can become an indispensable tool in your Google Sheets toolkit.