Doppio drop-down list in Google Sheets: semplifichiamo la selezione

Il problema

Prendiamo come esempio la gestione del foglio Ordini di un’azienda, dove l’esigenza è quella di immettere degli ordini in base alle offerte dei nostri clienti.

Abbiamo un Google Sheet con due fogli:

• Offers contenente la lista di tutti i clienti e le relative offerte, quindi con almeno le colonne CLIENT e OFFER.
• Orders che conterrà, tra le altre, le colonne CLIENT e OFFER in cui inseriremo gli ordini dei clienti.

Nel foglio Orders vogliamo che il primo drop-down list nella colonna CLIENT mostri la lista dei clienti del foglio Offers e il secondo drop-down list nella colonna OFFER, a fronte della selezione di un cliente, mostri la lista delle offerte di quel cliente ricavandole sempre dal foglio Offers.

La soluzione con Google Sheets e codice Javascript

Per prima cosa inseriamo una regola di validazione sulla colonna CLIENT nel foglio Orders:

• Selezioniamo la colonna CLIENT.
• Dal menu in alto clicchiamo Data dopodiché Data Validation.
• Nel form che ci appare (immagine seguente) impostiamo la nostra regola selezionando dal drop-down list Criteria l’opzione List from a Range e a fianco impostiamo la sorgente dal quale prendere i dati, OFFERS!B2:B dopodiché clicchiamo su Save.

Nella colonna CLIENT appare il drop-down list con i valori dei clienti (colonna CLIENT del foglio Offers).

Occupiamoci ora della colonna OFFER aggiungendo poche semplici righe di codice JavaScript:

• Dal menu selezioniamo Tools dopodiché Script Editor.
• Dalla pagina dell’editor dovremo scrivere le funzioni onEdit() e al suo interno updateOffers(); onEdit è l’evento che si attiva ogni volta che viene fatta qualche modifica al foglio Google, mentre updateOffers() è la funzione che viene richiamata e che contiene nel nostro caso la logica vera e propria.

function onEdit(e) {  
  updateOffers();
}
 
function updateOffers() {
}

Copiamo il seguente codice all’interno della funzione updateOffers():

function updateOffers() {    
  const  SHEET_ORDERS = "ORDERS";
  const  SHEET_OFFERS = "OFFERS";
  const  ORDERS_COL_CLIENT = 3;
[expand title="Mostra il resto del codice"]
 const  ORDERS_COL_OFFER = 4;
 const  ORDERS_FIRST_ROW = 2;
 const  OFFERS_COL_CLIENT = 2;
 const  OFFERS_COL_OFFER = 3;
 const  OFFERS_FIRST_ROW = 2;
  
 var activeSS = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();    
 var activeCell = activeSS.getActiveCell();
 var activeRow = activeCell.getRow();
 var activeCol = activeCell.getColumn();  
 
 if (activeSS.getName() === SHEET_ORDERS && activeCol === ORDERS_COL_CLIENT  && activeRow > ORDERS_FIRST_ROW -1){
   var client = activeCell.getValue();       
   var ssOffers = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(SHEET_OFFERS);             
   var offers = ssOffers.getRange(OFFERS_FIRST_ROW, OFFERS_COL_CLIENT, ssOffers.getLastRow() - OFFERS_FIRST_ROW + 1 , OFFERS_COL_OFFER).getValues();
    
   var clientOffers = offers.filter(function(o) { return o[0] === client});
   var offersList = clientOffers.map(function(o) {return o[1]});    
     
   r = activeSS.getRange(activeRow, ORDERS_COL_OFFER); 
   r.clearDataValidations();
   r.clearContent();        
   var rule = SpreadsheetApp.newDataValidation().requireValueInList(offersList, true).build();
   r.setDataValidation(rule);        
 }
}  
[/expand]

Spiegazione del codice Javascript

Questa riga di codice

if (activeSS.getName() === SHEET_ORDERS && activeCol === ORDERS_COL_CLIENT  && activeRow > ORDERS_FIRST_ROW -1)

descrive la condizione per la modifica riguardante la colonna CLIENT del foglio Orders.

Tramite le righe:

var client = activeCell.getValue();       
var ssOffers = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(SHEET_OFFERS);             
var offers = ssOffers.getRange(OFFERS_FIRST_ROW, OFFERS_COL_CLIENT, ssOffers.getLastRow() - OFFERS_FIRST_ROW + 1 , OFFERS_COL_OFFER).getValues()

Recuperiamo il valore del cliente e tutte le offerte dal foglio Offers

Per filtrare le offerte del cliente selezionato e salvarle in una lista sono necessarie queste righe di codice:

var clientOffers = offers.filter(function(o) { return o[0] === client});
var offersList = clientOffers.map(function(o) {return o[1]});

Assegniamo alla cella della colonna OFFER della riga in cui abbiamo modificato il CLIENT nel foglio Orders, la validazione con la lista delle offerte esistenti per il cliente:

r = activeSS.getRange(activeRow, ORDERS_COL_OFFER); 
r.clearDataValidations();
r.clearContent();        
var rule = SpreadsheetApp.newDataValidation().requireValueInList(offersList, true).build();
r.setDataValidation(rule);

Nel seguente video spiego quanto descritto nell’articolo:

Riferimenti

1. Google Sheets