C’est exactement ce que j’ai fais.
Connection a ma base de données MYSQL.
Si vous recherchez un hébergeur qui accepte les connexions distantes … moi j’utilise A2 Hosting
Je vais essayer de faire une tuto …
Je peux comprendre les reticences à ce sujet … mais franchement, les donnees que je stock ne sont pas secret d’etat …
Salut @Philazerty
Côté RGPD et Google, il faut un abonnement pro à 10,4 par mois pour s’assurer que les données soient en Europe et raccord avec la RGPD
@+
Ce n’est pas une histoire de secret mais de réglementation. Le RGPD doit être appliqué dès la mise en oeuvre tant qu’à faire ! Pour rappel, il y a tous vos clients et vos contacts, donc quelques données privées comme des noms, téléphones etc…
@+
Vous avez raison.
Cependant je suis sous google apps payant ! Donc cela devrait être OK non ?
Ok Démarrons une tuto :
Premiere Etape :
Hébergement (Sur A2Hosting par exemple)
Rajout de l’adresse IP de mon serveur sur Google
Controlling Access with Firewalls (Pour vous aider)
https://console.cloud.google.com/ (Pour faire le nécessaire)
Deuxième Etape :
Créer un document vierge sous Google Sheet
Renommer le « Sheet » comme « Products » (Dans l’exemple)
Troisième Etape :
Cliquez sur Tools (Outils) → Script Editor (Editeur de Script)
Quatrième Etape :
Créer un Menu.
Le mien s’appelle « Quote System ». Dans l’exemple, il va vous permettre de telecharger la liste des produits de Dolibarr vers votre Sheet « Products »
Copiez et coller le code ci-dessous :
function OnOpen() {
var ui = SpreadsheetApp.getUi();
ui.createMenu('Quote System')
.addItem('Update Products', 'GetProducts')
.addToUi();
}
Cinquième Etape :
Créer le script qui va se connecter a la base de donner et telecharger les produits.
Copiez et coller le code ci-dessous apres la fonction OnOpen() soit en ligne 8:
function GetProducts() {
SpreadsheetApp.getActiveSpreadsheet().toast('Getting Products'); // Affichage d'une petite fenetre marquant l'etape.
// variables de connexions
var dbUrl = "jdbc:mysql://ladresse.demon.serveur:3306/nom_de_ma_table"; // changez les variables ici pour celles correspondant a votre base de données
var user = "mon_utilisateur";
var userPwd = "mon_password";
var connection = Jdbc.getConnection(dbUrl, user, userPwd);
// Lancer la requete
var SQLstatement = connection.createStatement();
var result = SQLstatement.executeQuery("SELECT rowid, ref, entity, ref_ext, datec, tms, fk_parent, label FROM matable.llxo7_product WHERE tosell=1"); // remplacer matable par le nom de votre table. Cette requete ne telechargera que les produits actifs et llxo7 correspond au prefixe de ma table. vous devez donc remplacer llxo7.product par votre table.
// Definition de la Cible
var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Products");
var cell = ss.getRange('A2');
// loop dans les resultats
var row = 0;
while(result.next()) {
for(var i=0; i<8; i++) { // i<8 donne le nombre de colonnes dans la base
cell.offset(row, i).setValue(result.getString(i+1));
}
row++;
}
// fermeture des connexions
result.close();
SQLstatement.close();
connection.close();
//Browser.msgBox('Products Updated'); // ou
SpreadsheetApp.getActiveSpreadsheet().toast('Products Updated');
};
// Fin du script
Ensuite sauvegardez le script, fermez le document et ouvrez le a nouveau.
Vous devriez avoir un menu « Quote System » qui apparait juste apres AIDE
Cliquez dessus puis sur « Update Products »
Vous devriez voir la feuille se remplir au fur et a mesure.
Si jamais le menu n’apparait pas … cela arrive de temps en temps, forcez l’ouverture en passant par la fontion automatisation (Triggers) de google script.
Et voila !!!
Vous pouvez biensur utiliser ce principe pour toutes les tables que vous souhaitez telecharger … vous pourrez apres cela utiliser google sheet pour faire des croisement et construire toute sorte de nouveau tableau (index, vlookup …)
A vous de jouer !
ATTENTION : en copiant le code, les ’ deviennent des <> et les " deviennent << >>
Donc remplacez les < et > par ’ et les << et >> par " dans votre code sous google script sinon ca ne marchera pas.
2 « J'aime »
Oui si vous avez choisit un pays d’europe comme lieu de stockage.
Merci en tout cas pour la contribution
@+
Quelqu’un a t’il essayer la tuto ?
Je compte essayer, mais ce n’est pas encore fait. Je vous ferai un retour.
Si tu encadres par trois antiquotes (AltGr 7) au début et à la fin, tu as une présentation formatée comme du code et les ' restent.
Merci pour ce partage
alors, vous avez essayé ?
Hier non, mais comme cette solution m’intéresse beaucoup, je viens de tester. En deux mots, c’est un beaucoup trop technique pour moi. Je bute à presque toutes les étapes du tuto. Donc à réserver à des utilisateurs plus avancés que moi.
Si je détaille mes difficultés :
Etape 1 :
Je comprends qu’il faut récupérer l’adresse IP du serveur qui héberge son instance Dolibarr pour l’ajouter à console.cloud.google.
En suivant les instructions « Controlling Accesse with Firewalls » en voulant créer une règle de de pare-feu, j’ai tout de suite une erreur. En bidouillant un peu à l’aveugle, je crée une application dans App Engine, et je peux alors créer une règle de pare feu pour une plage d’adresses IP. Ce sera vraiment de la chance si c’est ce qu’il fallait faire…
Etape 2 : ouf c’est facile…
Etape 3 : Ça va encore. ma feuille s’appelle également « Products »
Etapes 4 et 5 : j’ai supprimé le code par défaut lorsque l’on ouvre l’éditeur de script. Je rajoute les deux blocs de code. J’ai utilisé le lien direct pour copier le code, donc normalement pas d’erreur de copie. Mon deuxième bloc de code commence en ligne 7 puisqu’il y a 6 lignes pour le premier bloc. J’enregistre. Je ferme et j’ouvre à nouveau « Products »
Je n’ai pas le nouveau menu « Update Products ».
Je suis la procédure « Installable Triggers » et je crois m’en sortir. Je ferme le fichier Products. Je l’ouvre à nouveau.
Et toujours rien !
Bref étant incapable de comprendre ce que je fais, ce n’est pas à ma portée.
PS : j’ai bien fait attention aux remplacements de ’ et ". Chez moi il n’y a pas eu de problème.
Etape 1 :
Je comprends qu’il faut récupérer l’adresse IP du serveur qui héberge son instance Dolibarr pour l’ajouter à console.cloud.google.
Oui … mais votre hebergeur doit vous donner l’adresse IP. Tous les hebergeurs ne donne pas cette possibilite.
En suivant les instructions « Controlling Accesse with Firewalls » en voulant créer une règle de de pare-feu, j’ai tout de suite une erreur. En bidouillant un peu à l’aveugle, je crée une application dans App Engine, et je peux alors créer une règle de pare feu pour une plage d’adresses IP. Ce sera vraiment de la chance si c’est ce qu’il fallait faire…
C’est cela … vous devez autoriser votre adresse IP.
Etape 2 : ouf c’est facile…
LOL
Etape 3 : Ça va encore. ma feuille s’appelle également « Products »
Etapes 4 et 5 : j’ai supprimé le code par défaut lorsque l’on ouvre l’éditeur de script. Je rajoute les deux blocs de code. J’ai utilisé le lien direct pour copier le code, donc normalement pas d’erreur de copie. Mon deuxième bloc de code commence en ligne 7 puisqu’il y a 6 lignes pour le premier bloc. J’enregistre. Je ferme et j’ouvre à nouveau « Products »
Je n’ai pas le nouveau menu « Update Products ».
Il y a un code pour creer le menu. Si le OnOpen ne fonctionne pas, il faut passer par les triggers pour lancer l’ouverture du menu.
Je suis la procédure « Installable Triggers » et je crois m’en sortir. Je ferme le fichier Products. Je l’ouvre à nouveau.
Re-essayez d’ouvrir avec le triggers pour la fonction OnOpen
ATTENTION : en copiant le code, les ’ deviennent des <> et les " deviennent << >>
Donc remplacez les < et > par ’ et les << et >> par " dans votre code sous google script sinon ca ne marchera pas.
Merci de votre retour rapide. J’avais bien vérifié l’absence d’erreur de copié/collé. Je n’ai pas eu de modification des caractères ’ et "
Bonjour @vmamet , merci beaucoup pour ce tuto ! J’ai réussi à mettre en place un script d’import adapté à mon besoin (import des projets, commandes, factures).
J’ai fait quelques ajustements toutefois :
-
Concernant l’ajout de l’adresse IP ; je n’ai pas eu besoin de bidouiller dans console.cloud.google ; en revanche pour permettre à Google Script de faire les requêtes vers ma base de données il fallait côté hébergeur whitelister les adresses IP de Google ; voir la doc ici developers.google → jdbc
-
j’ai utilisé un autre script que celui de votre tuto, en effet avec le script en exemple j’avais une erreur au moment où le tableau se remplit dans Sheet.
Voici le code que j’ai utilisé :
function onOpen(e) {
SpreadsheetApp.getUi()
.createMenu('Dolibarr')
.addItem('import projets', 'getProjets')
.addItem('import commandes', 'getCommandes')
.addItem('import factures', 'getFactures')
.addToUi();
}
var server = "xxxx_adresse_de_votre_serveur";
var port = 3306;
var dbName = "Nom_de_la_base_mysql";
var username = "username_mysql";
var password = "password_username_mysql^";
var url = "jdbc:mysql://"+server+":"+port+"/"+dbName;
function getFactures() {
var conn = Jdbc.getConnection(url, username, password);
var stmt = conn.createStatement();
var results = stmt.executeQuery("SELECT DISTINCT f.rowid as f_rowid, s.nom as s_nom, f.ref as f_ref, f.ref_client as f_ref_client, f.datef as f_datef, f.date_lim_reglement as f_date_lim_reglement, REPLACE(f.total_ht, '.', ',') as f_total_ht, REPLACE(f.total_ttc, '.', ',') as f_total_ttc, f.paye as f_paye, f.fk_statut as f_fk_statut, f.close_code as f_close_code, pj.ref as pj_ref, pj.title as pj_title FROM llx_societe as s LEFT JOIN llx_societe_extrafields as extra4 ON s.rowid = extra4.fk_object LEFT JOIN llx_societe as ps ON ps.rowid = s.parent LEFT JOIN llx_c_country as c on s.fk_pays = c.rowid LEFT JOIN llx_c_departements as cd on s.fk_departement = cd.rowid, llx_facture as f LEFT JOIN llx_projet as pj ON f.fk_projet = pj.rowid LEFT JOIN llx_user as uc ON f.fk_user_author = uc.rowid LEFT JOIN llx_user as uv ON f.fk_user_valid = uv.rowid LEFT JOIN llx_facture_extrafields as extra ON f.rowid = extra.fk_object , llx_facturedet as fd LEFT JOIN llx_facturedet_extrafields as extra2 on fd.rowid = extra2.fk_object LEFT JOIN llx_product as p on (fd.fk_product = p.rowid) LEFT JOIN llx_product_extrafields as extra3 on p.rowid = extra3.fk_object WHERE f.fk_soc = s.rowid AND f.rowid = fd.fk_facture AND f.entity IN (1) ORDER BY f_datef DESC");
var metaData=results.getMetaData();
var numCols = metaData.getColumnCount();
var spreadsheet = SpreadsheetApp.getActive();
var sheet = spreadsheet.getSheetByName("Factures");
sheet.clearContents();
var arr=[];
for (var col = 0; col < numCols; col++) {
arr.push(metaData.getColumnName(col + 1));
}
sheet.appendRow(arr);
while (results.next()) {
arr=[];
for (var col = 0; col < numCols; col++) {
arr.push(results.getString(col + 1));
}
sheet.appendRow(arr);
}
results.close();
stmt.close();
//sheet.autoResizeColumns(1, numCols+1);
SpreadsheetApp.getActiveSpreadsheet().toast('Import achevé');
}
function getCommandes() {
var conn = Jdbc.getConnection(url, username, password);
var stmt = conn.createStatement();
var results = stmt.executeQuery("SELECT DISTINCT s.rowid as s_rowid, s.nom as s_nom, c.ref as c_ref, c.ref_client as c_ref_client, c.date_commande as c_date_commande, REPLACE(c.amount_ht, '.', ',') as c_amount_ht, REPLACE(c.total_ht, '.', ',') as c_total_ht, REPLACE(c.total_ttc, '.', ',') as c_total_ttc, c.facture as c_facture, c.fk_statut as c_fk_statut, pj.ref as pj_ref FROM llx_societe as s LEFT JOIN llx_societe_extrafields as extra4 ON s.rowid = extra4.fk_object LEFT JOIN llx_societe as ps ON ps.rowid = s.parent LEFT JOIN llx_c_departements as d ON s.fk_departement = d.rowid LEFT JOIN llx_c_country as co ON s.fk_pays = co.rowid, llx_commande as c LEFT JOIN llx_projet as pj ON c.fk_projet = pj.rowid LEFT JOIN llx_user as uc ON c.fk_user_author = uc.rowid LEFT JOIN llx_user as uv ON c.fk_user_valid = uv.rowid LEFT JOIN llx_commande_extrafields as extra ON c.rowid = extra.fk_object , llx_commandedet as cd LEFT JOIN llx_commandedet_extrafields as extra2 on cd.rowid = extra2.fk_object LEFT JOIN llx_product as p on cd.fk_product = p.rowid LEFT JOIN llx_product_extrafields as extra3 on p.rowid = extra3.fk_object WHERE c.fk_soc = s.rowid AND c.rowid = cd.fk_commande AND c.entity IN (1) ORDER BY c_date_commande DESC");
var metaData=results.getMetaData();
var numCols = metaData.getColumnCount();
var spreadsheet = SpreadsheetApp.getActive();
var sheet = spreadsheet.getSheetByName("Commandes");
sheet.clearContents();
var arr=[];
for (var col = 0; col < numCols; col++) {
arr.push(metaData.getColumnName(col + 1));
}
sheet.appendRow(arr);
while (results.next()) {
arr=[];
for (var col = 0; col < numCols; col++) {
arr.push(results.getString(col + 1));
}
sheet.appendRow(arr);
}
results.close();
stmt.close();
//sheet.autoResizeColumns(1, numCols+1);
SpreadsheetApp.getActiveSpreadsheet().toast('Import achevé');
}
function getProjets() {
var conn = Jdbc.getConnection(url, username, password);
var stmt = conn.createStatement();
var results = stmt.executeQuery("SELECT DISTINCT p.rowid as p_rowid,s.nom as s_nom, p.ref as p_ref, p.title as p_title, p.dateo as p_dateo FROM llx_projet as p LEFT JOIN llx_projet_extrafields as extra ON p.rowid = extra.fk_object LEFT JOIN llx_c_lead_status as cls ON p.fk_opp_status = cls.rowid LEFT JOIN llx_projet_task as pt ON p.rowid = pt.fk_projet LEFT JOIN llx_projet_task_extrafields as extra2 ON pt.rowid = extra2.fk_object LEFT JOIN llx_projet_task_time as ptt ON pt.rowid = ptt.fk_task LEFT JOIN llx_societe as s ON p.fk_soc = s.rowid WHERE p.entity IN (1) ORDER BY p_dateo DESC");
var metaData=results.getMetaData();
var numCols = metaData.getColumnCount();
var spreadsheet = SpreadsheetApp.getActive();
var sheet = spreadsheet.getSheetByName("Projets");
sheet.clearContents();
var arr=[];
for (var col = 0; col < numCols; col++) {
arr.push(metaData.getColumnName(col + 1));
}
sheet.appendRow(arr);
while (results.next()) {
arr=[];
for (var col = 0; col < numCols; col++) {
arr.push(results.getString(col + 1));
}
sheet.appendRow(arr);
}
results.close();
stmt.close();
//sheet.autoResizeColumns(1, numCols+1);
SpreadsheetApp.getActiveSpreadsheet().toast('Import achevé');
}
Deux petites astuces par ailleurs qui m’ont été utiles :
- pour générer des requêtes SQL facilement il est possible d’utiliser l’outil export de l’admin de dolibarr, au moment où le fichier CSV d’un export est généré on peut copier/coller le code SQL.
- en fin de script SQL j’ai ajouté une option pour trier par date : ORDER BY f_datef DESC
- le séparateur décimal dans la base de données étant le point j’ai remplacé par un virgule directement dans la requête SQL : REPLACE(f.total_ht, ‹ . ›, ‹ , ›) as f_total_ht
Idée super intéressante.
Serait-il possible de transposer cette idée avec un service tel que :
Grids de chez Infomaniak
Framacalc de chez Framasoft
ou autres tableurs partagés…
Merci d’avance
Je ne saurai vous dire … je n’ai jamais travailler avec eux. Mais si leur base de données est accessible … ca doit etre jouable
