Een beginnershandleiding voor Google Spreadsheets

Hoe

DoorRyan Dube

Laatst geupdate op6 augustus 2020

Als u Google Spreadsheets gebruikt, is het wellicht interessant om te weten dat Google Scripts uw spreadsheets aanzienlijk kan verbeteren.

Denk aan Google Scripts zoals VBA in Microsoft Excel. Hiermee kunt u code aan uw spreadsheet toevoegen om gegevensinvoer te automatiseren. U kunt het ook gebruiken om externe gegevens in uw bladen te brengen. Het beste van alles is dat u berekeningen volgens een periodiek schema kunt uitvoeren, zelfs als u de spreadsheet niet heeft geopend.

In dit artikel leert u hoe u Google Scripts inschakelt en hoe u een eenvoudig script schrijft en uitvoert.

Hoe Google Scripts te gebruiken

Er zijn een paar dingen in Google Spreadsheets die u moet inschakelen voordat u daar Google Scripts kunt schrijven.

Selecteer het menu Extra en selecteer Scripteditor uit het menu.

Hiermee wordt de Google Scripts-editor geopend in een naamloos projectvenster.

Dit script bevindt zich feitelijk in uw Google-account in de Google Scripts-tool. Dit specifieke project is verbonden met de spreadsheet waaraan je momenteel werkt.

Je kunt een script schrijven, maar pas uitvoeren als scripts daadwerkelijk zijn ingeschakeld in het blad. U moet dit de eerste keer dat u het uitvoert doen (zoals u hieronder zult zien).

Om dit in actie te zien, maken we een eenvoudig script.

Maak uw eerste Google-script

In dit voorbeeld ga je een script schrijven waarin alle quiz- en eindexamenscores voor de B-kolom worden opgeteld (Chris's scores).

Dit is de voorbeeldspreadsheet.

Terug in het Google Scripts-venster, plak de volgende code in de functie myFunction () accolades, zoals zo:


functie myFunction () {
var ss = SpreadsheetApp.getActiveSpreadsheet ();
var sheet = ss.getSheets () [0] var bereik = sheet.getRange (2,2,5);
var waarden = bereik.getValues ​​();
var som = 0
voor (var rij in waarden) {
voor (var col in waarden [rij]) {
som = waarden [rij] [kol] + som;
}
}
sheet.getRange (7,2) .setValue (som);
}


Deze code ziet er misschien ingewikkeld uit, maar laten we precies opsplitsen wat hij doet. Het is eenvoudiger dan het lijkt.

Laten we deze code regel voor regel opsplitsen.

De variabelen definiëren

1. var ss = SpreadsheetApp.getActiveSpreadsheet ();

Deze regel haalt de huidige spreadsheet op die actief is. Aangezien u dit script alleen uitvoert wanneer de spreadsheet waarop u de berekening wilt uitvoeren actief is, krijgt deze altijd de juiste spreadsheet. Het blad wordt opgeslagen als een "object" -variabele genaamd "ss".

2. var sheet = ss.getSheets () [0]

Dit haalt het eerste blad op en slaat het op als een objectvariabele met de naam blad. Er is een alternatieve functie die u zou kunnen gebruiken om het blad bij de bladnaam te krijgen.

Dat zou zijn var sheet = ss.getSheetByName ("Sheet1");

U kunt elke gewenste functie gebruiken.

3. var bereik = sheet.getRange (2,2,5);

Deze functie haalt een reeks waarden op per rij, kolom en aantal rijen. In dit geval halen we alle waarden op vanaf de tweede rij, de tweede kolom en krijgen we waarden vijf rijen lager.

De variabele 'bereik' wordt een zogenaamde 'array'. Een array is een variabele die meerdere waarden bevat. In Google Script verwijst u naar elk van deze nummers met behulp van een speciaal adresschema voor elementen met haakjes, zoals u hieronder zult zien.

4. var waarden = bereik.getValues ​​();

De functie getValues ​​() haalt de waarden daadwerkelijk uit het bereikobject dat u hierboven hebt gedefinieerd.

5. var som = 0

Dit is slechts een variabele met één getal die we initialiseren met het getal 0. Dit wordt het begin van hoe we alle cijfers in de kolom gaan samenvatten.

De For Loops maken

6. voor (var rij in waarden) {

Dit is wat een "For" -lus wordt genoemd. Dit soort lus doorloopt een specifiek aantal items in een lijst. In dit geval gaat het door de array "waarden" die we hierboven hebben besproken. Deze eerste lus loopt door alle rijen in de array. In dit geval is er maar één.

Opmerking: matrixrijen zijn niet hetzelfde als spreadsheetrijen. Toen de array de waarden uit de kolom in het werkblad laadde, werden ze in een enkele regel (of rij) met gegevens geplaatst, als volgt: "89, 92, 78, 98, 89". Dus in dit geval heeft de array één regel (rij) en vijf getallen (kolommen).

7. voor (var col in waarden [rij]) {

Deze tweede for-lus doorloopt alle "rijen" in de array. In dit geval worden alle 5 waarden doorlopen.

8. som = waarden [rij] [kol] + som;

Elke keer dat de lus wordt uitgevoerd, blijft deze instructie de som van de nieuwe waarde bij zichzelf optellen. Tegen de tijd dat alle 5 lussen plaatsvinden, moeten alle 5 getallen worden opgeteld in de "som" -variabele.

9 sheet.getRange (7,2) .setValue (som);

Dit is een verklaring met twee functies. De functie getRange verwijst naar de cel op de 7e rij en de 2e kolom. De functie setValue plaatst vervolgens de waarde van de variabele "sum" in die cel.

Nu je begrijpt hoe elke regel van de code werkt, is het tijd om alles in actie te zien.

Uw Google-script opslaan

Als u klaar bent met het typen van uw Google-script, wordt het tijd om het op te slaan en de code stapsgewijs te doorlopen. Typ eerst "Untitled project" om uw project een naam te geven. Selecteer vervolgens het floppydisk-pictogram in het menu om het script op te slaan.

Mogelijk ziet u een foutmelding wanneer u probeert op te slaan. Geen paniek. Dit is eigenlijk een onderdeel van het foutopsporingsproces. Let goed op het regelnummer waar staat dat u een fout heeft. Kijk goed naar uw code en los eventuele typefouten of syntaxisfouten op.

In dit voorbeeld miste mijn "for" -lus op regel 8 het openings-symbool "("). Eenmaal opgelost, probeer opnieuw op te slaan. Zodra u uw code kunt opslaan, beschouwt Google uw codesyntaxis als in orde.

Dit betekent niet dat uw code zal werken zoals bedoeld, het betekent alleen dat deze prima kan "compileren", zonder syntaxisproblemen.

Uw Google-script uitvoeren

Selecteer nu het afspeelpictogram bovenaan om door uw code te lopen. Aangezien dit de eerste keer is dat u uw nieuwe script uitvoert, moet u de Google Script-app toestemming geven voor toegang tot uw spreadsheet (gegevens) in uw Google-account. Selecteer Bekijk machtigingen in het pop-upvenster.

U moet inloggen op uw Google-account. En aangezien dit een app op maat is en niet een app die bij Google is geregistreerd, ziet u een waarschuwing dat de app niet is geverifieerd. Selecteer de Geavanceerd link en selecteer vervolgens Ga naar voorbeeld Google-script (onveilig) doorgaan.

Op het volgende scherm moet je selecteren Toestaan om Google Script toegang te geven tot uw Google-account. Zodra u dat doet, wordt het script uitgevoerd.

Als u geen fouten ziet, schakelt u terug naar de spreadsheet en ziet u de resultaten van uw script onder aan de eerste kolom verschijnen.

Geavanceerde Google-scripts schrijven

Dit was een heel eenvoudig Google-script dat de cellen eenvoudig in een enkele kolom optelde. Als je variabelen aan de twee For-lussen in de code wilt toevoegen, kun je ook alle kolommen doorlopen en voor alle kolommen een samenvatting geven in rij 7.

Google Scripts biedt allerlei mogelijkheden, zoals e-mail verzenden vanuit een spreadsheet. U kunt er zelfs gebruik van maken speciale Google API's waarmee je andere Google-apps zoals Gmail, Agenda en zelfs Google Analytics kunt integreren. Zodra u begint te leren hoe u Google-scripts schrijft, zijn de mogelijkheden eindeloos.