Exécuter des requêtes SQL dans Google Sheets sans JDBC Apps Script

Google Sheets · SQL sans Apps Script

Exécuter SQL dans Google Sheets sans écrire de script JDBC Apps Script

Le service JDBC d'Apps Script fonctionne, et cette page vous en présente la version transparente : le code, la limite de six minutes, les plages d'adresses IP et le mot de passe dans le script. Elle vous montre ensuite une autre approche : un petit agent installé à côté de votre base de données exécute la requête SQL, et une extension Google Sheets importe les données dans votre feuille de calcul. Aucun JavaScript, aucune modification du pare-feu : tout est inclus dans la version gratuite.

Environ 15 minutes SQL uniquement, pas de JavaScript Votre mot de passe reste sur votre réseau MySQL, PostgreSQL, SQL Server et bien plus encore

Si vous souhaitez toujours utiliser Apps Script JDBC, voici la version honnête.

Google Service JDBC Permet de connecter Apps Script à MySQL, Google Cloud SQL pour MySQL, PostgreSQL, Microsoft SQL Server et Oracle. Le plus petit script fonctionnel qui remplit une feuille de calcul ressemble à ceci :

fonction charger les clients() {
  const propriétés = Services de propriétés.obtenirScriptPropriétés();          // Ne pas inclure le mot de passe dans le code
  const conn = Jdbc.obtenirConnection('jdbc:mysql://db.example.com:3306/sales', accessoires.obtenirPropriété('DB_USER'), accessoires.obtenirPropriété('DB_PASS'));
  const rs = conn.créer une déclaration().exécuterRequête('SELECT customer_id, name, city FROM customers LIMIT 1000');
  const lignes = [];
  alors que (rs.suivant()) lignes.pousser([rs.obtenirString(1), rs.obtenirString(2), rs.obtenirString(3)]); rs.fermer(); conn.fermer();
  const feuille = Application de feuille de calcul.obtenirActiveSpreadsheet().obtenirFeuilleParNom('Clients'); feuille.effacer le contenu(); feuille.obtenirRange(1, 1, lignes.longueur, 3).définir les valeurs(lignes); }

Cet exemple utilise MySQL. Pour les autres moteurs, seule la chaîne de connexion change : jdbc:postgresql://, jdbc:sqlserver:// et jdbc:oracle:thin:@ suivent la même forme, et Cloud SQL utilise jdbc:google:mysql://Avant cela, votre base de données doit accepter les connexions des serveurs de Google. Google publie les plages d'adresses sous forme de fichier JSON que vous ajoutez à votre pare-feu de base de données et que vous maintenez à jour, et la base de données doit écouter sur un port 1025 ou supérieur. Les limites qui posent problème en pratique proviennent de Google. page des quotas:

L'exemple ci-dessus masque deux choses. Les exemples de Google eux-mêmes transmettent le nom d'utilisateur et le mot de passe en clair dans le code, et la plupart des scripts en circulation les copient. L'astuce des propriétés du script les empêche d'apparaître dans le code source, mais toute personne ayant accès au script peut toujours les lire. De plus, chaque ligne doit tenir dans la mémoire du script et le traitement doit être terminé en moins de six minutes. Par conséquent, une requête lente ou une table volumineuse implique de découper le travail manuellement. Si votre table est petite, que votre base de données est déjà accessible depuis Internet et qu'un seul développeur est responsable du script, JDBC convient parfaitement. La suite de cette page s'adresse à tous les autres utilisateurs.

Ce que vous obtiendrez à la place

Une feuille Google Sheets qui contient le résultat d'une requête SQL sur votre base de données, avec un Exécuter une requête Un bouton dans le panneau latéral permet d'afficher les dernières lignes à tout moment. Par exemple :

Ventes Enregistré sur Drive

DéposerModifierVoirInsérerFormatDonnéesOutilsExtensionsAide

	A	B	C	D	E
1	numéro de client	prénom	nom de famille	pays	total
2	1042	Marie	Alvarez	USA	1,284.50
3	1043	Daniel	Okafor	USA	612.00
4	1047	Priya	Nair	USA	2,090.75
5	1051	Thomas	Berg	USA	338.20
6	1056	Lucía	Ferreira	USA	1,455.00
7	1060	Kenji	Sato	USA	874.10
8

+ clients Feuille1 En direct de votre base de données

La différence réside dans l'endroit où la requête est exécutée. Avec JDBC, les serveurs de Google établissent une connexion à votre base de données ; votre pare-feu doit donc autoriser cette connexion et le script doit contenir le mot de passe. Avec Query Streams, un petit programme appelé Agent s'exécute sur un ordinateur ayant déjà accès à la base de données, établit une connexion sortante vers Query Streams et y exécute la requête SQL. L'extension Google Sheets demande les lignes et seules ces lignes sont transmises. Aucune connexion n'est ouverte au niveau de votre pare-feu et le mot de passe ne quitte jamais l'Agent.

Ce dont vous aurez besoin

Un compte Query Streams

Le forfait gratuit suffit pour ce guide. Inscrivez-vous sur mon.querystreams.com.

L'agent installé sur un ordinateur pouvant accéder au serveur

Un petit programme qui s'exécute en arrière-plan et interroge la base de données lorsque Google Sheets le demande. Il s'agit généralement du serveur de base de données lui-même, ou de tout ordinateur toujours allumé sur le même réseau.

Vos propres informations de connexion

Le nom du serveur, le nom de la base de données, ainsi qu'un nom d'utilisateur et un mot de passe permettant d'y accéder. Si la base de données est gérée par une autre personne, c'est à elle qu'il faut demander ces informations.

Configurez-le en quatre étapes

1. Créez votre compte

   Aller à mon.querystreams.com et inscrivez-vous avec le compte Google que vous utilisez pour Sheets. Choisissez le Gratuit Il s'agit d'un véritable plan, et non d'un essai, qui couvre deux connexions et des requêtes SQL ad hoc depuis Google Sheets.

2. Installez l'agent sur un ordinateur pouvant accéder au serveur.

   Connectez-vous, ouvrez TéléchargerInstallez ensuite l'agent sur une machine ayant déjà accès à votre base de données : généralement le serveur de base de données lui-même, ou tout ordinateur toujours allumé sur le même réseau. L'installation prend une minute et l'agent s'exécute ensuite automatiquement.

   Pourquoi un petit programme plutôt qu'un pilote ? L'agent s'installe en une seule étape sur Windows, Mac ou Linux (environ 85 Mo sur Windows, un peu plus de 100 Mo sur Mac et Linux) et fonctionne discrètement en arrière-plan. Il établit une connexion sortante chiffrée avec Query Streams, comme le ferait un navigateur ; vous n'avez donc pas besoin d'ouvrir de port dans votre pare-feu ni de configurer de VPN. Votre base de données reste sur votre réseau et les données sont accessibles où que vous soyez, via Internet si nécessaire. Seul le module complémentaire est requis de votre côté.

   La page de téléchargement affiche Votre clé d'accèsCollez-le dans l'Agent lorsqu'il vous le demande, et attendez que le badge affiche Connecté aux flux de requêtesC'est la seule chose que vous devez faire sur l'Agent lui-même.

   Des guides complets sont disponibles si vous le souhaitez : Fenêtres, Mac, Linux.

3. Ajoutez votre connexion à la base de données

   Dans le portail ouvert Données → Agents de données, cliquez Ajouter un connecteur Sur votre agent, choisissez le type de base de données et renseignez l'hôte, le port, la base de données et un identifiant de connexion en lecture seule. Cliquez Test de connexion, puis ÉconomiserLa connexion atteint l'agent en quelques secondes. Chaque base de données possède son propre guide détaillant les champs exacts : MySQL, PostgreSQL, Serveur SQL, OracleLes bases de données Cloud SQL, Amazon RDS et Azure se connectent de la même manière ; l’agent doit simplement pouvoir y accéder.

   Aucune liste blanche d'adresses IP. L'agent se connecte à votre base de données depuis votre réseau interne et interroge les flux sortants via HTTPS. Aucune liste d'adresses Google à ajouter au pare-feu ni aucune configuration à gérer n'est requise.

4. Exécutez la requête SQL depuis Google Sheets

   Ouvrez votre feuille de calcul et installer l'extension Query Streams Téléchargez-le depuis Google Workspace Marketplace si vous ne l'avez pas encore fait. Il reconnaît le compte Google avec lequel vous êtes déjà connecté ; aucune connexion supplémentaire n'est requise. Dans le panneau latéral, cliquez sur Flux de requêtes, sélectionnez votre connexion et collez la même requête SELECT que celle que vous auriez utilisée dans le script :

   Exemple · SQL

   SELECTIONNER identifiant_client, nom, ville
   DE clients
   COMMANDE PAR nom
   LIMITE 1000;

   Choisissez l'onglet sous Fiche de destination et cliquez Exécuter une requêteLes lignes se placent sous vos en-têtes de colonnes. Cliquez Exécuter une requête Vous pouvez le faire à nouveau chaque fois que vous souhaitez obtenir les données les plus récentes. Pour que la feuille s'actualise automatiquement, enregistrez la requête dans le Générateur de requêtes et configurez un Automatisation, qui l'exécute selon une fréquence allant de toutes les cinq minutes à une fois par semaine.

Côté à côte, seul ce qui tient la route

Ce que les plans permettent

L'exécution de requêtes SQL ad hoc via l'extension Google Sheets est incluse dans tous les forfaits, y compris le forfait gratuit. Ce dernier comprend deux connexions et un quota mensuel de 5 Mo de résultats, suffisant pour une table comme celle présentée ci-dessus, mise à jour plusieurs fois par jour. L'exécution des requêtes enregistrées dans Query Builder de l'extension est disponible à partir du forfait Personal Core ; les requêtes partagées par un collègue sont compatibles avec tous les forfaits. Consultez la page des prix pour les indemnités prévues par le régime.

Questions que se posent les gens

Guides associés

• Comment connecter MySQL à Google Sheets
• Comment connecter PostgreSQL à Google Sheets
• Comment connecter SQL Server à Google Sheets
• Synchroniser MySQL avec Google Sheets selon un calendrier
• Installez le module complémentaire Google Sheets.
• Power Query pour Google Sheets