I have a Google Apps Script that updates module values in columns Y, Z, AA, and AB based on an area value in column W. The script works by getting the selected cells and matching their area values with a catalog in another sheet. However, when I'm working with filtered views and select cells in column W, the script often fails to properly update the module columns (Y-AB), even though the area values match. It seems like the script is not handling filtered views correctly.
function updateModuliFromSelection() {
try {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const dipendentiSheet = ss.getSheetByName('DIPENDENTI');
if (!dipendentiSheet) {
throw new Error('Sheet "DIPENDENTI" not found');
}
const moduliSheet = ss.getSheetByName('MODULI');
if (!moduliSheet) {
throw new Error('Sheet "MODULI" not found');
}
// Get active selection
const selection = dipendentiSheet.getSelection();
if (!selection) {
throw new Error('Please select some cells first');
}
// Get all selected ranges
const selectedRanges = selection.getActiveRangeList().getRanges();
if (!selectedRanges || selectedRanges.length === 0) {
throw new Error('No ranges selected');
}
// Get the first selected cell's area value to use as reference
const firstSelectedRange = selectedRanges[0];
const firstRow = firstSelectedRange.getRow();
const referenceArea = dipendentiSheet.getRange(firstRow, 23, 1, 1).getValue();
if (!referenceArea) {
throw new Error('No area value found in the first selected row');
}
// Get the catalog data
const catalogData = moduliSheet.getRange('A2:C' + moduliSheet.getLastRow()).getValues();
// Process each selected range
selectedRanges.forEach(range => {
const startRow = range.getRow();
const numRows = range.getNumRows();
// Process each row in the current range
for (let i = 0; i < numRows; i++) {
const currentRow = startRow + i;
// Get the area value from column W (23)
const areaValue = dipendentiSheet.getRange(currentRow, 23, 1, 1).getValue();
// Only process the row if its area matches the reference area
if (areaValue && areaValue.trim() === referenceArea.trim()) {
// Get current module values
const currentModules = dipendentiSheet.getRange(currentRow, 25, 1, 4).getValues()[0];
// Find matching modules for the area
const matchingModules = catalogData.filter(([module, , catArea]) =>
catArea && catArea.trim() === areaValue.trim()
).map(([module]) => module);
// Prepare the data for the four module columns
// Keep existing values if no new matches
const moduleData = [
[
matchingModules[0] || currentModules[0] || '',
matchingModules[1] || currentModules[1] || '',
matchingModules[2] || currentModules[2] || '',
matchingModules[3] || currentModules[3] || ''
]
];
// Update columns Y, Z, AA, AB only if there are new values
if (matchingModules.length > 0) {
dipendentiSheet.getRange(currentRow, 25, 1, 4).setValues(moduleData);
}
}
}
});
SpreadsheetApp.getActive().toast('Aggiornamento completato con successo');
} catch (error) {
SpreadsheetApp.getActive().toast('Errore: ' + error.message, 'Errore', 30);
console.error(error);
}
}
function onOpen() {
const ui = SpreadsheetApp.getUi();
ui.createMenu('Funzioni Personalizzate')
.addItem('Aggiorna Moduli dalla Selezione', 'updateModuliFromSelection')
.addToUi();
}
The script should:
Take the selected cells in column W Get their area values Match these values with a catalog in another sheet Update columns Y, Z, AA, AB accordingly
I suspect the issue might be related to how getSelection() and getActiveRangeList() work with filtered views, but I'm not sure how to fix it.
Any suggestions on how to make this work correctly with filtered views?
Expected behavior: When selecting cells in column W in a filtered view, the corresponding module columns should update properly.
Actual behavior: The script either doesn't update the modules or updates them inconsistently when working with filtered views.