Google Sheets - Display Note From Reference Sheet Using Apps Script

Question

Hello Helpful Strangers

I am busy building an inventory location system using Google Sheets. I have two tabs:

Tab 1: "Basket" This shows items as chosen by the customer

Basket 1	Basket 2	Basket 3
Banana	Pear	Apple
Orange	Orange	Pear
Lettuce	Cucumber	Cucumber
Broccoli	Lettuce	Broccoli
Pumpkin

Tab 2: "Data" This shows more details about each item such as category and price.

Item	Category	Price
Apple	Fruit	$1
Banana	Fruit	$1.50
Pear	Fruit	$1.25
Orange	Fruit	$1
Cucumber	Vegetable	$1.33
Lettuce	Vegetable	$2
Broccoli	Vegetable	$2.10
Pumpkin	Vegetable	$3

I would like to use Google Sheets' notes feature to display the category and price on the Basket sheet when I hover over an item as in the image below:

I have no experience in Apps Script and require some assistance please.

An added bonus would be to auto-size (trim) the note size according to the displayed content.

Many thanks for your help in advance.

Answer 1

Caveat:

This solution is very specific to the data and 2 sheets, Basket and Data you provide above. I would suggest you study the app script carefully before using it.

Sample Script

function addNotes() {
  try {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var bsh = ss.getSheetByName("Basket");
    var dsh = ss.getSheetByName("Data");
    var bValues = bsh.getDataRange().getValues();
    bValues.shift();  // remove the first row
    var dValues = dsh.getDataRange().getValues();
    dValues.shift();  // remove the first row
    dValues = dValues.map( 
      row => row[0]+" - "+row[1]+" - $"+(( typeof row[2] === "number" ) ? row[2].toFixed(2) : row[2] )
    );
    var notes = [];
    bValues.forEach( row => {
        temp = [];
        row.forEach( col => {
          temp.push( dValues.find( item => item.includes(col) ) );
        } );
        notes.push(temp);
      } );
    bsh.getRange(2,1,notes.length,notes[0].length).setNotes(notes);
  }
  catch(err) {
    console.log(err);
  }
}

References:

• https://developers.google.com/apps-script/reference/spreadsheet/range#setNotes(Object)