SQL-query's uitvoeren in Google Sheets zonder Apps Script JDBC

Google Sheets · SQL zonder Apps Script

Ren SQL in Google formulieren zonder Apps Script JDBC te schrijven

De JDBC-service van Apps Script werkt, en deze pagina laat je de eerlijke versie ervan zien: de code, de limiet van zes minuten, de IP-bereiken en het wachtwoord in het script. Vervolgens wordt de andere manier getoond: een kleine agent naast je database voert de SQL-query uit en een Google Sheets-add-on haalt de rijen op en plaatst ze in je spreadsheet. Geen JavaScript, geen wijzigingen in de firewall en het is inbegrepen bij het gratis abonnement.

Ongeveer 15 minuten Alleen SQL, geen JavaScript. Het wachtwoord blijft op uw netwerk. MySQL, PostgreSQL, SQL Server en meer.

Als je Apps Script JDBC nog steeds wilt gebruiken, is dit de eerlijke versie.

Google's JDBC-service Hiermee kan Apps Script verbinding maken met MySQL, Google Cloud SQL voor MySQL, PostgreSQL, Microsoft SQL Server en Oracle. Het kleinste werkende script dat een spreadsheet vult, ziet er als volgt uit:

functie klanten laden() {
  constant rekwisieten = PropertiesService.getScriptProperties();          // Houd het wachtwoord buiten de code
  constant verbinding = Jdbc.verbinding tot stand brengen('jdbc:mysql://db.example.com:3306/sales'rekwisieten.getEigenschap('DB_USER'), rekwisieten.getEigenschap('DB_PASS'));
  constant rs = verbinding.createStatement().uitvoerenQuery('SELECT customer_id, name, city FROM customers LIMIT 1000');
  constant rijen = [];
  terwijl (rs.volgende()) rijen.duw([rs.getString(1), rs.getString(2), rs.getString(3)]); rs.dichtbij(); verbinding.dichtbij();
  constant blad = Spreadsheet-app.getActiveSpreadsheet().getSheetByName('Klanten'); laken.inhoud wissen(); laken.bereik(1, 1, rijen.lengte, 3).setValues(rijen); }

Het voorbeeld maakt gebruik van MySQL. Voor de andere database-engines hoeft alleen de verbindingsreeks te worden aangepast: jdbc:postgresql://, jdbc:sqlserver:// en jdbc:oracle:thin:@ volgen dezelfde vorm, en Cloud SQL gebruikt jdbc:google:mysql://Voordat dat kan, moet uw database verbindingen van de servers van Google accepteren. Google publiceert de adresbereiken als een JSON-bestand die je toevoegt aan je databasefirewall en die je in stand moet houden, en de database moet luisteren op poort 1025 of hoger. De beperkingen die in de praktijk van invloed zijn, komen van Google. quotapagina:

Twee dingen worden in het bovenstaande voorbeeld verzwegen. Google zelf geeft de gebruikersnaam en het wachtwoord als platte tekst door in de code, en de meeste scripts kopiëren dat; de truc met de scripteigenschappen zorgt ervoor dat ze niet in de broncode terechtkomen, maar iedereen die het script kan bewerken, kan ze nog steeds lezen. Bovendien moet elke rij in het geheugen van het script passen en binnen zes minuten worden verwerkt, dus een trage query of een grote tabel betekent dat het werk handmatig moet worden opgedeeld. Als uw tabel klein is, uw database al via internet bereikbaar is en één ontwikkelaar verantwoordelijk is voor het script, is JDBC prima. De rest van deze pagina is voor alle anderen.

Wat je uiteindelijk in plaats daarvan krijgt

Een Google Sheet met het resultaat van een SQL-query op je database, inclusief een Query uitvoeren Een knop in het zijpaneel waarmee je de meest recente rijen kunt ophalen wanneer je maar wilt. Zoals dit:

Verkoop Opgeslagen op Drive

BestandBewerkingWeergaveInvoegenFormaatGegevensHulpmiddelenUitbreidingenHulp

	A	B	C	D	E
1	klant_id	voornaam	achternaam	land	totaal
2	1042	Maria	Alvarez	VS	1,284.50
3	1043	Daniël	Okafor	VS	612.00
4	1047	Priya	Nair	VS	2,090.75
5	1051	Thomas	Berg	VS	338.20
6	1056	Lucía	Ferreira	VS	1,455.00
7	1060	Kenji	Sato	VS	874.10
8

+ klanten Blad1 Live vanuit uw database

Het verschil zit hem in waar de query wordt uitgevoerd. Bij JDBC openen de servers van Google een verbinding met je database, waardoor je firewall toegang moet verlenen en het script het wachtwoord moet bewaren. Bij Query Streams draait een klein programma, de Agent, op een computer die al toegang heeft tot de database. Deze Agent maakt één uitgaande verbinding met Query Streams en voert daar de SQL-query uit. De Google Sheets-add-on vraagt om de rijen en alleen de rijen worden verzonden. Er wordt niets geopend in je firewall en het wachtwoord verlaat de Agent nooit.

Wat je nodig hebt

Een Query Streams-account

Het gratis abonnement is voldoende voor deze handleiding. Meld je aan via mijn.querystreams.nl.

De agent bevindt zich op een computer die de server kan bereiken.

Een klein programma dat op de achtergrond draait en de database raadpleegt wanneer Google Sheets daarom vraagt. Vaak is dit de databaseserver zelf, of een computer die altijd aan staat in hetzelfde netwerk.

Uw eigen verbindingsgegevens

De servernaam, de databasenaam en een gebruikersnaam en wachtwoord waarmee de database kan worden gelezen. Als iemand anders de database beheert, moet je deze gegevens aan die persoon vragen.

Stel het in vier stappen in.

1. Maak uw account aan

   Ga naar mijn.querystreams.nl en meld je aan met het Google-account dat je voor Sheets gebruikt. Kies de Gratis plan. Het is een echt plan, geen proefproject, en het omvat twee verbindingen en ad-hoc SQL vanuit Google Sheets.

2. Installeer de agent op een computer die de server kan bereiken.

   Aanmelden, openen DownloadInstalleer de agent op een machine die al toegang heeft tot uw database: vaak de databaseserver zelf, of een computer die altijd aan staat in hetzelfde netwerk. Dit duurt een minuut en de agent start daarna automatisch.

   Waarom een klein programma in plaats van een driver? De agent is een eenmalige installatie voor Windows, Mac of Linux (ongeveer 85 MB op Windows, iets meer dan 100 MB op Mac en Linux) die stil op de achtergrond draait. Het maakt één uitgaande, versleutelde verbinding met Query Streams, net zoals een browser dat doet, dus u hoeft geen firewallpoort te openen en geen VPN in te stellen. Uw database blijft op uw netwerk en de rijen worden naar uw locatie verzonden, indien nodig via internet. Het enige dat u nodig heeft, is de add-on.

   De downloadpagina toont Uw toegangssleutelPlak het in de Agent wanneer daarom wordt gevraagd en wacht tot de badge is weergegeven. Verbonden met querystreamsDat is het enige wat je op de agent zelf doet.

   Complete walkthroughs zijn beschikbaar als je die wilt: Windows, Mac, Linux.

3. Voeg uw databaseverbinding toe

   Open het portaal Gegevens → Gegevensagenten, klik Connector toevoegen Selecteer op uw agent het databasetype en vul de host, poort, database en een alleen-lezen login in. Klik Testverbinding, dan SlaDe verbinding bereikt de agent binnen enkele seconden. Elke database heeft zijn eigen stappenplan met de exacte velden: MySQL, PostgreSQL, SQL Server, OracleCloud SQL, Amazon RDS en Azure-databases maken op dezelfde manier verbinding; de agent hoeft ze alleen maar te kunnen bereiken.

   Geen IP-adressen op de whitelist. De agent maakt vanuit uw eigen netwerk verbinding met uw database en verzendt uitgaande querystreams via HTTPS. Er is geen lijst met Google-adressen die aan de firewall moet worden toegevoegd en er is verder niets om rekening mee te houden.

4. Voer de SQL-query uit vanuit Google Sheets.

   Open uw spreadsheet en Installeer de Query Streams-add-on. Download de app vanuit de Google Workspace Marketplace als je dat nog niet hebt gedaan. De app herkent het Google-account waarmee je al bent aangemeld; je hoeft niet apart in te loggen. Klik in het zijpaneel op Query StreamerKies je verbinding en plak dezelfde SELECT-query die je in het script zou hebben gebruikt:

   Voorbeeld · SQL

   SELECT klant-ID, naam, stad
   VAN klanten
   VOLGORDE DOOR naam
   LIMIET 1000;

   Kies het tabblad onder Bestemmingsblad en klik Query uitvoerenDe rijen komen onder uw kolomkoppen te staan. Klik Query uitvoeren U kunt de gegevens opnieuw ophalen wanneer u maar wilt. Om het werkblad automatisch te laten vernieuwen, slaat u de query op in Query Builder en stelt u een Automatisering, die het uitvoert volgens een schema dat varieert van elke vijf minuten tot wekelijks.

Naast elkaar, alleen dat wat standhoudt.

Wat de plannen toestaan

Ad-hoc SQL vanuit de Google Sheets-add-on is beschikbaar in alle abonnementen, inclusief het gratis abonnement. Het gratis abonnement omvat twee verbindingen en een maandelijkse datalimiet van 5 MB aan resultaten, voldoende voor een tabel zoals de bovenstaande die meerdere keren per dag wordt vernieuwd. Het uitvoeren van query's die u hebt opgeslagen in Query Builder vanuit de add-on is mogelijk vanaf Personal Core; query's die een collega met u deelt, werken in alle abonnementen. Bekijk de prijspagina. voor de vergoedingen per plan.

Vragen die mensen stellen

Gerelateerde handleidingen

• Hoe verbind je MySQL met Google Sheets?
• Hoe PostgreSQL te verbinden met Google Sheets
• Hoe verbind je SQL Server met Google Sheets?
• Synchroniseer MySQL met Google Sheets volgens een schema.
• Installeer de Google Sheets-add-on
• Power Query voor Google Sheets