CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
Thibaud GARNIER
Thibaud GARNIER

Reputation: 53

How to set quickly different notes on differents values with a script?

I would like to make a script that will set a note on each cell that have a specific value.

enter image description here

I made a script that creates new conditionnal format rule to color the cells in function of their value :

function couleurs() 
{
  var ss = SpreadsheetApp.getActive();
  var NP = ss.getSheetByName("Network Patching");
  var VLANS = ss.getSheetByName("VLANS");
  var conditionalFormatRules = NP.getConditionalFormatRules();

  let listeVlansIDBrute = VLANS.getRange(3,6, VLANS.getLastRow(),3).getValues();         //On recupere les valeurs des cellules dans la plage donnée.
  let listeCouleursBrute = VLANS.getRange(3,6, VLANS.getLastRow(),1).getBackgrounds();
  let listeFontColor = VLANS.getRange(3,6, VLANS.getLastRow(),1).getFontColors();
  
  conditionalFormatRules.splice(0, conditionalFormatRules.length)
  console.log(listeVlansIDBrute)
  console.log(listeCouleursBrute)
  for(let i=0; i<listeVlansIDBrute.length;i++)
  {
    if(listeVlansIDBrute[i][0]!=""&& listeVlansIDBrute[i][0]!=null && listeVlansIDBrute[i][3]!="" && listeCouleursBrute[i]!="#ffffff")
    {    
      NP.getRange(1,1, NP.getLastRow(), NP.getLastColumn()).activate();
      NP.setConditionalFormatRules(conditionalFormatRules);
      conditionalFormatRules = NP.getConditionalFormatRules();
      conditionalFormatRules.splice(0, 0, SpreadsheetApp.newConditionalFormatRule().setRanges([NP.getRange(1,1, NP.getLastRow(), NP.getLastColumn())])
      .whenTextEqualTo(listeVlansIDBrute[i][0]).setBackground(listeCouleursBrute[i]).setFontColor(listeFontColor[i]).build());
    }
  }
  NP.setConditionalFormatRules(conditionalFormatRules);
}

I would like to add a note on each cell, where its value has to be the associated one in the column AH. Exemple : On each cell that contains "2404", set a note with "N00-Infographie_T2" I tried to use a textFinder but it is too slow.

Do you have an other idea ? Thank you

Upvotes: 1

Views: 52

Answers (1)

Yuri Khristich
Yuri Khristich

Reputation: 14537

This script takes notes from the range 'AG6:AH8' (you can change it) and adds them to selected cells.

// menu
function onOpen() {
  SpreadsheetApp.getUi().createMenu('🛠 Scripts')
  .addItem('Add notes to selected cells', 'add_notes_to_selected_cells')
  .addToUi();
}

// main function
function add_notes_to_selected_cells() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();

  var notes_table = sheet.getRange('AG6:AH8').getValues(); // redefine your range
  var notes_obj = {}
  notes_table.forEach(n => notes_obj[n[0]] = n[1]);

  var active_range = sheet.getActiveRange();
  var data = active_range.getValues();
  var active_row = active_range.getRow();
  var active_col = active_range.getColumn();

  for (var row in data) {
    for (var col in data[row]) {
      try {
        var note = notes_obj[data[row][col]];
        sheet.getRange(active_row + +row,active_col+ +col).setNote(note);
      }
      catch(e) {}
    }
  }
}

The script starts via the custom menu: 'Scripts > Add notes to selected cells'. Just select any cell(s) and click on the menu:

enter image description here

Upvotes: 2

Related Questions