Tutorial Google Apps Script pentru stăpânirea macrocomenzilor

Directorii eficienți știu că timpul este factorul limitator... Nimic altceva, poate, nu îi distinge pe directori eficienți la fel de mult ca grija lor tandră iubitoare de timp.

Peter Drucker

Timpul este resursa noastră cea mai valoroasă. Dorim să o cheltuim pentru activitățile cu cel mai mare impact și cu cea mai mare valoare pe care le putem, nu numai pentru că acestea au, de obicei, cea mai mare valoare monetară, ci și pentru a ne provoca în mod continuu și pentru a ne maximiza satisfacția în muncă.

Există multe modalități de a vă îmbunătăți eficiența și productivitatea pentru a vă folosi mai bine timpul. Într-un articol anterior despre Google Sheets, am explicat modul în care puterea colaborării online este una dintre cheile pentru creșterea productivității.

Într-un alt articol, am demonstrat cum limbajul de programare Python poate fi un instrument puternic de analiză și automatizare a sarcinilor pentru profesioniștii din domeniul finanțelor.

Inspirându-mă din asta, acum vreau să vă prezint un tutorial Google Apps Script. Google Apps Script vă permite să scrieți scripturi și programe în JavaScript pentru a automatiza, conecta și extinde produsele din G Suite Google, inclusiv Sheets, Docs, Slides, Gmail, Drive și multe altele. Învățarea acestuia necesită o investiție în timp, la fel ca și scrierea scenariilor, dar productivitatea crește și oportunitățile suplimentare pe care le deschide îl fac să merite din plin.

Ca prim pas, să începem prin a privi un concept familiar: macrocomenzi.

Înregistrarea și utilizarea macrocomenzilor în Foi de calcul Google

Dacă ați petrecut mult timp lucrând cu Excel, atunci sigur că ați intrat în contact cu interfața macro VBA (Visual Basic pentru aplicații) a Excel la un moment dat. Fie prin înregistrarea, fie prin scrierea lor personală, fie prin preluarea celor create de alții.

Macro-urile sunt o modalitate excelentă de a automatiza fluxurile de lucru repetitive și plictisitoare. S-ar putea ca VBA să nu fie o limbă pe care ai dedicat mult timp pentru a-l învăța, dar frumusețea sa era că nu aveai nevoie pentru a deveni productiv și pentru a-ți crea propriile macrocomenzi. Puteți pur și simplu să înregistrați fluxul de lucru pe care doriți să îl automatizați, apoi să intrați în cod și să faceți orice modificări mici necesare pentru a face macrocomandă mai generală.

În anumite privințe, VBA este o lecție grozavă și uitată despre cum să introduci codificarea persoanelor netehnice . Modul în care ai putea înregistra acțiuni și apoi ai completa codul pentru o revizuire ulterioară este într-adevăr un mod mult mai pragmatic de a învăța decât citirea manualelor și vizionarea pasivă a tutorialelor.

Aceeași funcționalitate de înregistrare a VBA este disponibilă în Foi de calcul Google. Iată un exemplu simplu de utilizare:

Să începem cu câteva exemple de date, folosind o interogare IMPORTHTML pentru a importa un tabel. În acest exemplu, am descărcat o listă de pe Wikipedia cu cele mai mari 15 fonduri speculative din lume. Este de la sine înțeles, dar acesta este un exemplu arbitrar; intenția este ca tu să te concentrezi mai mult asupra aplicației, asupra subiectului.

Procesul de înregistrare macro este inițiat prin următoarea cale de meniu: Instrumente > Macrocomenzi > Înregistrare macro.

Apoi parcurgem acțiunile (format PC) pe care dorim să le înregistrăm:

1. Selectați primul rând
2. Apăsați Shift + Ctrl + Săgeată în jos pentru a selecta totul
3. Ctrl + C pentru a copia
4. Shift + F11 pentru a crea o foaie nouă
5. Dați foii un nume nou
6. Apăsați Shift + Control + V pentru a lipi valori

După ce ați terminat, apăsați butonul Salvare din fereastra macro din partea de jos, dați-i un nume și o comandă rapidă opțională de la tastatură.

Pentru acțiuni mai simple care pot fi replicate exact prin acești pași, procesul s-ar termina aici și puteți începe să utilizați macrocomanda imediat. În acest caz, totuși, trebuie să facem unele modificări înainte ca codul să fie utilizabil. De exemplu, foaia pe care o copiem va trebui să aibă de fiecare dată un nume diferit. Să vedem cum să facem asta.

Scrierea manuală a scriptului Google Apps

Acum vom vedea pentru prima dată elementele Google Apps Script; platforma de programare care rulează pe serverele Google. Acest lucru alimentează macrocomenzile noastre și vă permite să creați fluxuri de lucru foarte complexe și chiar suplimente pentru aplicațiile în sine. Poate fi folosit pentru a automatiza nu numai lucrul cu foile de calcul, ci și aproape orice este interconectat în cadrul G Suite-ului Google.

Limbajul de programare al Apps Script este JavaScript , unul dintre cele mai populare limbaje de programare, ceea ce înseamnă că există o mulțime de resurse pentru oricine dorește să învețe în mod extensiv. Dar, la fel ca și în cazul VBA, nu prea este nevoie: puteți folosi aceeași funcționalitate de înregistrare și pur și simplu faceți pașii pe care doriți să îi puteți repeta automat. Ieșirea din înregistrare ar putea părea brută și cel mai probabil nu se va potrivi perfect cu ceea ce doriți să faceți, dar va oferi un punct de plecare suficient de solid. Să o facem acum pentru scenariul pe care tocmai l-am înregistrat.

Când înregistrați, este logic să aveți grijă să nu înregistrați accidental pași suplimentari pe care nu doriți să fie capturați în înregistrarea finală, dar uneori este dificil de evitat: ceva la fel de simplu ca să selectați o altă celulă înainte de a apăsa butonul Stop. Butonul de înregistrare va fi capturat și ulterior repetat de fiecare dată când rulați scriptul. Primul pas atunci când editați scriptul nostru ar fi să-l curățați și să eliminați orice astfel de pași. Să ne aprofundăm accesând Instrumente > Editor de scripturi din meniul fișierului.

Dacă cunoașteți JavaScript, veți recunoaște acest lucru instantaneu și, de asemenea, ați putea fi surprins să vedeți cuvântul cheie „var” în loc de „let” sau „const”, așa cum ați vedea în JavaScript modern. Acest lucru reflectă faptul că versiunea JavaScript din Apps Script este destul de veche și nu acceptă multe dintre caracteristicile mai recente ale limbajului. Spre sfârșit, voi introduce o soluție, totuși, pentru cei care ar dori să folosească cele mai recente caracteristici ale limbii.

Când rulați scriptul prima dată, acesta va cere autorizare, ceea ce are sens, deoarece scripturile vă pot modifica (și pot șterge) toate datele. Cel mai probabil veți recunoaște procesul de autorizare de la alte produse Google.

Acum putem începe să modificăm codul. Modificările pe care trebuie să le facem sunt minore, dar dacă faceți acest lucru pentru prima dată, este posibil să fie necesară o căutare rapidă prin documentația Sheets Apps Script și/sau o căutare rapidă a unui concept JavaScript, cum ar fi lucrul cu date. Aici, faptul că JavaScript este un limbaj atât de răspândit este util: O soluție pentru orice problemă cu care vă confruntați sau pentru orice funcționalitate vă vine în minte poate fi găsită de obicei rapid dacă exprimați termenul de căutare într-un mod simplu.

Modificările făcute în această versiune a scriptului față de versiunea originală înregistrată sunt că, în loc de numele hardcoded pentru noua foaie pe care o creăm, acum o denumim cu data de astăzi. În plus, schimbăm și calea de copiere la sfârșit pentru a face referire la această nouă foaie. Ultimele patru rânduri demonstrează, de asemenea, cum să efectuați unele operațiuni de formatare, cum ar fi modificarea valorii unei celule, redimensionarea coloanelor și ascunderea liniilor de grilă.

 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); };

Rularea scriptului acum va arăta că noua foaie este într-adevăr denumită cu data de astăzi și conține informațiile copiate ca valori (nu formule) din foaia principală.

Vizualizările grafice pot fi acum adăugate utilizând același proces de înregistrare. Am folosit asta pentru a crea trei diagrame simple.

Curățarea codului pentru fiecare va arăta cam așa:

 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); };

Din nou, nu vă faceți griji dacă unele dintre opțiuni par confuze: toate acestea sunt generate automat, trebuie doar să înțelegeți suficient pentru a elimina pașii inutile și, probabil, a face mici ajustări mai târziu.

Exemple avansate de script Google Apps: Conectarea foilor de calcul la Google Drive și Slides

Totul începe acum să prindă contur, dar dacă rezultatul real pe care îl dorim nu este o foaie de calcul, ci o prezentare? Dacă acesta este cazul, atunci cea mai mare parte a muncii de aici ar putea fi în continuare manuală și nu am economisit mult timp dacă trebuie să facem acest lucru în mod repetat.

Să explorăm acum cum ar putea arăta automatizarea creării unei prezentări folosind exemplele de date din foaia noastră de calcul.

Acest exercițiu devine acum mai avansat din două motive:

1. Va trebui să ne familiarizăm cu modul de lucru cu Google Slides (și Google Drive) în plus față de Sheets.
2. În Slides, sau când lucrați între aplicațiile G Suite în general, nu este disponibilă nicio funcționalitate „Înregistrați macrocomandă”. Aceasta înseamnă că trebuie să cunoașteți suficient despre Apps Script (și să fiți confortabil în navigarea documentației pentru fiecare dintre produsele G Suite) pentru a scrie scripturi de la zero.

Acest exemplu următor este menit să ofere câteva elemente de bază pentru a începe și a vă familiariza.

Pentru început, să creăm un șablon pe care mai târziu dorim să-l umplem cu conținut folosind scriptul nostru. Iată două diapozitive simple de prezentare pe care le-am pus împreună:

În continuare, va trebui să obțineți ID-ul acestui șablon, deoarece va trebui să vă referiți la el în scriptul dvs. În mod subconștient, veți fi văzut acest ID de multe ori, deoarece este, de fapt, secvența aleatorie de caractere și numere pe care o vedeți în adresa URL a browserului dvs.:

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

Acum trebuie să adăugăm următoarele rânduri la scriptul nostru original. Acest lucru va solicita din nou autorizarea, de data aceasta pentru a accesa Google Drive.

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

Nu veți vedea niciun feedback vizual imediat dacă rulați acest fragment de cod, dar dacă vă uitați în dosarul din Google Drive unde ați stocat șablonul, veți descoperi că o copie a acestuia a fost într-adevăr creată și are cea de astăzi. data în numele fișierului. Avem un început bun!

Să folosim acum mai multe blocuri pentru a începe să-l umplem cu conținut, în mod programatic și nu manual. Adăugați următoarele rânduri la aceeași funcție:

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

Acum lucrurile devin puțin mai interesante, deoarece am schimbat prima pagină pentru a include data de astăzi. În Slides, ca și în Sheets, lucrați cu obiecte (reprezentate prin clase) care au fiecare proprietăți și metode (adică funcționalitate atașată). Acestea sunt organizate într-o ierarhie, cu SpreadsheetsApp, DriveApp sau SlidesApp fiind obiectul de nivel superior. În fragmentul de cod de mai sus, trebuie să trecem prin această ierarhie pas cu pas pentru a ajunge la elementul pe care vrem să-l edităm, în acest caz: Textul dintr-o casetă de text. Practic, aceasta înseamnă să ajungem prin obiectele Prezentare, Slide, PageElement și Shape, până când ajungem în sfârșit la obiectul TextRange pe care vrem să-l edităm.

Urmărirea tipului de obiect cu care aveți de-a face poate fi confuză, iar erorile care rezultă din încercarea de a aplica o operație la obiectul greșit pot fi greu de rezolvat. Din păcate, funcționalitatea de ajutor și mesajele de eroare din Editorul de script în sine nu oferă întotdeauna o mulțime de îndrumări aici, concluzia fiind că o astfel de atenție vă va îmbunătăți cel puțin practicile de control al calității.

După ce am creat prezentarea și am actualizat titlul, acum este timpul să inserăm unul dintre noile noastre diagrame în ea. Ținând cont de ierarhia obiectelor, următorul cod ar trebui să aibă acum sens:

 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);

Dacă rulați scriptul complet, prezentarea de ieșire ar trebui să arate cam așa:

Sperăm că acest exemplu ilustrează principiile și oferă inspirație pentru a vă ajuta să începeți propria experimentare. Dacă vă gândiți bine, sunt sigur că puteți găsi cel puțin câteva exemple de lucrări manuale efectuate în compania dvs. astăzi, care ar trebui să fie automat automatizate în acest fel. Servește pentru a elibera timp pentru a gândi, analiza și aplica judecată, mai degrabă decât pentru amestecarea mecanică a datelor dintr-un format și/sau loc în altul. Îmbunătățirea experienței de dezvoltare După cum sa menționat anterior, versiunea JavaScript acceptată în Google Apps Script este veche, iar funcționalitatea editorului de script online este foarte limitată. Dacă doar înregistrați o macrocomandă sau scrieți câteva zeci de rânduri, nu veți observa cu adevărat. Dacă, totuși, aveți planuri ambițioase de a automatiza toate aspectele raportării săptămânale sau lunare sau doriți să construiți pluginuri, atunci veți fi bucuroși să aflați că există un instrument de linie de comandă care vă permite să dezvoltați folosind mediul de dezvoltare preferat. .

Dacă sunteți la astfel de niveluri de competență, atunci probabil că veți dori, de asemenea, să profitați de cele mai recente caracteristici pe care JavaScript le are de oferit și, potențial, chiar mai mult, deoarece cu instrumentul de linie de comandă puteți dezvolta și în TypeScript.

Utilizarea Python pentru programarea Google Sheets

Dacă descoperiți că lucrul cu Apps Script nu este ceașca dvs. de ceai, atunci există și alte opțiuni, în funcție de cazul de utilizare. Dacă doriți să faceți o analiză mai avansată a numerelor, să vă conectați cu API-uri sau baze de date sau pur și simplu preferați limbajul de programare Python decât JavaScript, atunci Google's Colaboratory este un produs neprețuit. Vă oferă un notebook Jupyter care rulează pe serverele Google, care vă permite să scrieți scripturi Python care se integrează perfect cu fișierele dvs. Google Drive și, prin biblioteca „gspread”, ușurează lucrul cu datele din foile de calcul.

Am subliniat multe dintre beneficiile Python într-un articol despre cum să îl utilizați pentru funcții financiare, care servește și ca o introducere blândă în lucrul cu notebook-urile Python și Jupyter într-un context financiar și de afaceri. Un beneficiu foarte important pentru mine este că, spre deosebire de Apps Script, blocnotesul Python din Colaboratory este interactiv, așa că vedeți rezultatele (sau mesajul de eroare) după executarea fiecărei linii sau blocuri mici de cod.

Automatizarea creează dependență

Acest tutorial Google Apps Script a arătat o privire a ceea ce este posibil prin limbajul de codare Google. Posibilitățile sunt practic nesfârșite. Cu toate acestea, dacă nu aveți cunoștințe tehnice, exemplele de cod ar putea părea descurajantă și s-ar putea să vă gândiți că câștigurile de productivitate obținute din învățarea Google Apps Script ar putea să nu fie suficient de suficiente pentru a depăși investiția semnificativă în termeni de timp necesar. să-l învețe.

Acest lucru, desigur, depinde de mulți factori, inclusiv de ce tip de rol aveți sau așteptați să aveți în viitor. Dar chiar dacă nu vă așteptați să faceți ceva similar cu exemplele prezentate aici, înțelegerea a ceea ce este posibil și a câtă muncă ar fi nevoie pentru implementare poate declanșa gânduri și idei despre cum să îmbunătățiți productivitatea în compania dvs. clienții tăi sau personal.

Personal, pot atesta satisfacția de a sta pe spate și de a apăsa un buton care completează o oră de muncă manuală obositoare în mai puțin de un minut. După ce ai făcut acest lucru pentru a 50-a oară, vei fi recunoscător pentru cele două ore petrecute combinând totul împreună, în primul rând, care, în cele din urmă, a servit să-ți elibereze timp pentru activități cu valoare suplimentară. După un timp, aceste beneficii de scalabilitate devin dependență.