Extract Cell notes into Another Column Using Google Apps Script

In this blog I am going to show you how to extract cell notes into another column using Google Apps Script.

So let’s get started.

What are Cell Notes ?

Cell Notes are simply little text boxes added to the cell which just add extra information. These are viewed by hovering over the cell.

Step 1 : Sample Google Sheet

The Google Sheet that I will be using is this blog, contains the following details: If you prefer working with the Google Sheet, click here.

1. Name of the city
2. Name of the product
3. Quantity sold
4. Total price sold
5. Category of the product

Step 2 : Add Cell Notes

To add cell notes to the Google Sheet, follow these steps:

1. Right click on the cell, you want to add the note in.

2. A text box will appear on clicking Insert note.

3. Add the required text in the cell note.

4. Here you have successfully added a note to the cell.

Step 3 : Write the Automation Script

While you are in the sheet, launch the Apps Script Editor.

To do that:

1. Click on the tools button next to the Add-ons button.

2. Next click on the Script Editor option. This brings up the Script Editor as shown below.

We have reached the Script Editor. Let’s Code.

function get Note()
{
   (1) var ss = SpreadsheetApp.getActiveSpreadsheet();
   (2) var sheet = ss.getActiveSheet();
   (3) var range = sheet.getDataRange();
   (4) var notes = range.getNotes();

Declaring the function:

1. Get the Active Spreadsheet
2. Get the Active Sheet(Since we have only one sheet)
3. Get the data range
4. Get the notes in the range

for(var i = 0;i < notes.length;i++){
  for (var j = 0; j < notes[0].length; j++){
    if(notes[i][j]){
      var note = notes[i][j];
      var cell = range.getCell(i+1,j+1);
      cell.offset(0,1).setValue(note);
    }
  }
  }
 }

The cell notes that we get are in the form of an array. The first part of the array contains the index number of the cell note, followed by the cell note content.

1. Store the note in an array.
2. Print the note
3. Get the cell next to the cell note
4. Using the offset function set the note in the cell

Step 4 : Check the Output

We are done with the code, go ahead and save it. Select the correct function (getCellNote) as shown below and run the program.

On successful execution, you will see the following output:

Here you can see that the cell note has been inserted next to the original cell.

If you prefer watching the video, check out the link below.

Summary

We saw how you can extract cell notes into another column using Google Apps Script. To sum up :

1. Accessed the Spreadsheet followed by the Active sheet
2. Got the Data Range
3. Got the notes from the specified range
4. Used two for loops to check if the note is not empty
5. Stored the note (or notes) in an array
6. Got the cell next to the cell note
7. Set the note next to the original cell

I hope you have understood how to Extract Cell notes into another column using Google Apps Script. You can send your feedback to aryanirani123@gmail.com .

Originally published on the YAMM blog