Hey I don’t know how to code. I don’t know java script. And I started doing it using chatGpt. I was doing things with sheet google. But it was limited. Sheet doesnt allow to use pop without create web app. So I tried to create one. Sorry for my english i’m french.
I have a file named “Fichier Accord” which contains my database with columns A, B, C, D, E, F, representing the following information: Last Name, First Name, Phone Number, Address, Date, and Need. This database is located in the “Parameter” sheet.
I have another file called “Base de donnée” where I have used a VLOOKUP(importrange) formula in the “Exercise 1” sheet:
=VLOOKUP(A5, IMPORTRANGE(“Drive_link ‘Fichier Accord'”, “Parameter!A1:A25”), IMPORTRANGE(“Drive_link ‘Fichier Accord'”, “Parameter!A1:F25”), 2)
This formula allows me to display the desired data from the Parameter sheet when I modify cell A5. In other words, when I search for a name (in column A), it displays all the information on one row (row 5).
Now, I would like that when I modify cell A6 in the “Exercise 1” sheet, it shows pop-up windows:
- If the user exists, it displays a window with their information.
- If the user doesn’t exist, it shows a pop-up window allowing me to enter the user’s information: Last Name, First Name, Phone Number, Date, Address, and Need. It then saves this information in the “Fichier Accord” file, in the “Parameter” sheet, just below the last entered user. The pop-up window closes after successfully saving the user, displaying a message saying “User successfully saved.” When the user is found, there is an “OK” button to close the pop-up window.
Please note that the formulas and references provided are based on the information you provided in French. If you need further assistance or clarifications, feel free to ask.
I write my htlm code and my script code. I think I modify the code many times. I tried to know what the function are for. But i think I need someone to explain me what I did wrong so that I can learn from something I like.
Here my code.
function onEdit(e) {
if (e && e.range && e.range.getA1Notation() === "Exercice 1'!A6") {
var activeCell = e.range;
var searchValue = activeCell.getValue();
Logger.log("Search value: " + searchValue);
showSearchUserDialog(searchValue);
}
}
function showSearchUserDialog(searchValue) {
var htmlOutput = HtmlService.createHtmlOutputFromFile('recherche_utilisateur');
SpreadsheetApp.getUi().showModalDialog(htmlOutput, "Recherche utilisateur");
var searchUserScript = "onSearchUser('" + searchValue + "');";
var scriptTag = "<script>" + searchUserScript + "</script>";
htmlOutput.append(scriptTag);
}
function onSearchUser(searchValue) {
var dataFile = SpreadsheetApp.openById("ID_Fichier Accord");
var dataSheet = dataFile.getSheetByName("Parameter");
var searchRange = dataSheet.getRange("A:A");
var searchResult = searchRange.createTextFinder(searchValue).findAll();
Logger.log("Search result length: " + searchResult.length);
if (searchResult.length > 0) {
var rowData = searchResult[0].getRow();
var infoA = dataSheet.getRange("A" + rowData).getValue();
var infoB = dataSheet.getRange("B" + rowData).getValue();
var infoC = dataSheet.getRange("C" + rowData).getValue();
var infoD = dataSheet.getRange("D" + rowData).getValue();
var infoE = dataSheet.getRange("E" + rowData).getValue();
var infoF = dataSheet.getRange("F" + rowData).getValue();
var message = "Nom: " + infoA + "n" +
"Prénom: " + infoB + "n" +
"Numéro de téléphone: " + infoC + "n" +
"Adresse: " + infoD + "n" +
"Date: " + infoE + "n" +
"Besoin: " + infoF;
SpreadsheetApp.getUi().alert("Informations Utilisateur", message, SpreadsheetApp.getUi().ButtonSet.OK);
var htmlOutput = HtmlService.createHtmlOutput('<h2>Informations Utilisateur</h2><p>Nom: ' + infoA + '</p><p>Prénom: ' + infoB + '</p><p>Numéro de téléphone: ' + infoC + '</p><p>Adresse: ' + infoD + '</p><p>Date: ' + infoE + '</p><p>Besoin: ' + infoF + '</p>');
SpreadsheetApp.getUi().showModalDialog(htmlOutput, "Informations Utilisateur");
} else {
var htmlOutput = HtmlService.createHtmlOutputFromFile("form.html");
SpreadsheetApp.getUi().showModalDialog(htmlOutput, "Nouvel utilisateur");
}
}
function saveUserInfo(data) {
var file = SpreadsheetApp.openById("Id_Fichier Accord");
var parameterSheet = file.getSheetByName("Parameter");
var lastRow = parameterSheet.getLastRow() + 1;
parameterSheet.getRange("A" + lastRow).setValue(data.nom);
parameterSheet.getRange("B" + lastRow).setValue(data.prenom);
parameterSheet.getRange("C" + lastRow).setValue(data.telephone);
parameterSheet.getRange("D" + lastRow).setValue(data.adresse);
parameterSheet.getRange("E" + lastRow).setValue(data.date);
parameterSheet.getRange("F" + lastRow).setValue(data.besoin);
Logger.log("User information saved: " + JSON.stringify(data));
var template = HtmlService.createTemplateFromFile('informations_utilisateur');
template.infoA = data.nom;
template.infoB = data.prenom;
template.infoC = data.telephone;
template.infoD = data.adresse;
template.infoE = data.date;
template.infoF = data.besoin;
var htmlOutput = template.evaluate();
SpreadsheetApp.getUi().showModalDialog(htmlOutput, "Informations utilisateur enregistrées avec succès!");
SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange("A6").clearContent(); // Réinitialiser la cellule de recherche
}
function doGet() {
var htmlOutput = HtmlService.createHtmlOutputFromFile('recherche_utilisateur');
return htmlOutput;
}
<!-- informations_utilisateur.html -->
<!DOCTYPE html>
<html>
<head>
<base target="_top">
<style>
/* Ajoutez ici votre style CSS personnalisé pour la fenêtre modale */
</style>
</head>
<body>
<h2>Informations Utilisateur</h2>
<p>Nom: <?!= infoA ?></p>
<p>Prénom: <?!= infoB ?></p>
<p>Numéro de téléphone: <?!= infoC ?></p>
<p>Adresse: <?!= infoD ?></p>
<p>Date: <?!= infoE ?></p>
<p>Besoin: <?!= infoF ?></p>
</body>
</html>
<!-- nouvel_utilisateur.html -->
<!DOCTYPE html>
<html>
<head>
<base target="_top">
<style>
/* Ajoutez ici votre style CSS personnalisé pour la fenêtre modale */
</style>
</head>
<body>
<h2>Nouvel utilisateur</h2>
<form id="userForm">
<label for="nom">Nom:</label>
<input type="text" id="nom" name="nom" required><br>
<label for="prenom">Prénom:</label>
<input type="text" id="prenom" name="prenom" required><br>
<label for="telephone">Numéro de téléphone:</label>
<input type="text" id="telephone" name="telephone" required><br>
<label for="adresse">Adresse:</label>
<input type="text" id="adresse" name="adresse" required><br>
<label for="date">Date:</label>
<input type="text" id="date" name="date" required><br>
<label for="besoin">Besoin:</label>
<input type="text" id="besoin" name="besoin" required><br>
<input type="button" value="Enregistrer" onclick="saveUserInfo()">
</form>
<script>
function saveUserInfo() {
var nom = document.getElementById('nom').value;
var prenom = document.getElementById('prenom').value;
var telephone = document.getElementById('telephone').value;
var adresse = document.getElementById('adresse').value;
var date = document.getElementById('date').value;
var besoin = document.getElementById('besoin').value;
google.script.run.saveUserInfo({ nom: nom, prenom: prenom, telephone: telephone, adresse: adresse, date: date, besoin: besoin });
}
</script>
</body>
</html>
<!-- informations_utilisateur.html -->
<!DOCTYPE html>
<html>
<head>
<base target="_top">
<style>
/* Ajoutez ici votre style CSS personnalisé pour la fenêtre modale */
</style>
</head>
<body>
<h2>Informations Utilisateur</h2>
<p>Nom: <?!= infoA ?></p>
<p>Prénom: <?!= infoB ?></p>
<p>Numéro de téléphone: <?!= infoC ?></p>
<p>Adresse: <?!= infoD ?></p>
<p>Date: <?!= infoE ?></p>
<p>Besoin: <?!= infoF ?></p>
</body>
</html>
