How can I comment several dozen cells in a Google Spreadsheet using data from cells?

Question

I want to turn some long-winded strings into comments on 9 sets of 96 cells so they're just visible on mouse-over. How can I do this without manually going to Insert Comment and copy-pasting 864 times?

Answer 1

Apparently this is easy enough to script.

Set Up:

1. Create an empty script for your Spreadsheet (Tools >> Script Editor... >> Blank Project)

2. Paste the following code into the editor:

   function columnToComments() {
     // find selection (the source comments)
     var workbook = SpreadsheetApp.getActiveSpreadsheet();
     var sheet = workbook.getActiveSheet();
     var source = sheet.getActiveSelection();
   
     // determine target (the column to the left of the selection)
     var target = source.offset(0, -1);
   
     // copy comments
     comments = source.getValues();
     target.setComments(comments);
   
     // remove temporary comment column
     askclean = Browser.msgBox("Remove selected 'source' column?", 
                               Browser.Buttons.YES_NO);
     if (askclean == "yes") {
       sheet.deleteColumn(source.getColumn());
     }
   }
   

3. Save the project (File >> Save), give it some name (e.g. "Commentator").

4. Close the script window and return to your spreadsheet.

Using it:

1. Insert a column to the right of the column you wish to add comments to and fill it with the source material.

2. Select all the comments then run the script by going to Tools >> Script manager..., select the function (columnToComments), then click Run. The script will remove the comment source column after it copies the comments if you like.

   • If something went horribly, horribly wrong ^{_{(Disclaimer: not my fault, blah be blah blah...)}}, you can undo a couple times and it should restore the column then remove all the comments.