Apps Script _ Import en fonction de critères
Bonjour,
Je souhaite, dans un fichier Google Sheet, importer des données à partir d'une base (située sur un autre fichier) en fonction de critères, par un script.
Grâce à l'aide précieuse de Steelson, j'ai pu mettre en place une fonction d'import "simple" dans Apps Scripts :
function BDDimport(){
// source
var ss = SpreadsheetApp.openById('1AJYCMTMEZbohwj_OP_OBMG-iuCo964v383URoW-Z8R0');
var data = ss.getSheetByName('Feuille 1').getDataRange().getValues()
// destination
var sh = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Stock Fruits')
SpreadsheetApp.getActive().getSheetByName('data_sales_source').clear()
sh.getRange(1,1,data.length,data[0].length).setValues(data)
SpreadsheetApp.getActive().getSheetByName('Settings').getRange('G6').setValue(new Date())
}Mais je bute dorénavant sur la mise en place des critères, et n'arrive pas à trouver exactement ce que je recherche sur les différents forums.
FICHIER BASE DE DONNEES
https://docs.google.com/spreadsheets/d/1AJYCMTMEZbohwj_OP_OBMG-iuCo964v383URoW-Z8R0/edit?usp=sharing
FICHIER D'IMPORT
https://docs.google.com/spreadsheets/d/1meE8T3iDxdEnl2SPb07Zz_13h15tnQUdruIRaA-efo0/edit?usp=sharing
Sur ce fichier, un onglet "Paramètres" sur lequel j'entre les critères que le script doit aller chercher dans la BDD. Dans mon exemple, je ne souhaite importer que les lignes de stock de pommes venant de France.
L'idée étant que le script vienne chercher en "Paramètres!B1" et "Paramètres!B2" les critères d'importation souhaités.
Merci d'avance pour votre aide, si cela est réalisable.
Bonjour,
function BDDimport() {
// source
var ss = SpreadsheetApp.openById('1AJYCMTMEZbohwj_OP_OBMG-iuCo964v383URoW-Z8R0');
var data = ss.getSheetByName('Feuille 1').getDataRange().getValues()
// critères
var ss = SpreadsheetApp.getActiveSpreadsheet()
var param = ss.getSheetByName('Paramètres')
var type = param.getRange('B1').getValue()
var pays = param.getRange('B2').getValue()
data = data.filter((r,i) => (i==0 || (r[1] == type && r[3] == pays))) // i=0 pour les en-têtes et ensuite application des critères
console.log(data)
// à mettre ensuite dans la bonne feuille
}dans
data.filter((r,i) => (i==0 || (r[1] == type && r[3] == pays)))on a r qui est la ligne complète et i l'index de cette ligne (qui commence à 0
on filtre selon différents critères
- si i=0 pour conserver l'en-tête
- si r[1] (1 = colonne B) est le type et r[3] (3 colonne D) la pays
Bonjour,
Merci pour ta réponse.
Le script fonctionne, j'ai malheureusement une erreur qui se déclenche.
Je comprends que cela vient de ma base qui est certainement trop grande (6 colonnes, jusqu'à 500k lignes).
Là, j'ai bien peur qu'il n'y ait pas de solution. je cherchais justement à intégrer des critères pour passer outre cette erreur...
en effet, on importe d'abord et ensuite on filtre
mais dans ce cas, il faudrait que le filtre soit fait côté BdD et ensuite importer, je vais voir si je peux le faire de cette façon sur les fichiers réduits que tu as donnés
Ok, si je comprends bien, l'idée est - dans le script - d'utiliser les critères afin d'appliquer le filtre à la BdD avant import ?
oui, mais pas encore testé faute de temps, à moins que tu aies pu le faire de ton côté
edit1 : premier essai en balayant ensuite les données avec isRowHiddenByFilter ... il faut oublier, c'est beaucoup trop long
edit2: essai avec une feuille navette provisoire dans le fichier source
function BDDimport() {
// critères
var ss = SpreadsheetApp.getActiveSpreadsheet()
var param = ss.getSheetByName('Paramètres')
var type = param.getRange('B1').getValue()
var pays = param.getRange('B2').getValue()
// source
var sheet = SpreadsheetApp.openById('1AJYCMTMEZbohwj_OP_OBMG-iuCo964v383URoW-Z8R0').getSheetByName('Feuille 1');
var newSht = SpreadsheetApp.openById('1AJYCMTMEZbohwj_OP_OBMG-iuCo964v383URoW-Z8R0').getSheetByName('Feuille 2');
var filter = sheet.getDataRange().getFilter();
if (filter == null) {
filter = sheet.getDataRange().createFilter();
}
var criteria = SpreadsheetApp.newFilterCriteria().whenTextEqualTo(type).build();
filter.setColumnFilterCriteria(2, criteria);
var criteria = SpreadsheetApp.newFilterCriteria().whenTextEqualTo(pays).build();
filter.setColumnFilterCriteria(4, criteria);
// recopie sur feuille navette
newSht.clear()
sheet.getRange(1, 1, sheet.getLastRow(), sheet.getLastColumn()).copyTo(newSht.getRange(1, 1))
data = newSht.getDataRange().getValues()
// recopie dans nouveau fichier
ss.getSheetByName('Stock Fruits').clear()
ss.getSheetByName('Stock Fruits').getRange(1, 1, data.length, data[0].length).setValues(data)
}Bonjour Steelson,
J'ai bien tenté en effet de trouver en cherchant sur l'outil développeur de Google.
Mais en voyant ton résultat, je dois avouer que je n'y serais jamais arrivé seul... et la nuit je dors
Merci beaucoup, cela fonctionne parfaitement. Il a fallu 6min pour filtrer et importer 20k lignes. J'espère juste que cela ne bloquera pas lorsqu'il y aura des plages plus grandes à importer.
Mais en voyant ton résultat, je dois avouer que je n'y serais jamais arrivé seul... et la nuit je dors
Merci beaucoup, cela fonctionne parfaitement. Il a fallu 6min pour filtrer et importer 20k lignes. J'espère juste que cela ne bloquera pas lorsqu'il y aura des plages plus grandes à importer.
wahoo, merci pour ce résultat c'était inespéré (attention, 6 mn c'est la limite max pour les scripts, resterait une autre solution à tester si besoin en faisant juste le filtre sur Feuille1 et ensuite en allant chercher les données via le "endpoint" en csv par exemple
je vais tenter, ce qui te donnera une autre possibilité ... quoique ce sera plus long de décoder le csv !
(attention, 6 mn c'est la limite max pour les scripts
C'est étrange cette limite des 6mins dont j'avais eu connaissance sur "Quotas for Google Services". J'ai pourtant des scripts qui tournent parfois jusqu'à 10min sans difficulté.