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).

5 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

I am struggling with a similar issue. I have several scripts that I want to run on the data. The first is to trigger an action only when a new row is added. Using onChange works fine, but onChange triggers actions when I update a cell, as well as when a new row is added. So, going through the documentation and various web sources, I arrived at the changeType solution. I just cannot get it work. Here is the relevant code:

function newRow(to, body) {
var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
if(e.changeType === ‘INSERT_ROW’){

When using this code, onChange does not trigger the action, even though I aded a new row to the sheet.

Any guidance would be appreciated.

Thank you

This might seem non-intuitive, but when Glide adds a new row, the changeType will be EDIT (not INSERT ROW)

EDIT: Actually, I should qualify that. In actual fact, when Glide adds a new row the changeType could be EDIT, or it could be INSERT ROW. Which depends on whether or not there are any empty rows at the bottom of the sheet.

  • If there are empty rows, the changeType will be EDIT
  • If there are NO empty rows, the changeType will be INSERT ROW

In general, I avoid using the changeType, and instead take actions based on the sheet that was changed (event.source.getActiveSheet().getName();)

2 Likes

Here is a bit of code I grabbed from one of my tests that may help.

// create a Google App Trigger for this function
// go to Edit/Current project's triggers. Add an OnChange trigger that runs this function
// it will increment the value in sheet Counter cell A2 every time this function runs
function onChange(e) {
  var sheet = SpreadsheetApp.getActive().getSheetByName('Counter');
  var range = sheet.getRange("A2");
  var data = range.getValue();
  var changeType = e.changeType;
  var sheetName = SpreadsheetApp.getActive().getActiveSheet().getName();
  
  // just see if this fired by incrementing this counter
  range.setValue(++data);
  
  SpreadsheetApp.getActive().toast(changeType, sheetName);
  
  // The type of change (EDIT, INSERT_ROW, INSERT_COLUMN, REMOVE_ROW, REMOVE_COLUMN, INSERT_GRID, REMOVE_GRID, FORMAT, or OTHER).
  if (e.changeType == 'INSERT_ROW') {
    // do something
  }
}
4 Likes

3 ticks before code and 3 ticks after…What is symbol for the tick?

On my Macbook keyboard it is in the upper left corner. ` shift of it is a ~

thank you

Special thanks to George B and Darren. I was able to get my script to work and only trigger when a new row is added. You guys are great.

New challenge. Sheet has many records, but my app will let the user check a box on a particular record for a particular column. Eg, Task is now complete, check the box. After user checks the box, I want to pull data from that row and send emails and SMS based on field values in the row. I looked at filters and loops, but feel like I am missing something conceptually. As always, guidance appreciated.