JavaScript, HTML, CSS e... !
8 commenti

Con il metodo setFormula() della Classe Range

Utilizzare le funzioni native di Google Sheets da Apps Script

28 November 2018di Michele Pisani

Personalmente preferisco gestire le operazioni effettuate sulle celle di un Foglio di Google utilizzando gli strumenti messi a disposizione dal Javascript, considerando che la logica e la sintassi dei Google Script si basa appunto su questo linguaggio. È tuttavia fattibile poter inserire all'interno di una cella le formule native dello Spreadsheet.
Ciò non significa che possono essere utilizzate all'interno degli script per effettuare calcoli ed operazioni ma dallo script è possibile assegnarle direttamente ad una cella o più celle.

Nonostante possano esserci alcune controindicazioni nell'adottare questa pratica, come ad esempio la leggibilità del codice oppure la generazione di tempi di ritardo di elaborazioni non gestite direttamente dallo script così come il fatto che la formula rimarrà assegnata alla cella anche ad un'apertura successiva del Foglio di Google, può capitare di dover gestire una situazione dove ne è previsto l'uso.
Per poter inserire una formula nativa da Apps Script in una cella di un Foglio di Google possiamo fare uso del metodo setFormula(formula) della Classe Range (del Servizio Spreadsheet).

Il codice seguente, similmente presente anche nella documentazione ufficiale, mostra la sintassi da adottare per inserire la formula nativa della somma (SUM) all'interno della cella B6 basata sui valori delle celle da B1 a B5:

function setNativeFormula() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[0];
  
  var cell = sheet.getRange("B6");
  cell.setFormula("=SUM(B1:B5)"); // <-- assegnazione della formula nativa alla cella indicata

  cell.setBackground('yellow');
}

L'immagine seguente ne evidenzia il risultato:



apps script metodo setFormula della classe range

Utilizzo del metodo setFormula() della Classe Range da Google Apps Script


Posizionando il focus sulla cella in questione, alla quale ho modificato il colore di sfondo per renderla più evidente, e guardando all'interno della barra delle funzioni (fx) è possibile vedere che l'assegnazione della formula nativa è andata a buon fine (come appunto conferma il valore all'interno della cella). 

 

Tags

Google Apps ScriptGoogle Sheets
Michele Pisani

Michele Pisani

Sviluppatore Javascript ed esperto in Digital Analytics

L'esperienza nel settore Digital Analytics unita ad anni di sviluppo in Javascript ha trovato la massima espressione in Google Apps Script che mi ha permesso, con estrema facilità e poche righe di codice, di realizzare potenti applicazioni interattive e processi automatizzati integrati con i prodotti della G Suite.

Come contattarmi
scrivi un commento

8 Commenti

  1. Saturday, June 8, 2019 alle ore 08:03 Roberto

    Buongiorno Michele,
    eseguo una macro che compila ed invia il corpo della mail prendendo i valori da varie celle.
    questo è lo script che richiamo con un tasto, invia la mail e aggiunge una nuova riga:
    Vorrei poter automatizzare la macro quando determinate celle vengono compilate da un modulo esterno..

    Grazie

    var EMAIL_SENT = 'Email Inviata';

    function sendEmails1() {
      var sheet = SpreadsheetApp.getActiveSheet();
      var startRow = 4; // Prima riga di processo
      var numRows = 1; // Numero righe da processare
      var dataRange = sheet.getRange("A4:E4");
      var data = dataRange.getValues();
        for (var i = 0; i < data.length; ++i) {
      var row = data[i];
      var Data_di_Utilizzo = row[1];
      var Numero_Valigetta_Strumento = row[2];
      var Tecnico_Utilizzatore = row[3];
      var Note = row[4];
      var message =  'Promemoria Prelievo Srumenti
    per il giorno ' + Data_di_Utilizzo + '

    Per cortesia Mettere sul banchetto la Valigetta N° ' + Numero_Valigetta_Strumento + '

    Per il Tecnico ' + Tecnico_Utilizzatore + '

    Eventuali Note: 
    ' + Note + '


    Link al Registro' + '
     https://docs.google.com/spreadsheets/';
      var emailAddress = 'mail@mail.it'
      var emailSent = row[6]; // Terza riga
        if (emailSent != EMAIL_SENT) {
          var subject = 'Predisporre Valigetta Strumenti N° ' + Numero_Valigetta_Strumento + ' per ' + Tecnico_Utilizzatore;
          MailApp.sendEmail(emailAddress, subject, message);
          sheet.getRange(startRow + i, 6).setValue(EMAIL_SENT);
          SpreadsheetApp.flush();
          sheet.insertRowBefore(4);
        }
      }
    }

    Rispondi a questo commento
  2. Thursday, April 9, 2020 alle ore 12:24 Paolo

    Ottima Guida

    Rispondi a questo commento
  3. Friday, November 20, 2020 alle ore 15:56 Dario Amoroso d'Aragona

    Ciao, ho un problema con il codice qui in questione:


    /** @OnlyCurrentDoc */

    function Presenze() {
      var spreadsheet = SpreadsheetApp.getActive();
      spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Modulo Registro Presenze'));
      var rowToWork = spreadsheet.getLastRow();
      var data = spreadsheet.getRange('B' + rowToWork).getValue();
      var luogo =  spreadsheet.getRange('C' + rowToWork).getValue();
      var assentiCocco = spreadsheet.getRange('D' + rowToWork).getValue();
      var assentiGabbiane = spreadsheet.getRange('E' + rowToWork).getValue();
      var assentiAironi = spreadsheet.getRange('F' + rowToWork).getValue();
      var assentiAlbatros = spreadsheet.getRange('G' + rowToWork).getValue();
      spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Presenze Completo'));
      var colNumber = spreadsheet.getLastColumn() + 1;
      var colToWork = columnToLetter(colNumber);
      spreadsheet.getRange(colToWork + '2').setValue(data);
      spreadsheet.getRange(colToWork + '4').setValue(luogo);
      var formulaPrimaParte = "=SE(VAL.NUMERO(RICERCA(";
      var formulaSecondaParte = ";'Modulo Registro Presenze'!";
      var formulaTerzaParte = "));"ASSENTE";"PRESENTE")";
      var inToCheck = 'D' + rowToWork;
      
      for (i = 6; i<16; i++){
        var name = 'A' + i;
        var formula = "=SE(VAL.NUMERO(RICERCA("+name+";'Modulo Registro Presenze'!"+inToCheck+"));"ASSENTE"; "PRESENTE")";
        spreadsheet.getRange(colToWork + i).setFormula(formula).activate();
      }
      
      inToCheck = 'E' + rowToWork;

      for (i = 17; i<24; i++){
        var name = 'A' + i;
        var formula = formulaPrimaParte + name + formulaSecondaParte + inToCheck + formulaTerzaParte;
        spreadsheet.getRange(colToWork + i).setFormula(formula).activate();
      }
      
      inToCheck = 'F' + rowToWork;
      
      for (i = 25; i<35; i++){
        var name = 'A' + i;
        var formula = formulaPrimaParte + name + formulaSecondaParte + inToCheck + formulaTerzaParte;
        spreadsheet.getRange(colToWork + i).setFormula(formula).activate();
      }
      
      inToCheck = 'G' + rowToWork;

      for (i = 36; i<42; i++){
        var name = 'A' + i;
        var formula = formulaPrimaParte + name + formulaSecondaParte + inToCheck + formulaTerzaParte;
        spreadsheet.getRange(colToWork + i).setFormula(formula).activate();
      }
      
      SpreadsheetApp.flush();
      
     };


    function columnToLetter(column)
    {
      var temp, letter = '';
      while (column > 0)
      {
        temp = (column - 1) \% 26;
        letter = String.fromCharCode(temp + 65) + letter;
        column = (column - temp - 1) / 26;
      }
      return letter;
    }

    function letterToColumn(letter){
      var column = 0, length = letter.length;
      for (var i = 0; i < length; i++)
      {
        column += (letter.charCodeAt(i) - 64) * Math.pow(26, length - i - 1);
      }
      return column;
    }



    in particolare il set.formula(..) inserisce la formula nella cella corretta ma poi nel foglio di lavoro mi compare l'errore "se. Funzione non riconosciuta".

    Rispondi a questo commento
    • Saturday, November 21, 2020 alle ore 11:32 Michele PisaniAutore

      Ciao Dario,
      dal messaggio di errore che indichi mi viene in mente che lo Spreadsheet possa aspettarsi la formula in lingua inglese, ovvero IF anziché SE. Di conseguenza anche le altre funzioni della formula dovrebbero essere convertite.

      Rispondi a questo commento
  4. Sunday, April 9, 2023 alle ore 18:06 MANUEL

    Buongiorno Michele, cortesemente potresti spiegarmi la differenza tra .setValue e .setFormula per inserire una funzione nativa in una cella ?
    Ho provato a utilizzare entrambi i metodi inserendo la funzione =SUM(D1:D2) ed entrambi sembrano fare la stessa cosa e funzionare allo stesso modo. Grazie.

    function inserimento_formula_nativa_con_setFormula_e_SetValue() {

    //DEFINISCO APPLICAZIONE E FILE
    const ss = SpreadsheetApp.getActiveSpreadsheet();
    //DEFINISCO FOGLIO
    const sh = ss.getSheetByName('Base Dati');
    //SCRIVO NELLA CELLA D2
    sh.getRange("D2").setValue(10);
    //SCRIVO NELLA CELLA D3
    sh.getRange("D3").setValue(20);
    //SCRIVO FORMULA NATIVA =SUM(D2:D3) NELLA CELLA D4 CON .setFormula
    sh.getRange("D4").setFormula("=SUM(D2:D3)");
    //SCRIVO FORMULA NATIVA =SUM(D2:D3) NELLA CELLA D5 CON .setValue
    sh.getRange("D5").setValue("=SUM(D2:D3)");

    }

    Rispondi a questo commento
  5. Sunday, April 9, 2023 alle ore 18:06 MANUEL

    Buongiorno Michele, cortesemente potresti spiegarmi la differenza tra .setValue e .setFormula per inserire una funzione nativa in una cella ?
    Ho provato a utilizzare entrambi i metodi inserendo la funzione =SUM(D1:D2) ed entrambi sembrano fare la stessa cosa e funzionare allo stesso modo. Grazie.

    function inserimento_formula_nativa_con_setFormula_e_SetValue() {

    //DEFINISCO APPLICAZIONE E FILE
    const ss = SpreadsheetApp.getActiveSpreadsheet();
    //DEFINISCO FOGLIO
    const sh = ss.getSheetByName('Base Dati');
    //SCRIVO NELLA CELLA D2
    sh.getRange("D2").setValue(10);
    //SCRIVO NELLA CELLA D3
    sh.getRange("D3").setValue(20);
    //SCRIVO FORMULA NATIVA =SUM(D2:D3) NELLA CELLA D4 CON .setFormula
    sh.getRange("D4").setFormula("=SUM(D2:D3)");
    //SCRIVO FORMULA NATIVA =SUM(D2:D3) NELLA CELLA D5 CON .setValue
    sh.getRange("D5").setValue("=SUM(D2:D3)");

    }

    Rispondi a questo commento

Scrivi un commento

Il tuo indirizzo email non sarà pubblicato.I campi contrassegnati da un * sono obbligatori
Puoi utilizzare i seguenti tag nei commenti:[bold]testo[/bold] se vuoi evidenziare un testo con il grassetto[code]function helloworld() { }[/code] se vuoi pubblicare una porzione di codice[url]https://www.appsscript.it[/url] se devi riferirti ad un indirizzo web