These days, we can drive cars without knowing how they work. The same goes for Google Workspace and its lineup of apps such as Forms and Sheets. Those who learn the mechanics of cars can modify them to accomplish special tasks for specific purposes. The same can be said for Google Workspace apps. You can learn the mechanics of these apps by learning what is called Google Apps Script (GAS), which is a scripting language based on JavaScript that sits behind a number of Google Workspace products, such as Google Docs, Google Forms, and Google Sheets” (Roberts, 2021, p. 7). GAS is a great programming language to learn for novices. You do not need to install anything, as the processing is done on Google’s servers. You only need a Gmail account, any web browser, and an internet connection. All the data is stored in the cloud. GAS can automate tasks, extend the functionality of Google Apps, and integrate app functions for projects that require multiple app use. AI can also be used to assist you with writing GAS. The GAS in this article was written with the assistance of ChatGPT 3.5 (OpenAI, 2024).
I became more interested in using technology in my teaching due to the pandemic. I was using Google Forms and Google Sheets in my teaching as introduced by Paton (2021), who describes how to integrate Google Forms and Google Sheets. I used Google Forms for quizzes and surveys and Google Sheets to record the results from the Google Forms into more usable configurations, allowing me to track each student’s progress in real time and calculate the grades easily at the end of the semester.
As I became comfortable using Google Forms and Google Sheets, I began relying on them for various class assignments, creating many Form files to accomplish my tasks. As a result, I could go almost paperless in my classroom—no more time spent photocopying handouts! However, I was spending a lot of time on repetitive tasks, such as deleting Form responses for reuse in a new class. I felt that by changing from paper handouts to Google Forms, I exchanged one set of repetitive tasks associated with paper handouts (e.g., photocopying handouts) to another set of repetitive tasks associated with Google Forms (e.g., deleting responses). Executing GAS allows me to automate these repetitive tasks. I will explain how to construct the GAS that has allowed me to delete responses from multiple Google Forms simultaneously within a single Google Drive folder.
How to Create and Run GAS
Creating a GAS that involves performing the task of deleting responses from multiple Google Forms saved in a single folder on Google Drive is no different from creating any other new Google app document. I would recommend creating a folder with a few dummy forms so that you are not accidentally erasing results from Form files you do not want to be erased.
To start, we will need to create a new GAS inside Google Drive (drive.google.com), which can be done by selecting “+ New” at the top left corner, selecting “More” at the bottom of the menu, and selecting “Google Apps Script” (see Figure 1). We do not need to create this new script in the folder where all the forms are located. Inside the GAS app, we will see a blank workspace (see Figure 2). The workspace begins with four lines of code. Because we will be using code from this article, let us erase the first 4 lines in the workspace.
Figure 1
Location of Google Apps Script in Google Drive’s Create Menu
Figure 2
New Google Apps Script Workspace Inside a Web Browser
Every action is represented by a function as seen below. Our function is to erase the form content, so our function is titled eraseForms. This should be added to line 1 in the workspace.
function eraseForms() {
Next, we want to identify which folder in Google Drive has the forms we want to reset. Copy the line below onto the next lines in the workspace.
var folder = DriveApp.getFolderById(‘1lsGVBGN0n8-jaOvnW4iyy1E3mPIorsaV’);
The series of letters and numbers inside the parentheses is the folder’s ID. We need to replace that series with your folder’s ID, which is found in the folder’s URL. In our example as seen in Figure 3, the folder’s URL is https://drive.google.com/drive/u/0/folders/1lsGVBGN0n8-jaOvnW4iyy1E3mPIo... so the folder`s ID is ‘1lsGVBGN0n8-jaOvnW4iyy1E3mPIorsaV’.
Figure 3
The ID of a Folder in a Google Drive
Next, we want the script to take inventory of all the files in the folder with the following three lines.
var files = folder.getFiles();
while (files.hasNext()) {
var file = files.next();
Now, we can have the script pinpoint the form files and have all the responses deleted as well as allow new responses to be accepted (with the assumption that accepting responses was turned off). If we do not want responses to be accepted, simply change the word “true” to “false” in the fourth line below.
if (file.getMimeType() == “application/vnd.google- apps.form”) {
var form = FormApp.openById(file.getId());
form.deleteAllResponses();
form.setAcceptingResponses(true);
The script ends with the actions taken being logged and closing all open braces.
Logger.log(file);
}
}
}
Make sure to save the script by selecting the save icon (floppy disk) in the top menu. To verify that the script works, select “Run” in the top menu. An execution log should appear at the bottom of the screen with the names of all the Form files in the identified folder (remember that this means all listed forms’ responses have been deleted; see Figure 4). From this point, anytime we want to quickly clear all the Form files in the identified folder, double-click on the GAS file and select “Run” from the top menu. There are ways to turn this GAS into a web app, but that is an explanation for another time. You can view a full copy of the script in Figure 5.
Figure 4
The Save Icon, Run Button, and the Execution Log
Conclusion
You have just created and executed your first GAS. Congratulations! To continue your coding journey, I suggest that you read Roberts (2021) Beginner’s Guide to Google Apps Script. This is the book that started me on my coding journey, and I cannot recommend it enough.
References
OpenAI. (2024). ChatGPT (Jan 22 version) [Large language model]. https://chat.openai.com/chat
Paton, S. (2021). Get your spreadsheet data in order with VLOOKUP. The Language Teacher, 45(3), 30–33. https://doi.org/10.37546/JALTTLT45.3
Roberts, B. (2021). Beginner’s guide to Google Apps Script: How to automate Google Sheets & Forms using Apps Script. Amazon Digital Services LLC.
