0
137
VistasHow 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.
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
·
Juan Pablo Isaza
1 Respuestas
Responde la pregunta0
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:
·
Juan Pablo Isaza
Denunciar
Responde la pregunta