Automating Microsoft Forms creation

5 min readFeb 12, 2020

How this got started

Jenzus Hsu got me piqued with his post on using powerapps to generate a single form for contact tracing (link).

In the event that PowerApps is not possible for whatever reasons, or simply other use cases where Microsoft Forms is a better and easier use case.

How can Contoso Campus go about automating the creation of thousands of Microsoft Forms

Desired Outcome

The desired outcome is pretty straightforward: To come up with a program/system/script to automate the duplication of thousands of Microsoft Forms.

Instructions to automate

Identify an input source (SQL, Excel, CSV)
Login to Microsoft Forms
For each element in #1 perform the following steps

Navigate to the Form you want to duplicate (Duplicate It Link of the original form)
Click on “Duplicate it”
Make the needful changes (e.g edit header, update options etc).
Click on “Share”
Extract the Share URL and save it to an external location

Gotchas

Things are never as simple as they seem. Here are a few things to consider when automating this form

Microsoft Forms has a 200 forms limit per account; and given this is an education scenario a 50,000 response limit (https://support.office.com/en-us/article/form-question-and-response-limits-in-microsoft-forms-ec15323d-92a4-4c33-bf88-3fdb9e5b5fea)
Possible input limits to the headers, question text and options. I shall leave this as an exercise to the viewer
Incomplete download. Microsoft Forms uses modern javascript techniques to load the page. Sometimes things are not downloaded or executed properly
Authentication. Most organizations now use either AzureAD or ADFS. This should be catered in the solution to minimize inputs.
Lack of Forms API. Ideally the availability of an API will make the solution more elegant.
Modern browser capabilities. Microsoft Forms uses modern techniques to improve the look and feel of the page. This invariably means that some elements do not have a unique named identifier and you will need to interact with the browser as a “real” human

Technology choice

RPA screams out as the single-most and easiest way to get this done. Unfortunately where RPA shines is in the desktop environment, browser automation does require you to roll up your sleeves and do some form of coding.

In this case it is probably more efficient to using c# and Selenium to automate the generation of the forms. Alternative to this is to look at Selenium IDE for Firefox or Chrome, but bear in mind Gotcha #6.

Developing the solution

Since this is a Selenium based project, go ahead and create a new Console app

Import the binaries based on your input source using nuget package manager or simply editing packages.config

Excel: EPPlus
JSON: Newtonsoft.Json

Including Selenium.WebDriver, your packages.config should look something like the image below

Now we get to the code development proper

Open up program.cs and add the following dependencies at the top

using OfficeOpenXml;
using OpenQA.Selenium;
using OpenQA.Selenium.Firefox;

Trigger a new Firefox Driver

using (var driver = new FirefoxDriver()) {

Start off by navigating to the forms page and use selenium FindElement to identify the sign-in link and proceed with the sign-in based on your organization setup

driver.Navigate().GoToUrl("https://forms.office.com/");

Once you have signed into Forms, you can now navigate to the Forms you want to duplicate. In this case i’ve created an object to store the inputs needed for the automation.

driver.Navigate().GoToUrl(Inputs.DuplicateURL);

Once the page load is completed, instruct Selenium to click on the “Duplicate It” button

var theDiv = driver.FindElementsByTagName("div").Where(p => p.Text == "Duplicate it").FirstOrDefault();
theDiv.Click();

Note: This will happen throughout the automation. Because it is a modern website you WILL need to check and ensure the element is available before performing any actions on it. Otherwise Selenium may either throw you an element not ready or element is stale exception

Now make the changes you need to the form. For example if you want to change the header text, you may use the following code snippets

//Find the Header of the form
var theHeader = driver.FindElementsByTagName("span").Where(p => p.Text == Inputs.HeaderText).FirstOrDefault();
                                                            
theHeader.Click();//Once you click on the header, it becomes a textarea. And since there is only one textarea in the entire form, you just need to find the first one
var theTextArea = driver.FindElementByTagName("textarea");//The following commands is simple to delete all text in the textarea                                                 
theTextArea.SendKeys(Keys.End);
                                                    theTextArea.SendKeys(Keys.Shift + Keys.Home);
                                                    theTextArea.SendKeys(Keys.Delete);var ReplacedHeader = Inputs.ReplaceText;//Header has a 90 character limit, ensure ReplacedHeader is <= 90 characters
if (ReplacedHeader.Length > 90)
                                                        ReplacedHeader = ReplacedHeader.Substring(0, 90);

//Send the keys over to the textarea                                       theTextArea.SendKeys(ReplacedHeader);

Once all the inputs are updated, you will need to click on the share link

var theShareButton = driver.FindElementsByTagName("div").Where(p => p.Text == "Share").FirstOrDefault();
                                                    theShareButton.Click();

Looks like the share textbox has an ID, this makes it alot more efficient to search for it

var theShareInput = driver.FindElement(By.Id("flex-pane-textbox-link"));var ShareURL = theShareInput.GetAttribute("value");

As a counter-check, it will be good to navigate to the new forms url and ensure that the changes you have done are there

driver.Navigate().GoToUrl(ShareURL);
                                                    
var validateSpan = driver.FindElementsByTagName("span").Select(p => p.Text);var isValid = validateSpan.Contains(ReplacedHeader)if (isValid) {..Save the output}
else {..Throw error}

You are not out of the woods yet, during the setup of WebDriver, there is an instruction to download geckodriver and place it inside the executable path (e.g bin\debug) AND to install Microsoft Visual Studio redistributable
runtime

Once you got these all sorted out, sit back and watch the magic 😃

In conclusion

Selenium was and is still the main tool for browser automation. It offers multiple browser support, deep integration into the platform and pretty much opens up the full browser for you to do with it what you wish.

Finally, the console app you have created runs firefox in its own sandbox environment. This means that you can run as many of them as you wish as long as you have the compute, ram and disk space.