Enhancing Google Sheets with Google Apps Script: Selecting Active Cells and Adding Comments with Range Option

by

Google Sheets is a powerful tool for collaboration and data management. Sometimes, you might want to enhance the interactivity of your sheets by programmatically adding comments to cells or ranges of cells. Using Google Apps Script, you can automate this process, making it easier to manage larger data sets or collaborative projects. In this blog post, we’ll explore how to select active cells or specified ranges and add comments to them, enhancing the user experience and functionality of Google Sheets.

Selecting Active Cells and Adding Comments

First, let’s start with a basic script that selects an active cell and adds a comment to it. This can be particularly useful for providing feedback or notes directly within the sheet.

Basic Example: Adding Comments to the Active Cell

function addCommentToActiveCell() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const cell = sheet.getActiveCell();

  // Set the comment on the active cell
  const comment = 'Reviewed by user on ' + new Date().toDateString();
  cell.setNote(comment);
}

In this simple script:

  • We retrieve the active sheet and then the active cell.
  • We define a comment that includes a timestamp.
  • We use the setNote() method to attach the comment to the cell.

Extending Functionality: Selecting a Range and Adding Comments

Now, let’s expand this functionality to allow users to select a range of cells and add the same comment to all selected cells. This feature is beneficial when you want to comment on multiple cells at once.

function addCommentToRange(range) {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const selectedRange = sheet.getRange(range);

  // Set the comment on the selected range
  const comment = 'Reviewed by user on ' + new Date().toDateString();
  selectedRange.setNotes(Array(selectedRange.getNumRows()).fill(Array(selectedRange.getNumColumns()).fill(comment)));
}

In this enhanced script:

  • We accept a range parameter, which specifies the cell range (e.g., ‘A1:C3’).
  • We retrieve this range from the active sheet.
  • We use the setNotes() method to apply the same comment to all cells in the range. This method requires a 2D array matching the dimensions of the range.

Usage Example

Here’s how you could use these functions in practice:

  1. Single Cell Comment:
    • Run addCommentToActiveCell() to add a comment to the currently selected cell.
  2. Range Comment:
    • Specify the range and run addCommentToRange('B2:D4') to add comments to all cells within the specified range.

Conclusion

By utilizing Google Apps Script, you can significantly extend the functionality of Google Sheets. Adding comments programmatically to selected cells or ranges can help streamline workflows, especially in environments where team collaboration and data verification are frequent. These scripts provide a foundation that you can further customize to fit specific needs, such as integrating user inputs or handling dynamic ranges based on certain conditions. Whether you’re managing project timelines, feedback sessions, or data audits, these tools can enhance your productivity and data management efficiency in Google Sheets.