Hi there,
I’m a big fan of Zapier and use it a lot, either for a personal or professional context. However, for me it’s missing two very important features:
- Ability to delete a line, not just clear it. Especially since glide limit the number of lines on his plans.
- Ability to delete (or even clear) multiple lines. For example, I can’t automatically clear a
cart sheet when I insert a row into my order sheet
So, have you people find a solid alternative that can connect to Google Spreadsheet and do some magic here ?
You can create a loop array create rows action then listen to it by a 2nd zap then delete a row but it won’t delete it totally it will only just clear it.
Please read carefully the below method am using for more than 4 years already
- 1st zap use it to get it in a separate sheet empty one in the same spreadsheet and use the text join function with filter here below am using it to get me all the licenses that are active for a certain user filtered
Example =textjoin(",",true,FILTER(‘License Per Company’!$B$2:$B,‘License Per Company’!$I$2:$I=6945,‘License Per Company’!$O$2:$O=“Active”))
The result will get you this example array of user IDs 9b175371,499148d6,d12edf77,2f64cc68,97296233,f533c8ee,a350a796
Sometimes I use the =Row() formula in a column so I can get the row ids normally
In the same 1st zap you will use formatted utility text to line item and separated by comma this will create for you a list of line items that you will send to a second sheet through "create spreadsheet row(s) "Create one or more new rows in a specific spreadsheet (with line item support).
- Create another zap that will trigger on each row that will be added newly from the previous zap then delete it by mapping the ROWID from the previous directly to the custom row and it will clear for you the rows immediately
Glide will not count rows that are empty.
I don’t have experience, but I read that a lot of people are happy with Integromat.
Oh. Then it “could” be something usable (with a bit of manual cleaning from time to time).
1 Like
Google Apps Scripts can do the job for you. https://developers.google.com/apps-script/reference/spreadsheet/sheet
I use it in an app to automatically delete rows when they are irrelevant.
I though about that but I only managed to have my scripts launched by a menu on the sheet, not reacting to new insert on the sheet.
Are you talking about the triggers? I prefer to use time based triggers because OnEdit/OnChange triggers don’t work well with Glide sometimes
The thing is, in most of my scenario I would require some kind of “instant” actions.
I was talking about the Google App Script. I tried it but didn’t find yet how to have something running on change of a row by Google App Script.
1 Like
So you’re idea is to have a utility sheet which store by any mean the row IDs to delete and use a trigger on that sheet, when a new row, use the content (which is the other sheet row id) to delete the row.
I definitly need to give a try, that’s a nice work around !
Just wanted to share the google script I made to answer the need I exposed before
/**
* Find the email for a client order
* @param {Event} e The onEdit event
* return {String} The email of the client
*/
function getClientEmail(e) {
return e.range.getCell(1,2).getDisplayValue();
}
/**
* Find all the lines in cart for this email
* @param {String} email The client's email
* return {Array} of Range
*/
function getCartLines(email) {
Logger.log(email);
// Get the correct Sheet to search
var cartSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Panier");
// Get all occurences of email
var found = cartSheet.getRange(1,1,cartSheet.getLastRow()).createTextFinder(email).matchCase(false).findAll();
// return the lines (it's range element actually)
return found;
}
/** Delete the row of that range
* @param {Range} the row to delete
*/
function deleteLine(range) {
var rowNumber = range.getRow();
range.getSheet().deleteRow(rowNumber);
}
/**
* The event handler triggered when editing the spreadsheet.
* @param {Event} e The onEdit event.
*/
function onEdit(e) {
// prevent execution on any other sheet than Commandes
if (e.source.getActiveSheet().getName() != "Commandes")return;
// Try to get client email, if unsuccessfull, exit.
var client = getClientEmail(e);
if (client == null) return;
// Try to get Cart for the client, then reverse the array to start deleting by the end of the pile
var lines = getCartLines(client).reverse();
if (lines == null) return;
// Iterate over each line and delete them
lines.forEach(line => deleteLine(line));
// Set a comment on the edited cell to indicate when it was changed.
e.range.getCell(1,1).setNote(lines.length + ' lines of cart deleted');
}
This is a simple Trigger and it will NOT work when glide is making API call and insert struff in the sheet. My next move is to use installable trigger and publish my code (I think it’s require Google approves it before you can “install” it on your sheet) to be able to catch insertion by API 
1 Like
The easiest is to just add an onChange trigger through google scripts. This will trigger any time data comes from glide or something in the sheet changes. I have used this in the past as well as some time based triggers. It’s a pretty simple process to set up.
1 Like
Except I have a big error screen from Google 
Saying my app is unverified so Google login is deactivated.
That should be fine. A new trigger will usually need authorization from you to run on your behalf and since it’s not a verified app, you just need to override it anyway since you are the developer. This should have been presented to you somewhere when creating a new trigger.
Did you go through these steps when setting up the trigger?
1 Like
I realised I need to move the script for the default environment and create an app to in GCP do get the Oauth working 
Now it’s done, I can see that onChange event doesn’t get the range so I have no clue about what data have been modified which I had with enEdit (but which don’t work with Glide insertions).
Also, the changeType for Glide is EDIT and not INSERT_ROW… which don’t help. I could have imagine getting the last row when I’m sure it’s an INSERT_ROW event but with just EDIT … Could be anything.
