Accélérer traitement APP SCRIPT Google Sheet
Bonjour, j'ai récupéré un script que j'ai modifié pour mon utilisation. Formulaire de saisie pour alimenter une base sur une autre feuille. Le traitement est très long. Je pense qu'il y a trop de GetRange et SetValue. Est-il possible d'améliorer ? Merci
// Fonction pour soumettre les données à la feuille de base de données
function submitData() {
var myGooglSheet= SpreadsheetApp.getActiveSpreadsheet(); //déclarer une variable et définir avec la feuille google active
var shUserForm= myGooglSheet.getSheetByName("Formulaire"); //déclarer une variable et la définir avec la feuille de calcul du formulaire utilisateur
var datasheet = myGooglSheet.getSheetByName("Database"); //déclarer une variable et la définir avec la feuille de calcul Base de données
//pour créer l'instance de l'environnement d'interface utilisateur pour utiliser les fonctionnalités de la boîte de message
var ui = SpreadsheetApp.getUi();
// Affiche une boîte de dialogue avec un titre, un message et les boutons "Oui" et "Non". L'utilisateur peut également
//fermez la boîte de dialogue en cliquant sur le bouton de fermeture dans sa barre de titre.
var response = ui.alert("Enregistrement", 'Voulez-vous enregistrer la (Nouvelle Ligne) ',ui.ButtonSet.YES_NO);
// Vérification de la réponse de l'utilisateur et effacement du formulaire si l'utilisateur sélectionne Oui
if (response == ui.Button.NO)
{return;//exit from this function
}
//Validation de l'entrée. Si la validation est vraie, procédez au transfert des données vers la feuille de base de données
if (validateEntry()==true) {
var blankRow=datasheet.getLastRow()+1; //identify the next blank row
datasheet.getRange(blankRow, 1).setValue(shUserForm.getRange("B4").getValue());
datasheet.getRange(blankRow, 2).setValue(shUserForm.getRange("B5").getValue());
datasheet.getRange(blankRow, 3).setValue(shUserForm.getRange("B6").getValue());
datasheet.getRange(blankRow, 4).setValue(shUserForm.getRange("B7").getValue());
datasheet.getRange(blankRow, 5).setValue(shUserForm.getRange("B8").getValue());
datasheet.getRange(blankRow, 6).setValue(shUserForm.getRange("B9").getValue());
datasheet.getRange(blankRow, 7).setValue(shUserForm.getRange("B10").getValue());
datasheet.getRange(blankRow, 8).setValue(shUserForm.getRange("B11").getValue());
datasheet.getRange(blankRow, 9).setValue(shUserForm.getRange("B12").getValue());
datasheet.getRange(blankRow, 10).setValue(shUserForm.getRange("B13").getValue());
datasheet.getRange(blankRow, 11).setValue(shUserForm.getRange("B14").getValue());
datasheet.getRange(blankRow, 12).setValue(shUserForm.getRange("B15").getValue());
datasheet.getRange(blankRow, 13).setValue(shUserForm.getRange("B16").getValue());
datasheet.getRange(blankRow, 14).setValue(shUserForm.getRange("B17").getValue());
datasheet.getRange(blankRow, 15).setValue(shUserForm.getRange("D4").getValue());
datasheet.getRange(blankRow, 16).setValue(shUserForm.getRange("D5").getValue());
datasheet.getRange(blankRow, 17).setValue(shUserForm.getRange("D6").getValue());
datasheet.getRange(blankRow, 18).setValue(shUserForm.getRange("D7").getValue());
datasheet.getRange(blankRow, 19).setValue(shUserForm.getRange("D8").getValue());
datasheet.getRange(blankRow, 20).setValue(shUserForm.getRange("D9").getValue());
datasheet.getRange(blankRow, 22).setValue(shUserForm.getRange("D11").getValue());
datasheet.getRange(blankRow, 23).setValue(shUserForm.getRange("D12").getValue());
datasheet.getRange(blankRow, 24).setValue(shUserForm.getRange("D13").getValue());
datasheet.getRange(blankRow, 25).setValue(shUserForm.getRange("D14").getValue());
datasheet.getRange(blankRow, 26).setValue(shUserForm.getRange("D15").getValue());
datasheet.getRange(blankRow, 27).setValue(shUserForm.getRange("D16").getValue());
datasheet.getRange(blankRow, 28).setValue(shUserForm.getRange("D19").getValue());
datasheet.getRange(blankRow, 29).setValue(shUserForm.getRange("D21").getValue());
datasheet.getRange(blankRow, 30).setValue(shUserForm.getRange("E5").getValue());
datasheet.getRange(blankRow, 31).setValue(shUserForm.getRange("F5").getValue());
datasheet.getRange(blankRow, 32).setValue(shUserForm.getRange("G5").getValue());
datasheet.getRange(blankRow, 33).setValue(shUserForm.getRange("H5").getValue());
datasheet.getRange(blankRow, 34).setValue(shUserForm.getRange("E11").getValue());
datasheet.getRange(blankRow, 36).setValue(shUserForm.getRange("G11").getValue());
datasheet.getRange(blankRow, 37).setValue(shUserForm.getRange("E15").getValue());
datasheet.getRange(blankRow, 38).setValue(shUserForm.getRange("F15").getValue());
datasheet.getRange(blankRow, 39).setValue(shUserForm.getRange("H15").getValue());
datasheet.getRange(blankRow, 40).setValue(shUserForm.getRange("E20").getValue())
datasheet.getRange(blankRow, 41).setValue(shUserForm.getRange("F20").getValue());
// fonction de date pour mettre à jour la date et l'heure actuelles telles que soumises sur
datasheet.getRange(blankRow, 42).setValue(new Date()).setNumberFormat('yyyy-mm-dd h:mm'); //Submitted On
//obtenir l'adresse e-mail de la personne qui exécute le script et mettre à jour comme Soumis par
datasheet.getRange(blankRow, 43).setValue(Session.getActiveUser().getEmail()); //Submitted By
ui.alert(' "Nouvelle Enregistrement - Ligne ' + shUserForm.getRange("B4").getValue() +' "');
//Effacer les données du formulaire de saisie de données
shUserForm.getRange("B5").clearContent();
shUserForm.getRange("B6").clearContent();
shUserForm.getRange("B7").clearContent();
shUserForm.getRange("B8").clearContent();
shUserForm.getRange("B9").clearContent();
shUserForm.getRange("B10").clearContent();
shUserForm.getRange("B11").clearContent();
shUserForm.getRange("B12").clearContent();
shUserForm.getRange("B13").clearContent();
shUserForm.getRange("B14").clearContent();
shUserForm.getRange("B15").clearContent();
shUserForm.getRange("B16").clearContent();
shUserForm.getRange("B17").clearContent();
shUserForm.getRange("D4").clearContent();
shUserForm.getRange("D5").clearContent();
shUserForm.getRange("D6").clearContent();
shUserForm.getRange("D7").clearContent();
shUserForm.getRange("D8").clearContent();
shUserForm.getRange("D9").clearContent();
shUserForm.getRange("D10").clearContent();
shUserForm.getRange("D11").clearContent();
shUserForm.getRange("D12").clearContent();
shUserForm.getRange("D13").clearContent();
shUserForm.getRange("D14").clearContent();
shUserForm.getRange("D15").clearContent();
shUserForm.getRange("D16").clearContent();
shUserForm.getRange("D19").clearContent();
shUserForm.getRange("D21").clearContent();
}
}Bonjour,
Désolé, je n'avais pas vu passer ce topic.
Oui en effet, ce qui ralentit ce sont bien les getRange et setRange, il faut travailler avec des arrays et capter avant la boucle ou mettre à jour après la boucle globalement avec getValues et setValues (au pluriel)
Sauf que, dans ton cas, les valeurs ne sont pas contiguës. Il faut alors travailler avec batchupdate https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/batchUpdate et batchget https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets.values/batchGet
Il faut activer le service Sheets API (clique à gauche sur Services dans l'éditeur de script)
function reportValues() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const [src,dst] = ["Formulaire","Database"];
const rng = ['B4','B5','B6','B7','B8','B9','B10','B11','B12','B13','B14','B15','B16','B17','D4','D5','D6','D7','D8','D9','D11','D12','D13','D14','D15','D16','D19','D21','E5','F5','G5','H5','E11','G11','E15','F15','H15','E20','F20'].map(e => `'${src}'!${e}`);
const values = Sheets.Spreadsheets.Values.batchGet(ss.getId(), { ranges: rng })
var data = []
values.valueRanges.forEach(e => data.push(e.values ? e.values.flat().toString() : ""))
ss.getSheetByName(dst).appendRow(data)
}Pour l'effacement, tu peux utiliser
ss.getRangeList(['B4:B17','D4:D16','D19','D21']).ClearContent()Bonjour.
Merci super cela fonctionne à merveille. Dans ce formulaire j'ai donc la validation de données que tu viens de traiter pour m'aider à accélérer le traitement. J'ai la recherche. Celle-ci fonctionne assez rapidement je pense que ma function n'est pas top mais c'est tolérable.
Par contre j'ai une fonction permettant de modifier un enregistrement celle-ci ne fonctionne pas du tout. J'ai pas de message d'erreur mais quand je lance la function mon formulaire ne se complète pas mais ma database s'efface. Puissè-je mettre ici mon script ou faut il ouvrir un nouveau post ? Merci encore
c'est dans la continuité, donc mets ton script, mais si tu peux aussi accompagner avec un fichier simplifié ce serait parfait
quelle est la "clé" qui permet de retrouver la ligne dans la base de données ?
https://docs.google.com/spreadsheets/d/1mUt3J0QCnjF8Mtd9hJtxQX3BidJFalkYaY8dF_odKek/edit?usp=sharing
Bonsoir une extrait du script pour la recherche.
function searchRecord() {
var myGooglSheet= SpreadsheetApp.getActiveSpreadsheet(); //declare a variable and set with active google sheet
var shUserForm= myGooglSheet.getSheetByName("Formulaire"); //delcare a variable and set with the User Form worksheet
var datasheet = myGooglSheet.getSheetByName("Database"); ////delcare a variable and set with the Database worksheet
var str = shUserForm.getRange("C1").getValues();
var values = datasheet.getDataRange().getValues(); //getting the entire values from the used range and assigning it to values variable
var valuesFound=false; //variable to store boolean value
for (var i=0; i<values.length; i++)
{
var rowValue=values[i]; //declaraing a variable and storing the value
//checking the first value of the record is equal to search item
if (rowValue[0] == str) {
shUserForm.getRange("B4").setValue(rowValue[0]);
shUserForm.getRange("B5").setValue(rowValue[1]);
shUserForm.getRange("B6").setValue(rowValue[2]);
shUserForm.getRange("B7").setValue(rowValue[3]);
shUserForm.getRange("B8").setValue(rowValue[4]);j'ai essayé
shUserForm.getRange("B4:B8").setValue(rowValue['0','1','2','3','4']);
Ne fonctionne pas
extrait du script pour modifier un enregistrement ( il fonctionne mais très long )
<pre>//Function modifier un enregistrement
function editRecord() {
var myGooglSheet= SpreadsheetApp.getActiveSpreadsheet(); //declare a variable and set with active google sheet
var shUserForm= myGooglSheet.getSheetByName("Formulaire"); //delcare a variable and set with the User Form worksheet
var datasheet = myGooglSheet.getSheetByName("Database"); ////delcare a variable and set with the Database worksheet
//to create the instance of the user-interface environment to use the messagebox features
var ui = SpreadsheetApp.getUi();
// Display a dialog box with a title, message, and "Yes" and "No" buttons. The user can also
// close the dialog by clicking the close button in its title bar.
var response = ui.alert("Alerte", 'Voulez-Vous Modifier la ligne ?',ui.ButtonSet.YES_NO);
// Checking the user response and proceed with clearing the form if user selects Yes
if (response == ui.Button.NO)
{return;//exit from this function
}
var str = shUserForm.getRange("C1").getValues();
var values = datasheet.getDataRange().getValues(); //getting the entire values from the used range and assigning it to values variable
var valuesFound=false; //variable to store boolean value to validate whether values found or not
for (var i=0; i<values.length; i++)
{
var rowValue=values[i]; //declaraing a variable and storing the value
//checking the first value of the record is equal to search item
if (rowValue[0] == str) {
var iRow = i+1; //identify the row number
datasheet.getRange(iRow, 1).setValue(shUserForm.getRange("B4").getValue());
datasheet.getRange(iRow, 2).setValue(shUserForm.getRange("B5").getValue());
datasheet.getRange(iRow, 3).setValue(shUserForm.getRange("B6").getValue());
datasheet.getRange(iRow, 4).setValue(shUserForm.getRange("B7").getValue());
datasheet.getRange(iRow, 5).setValue(shUserForm.getRange("B8").getValue());
datasheet.getRange(iRow, 6).setValue(shUserForm.getRange("B9").getValue());
datasheet.getRange(iRow, 7).setValue(shUserForm.getRange("B10").getValue());</pre>
<p></p>on ouvre quel projet ? il faudrait en faire le ménage et n'en conserver qu'un seul
J'ai un soucis de lecture de ton fichier ! jamais vu cette erreur.
Pour retrouver la ligne, commence par faire ceci
repris ci-dessouset dis moi si ce sont bien les données recherchées
Bonjour c'est le projet TVX23
pour reporter les données
complété ci-dessousSuper la fonction recherche fonctionne et super rapide. Deux petits soucis.
Mon alerte pas d'enregistrement trouvé ne fonctionne plus. je ne trouve pas la solution.
if(valuesFound==false){ //to create the instance of the user-interface environment to use the messagebox features var ui = SpreadsheetApp.getUi(); ui.alert("Pas d'enregistrement trouvé !!!!");
Autre petit soucis j'ai les dates qui sont renvoyées dans le formulaire sous cette forme. ( Mon Jul 25 00:00:00 GMT+02:00 2022) J'ai beau chercher et je ne trouve pas de solution. Merci mille fois de Votre aide.
Reste la fonction modifier une ligne .
function editRecord() {
function reporterValeurs() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const [dst, src] = ["Formulaire", "Database"];
const rng = ['B4', 'B5', 'B6', 'B7', 'B8', 'B9', 'B10', 'B11', 'B12', 'B13', 'B14', 'B15', 'B16', 'B17', 'D4', 'D5', 'D6', 'D7', 'D8', 'D9', 'D10', 'D11', 'D12', 'D13', 'D14', 'D15', 'D16', 'D19', 'D21', 'E5', 'F5', 'G5', 'H5', 'E11', 'F11', 'G11', 'E15', 'F15', 'H15', 'E20', 'F20']
const str = ss.getSheetByName(dst).getRange("C1").getValues();
const values = ss.getSheetByName(src).getDataRange().getDisplayValues().filter(r => r[0] == str).flat()
if (values.length == 0) {
Browser.msgBox('pas trouvé !')
} else {
const ranges = []
rng.forEach(c => { ranges.push(`'${dst}'!${c}`) })
const data = ranges.map((e, i) => ({ range: e, values: [[values[i]]] }));
Sheets.Spreadsheets.Values.batchUpdate({ data, valueInputOption: "USER_ENTERED" }, ss.getId());
}
}Merci mille fois. j'abuse de votre temps. Si vous pouviez regarder la function editRecord() { Elle fonctionne mais comme un TER si vous pouviez passer au TGV.
Merci
Ps : J'ai fait du ménage dans les projets !!!!!!
je m'y attendais
il faut surtout que les identifiants de ligne soient uniques
pour la mise à jour
function maj() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const [src, dst] = ["Formulaire", "Database"];
const rng = ['B4', 'B5', 'B6', 'B7', 'B8', 'B9', 'B10', 'B11', 'B12', 'B13', 'B14', 'B15', 'B16', 'B17', 'D4', 'D5', 'D6', 'D7', 'D8', 'D9', 'D11', 'D12', 'D13', 'D14', 'D15', 'D16', 'D19', 'D21', 'E5', 'F5', 'G5', 'H5', 'E11', 'G11', 'E15', 'F15', 'H15', 'E20', 'F20'].map(e => `'${src}'!${e}`);
const str = ss.getSheetByName(src).getRange("B4").getDisplayValue();
const ids = ss.getSheetByName(dst).getRange('A:A').getDisplayValues().flat()
const ligne = +ids.indexOf(str) + 1
if (ligne == 0) {
Browser.msgBox('pas trouvé !')
} else {
const values = Sheets.Spreadsheets.Values.batchGet(ss.getId(), { ranges: rng })
var data = []
values.valueRanges.forEach(e => data.push(e.values ? e.values.flat().toString() : ""))
ss.getSheetByName(dst).getRange(ligne,1,1,data.length).setValues([data])
}
}Bonjour désolé. J'ai entré et apporté quelques modification au dernier script. Fonctionne super merci encore. je voudrais apporter quelque modifications. A savoir : L' onglet formulaire saisi renvoie sur l'onglet database au fil des colonnes de database. Est-il possible de sélectionner une colonne précise de réception dans databas. Encore mille merci.
Est-il possible de sélectionner une colonne précise de réception dans databas. Encore mille merci.
dans le dernier script, tu peux modifier le second paramètre ici
ss.getSheetByName(dst).getRange(ligne,___ici_n°_de_colonne_____,1,data.length).setValues([data])à condition que les données commencent sans interruption à partir de cette colonne
Bonjour, j'ai essayé ne fonctionne pas.
J'ai remplacé dans la function submitData() {
ss.getSheetByName(dst).appendRow(data)
par
ss.getSheetByName(dst).getRange(ligne,[2],[1],data.length).setValues([data]).
L'idée: Est-ce possible par exemple que la saisie du formulaire enregistre les données dans des colonnes spécifiées
Exemple
la saisie dans b4 du formulaire soit enregistrée en colonne 1 de database
la saisie dans b5 du formulaire soit enregistrée en colonne 3 de database
la saisie dans b6 du formulaire soit enregistrée en colonne 6 de database
etc......
Merci
ss.getSheetByName(dst).getRange(ligne,[2],[1],data.length).setValues([data]).
ne pas mettre 2 et 1 entre crochets, et sans point final
L'idée: Est-ce possible par exemple que la saisie du formulaire enregistre les données dans des colonnes spécifiées
Exemple
la saisie dans b4 du formulaire soit enregistrée en colonne 1 de database
la saisie dans b5 du formulaire soit enregistrée en colonne 3 de database
la saisie dans b6 du formulaire soit enregistrée en colonne 6 de database
etc......
Merci
ce n'est généralement pas ce que l'on fait ... mais c'est possible
il faut modifier le script en conséquence, cela va me prendre un peu d etemps