Extract cell notes using Google Apps Script

June 09, 2021
Extract cell notes using Google Apps Script
Table of Contents
Table of Contents

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 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);
            }
        }
    }
}
function getNote()
{
(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.

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 .



Great! Next, complete checkout for full access to The Yet Another Mail Merge Blog
Welcome back! You've successfully signed in
You've successfully subscribed to The Yet Another Mail Merge Blog
Success! Your account is fully activated, you now have access to all content
Success! Your billing info has been updated
Your billing was not updated