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:
- cached values confuse me sometimes. The script or data is changed but not perceived by the sheet itself until renewing manually the cell's content or refreshing all table. Is there any relevant configuration available to this issue?
- Sometimes, at loading, a messing result appears. Almost all data are prohibited, for example (originally, all was fine!).
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.
