Al een hele tijd werk ik met Microsoft Forms. Het werkt eenvoudig en je kunt makkelijk data ophalen die je nodig hebt. Nu liep ik tegen het volgende aan:
“Hoe voeg ik antwoorden van een Forms formulier automatisch toe aan een bestaand Excel document?”
Met het stellen van deze vraag heb ik onderstaande procedures gedefinieerd, waardoor ik kon beginnen met het maken van een Power Automate flow.
Doel:
- Antwoorden van ingevulde formulieren moeten in een bestaande Excel bestand verwerkt worden
- Het nieuw gemaakte Excel bestand moet worden opgeslagen onder een bepaalde naam
- Mijn teamleden moeten een melding krijgen wanneer er een nieuw bestand is aangemaakt
Om het bovenstaande te realiseren heb ik onderstaande stappen doorgelopen. In dit blog ligt ik toe hoe dit geconfigureerd is.
- Maak een formulier
- Maak een valide Excel bestand om data in op te kunnen slaan
- Maak een Power Automate flow
- Genereer een melding
✅ Maak een formulier
In dit voorbeeld wil ik graag dat de geel gearceerde cellen worden gevuld vanuit het formulier.
👉 Ga naar https://forms.office.com
Maak hier een Nieuw Formulier
Geef vervolgens een naam aan je formulier en eventueel een beschrijving. Voeg vervolgens eenvoudig de vragen toe. Uiteraard kun je het formulier naar eigen smaak opmaken en aanpassen.
✅Pas een bestaand Excel bestand aan
Om bepaalde cellen te laten vullen vanuit een formulier, moeten de cellen een bepaald ID hebben in een bepaalde kolom. Om deze ID’s te genereren, moet het bestaande Excel bestand wel een tabel hebben.
Stel dat jouw Excel bestand er als volgt uit ziet en je wil de geel gearceerde cellen laten vullen vanuit een formulier:
Belangrijk! Sla dit bestand op een Online locatie (bijv. in OneDrive, Teams of Sharepoint). In dit voorbeeld sla ik het op direct in mijn OneDrive.
👉 Voeg nu als eerste een extra kolom toe (in dit geval links) zodat hier straks de ID’s in kunnen.
Klik rechtermuisknop op kolom A en selecteer invoegen
👉 Een tabel definiëren
Selecteer nu alle cellen die je in de tabel wil hebben (inclusief de extra kolom) en ga vervolgens via Invoegen naar Tabel
Het vinkje Mijn tabel bevat kopteksten mag uit
Het kan zijn dat er nu automatisch een tabel opmaak wordt gemaakt en hierbij ook een “filter” optie. Wil je dit niet, ga dan naar Tabelontwerp, Tabelstijlen en selecteer Wissen. Ook het vinkje bij Filterknop kun je uitzetten.
In dit voorbeeld heb ik ook de bovenste rij met kolomnamen verborgen.
👉 CEL ID’s genereren
Nu is het van belang om in de eerste kolom welke we aangemaakt hebben, de cellen een ID te geven. Dit is eenvoudig te doen met een formule.
Let op! Controleer in de volgende stap welke naam jouw tabel heeft gekregen. Dit kun je ook aanpassen.
Ga naar Tabelontwerp en zie linksboven de tabelnaam
Ga nu in CEL A2 staan en vul de volgende formule in:
=RIJ(Tabel12[@])-RIJ(Tabel12[[#Kopteksten];[Kolom1]])
Voer hier dus de naam van de tabel in zoals in de vorige stap
Er worden nu ID’s in kolom1 geplaatst. Belangrijk, deze hebben we later nodig!
✅ Maak een Power Automate flow
👉 Ga naar https://flow.microsoft.com
Klik nu links in het menu op Maken en vervolgens op Geautomatiseerde cloudstroom.
Geef nu een naam aan de “flow” en selecteer de trigger Wanneer er een nieuwe reactie wordt verzonden
👉 Het juiste formulier selecteren
Selecteer nu bij Formulier-id het formulier wat je aangemaakt hebt in de eerste stap (in dit geval Demo registratieformulier)
Klik vervolgens op + Nieuwe stap
👉 Een “Time-Out” variabele genereren voor verwerking van data
Om te voorkomen dat de Flow sneller gaat dan het verwerken van de data genereer ik een variabele om de tijd te geven de data van het formulier op te halen. In dit voorbeeld houd ik 20 seconde aan. Deze variabele gebruiken we later.
Klik op + Nieuwe stap en selecteer vervolgens Variabele initialiseren
| Naam | Selecteer een naam voor de variabele |
| Type | Reeks |
| Waarde | addSeconds(utcnow(),20) |
Selecteer vervolgens Besturingselement en dan Op elk(e) toepassen
👉 De antwoorden van het formulier ophalen
Bij de optie Een uitvoer selecteren uit de vorige stappen dient een “Expressie” toegevoegd te worden.
Dit moet zijn array(triggerOutputs()?[‘body’])
👉 Een taak maken om antworden van het formulier op te halen
Voeg vervolgens een nieuw actie toe genaamd Reactiedetails ophalen (Microsoft Forms)
Nu deze actie is toegevoegd, moet ook hier weer het Formulier-id en het Reactie-id ingevuld worden
👉 De cellen in Excel bijwerken met de gegeven antwoorden in Forms
Voeg wederom een nieuwe actie toe genaamd Een rij bijwerken (Excel Online Business)
De onderstaande velden gaan we nu vullen zodat de Excel bijgewerkt kan worden
| Locatie: | Waar staat het Excel bestand. In deze demo is de locatie OneDrive for Business |
| Documentbibliotheek: | In dit geval is het OneDrive |
| Tabel: | Selecteer hier de tabel welke ook in de stap “CEL ID’s” genereren staat (in dit geval Tabel12) |
| Sleutelkolom: | Vul hier de kolom in waar de CEL ID’s in staan (in dit geval kolom1) |
| Sleutelwaarde: | Vul hier het ID in van de rij in Excel die je wil updaten (in dit geval 2) |
| Kolom1: | |
| Kolom2: | |
| Kolom3: | Aangezien we in deze demo een waarde willen plaatsen in Kolom3 vullen we hier de dynamische waarde Wat is je voonaam? In |
Herhaal nu bovenstaande stappen voor iedere cel die je bijgewerkt wil hebben. In dit geval dus 5x. Let op! Kijk bij iedere stap goed naar Sleutelwaarde en Dynamische waarde in Kolom 3
💡TIP!! Het is mogelijk om de stap die zojuist gedaan is te kopiëren.
Klik op … van de stap en selecteer Kopiëren naar het klembord (preview)
Klik vervolgens op Een actie toevoegen en ga naar Mijn klembord
Selecteer hier de zojuist gekopieerde stap Een rij bijwerken
👉 Een TimeOut instellen
In deze stap gaan we de TimeOut variabele die in het begin ingesteld is gebruiken. Dit om te zorgen dat alle data uit het formulier gevuld kan worden in het Excel bestand en dat daarna het bestand netjes wordt gekopieerd.
Voeg een nieuwe actie toe
👉 Het aangepaste Excel bestand kopiëren en hernoemen
Nadat het bestand gevuld is me de informatie uit het formulier, wil ik graag een kopie van dit bestand maken en kopiëren naar een andere locatie met een aangepaste bestandsnaam. In dit voorbeeld wordt de nieuwe naam van dit document Demo Registratieformulier_<voornaam>_<achternaam>.xlsx
Voeg een nieuwe actie toe
Zoek het Bestand op (in dit geval in de “root” van mijn OneDrive).
De volgende stap is het aanmaken van een “nieuw” bestand met de inhoud van het formulier (een kopie dus)
Voeg een nieuwe actie toe
Selecteer hier Bestand maken
Vervolgens geef je het volgende aan:
| Mappad | De locatie waar je het bestand aan wil maken. |
| Bestandsnaam | De naam van het nieuwe bestand (in onderstaand voorbeeld gebruik gemaakt van de variabele van het formulier). |
| Bestandsinhoud | Welke inhoud moet er gebruikt worden. |
👉 Een melding genereren door middel van een e-mail
Nu de hele flow ingesteld is en het bestand is aangemaakt, wil ik graag op de hoogte gehouden worden per e-mail. Dit is eenvoudig in te stellen door onderstaande stappen te volgen.
Voeg wederom een nieuwe actie toe.
Selecteer nu Een e-mail verzenden (V2)
| Tot | Het e-mail adres waar de notificatie naartoe gestuurd moet worden. |
| Onderwerp | Het onderwerp van de e-mail die verstuurd wordt. |
| Hoofdtekst | De inhoudelijke tekst in de te versturen e-mail. |
👉 Een melding genereren door middel van Teams notificatie
Ook wil ik graag een melding in Teams krijgen wanneer een formulier ingevuld is. Dit kun je doen door onderstaande stappen uit te voeren.
Voeg wederom een nieuwe actie toe.
Selecteer in de selectie Microsoft Teams, “Bericht posten in chat of kanaal”
Vervolgens defnieer je hierde manier waarop een melding gemaakt wordt:
👉 Samenvatting
Alle bovenstaande stappen zijn nu een behoorlijke flow geworden. Zie onderstaand hoe de complete flow er uit komt te zien:
👉 De resultaten
Een kopie van het Demo registratieformulier is aangemaakt met de juiste naam
Een e-mail is verstuurd en binnengekomen met hierin de melding dat er een formulier ingevuld is.
Een Teams melding is binnen gekomen
En natuurlijk het meest belangrijke: Het Excel bestand is gevuld met data uit het formulier 😀
👉 Conclussie
Mijn doel om bestaande Excel bestanden automatisch te laten vullen met Forms data is behaald. Uiteraard zijn er meerdere wegen die naar Rome leiden en kan het bovenstaande ook anders. Mij heeft het geholpen en hoop jullie hiermee in ieder geval wat handvatten te geven om ook efficiënter te kunnen werken 😀
