google-sheetsgoogle-apps-scriptasynchronouscustom-function

Avoid some problems with systematic tested data

In my current job with spreadsheet, all inserted data passes through a test, checking if the same value is found on the same index in other sheets. Failing, a caution message is put in the current cell.

//mimimalist algorithm
function safeInsertion(data, row_, col_)
{
  let rrow = row_ - 1; //range row
  let rcol = col_ - 1; // range col
  const active_sheet_name = getActiveSheetName(); // do as the its name suggest
  const all_sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  
  //test to evaluate the value to be inserted in the sheet    
  for (let sh of all_sheets)
  {
    if (sh.getName() === active_sheet_name)
      continue;  
    //getSheetValues do as its name suggest.
    if( getSheetValues(sh)[rrow][rcol] === data )
      return "prohibited insertion"
  }
  return data;  
} 
// usage (in cell): =safeInsertion("A scarce data", ROW(), COLUMN())

The problems are:

What can I do to obtain a stable sheet using this approach?

PS: The original function does more testing on each data insertion. Those tests consist on counting the frequency in the actual sheet and in all sheets.

EDIT:

In fact, I can't create a stable sheet. For test, a let you a copy of my code with minimal adaptations.

function safelyPut(data, max_onesheet, max_allsheet, row, col)
{
  // general initialization
  const data_regex = "\^\s*"+data+"\s*$"
  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const activesheet = spreadsheet.getActiveSheet();
  const active_text_finder = activesheet.createTextFinder(data_regex)
                                        .useRegularExpression(true)
                                        .matchEntireCell(true);
  const all_text_finder = spreadsheet.createTextFinder(data_regex)
                                     .useRegularExpression(true)
                                     .matchEntireCell(true);
  const all_occurrences = all_text_finder.findAll();

  //test general data's environment
  const active_freq = active_text_finder.findAll().length;
  if (max_onesheet <= active_freq)
    return "Too much in a sheet";
  const all_freq = all_occurrences.length;
  if (max_allsheet <= all_freq)
    return "Too much in the work";
  
  //test unicity in a position
  const active_sname = activesheet.getName();
  for (occurrence of all_occurrences)
  {
    const sname =  occurrence.getSheet().getName();

    //if (SYSTEM_SHEETS.includes(sname))
      //continue;

    if (sname != active_sname)
    if (occurrence.getRow() == row && occurrence.getColumn() == col)
    if (occurrence.getValue() == data)
    {
      return `${sname} contains same data with the same indexes.`;
    };
  }

  return data;  
}

Create two or three cells and put randomly in a short range short range a value following the usage

=safeInsertion("Scarce Data", 3; 5; ROW(), COLUMN())

Do it, probably you will get a unstable sheet.

Solution

  • About cached values confuse me sometimes. The script is changed but not perceived by the sheet until renewing manually the cell's content or refreshing all table. No relevant configuration available to this issue?, when you want to refresh your custom function of safeInsertion, I thought that this thread might be useful.

    About Sometimes, at loading, a messing result appears. Almost all data are prohibited, for example (originally, all was fine!). and What can I do to obtain a stable sheet using this approach?, in this case, for example, how about reducing the process cost of your script? I thought that by reducing the process cost of the script, your situation might be a bit stable.

    When your script is modified by reducing the process cost, how about the following modification?

    Modified script:

    function safeInsertion(data, row_, col_) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const range = ss.createTextFinder(data).matchEntireCell(true).findNext();
  return range && range.getRow() == row_ && range.getColumn() == col_ && range.getSheet().getSheetName() != ss.getActiveSheet().getSheetName() ? "prohibited insertion" : data;
}

    References: