Jeremy Grifski – Page 39 – The Renegade Coder

I wrote a script the automatically fills out Google Forms, and I thought it might be useful to other folks as well. At the very least, it helps me merge two forms. Fair Warning For context, I'm writing this article as someone who was trying to find a way to take data from an excel spreadsheet and load it into a Google Form. Unfortunately, there is no way that I know of to do this easily (i.e., automated). Now, if you've used Google Forms, you know that responses can be saved in a Google spreadsheet. Sadly, the spreadsheet is just a backup of the form data. As a result, you cannot just load data into this spreadsheet and hope Google Forms picks it up. Alternatively, there are tools like Form Ranger which can autopopulate response data in some cases, but I find it extremely limited. For example, I had free response questions and Form Ranger does not appear to be able to handle that data. Now, it's possible that there is some way to populate a Google Form using Google Apps Script, but I wasn't about to learn a new programming language just to write a quick tool. As a result, I turned to Python which gave me all the tools I needed. However, before we get to that, I need to warn you that you cannot transfer all of the data to Google Forms with the Python script I am about to show you. Specifically, timestamps cannot be transferred as they are generated when you submit the Google form. Potentially, there is a workaround for this by replacing the spreadsheet data post-hoc, but we won't be covering that here. At any rate, let's get into it! Making Use of Pre-filled Links One of the features of Google Forms is that you can create pre-filled links for users. The idea being that you can populate all the responses with some default value, so your respondents can spend less time filling them out. One of the cool features of these pre-filled links is that they can be used to generate a Google Form response without user intervention. As a result, if we can get a feel for how these links are formatted, we can create our own. Here's an example of what those links look like: url = f"https://docs.google.com/forms/d/e/1FAISfGp2BS_43Fb31hHfCIj-JKwTESfDGmmQw/viewform?usp=pp_url&entry.21325369=1&entry.190867=4" Now, I messed with this URL a bit, so you can't use it on my personal Google Form. That said, the idea is still the same. You have some URL that points to the Google Form. Then, at the end of that URL, you have a set of query strings that can be used to populate the form. In this case, we have three entry elements which we can use to populate the form. As you can imagine, you can put basically whatever you want after the equal sign of each entry. These values will then populate the form for the matching entries. Here's an example: Now, you can imagine that the first two entries in this URL answer the questions above. In other words, the first entry maps to the project question and the second entry maps to the rubric question. If someone were to drop the URL above into the browser, they would see a form that looks like this with the two questions autopopulated. How cool is that? Generating Pre-filled Links Now, in theory, we could use this pre-filled link trick to create a filled out form for all of our respondents. To do that, I make use of the f-string above with some carefully placed brackets: url = f"https://docs.google.com/forms/d/e/1FAISfGp2BS_43Fb31hHfCIj-JKwTESfDGmmQw/viewform?usp=pp_url&entry.21325369={project}&entry.190867={rubric}" If we play our cards right, we could generate random numbers to fill those two bracketed sections: import random for _ in range(10): project = random.randint(1, 11) rubric = random.randint(1, 5) url = f"https://docs.google.com/forms/d/e/1FAISfGp2BS_43Fb31hHfCIj-JKwTESfDGmmQw/viewform?usp=pp_url&entry.21325369={project}&entry.190867={rubric}" Now, this is cool if you want to create random data for your form, but I actually have a spreadsheet of data from a different form. As a result, I wrote some code that looked like this: import csv import urllib.parse with open("logistics\\feedback-forms\\assignment-survey\\data\\au-2021.csv", "r", encoding='utf-8') as f: reader = csv.DictReader(f) for row in reader: project_num = int(row["8059386: Which project are you reviewing?"].split(":")[0][-2:]) rating = row["8059590: On a scale from 1 (least) to 5 (most), how satisfied are you with this rubric?"] good = urllib.parse.quote(row["8059591: In what ways was this rubric helpful to you?"]) bad = urllib.parse.quote(row["8059592: In what ways could this rubric be more helpful?"]) time = row["8533354: How much time did you spend on this project in hours?"] Then, you can imagine I had some URL variable that made use of these five variables. And once we have code to generate the links, we can just print the links and click on them. Automating Form Submission But wait! This means that we still have to submit each form 1 by 1. That may be feasible if we only have a few responses in our CSV. In my case, I had over 160 responses. No way was I about to hit submit that many times. So, we can actually introduce Selenium for simulating those clicks. Here's what that looks like: from selenium import webdriver from webdriver_manager.chrome import ChromeDriverManager from selenium.webdriver.support.ui import WebDriverWait from selenium.webdriver.support import expected_conditions as EC from selenium.webdriver.common.by import By driver = webdriver.Chrome(ChromeDriverManager().install()) url = f"https://docs.google.com/forms/d/e/1FAISfGp2BS_43Fb31hHfCIj-JKwTESfDGmmQw/viewform?usp=pp_url&entry.21325369=1&entry.190867=4" driver.get(url) button = driver.find_element_by_xpath('//div/span/span[text()="Next"]') button.click() button = WebDriverWait(driver,30).until(EC.presence_of_element_located((By.XPATH, '//div/span/span[text()="Next"]'))) button.click() button = WebDriverWait(driver,30).until(EC.presence_of_element_located((By.XPATH, '//div/span/span[text()="Submit"]'))) button.click() Now, I won't go into the details of how this weeks because I don't fully understand it, but I can quickly give an overview. Basically, we want to open the URL in a browser. I chose Chrome (see lines 7 and 11). After we get the URL open, we need to be able to click through the form. In my case, the form had three pages, so we actually had to hit the "Next" button twice. This involves finding the button and making sure to click it (see lines 13 and 15). Unfortunately, the only easy button to hit is the first button. After that, we have to introduce a wait period for the next page to load. Then, we look for the next button before clicking it (see lines 17 and 19). Repeat this process for the submit button as well (see lines 21 and 23). With that said, if you combine the code for generating the URLs with the code for clicking through the survey, you can fully automate submission. Here's what that might look like for random values. import random from selenium import webdriver from webdriver_manager.chrome import ChromeDriverManager from selenium.webdriver.support.ui import WebDriverWait from selenium.webdriver.support import expected_conditions as EC from selenium.webdriver.common.by import By driver = webdriver.Chrome(ChromeDriverManager().install()) for _ in range(10): project = random.randint(1, 11) rubric = random.randint(1, 5) url = f"https://docs.google.com/forms/d/e/1FAISfGp2BS_43Fb31hHfCIj-JKwTESfDGmmQw/viewform?usp=pp_url&entry.21325369={project}&entry.190867={rubric}" driver.get(url) button = driver.find_element_by_xpath('//div/span/span[text()="Next"]') button.click() button = WebDriverWait(driver,30).until(EC.presence_of_element_located((By.XPATH, '//div/span/span[text()="Next"]'))) button.click() button = WebDriverWait(driver,30).until(EC.presence_of_element_located((By.XPATH, '//div/span/span[text()="Submit"]'))) button.click() Obviously, you will have to adapt this to your own needs, but I hope this gives you the foundation you need to automate your own Google Forms. Please refrain from using this to blow up someone else's form! Learning Through Experimentation As with many of the articles on this site, I can only provide this information for you by trying to do these things myself. You can support these types of niche articles by heading over to my list of ways to support the site. I'd appreciate it if you took the time to check that out! Otherwise, here are some additional Python resources from the folks at Amazon (#ad): And, here are some articles about Python that you might be interested in: Comparing Java to Python: A Syntax Mapping How to Use Python to Build a Simple Visualization Dashboard Using Plotly How to Generate Markdown in Python Using SnakeMD Thanks again for checking this article out!