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)?
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.
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).
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.
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?
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.
Here is what the trigger looks like:
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.
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();)
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
}
}
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.
