Comment utiliser des formules avec des réponses de formulaire Google dans des feuilles

Lorsque des personnes soumettent votre formulaire Google, une nouvelle ligne est insérée dans la feuille Google qui stocke les réponses du formulaire. Cette ligne de feuille de calcul contient une colonne Horodatage, la date réelle à laquelle le formulaire a été soumis, et les autres colonnes de la feuille contiennent toutes les réponses de l'utilisateur, une par colonne.

Vous pouvez étendre la feuille Google Forms pour inclure également des champs de formule et les valeurs des cellules sont automatiquement calculées chaque fois qu'une nouvelle ligne est ajoutée à la feuille par Google Form. Par exemple:

• Vous pouvez avoir une formule de numérotation automatique qui attribue un ID auto-incrémenté mais séquentiel à chaque réponse de formulaire. Cela peut être utile lorsque vous utilisez Google Forms pour la facturation.
• Pour les formulaires de commande client, une formule peut être écrite dans Google Sheets pour calculer le montant total en fonction de la sélection d'articles, du pays (les taux de taxe sont différents) et de la quantité sélectionnée dans le formulaire.
• Pour les formulaires de réservation d'hôtel, une formule permet de calculer automatiquement le loyer de la chambre en fonction des dates d'arrivée et de départ renseignées par le client dans le Google Form.
• Pour les quiz, un enseignant peut calculer automatiquement le score final de l'élève en faisant correspondre les valeurs saisies dans le formulaire avec les réponses réelles et en attribuant des scores.
• Si un utilisateur a soumis plusieurs formulaires, une formule peut vous aider à déterminer le nombre total d'entrées effectuées par un utilisateur dès qu'il soumet un formulaire.

Formules Google Sheets pour Google Forms

Dans ce guide étape par étape, vous apprendrez à ajouter des formules à Google Sheets associées à Google Forms. Les valeurs de cellule correspondantes dans les lignes de réponse seront automatiquement calculées lorsqu'une nouvelle réponse est soumise.

Pour mieux comprendre ce que nous essayons d'accomplir, ouvrez ce formulaire Google et soumettez une réponse. Ensuite, ouvrez cette feuille Google et vous trouverez votre réponse dans une nouvelle ligne. Les colonnes FK sont remplies automatiquement à l'aide de formules.

Tous les exemples ci-dessous utiliseront la fonction ArrayFormula de Google Sheets, bien que certains de ces exemples puissent également être écrits à l'aide de la fonction FILTER .

Numérotation automatique des réponses au formulaire avec un identifiant unique

Ouvrez la feuille Google qui stocke les réponses au formulaire, accédez à la première colonne vide et copiez-collez la formule suivante dans la ligne n ° 1 de la colonne vide.

 =ArrayFormula( IFS( ROW(A:A)=1, "Invoice ID", LEN(A:A)=0, IFERROR(1/0), LEN(A:A)>0, LEFT(CONCAT(REPT("0",5), ROW(A:A) -1),6) ) )

La fonction ROW() renvoie le numéro de ligne de la ligne de réponse actuelle. Il renvoie 1 pour la première ligne de la colonne de facture et nous définissons donc le titre de la colonne dans la première ligne. Pour les lignes suivantes, si la première colonne de la ligne (généralement Timestamp) n'est pas vide, l'ID de facture est généré automatiquement.

Les identifiants seront comme 00001 , 00002 et ainsi de suite. Il vous suffit de placer la formule dans la première ligne de la colonne et elle remplit automatiquement toutes les autres lignes de la colonne.

La fonction IFERROR renvoie le premier argument s'il ne s'agit pas d'une valeur d'erreur, sinon renvoie le second argument s'il est présent, ou un blanc si le second argument est absent. Donc, dans ce cas, 1/0 est une erreur et renvoie donc toujours une valeur vide.

Formule de calcul de date pour Google Forms

Votre formulaire Google comporte deux champs de date : la date d'arrivée et la date de départ. Les tarifs de l'hôtel peuvent varier à chaque saison, vous avez donc un tableau séparé dans la feuille Google qui maintient le loyer de la chambre par mois.

La colonne C de la feuille Google contient les réponses pour la date d'arrivée tandis que la colonne D stocke les dates de départ.

 =ArrayFormula( IF(ROW(A:A) = 1, "Room Rent", IF(NOT(ISBLANK(A:A)), (D:D - C:C) * VLOOKUP(MONTH(D:D), 'Room Rates'!$B$2:$C$13,2, TRUE), "" ) ) )

La formule utilise VLOOKUP pour obtenir les tarifs des chambres pour la date de voyage spécifiée dans la réponse du formulaire, puis calcule le loyer de la chambre en multipliant le loyer de la chambre par la durée du séjour.

La même formule peut également être écrite avec IFS au lieu de VLOOKUP

 =ArrayFormula( IF(ROW(A:A) = 1, "Room Rent", IFS(ISBLANK(C:C), "", MONTH(C:C) < 2, 299, MONTH(C:C) < 5, 499, MONTH(C:C) < 9, 699, TRUE, 199 ) ) )

Calculer le montant de la taxe en fonction de la valeur de la facture

Dans cette approche, nous utiliserons la fonction FILTER et cela pourrait conduire à une formule moins compliquée que l'utilisation de la fonction IF . L'inconvénient est que vous devez écrire le titre de la colonne dans la ligne 1 et coller les formules dans la ligne 2 (une réponse de formulaire doit donc exister pour que la formule fonctionne).

 =ArrayFormula(FILTER(E2:E, E2:E<>"")*1.35)

Ici, nous appliquons une taxe de 35 % à la valeur de la facture et cette formule doit être ajoutée à la ligne 2 de la colonne intitulée « Montant de la taxe », comme indiqué dans la capture d'écran.

Attribuer des scores de quiz dans Google Forms

Quelle ville est connue comme la grosse pomme ? Il s'agit d'une question à réponse courte dans Google Forms afin que les étudiants puissent donner des réponses comme New York, New York City, NYC et ils seront toujours corrects. L'enseignant doit attribuer 10 points à la bonne réponse.

 =ArrayFormula( IF(ROW(A:A) = 1, "Quiz Score", IFS( ISBLANK(A:A), "", REGEXMATCH(LOWER({B:B}), "new\s?york"), 10, {B:B} = "NYC", 10, TRUE, 0 ) ) )

Dans cette formule, nous utilisons la fonction IFS qui ressemble à une instruction IF THEN en programmation. Nous utilisons REGEXMATCH pour faire correspondre des valeurs comme New York, New York, newyork en une seule fois en utilisant des expressions régulières.

La fonction IFS renvoie un NA si aucune des conditions n'est vraie, nous ajoutons donc une vérification TRUE à la fin qui sera toujours évaluée à true si aucune des conditions précédentes ne correspond et renvoie 0 .

Extraire le prénom du répondant du formulaire

Si vous avez un champ de formulaire qui demande à l'utilisateur de saisir son nom complet, vous pouvez utiliser la fonction Google Sheets pour extraire le prénom du nom complet et utiliser ce champ pour envoyer des e-mails personnalisés.

 =ArrayFormula( IFS( ROW(A:A)=1, "First Name", LEN(A:A)=0, IFERROR(1/0), LEN(A:A)>0, PROPER(REGEXEXTRACT(B:B, "^[^\s+]+")) ) )

Nous avons utilisé la méthode RegexExtract ici pour récupérer la chaîne avant le premier espace dans le champ de nom. La fonction PROPER mettra en majuscule la première lettre du nom au cas où l'utilisateur aurait saisi son nom en minuscules.

Trouver les soumissions de formulaires Google en double

Si votre formulaire Google contient des adresses e-mail de collecte, vous pouvez utiliser ce champ pour détecter rapidement les réponses qui ont été soumises plusieurs fois par le même utilisateur.

 =ArrayFormula( IFS( ROW(A:A)=1, "Is Duplicate Entry?", LEN(A:A)=0, IFERROR(1/0), LEN(A:A)>0, IF(COUNTIF(B:B, B:B) > 1, "YES", "") ) )

En supposant que la colonne B stocke les adresses e-mail des répondants du formulaire, nous pouvons utiliser la fonction COUNTIF pour marquer rapidement les entrées en double dans notre feuille de calcul des réponses. Vous pouvez également utiliser la mise en forme conditionnelle dans Sheets pour mettre en surbrillance les lignes qui sont d'éventuelles entrées en double.

Réponses aux formulaires par e-mail avec valeurs de remplissage automatique

Vous pouvez utiliser Document Studio pour envoyer automatiquement un e-mail aux répondants du formulaire. L'e-mail est envoyé une fois que les valeurs du formulaire ont été remplies automatiquement par Google Sheet. La réponse du formulaire d'origine et les valeurs calculées peuvent également être incluses dans le document PDF généré.