Home Algemeen Een bestaand Excel bestand automatisch laten vullen door Forms en Power Automate

Een bestaand Excel bestand automatisch laten vullen door Forms en Power Automate

door Thijs Bisseling

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:

  1. Antwoorden van ingevulde formulieren moeten in een bestaande Excel bestand verwerkt worden
  2. Het nieuw gemaakte Excel bestand moet worden opgeslagen onder een bepaalde naam
  3. 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.

  1. Maak een formulier
  2. Maak een valide Excel bestand om data in op te kunnen slaan
  3. Maak een Power Automate flow
  4. 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

NaamSelecteer een naam voor de variabele
TypeReeks
WaardeaddSeconds(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) Kolom I 
Kolom 3 
1 Demo registratieformulier 
2 Voornaam: 
3 Achternaam: 
4 Adres: 
5 Postcode: 
6 Woonplaats:  
Sleutelwaarde:Vul hier het ID in van de rij in Excel die je wil updaten (in dit geval 2)
  1 Demo registratieformulier 
2 Voornaam: 
3 Ac ternaam: 
4 Adres: 
5 Postcode: 
6 Woonplaats:  
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 Kolom I Kolom2 
1 Demo registratieformulier 
2 Voornaam: 
3 Achternaam: 
4 Adres: 
5 Postcode: 
6 Woonplaats: 
Kolom 3  

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:

MappadDe locatie waar je het bestand aan wil maken.
BestandsnaamDe naam van het nieuwe bestand (in onderstaand voorbeeld gebruik gemaakt van de variabele van het formulier).
BestandsinhoudWelke 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)

TotHet e-mail adres waar de notificatie naartoe gestuurd moet worden.
OnderwerpHet onderwerp van de e-mail die verstuurd wordt.
HoofdtekstDe 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 😀

Related Posts

Laat een Opmerking achter

Deze website maakt gebruik van cookies. Accepteer Lees Meer

Geverifieerd door MonsterInsights