CODEWITHSUNDEEP
javascriptgoogle-apps-scriptoptimizationgoogle-sheets
Nad
Nad

Reputation: 179

Google sheets - Optimisation of function to create notes in a range (very slow)

I have a script to create Notes on cells based on their value, but the process is very slow and my sheet has 15000 rows. Is it possible to reduce the delay by optimizing the script ?

PS : I use spreadsheet with french parameters.

function InsertCellsNotes(){

  var plage = SpreadsheetApp.getActiveSpreadsheet().getSelection().getActiveRange(); 
  var Notes = plage.getValues();
  var NB_lines = Notes.length;
 
  for (var i=1; i<NB_lines+1; i++){  // ajouter +1 !
    var myCell = plage.getCell(i, 1);
    var cellValue = Notes[i-1];

    if (cellValue == "#N/A" || ""){   } 
    else {  myCell.setNote(cellValue); }
 } 
}

An example of the sheet : https://docs.google.com/spreadsheets/d/1lu7dEoyO2NDHV4phXeh8DAAkbBuQG5EQWwMA6SJDP1A/edit?usp=sharing

Upvotes: 1

Views: 145

Answers (1)

Marios
Marios

Reputation: 27350

Explanation:

Two tricks:

  • Avoid unnecessary api calls when possible. You are iteratively using methods that interact with the spreadsheet file and that causes extreme delays. Read best practices.

  • When you use null as an argument for setNote, no note is set. We can take advantage of this and construct an array by using the map method. Namely, if the value is #N/A or blank "", assign null to the element, otherwise take the value of the cell:

    var notes = rng.getValues().flat().map(v=>[v=="#N/A" || ""?null:v]);

This will allow you to get rid of the for loops but also create an array that can directly be used in the setNotes function: rng.setNotes(notes);

Solution - active range:

Select (with your mouse) a particular range and insert notes (depending on the condition):

function InsertCellsNotes(){
   var rng = SpreadsheetApp.getActiveRange();
   var notes = rng.getValues().flat().map(v=>[v=="#N/A" || ""?null:v]);
   rng.setNotes(notes);
}

Solution - predefined range:

This is a more static approach. You define a particular sheet "Sheet1" and for all the cells in column B (until the last row with content of the sheet) you insert notes (depending on the condition):

function InsertCellsNotes(){
   var plage = SpreadsheetApp.getActive().getSheetByName("Sheet1");
   var rng = plage.getRange(1,2,plage.getLastRow(),1);
   var notes = rng.getValues().flat().map(v=>[v=="#N/A" || ""?null:v]);
   rng.setNotes(notes);
}

Upvotes: 3

Related Questions