Esercitazione sugli script di Google Apps per la padronanza delle macro

I dirigenti efficaci sanno che il tempo è il fattore limitante... Nient'altro, forse, distingue i dirigenti efficaci tanto quanto la loro tenera cura amorevole del tempo.

Peter Drucker

Il tempo è la nostra risorsa più preziosa. Vogliamo spenderlo per le attività a maggior impatto e più accrescitive che possiamo, non solo perché quelle di solito hanno il più alto valore monetario, ma anche per sfidare continuamente noi stessi e massimizzare la nostra soddisfazione sul lavoro.

Ci sono molti modi per migliorare la tua efficienza e produttività al fine di utilizzare meglio il tuo tempo. In un precedente articolo su Fogli Google, ho spiegato come il potere della collaborazione online sia una di queste chiavi per aumentare la produttività.

In un altro articolo, ho dimostrato come il linguaggio di programmazione Python può essere un potente strumento di analisi e automazione delle attività per i professionisti della finanza.

Prendendo ispirazione da questo, ora voglio presentare un tutorial di Google Apps Script. Google Apps Script ti consente di scrivere script e programmi in JavaScript per automatizzare, collegare ed estendere i prodotti in G Suite di Google, inclusi Fogli, Documenti, Presentazioni, Gmail, Drive e molti altri. L'apprendimento richiede un investimento di tempo, così come la scrittura dei copioni, ma gli aumenti di produttività e le opportunità aggiuntive che offre ne valgono la pena.

Come primo passo, iniziamo osservando un concetto familiare: le macro.

Registrazione e utilizzo di macro in Fogli Google

Se hai trascorso molto tempo a lavorare con Excel, a un certo punto sei destinato a entrare in contatto con l'interfaccia macro VBA (Visual Basic for Applications) di Excel. O registrandoli o scrivendoli da soli o trasportando quelli creati da altri.

Le macro sono un ottimo modo per automatizzare flussi di lavoro ripetitivi e noiosi. VBA potrebbe non essere una lingua a cui hai dedicato molto tempo per imparare, ma la sua bellezza era che non ne avevi davvero bisogno per diventare produttivo e creare le tue macro. Potresti semplicemente registrare il flusso di lavoro che desideri automatizzare, quindi entrare nel codice e apportare le piccole modifiche necessarie per rendere la macro più generale.

In un certo senso, VBA è una lezione fantastica e dimenticata su come introdurre persone non tecniche alla programmazione . Il modo in cui è possibile registrare le azioni e quindi popolare il codice per una revisione successiva è davvero un modo molto più pragmatico di apprendere leggendo libri di testo e guardando passivamente tutorial.

La stessa funzionalità di registrazione di VBA è disponibile in Fogli Google. Ecco un semplice esempio di come usarlo:

Iniziamo con alcuni dati di esempio, utilizzando una query IMPORTHTML per importare una tabella. In questo esempio, ho scaricato un elenco da Wikipedia dei 15 più grandi hedge fund del mondo. Va da sé, ma questo è un esempio arbitrario; l'intenzione è che ti concentri maggiormente sull'applicazione, sull'argomento.

Il processo di registrazione della macro viene avviato tramite il seguente percorso di menu: Strumenti > Macro > Registra macro.

Quindi esaminiamo le azioni (formato PC) che vogliamo registrare:

1. Seleziona la prima riga
2. Premi Maiusc + Ctrl + Freccia giù per selezionare tutto
3. Ctrl + C per copiare
4. Maiusc + F11 per creare un nuovo foglio
5. Assegna un nuovo nome al foglio
6. Premi Maiusc + Ctrl + V per incollare i valori

Una volta terminato, premi il pulsante Salva nella finestra della macro in basso, assegnagli un nome e una scorciatoia da tastiera opzionale.

Per azioni più semplici che possono essere replicate esattamente attraverso questi stessi passaggi, il processo finirebbe qui e potrai iniziare a utilizzare la tua macro immediatamente. In questo caso, però, è necessario apportare alcune modifiche prima che il codice sia utilizzabile. Ad esempio, il foglio su cui copiamo dovrà avere ogni volta un nome diverso. Vediamo come farlo.

Scrivere manualmente lo script di Google Apps

Ora vedremo per la prima volta le ossa di Google Apps Script; la piattaforma di programmazione che gira sui server di Google. Ciò alimenta le nostre macro e ti consente di creare flussi di lavoro molto complessi e persino componenti aggiuntivi per le applicazioni stesse. Può essere utilizzato per automatizzare non solo il lavoro con i fogli di calcolo, ma in realtà quasi tutto ciò che è interconnesso all'interno di G Suite di Google.

Il linguaggio di programmazione di Apps Script è JavaScript , uno dei linguaggi di programmazione più popolari, il che significa che c'è una grande quantità di risorse là fuori per chiunque voglia imparare in modo estensivo. Ma, proprio come con VBA, non è necessario: puoi utilizzare la stessa funzionalità di registrazione ed eseguire semplicemente i passaggi che desideri essere in grado di ripetere automaticamente. L'output della registrazione potrebbe sembrare grezzo e molto probabilmente non corrisponderà perfettamente a ciò che vuoi fare, ma fornirà un punto di partenza sufficientemente solido. Ora facciamolo per lo script che abbiamo appena registrato.

Durante la registrazione, ha senso fare attenzione a non registrare per sbaglio ulteriori passaggi che non si desidera vengano catturati nella registrazione finale, ma a volte è difficile evitare: qualcosa di semplice come selezionare una cella diversa prima di premere il pulsante Stop Il pulsante di registrazione verrà acquisito e successivamente ripetuto ogni volta che esegui lo script. Il primo passaggio durante la modifica del nostro script sarebbe ripulirlo e rimuovere tali passaggi. Immergiamoci andando su Strumenti> Editor di script nel menu file.

Se conosci JavaScript, lo riconoscerai immediatamente e potresti anche essere sorpreso di vedere la parola chiave "var" invece di "let" o "const" come vedresti nel JavaScript moderno. Ciò riflette il fatto che la versione JavaScript in Apps Script è piuttosto vecchia e non supporta molte delle funzionalità più recenti del linguaggio. Verso la fine, introdurrò comunque una soluzione alternativa per coloro che desiderano utilizzare le funzionalità linguistiche più recenti.

Quando esegui lo script per la prima volta, verrà richiesta l'autorizzazione, il che ha senso, poiché gli script possono modificare (e potenzialmente eliminare) tutti i tuoi dati. Molto probabilmente riconoscerai il processo di autorizzazione da altri prodotti Google.

Ora possiamo iniziare a modificare il codice. Le modifiche che dobbiamo apportare sono minori, ma se lo fai per la prima volta, potrebbe comunque richiedere una rapida ricerca nella documentazione di Fogli Apps Script e/o una rapida ricerca di un concetto JavaScript, come lavorare con le date. Qui il fatto che JavaScript sia un linguaggio così diffuso è utile: una soluzione per qualsiasi problema o funzionalità che ti viene in mente di solito può essere trovata rapidamente se esprimi il termine di ricerca in modo diretto.

Le modifiche apportate in questa versione dello script rispetto alla versione registrata originale sono che invece del nome hardcoded per il nuovo foglio che creiamo, ora lo denominiamo con la data odierna. Inoltre, cambiamo anche il percorso di copia alla fine per fare riferimento a questo nuovo foglio. Le ultime quattro righe mostrano anche come eseguire alcune operazioni di formattazione, come modificare il valore di una cella, ridimensionare le colonne e nascondere le linee della griglia.

 function createSnapshot() { var spreadsheet = SpreadsheetApp.getActive(); var date = new Date().toISOString().slice(0,10); var destination = spreadsheet.insertSheet(date); spreadsheet.getRange('HTML!A1:F1').activate(); spreadsheet.getSelection() .getNextDataRange(SpreadsheetApp.Direction.DOWN) .activate(); spreadsheet.getActiveRange() .copyTo(SpreadsheetApp.setActiveSheet(destination) .getRange(1,1), SpreadsheetApp.CopyPasteType.PASTE_VALUES,false); var sheet = spreadsheet.setActiveSheet(destination) sheet.getRange("D1").setValue("AUM $bn") sheet.setHiddenGridlines(true); sheet.getRange("A1:D1").setFontWeight("bold"); sheet.autoResizeColumns(1, 4); };

L'esecuzione dello script ora mostrerà che il nuovo foglio è effettivamente denominato con la data odierna e contiene le informazioni copiate come valori (non formule) dal foglio principale.

È ora possibile aggiungere visualizzazioni di grafici utilizzando lo stesso processo di registrazione. L'ho usato per creare tre semplici grafici.

La pulizia del codice per ciascuno sarà simile a questa:

 function createColumnChart() { var spreadsheet = SpreadsheetApp.getActive(); spreadsheet.getRange('C1:D16').activate(); var sheet = spreadsheet.getActiveSheet(); chart = sheet.newChart() .asColumnChart() .addRange(spreadsheet.getRange('B1:D16')) .setMergeStrategy(Charts.ChartMergeStrategy.MERGE_COLUMNS) .setTransposeRowsAndColumns(false) .setNumHeaders(-1) .setHiddenDimensionStrategy(Charts.ChartHiddenDimensionStrategy.IGNORE_BOTH) .setOption('useFirstColumnAsDomain', true) .setOption('curveType', 'none') .setOption('domainAxis.direction', 1) .setOption('isStacked', 'absolute') .setOption('series.0.color', '#0b5394') .setOption('series.0.labelInLegend', 'AUM $bn') .setPosition(19, 6, 15, 5) .build(); sheet.insertChart(chart); };

Ancora una volta, non preoccuparti se alcune delle opzioni sembrano confuse: questo è tutto generato automaticamente, devi solo capire abbastanza per rimuovere i passaggi non necessari e forse apportare piccole modifiche in seguito.

Esempi avanzati di script di Google Apps: collegamento di fogli a Google Drive e Presentazioni

Ora tutto sta iniziando a prendere forma, ma cosa accadrebbe se l'output effettivo che vogliamo non fosse un foglio di calcolo ma una presentazione? In tal caso, la maggior parte del lavoro da qui potrebbe essere ancora manuale e non abbiamo risparmiato molto tempo se è necessario farlo su base ricorrente.

Esaminiamo ora come potrebbe essere automatizzare la creazione di una presentazione utilizzando i dati di esempio dal nostro foglio di calcolo.

Questo esercizio ora diventa più avanzato per due motivi:

1. Dovremo familiarizzare con come lavorare con Presentazioni Google (e Google Drive) oltre a Fogli.
2. In Presentazioni, o quando lavori tra le app di G Suite in generale, non è disponibile la funzionalità "Registra macro". Ciò significa che devi conoscere abbastanza Apps Script (ed essere a tuo agio nella navigazione nella documentazione per ciascuno dei prodotti G Suite) per scrivere script da zero.

Questo prossimo esempio ha lo scopo di fornire alcuni elementi costitutivi di base per iniziare e familiarizzare.

Per cominciare, creiamo un modello che in seguito vogliamo riempire di contenuto usando il nostro script. Ecco due semplici diapositive di presentazione che ho messo insieme:

Successivamente, dovrai ottenere l'ID di questo modello perché dovrai fare riferimento ad esso nel tuo script. Inconsciamente avrai visto questo ID molte volte perché è, in effetti, la sequenza di caratteri e numeri dall'aspetto casuale che vedi nell'URL del tuo browser:

https://docs.google.com/presentation/p/ this_is_your_presentation_ID /edit#slide=id.p.

Ora dobbiamo aggiungere le seguenti righe al nostro script originale. Ciò richiederà nuovamente l'autorizzazione, questa volta per accedere al tuo Google Drive.

 function createPresentation() { var template; var template = DriveApp.getFileById(templateId); var copy = template.makeCopy("Weekly report " + date).getId(); var presentation = SlidesApp.openById(copy); }

Non vedrai alcun feedback visivo immediato se esegui questo frammento di codice, ma se guardi nella cartella del tuo Google Drive in cui avevi memorizzato il modello scoprirai che una copia di esso è stata effettivamente creata e ha l'attuale data nel nome del file. Iniziamo bene!

Usiamo ora più elementi costitutivi per iniziare a riempirlo di contenuto, a livello di codice anziché a mano. Aggiungi le seguenti righe alla stessa funzione:

 presentation.getSlides()[0] .getPageElements()[0] .asShape() .getText() .setText("Weekly Report " + date);

Ora le cose stanno diventando un po' più interessanti, poiché abbiamo cambiato la prima pagina per includere la data odierna. In Presentazioni, come in Fogli, lavori con oggetti (rappresentati da classi) che hanno ciascuno proprietà e metodi (cioè funzionalità allegate). Questi sono organizzati in una gerarchia, con SpreadsheetsApp, DriveApp o SlidesApp come oggetto di primo livello. Nello snippet di codice sopra, dobbiamo muoverci attraverso questa gerarchia passo dopo passo per raggiungere l'elemento che vogliamo modificare, in questo caso: Il testo in una casella di testo. In pratica, questo significa raggiungere tramite gli oggetti Presentation, Slide, PageElement e Shape, fino ad arrivare finalmente all'oggetto TextRange che vogliamo modificare.

Tenere traccia del tipo di oggetto con cui si ha a che fare può creare confusione e i bug che derivano dal tentativo di applicare un'operazione all'oggetto sbagliato possono essere difficili da risolvere. Sfortunatamente, la funzionalità della guida e i messaggi di errore nello stesso Script Editor non forniscono sempre molte indicazioni qui, il lato positivo è che tale attenzione migliorerà almeno le tue pratiche di controllo della qualità.

Dopo aver creato la presentazione e aggiornato il titolo, è giunto il momento di inserirvi uno dei nostri nuovi grafici. Tenendo presente la gerarchia degli oggetti, il codice seguente dovrebbe ora avere senso:

 var spreadsheet = SpreadsheetApp.getActive(); var sheet = spreadsheet.getSheetByName(date); var chart = sheet.getCharts()[0]; var position = {left: 25, top: 75}; var size = {width: 480, height: 300}; presentation.getSlides()[1] .insertSheetsChart(chart, position.left, position.top, size.width, size.height);

Se esegui lo script completo, la presentazione di output dovrebbe essere simile a questa:

Si spera che questo esempio illustri i principi e fornisca ispirazione per iniziare con la propria sperimentazione. Se ci pensi, sono sicuro che troverai almeno qualche esempio di lavoro manuale svolto oggi nella tua azienda che dovrebbe davvero essere automatizzato in questo modo. Serve per liberare tempo per pensare, analizzare e applicare giudizi, piuttosto che spostare meccanicamente i dati da un formato e/o da un luogo all'altro. Miglioramento dell'esperienza di sviluppo Come accennato in precedenza, la versione JavaScript supportata in Google Apps Script è vecchia e la funzionalità dell'editor di script online è molto limitata. Se stai solo registrando una macro o scrivendo poche dozzine di righe, non te ne accorgerai davvero. Se, tuttavia, hai piani ambiziosi per automatizzare tutti gli aspetti dei tuoi rapporti settimanali o mensili o desideri creare plug-in, sarai felice di sapere che esiste uno strumento da riga di comando che ti consente di sviluppare utilizzando il tuo ambiente di sviluppo preferito .

Se sei a tali livelli di competenza, probabilmente vorrai anche sfruttare le funzionalità più recenti che JavaScript ha da offrire, e potenzialmente anche di più, poiché con lo strumento da riga di comando puoi anche sviluppare in TypeScript.

Utilizzo di Python per la programmazione di Fogli Google

Se trovi che lavorare con Apps Script non fa per te, allora ci sono altre opzioni, a seconda del caso d'uso. Se vuoi eseguire un crunching numerico più avanzato, connetterti con API o database o semplicemente preferisci il linguaggio di programmazione Python su JavaScript, allora Colaboratory di Google è un prodotto inestimabile. Ti offre un notebook Jupyter in esecuzione sui server di Google che ti consente di scrivere script Python che si integrano perfettamente con i tuoi file di Google Drive e, tramite la libreria "gspread", semplifica il lavoro con i dati del tuo foglio di calcolo.

Ho illustrato molti dei vantaggi di Python in un articolo su come utilizzarlo per le funzioni finanziarie, che funge anche da introduzione all'utilizzo dei notebook Python e Jupyter in un contesto aziendale e finanziario. Un vantaggio molto importante per me è che, a differenza di Apps Script, il notebook Python in Colaboratory è interattivo, quindi vedi i risultati (o il messaggio di errore) dopo aver eseguito ogni riga o piccolo blocco di codice.

L'automazione crea dipendenza

Questo tutorial di Google Apps Script ha mostrato un assaggio di ciò che è possibile fare attraverso il linguaggio di codifica di Google. Le possibilità sono praticamente infinite. Tuttavia, se non hai un background tecnico, gli esempi di codice potrebbero sembrare scoraggianti e potresti pensare che i guadagni di produttività guadagnati dall'apprendimento di Google Apps Script potrebbero non essere sufficienti per superare il notevole investimento in termini di tempo necessario per impararlo.

Questo, ovviamente, dipende da molti fattori, incluso il tipo di ruolo che hai, o ti aspetti di avere, in futuro. Ma anche se non ti aspetti di fare nulla di simile agli esempi mostrati qui, avere una comprensione di ciò che è possibile e quanto lavoro ci vorrebbe per implementarlo può innescare pensieri e idee su come migliorare la produttività nella tua azienda, perché i tuoi clienti o te stesso personalmente.

Personalmente, posso attestare la soddisfazione di sedermi e premere un pulsante che completa un'ora di noioso lavoro manuale in meno di un minuto. Dopo averlo fatto per la cinquantesima volta, sarai grato per il paio d'ore trascorse a mettere insieme tutto in primo luogo, il che alla fine è servito a liberare il tuo tempo per attività più a valore aggiunto. Dopo un po', questi vantaggi di scalabilità creano dipendenza.