Google Sheets built-in "On form submit" trigger

I built a simple script to clear the content of two columns on two different sheets:

function clearFormEntries() {
  
  // Activate 2nd sheet [Team] and delete G2:G [CHECKLIST]
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  SpreadsheetApp.setActiveSheet(spreadsheet.getSheets()[1]);
  var sheet = spreadsheet.getActiveSheet();
  sheet.getRange('G2:G').clear();
  
  // Activate 4th sheet [Rules] and delete D2:D [OTHER DESCRIPTION]
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  SpreadsheetApp.setActiveSheet(spreadsheet.getSheets()[3]);
  var sheet = spreadsheet.getActiveSheet();
  sheet.getRange('D2:D').clear();  

}

The script works as expected when I run it. The purpose of the script is to clear a checklist and text field. Otherwise, the next user sees the previous user’s input. [The app has two nested in-line list relations that present different options depending on what was selected in the previous list.]

I want the script to run after each time a user submits a form (I have a single form button in the app). I added Google Sheets’ built-in “On form submit” trigger:

The trigger does not appear to be working. I’ve looked through the Glide Community posts on triggers and didn’t see any discussion of this functionality, so I suspect it doesn’t work with Glide. Is that true? If so, how would I set up an event-driven trigger (e.g., when a new row is added to the Google sheet that captures Glide form submissions)?

Link to app: https://sa-taa.glideapp.io

2 Likes

I tried changing the built-in trigger to “On change” (from “On form submit”). This triggers the function on any change in the sheet, and it ends up clearing the entries I want to capture.

1 Like

Think I figured it out. Changed the first part of the script:

function clearFormEntries(e) {
  
  if(e.changeType=="INSERT_ROW")

and used the built in trigger “On change”. So, the script is triggered whenever there’s a change, but it only executes the commands when a new row is inserted (i.e., a Glide form is submitted).

4 Likes

Well done. I’ve been attempting something similar with clearing the contents of certain cells in a particular row when a particular cell in the same row becomes empty. This gives me some ideas. :thinking:

Hi

I want to clear out records which are 30 days old based upon a date time field within the sheet, It needs to Trigger on new row being added.

I thought the “On form submit” event trigger was only when an attached Google Form adds a record. Are you saying it works when you add a row to a sheet via the Glide Form?

No, it does not work with the Glide Form.

I’m getting this:
TypeError: Cannot read property ‘changeType’ of undefined
Any ideas?

function clearFormEntries(e) {

if(e.changeType==“INSERT_ROW”)
// Activate 1st sheet [Sheet 1] and delete G2:G [FUEL]
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
SpreadsheetApp.setActiveSheet(spreadsheet.getSheets()[1]);
var sheet = spreadsheet.getActiveSheet();
sheet.getRange(‘G2:G’).clear();

}

This function needs to be called by an On change trigger. That is what passes in the e variable which has the changeType property. While in the editor click on that icon that looks like a clock and add a trigger for this function. Select a trigger type of onChange.

FYI, when you paste code you should proceed it with 3 ticks on a line and follow it with 3 ticks on a line. The lines of text between will be formatted correctly.

Geo,
Thx for responding.
I am trying to clear the fuel qty after form submit. The cell is G2.
image
Here is what the trigger looks like:
image
Here is the code again

  
  if(e.changeType=="INSERT_ROW")
    
  // Activate 1st sheet [Sheet 1] and delete G2:G [FUEL]
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  SpreadsheetApp.setActiveSheet(spreadsheet.getSheets()[1]);
  var sheet = spreadsheet.getActiveSheet();
  sheet.getRange('G2:G').clear();
  
}```

Needless to say, still not working. Same error message.

I assume you did not copy and paste the code because as it is it is missing the proper formatting, specifically { } formatting as part of the if statement.

Your original error indicates that there is no parameter “e” being passed into the function. You can’t call the function in the debugger because it depends on the event. The e actually stands for Event and is the event that triggered the function. The event in this case should be the “OnChange” event which has various properties, one of which is the changeType. Do some googling for Google Script onchange. That should show you a bunch examples of how to set it up.

I would start off by logging a bunch of stuff so you can view it after the event is fired. Or better yet do a .toast to see what is being passed in.

I can’t read the words on the image of the trigger so I can’t see if that is correct or not.

Geo,
Maybe you can help me out. I’ve been banging my head against a wall all day with this to no avail. I’ve tried all the suggestions to the best of my ability. In case you can’t tell, I don’t know code.
I’m just trying to clear G2 after a Glide form submission so the next user has a blank field to fill in.
Can you assist?
Pete