Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

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?

share|improve this question

1 Answer 1

up vote 2 down vote accepted

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.
share|improve this answer
    
Preface: Make a copy of your spreadsheet and try this first so that you don't have to do the undo. –  Dez Jul 19 '12 at 2:38
    
That said, it worked fine for me on my several hundred cells. –  Nick T Jul 19 '12 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 '13 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 '13 at 3:58
    
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 '13 at 4:36

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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