Automatically copy comment from the cell once the cell is selected

Question

I need some help from the experts here.

Please take a look at this spreadsheet:

https://docs.google.com/spreadsheets/d/1tEJNDqJwGxcgt5Xm7tR4wkuQk88jOH9hPQaaWr9_BSE/edit?usp=share_link

Here is the story (in short):

I work with many comments, and I need to respond to them. Because there are many of them I need to have quick access to my pre saved answers. So I have a spreadsheet, and my answers are written as comments to cells, and the cells itself just show me category (for easier search)

So I need when I make single mouse click on the cell it copies automatically the comment from the selected cell to the clipboard. Is there any way to make it using Apps Script?

Because I'm not a developer and can't write the code by myself I asked chatGPT to write the script for me.

The result didn't work for me. It's just doesn't work

function onSelectionChange(e) {
  var sheet = SpreadsheetApp.getActiveSheet();
  var cell = sheet.getActiveCell();
  var comment = cell.getComment();
  
  if(comment != "") {
    var clipboard = Utilities.getClipboard();
    clipboard.setContent(comment);
    SpreadsheetApp.getActive().toast('Comment copied to clipboard');
  }
}

function onOpen() {
  var spreadsheet = SpreadsheetApp.getActive();
  var entries = [{
    name : "onSelectionChange",
    trigger : "onSelectionChange"
  }];
  ScriptApp.newTrigger("onSelectionChange").forSpreadsheet(spreadsheet).onSelectionChange().create();
}

When I run this script inside Apps Script it show that everything works, but there is no change inside the spreadsheet itself.

score 0 · Accepted Answer · answered Mar 08 '23 at 15:28

There are a couple of reasons that explain why this is not working:

1. Google Apps Script is not able to interact with the Clipboard as it runs on the server side, you would need to use an HtmlService method as mentioned by @Cooper in his comment at this post for example.
2. .getComment() is an old method designed for the old version of Comments as mentioned here, it is only taking the Notes(Right click > Insert notes) from the cell and the option to get Comments(Right click > comments) is still a Feature Request.

Unfortunately what you're trying to achieve is not possible with Apps Script due to the fact that comments cannot be extracted using code yet, this is a Feature Request at the moment, you can add your +1 on it at https://issuetracker.google.com/issues/36756650.

More technical data:

Thanks a lot! You've helped me I thought there was my mistake somewhere It's unfortunate that this feature is currently unavailable — Just don't, Mar 09 '23 at 08:00

Automatically copy comment from the cell once the cell is selected

1 Answers1