Google表格-优化功能以在一定范围内创建笔记(非常慢) [英] Google sheets - Optimisation of function to create notes in a range (very slow)

问题描述

我有一个脚本，可以根据它们的值在单元格上创建Notes，但是过程非常缓慢，我的工作表有15000行.通过优化脚本是否可以减少延迟?

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:我使用带有法语参数的电子表格.

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); }
 } 
}

工作表示例: https://docs.google.com/spreadsheets/d/1lu7dEoyO2NDHV4phXeh8DAAkbBuQG5EQWwMA6SJDP1A/edit?usp = sharing

推荐答案

说明:

两个技巧:

• 在可能的情况下，避免不必要的api调用.您正在迭代地使用与电子表格文件交互并导致极端延迟的方法.阅读最佳做法.

当将 null 用作 setNote 的参数时，未设置任何注释.我们可以利用，则将 null 分配给元素，否则采用单元格:

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]);

这将使您摆脱 for 循环，还可以创建可以直接在

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);

(使用鼠标)选择特定范围并插入注释(取决于条件):

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);
}

解决方案-预定范围:

这是一种更静态的方法.您定义一个特定的工作表"Sheet1" ，并为 B 列中的所有单元格(直到具有工作表内容的最后一行)插入注释(取决于条件)):

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);
}

这篇关于Google表格-优化功能以在一定范围内创建笔记(非常慢)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！