2

I want to turn some long-winded strings into comments on nine 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?

1 Answer 1

2

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.
6
  • Preface: Make a copy of your spreadsheet and try this first so that you don't have to do the undo. Jul 19, 2012 at 2:38
  • That said, it worked fine for me on my several hundred cells.
    – Nick T
    Jul 19, 2012 at 3:35
  • I think this is Column to NOTES not Column to Comments...it shows up as regular notes for me. Anyway to make them go to actual Comments?
    – user41444
    Jun 5, 2013 at 3:21
  • Probably right, there's somewhat mixed nomenclature in this area...Excel only has comments, Docs has comments + notes, and the Docs API call to make notes is called setComments... Anyways, if you have a question about a batch comment adder, ask a new question, and point this one out as being somewhat wrong
    – Nick T
    Jun 5, 2013 at 3:58
  • 1
    Actually looking at the docs, setComments is deprecated (now it's setNotes), I think they changed what it was called since I tried to do this.
    – Nick T
    Jun 5, 2013 at 4:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.