Using Google Apps Script GetDataRange Function in Google Sheets to Extract Cell Notes

Cell notes only show up when you hover over a cell. This isn’t very conducive when there are multiple cell notes to analyze. However, it is possible to extract those cell notes into another column by using the Google Apps Script GetDataRange Function.

So let's get started on learning how to use the App Script getdatarange function.

What are Google Sheet Cell Notes?

Google Sheets 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, which contains the following details:

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

↗ Enlarge

Copy the template sheet

Step 2: Add Google Sheet notes

If you want to know how to add note in Google Sheets, follow these steps:

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

   ↗ Enlarge

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

3. Add the required text in the cell note.

   ↗ Enlarge

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

Step 3 : Write the automation script using Getdatarange in Google Apps 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.

   ↗ Enlarge

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

   ↗ Enlarge

We have reached the Script Editor. Let’s Code using various functions such as getActiveSpreadsheet, getActiveSheet, getNotes, and getCell.

function getNote()
{
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getActiveSheet();
    var range = sheet.getDataRange();
    var notes = range.getNotes();
    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);
            }
        }
    }
}

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

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.

↗ Enlarge

On successful execution, you will see the following output:

↗ Enlarge

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

Using Google Apps Script GetDataRange Function in Google Sheets to Extract Cell Notes: A Summary

We saw how you can extract cell notes into another column in Google Sheets using the Google Apps Script getdatarange function. 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 the Apps Script getdatarange function.