menu

Glide

🚨🚨🚨 The Glide community forum has moved! Please click the new link below:

Channels
# All channels
view-forward
# General
view-forward
# Announcements
view-forward
# Bugs
view-forward
# Bugs (Fixed)
view-forward
# Feature Requests
view-forward
# Help
view-forward
Team

Triggering onEdit() in Google Sheets when edits are made from the app

May 3, 2019 at 6:42pm

Triggering onEdit() in Google Sheets when edits are made from the app

May 3, 2019 at 6:42pm
I built an app for translating some commonly used words into a different language.
Since GoogleTranslate doesn't work with ARRAYFORMULAS, I had to write a script to drag down the translation formula to the end of the data and tie it with the inbuilt onEdit function in Google Apps Script.
BUT
When I add a new word to my list, Glide updates the word in the Google Sheet, but that doesn't trigger the inbuilt onEdit() function.
and - If it wouldn't take a lot of your time, can you please check this?

May 5, 2019 at 9:48pm
This would be great but it look like onEdit only runswhen you manually edit your sheet within Google Sheets–API calls cannot trigger it.
Edited
  • reply
  • like
This would be great but it look like onEdit only runswhen you manually edit your sheet within Google Sheets–API calls cannot trigger it.
Never mind, David. I just experimented by manually configuring a trigger. Works like a charm now. Thanks for the additional info! :)
  • reply
  • like
Hi all. The onedit(e) seems to either have a simple trigger that needs no Hi all. The o but there is an nedit(e) seems to either have a simple trigger that needs no Authorisation but there is an installable trigger that I think you can grant authorisation through your account. Does anyone know if this process can run with the glide app? I can't seem to find too much info about it at prezent
  • reply
  • like
Hey : Used onChange event to install a trigger and it works. Don't need Zapier for this part of the problem.
  • reply
  • like
Hey : Used onChange event to install a trigger and it works. Don't need Zapier for this part of the problem.
Nice! I'll look into how to do that tomorrow
  • reply
  • like
Hi Amit, Would love to see you code, I've been trying with this: function createSpreadsheetChangeTrigger() { var ss = SpreadsheetApp.getActive(); ScriptApp.newTrigger('onChange') .forSpreadsheet(ss) .onChange() .create(); } //CORE VARIABLES // The column you want to check if something is entered. var COLUMNTOCHECK = 2; // Where you want the date time stamp offset from the input location. [row, column] var DATETIMELOCATION = [0,-1]; // Sheet you are working on var SHEETNAME = 'Shopping'
function onChange() { //-this doesnt work e.authMode == ScriptApp.AuthMode.NONE; var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheet = ss.getActiveSheet(); //checks that we're on the correct sheet. if( sheet.getSheetName() == SHEETNAME ) { var selectedCell = ss.getActiveCell(); //checks the column to ensure it is on the one we want to cause the date to appear. if( selectedCell.getColumn() == COLUMNTOCHECK) { var dateTimeCell = selectedCell.offset(DATETIMELOCATION[0],DATETIMELOCATION[1]); dateTimeCell.setValue(new Date()); } } }
  • reply
  • like
No joy so far, any help greatly appreciated, kind regards, Max Drake
  • reply
  • like
Hey - What I am doing is not in the code. Simply write a function that you'd like to be executed when there is a change. You can then 'install' a trigger from here: https://script.google.com/home/triggers
I hope this info helps.
  • reply
  • like

May 6, 2019 at 3:10pm
This is fantastic. Would you be willing to share the Google sheet? The pronunciation button doesn’t seem to work by the way.
  • reply
  • like

July 12, 2019 at 7:47am
Would you mind explaining a bit more about how you managed to solve that? I'm having the same issue with onChange triggers. I have a script that works whenever a new row is added to the spreadsheet, and added an onChange trigger through apps script triggers, but that doesn't work for lines added from within Glide 😔
Edited
  • reply
  • like
This works only if the spreadsheet is open in your browser and the OnChange event happens manually by a user. It will not work when a user makes a change via the Glide App. You would need to create a timed trigger instead of the onChange trigger. You can run these timed triggers at intervals as short as 1 min but keep in mind there will be that 1 min delay along with possibly a bit more delay with the Glide refresh.
  • reply
  • like
Thank you for your help! 🙂
  • reply
  • like
I'm afraid a timed trigger doesn't work, since my script is based on the onchange. Is there some other way I can approach this? This is the script:
function AddRow(e) { var sh = SpreadsheetApp.getActiveSheet(); if(e.changeType === 'INSERT_ROW') { var row = sh.getActiveRange().getRow(); sh.getRange(row, 14).setValue('defaultvalue') } }
Edited
  • reply
  • like
Create the timed trigger and have it run your AddRow() function. Maybe I'm missing something, but timed triggers can run any function, even one called onChange(). [edit] Wait.... Now I see what your are wanting to do... Let me think on this.
Edited
  • reply
  • like
The only thing I can think of is to create a function called something like setDefaultValues(). It would scan column 14 looking for blanks and insert 'defaultvalue'. You would then timed trigger this.
Edited
  • reply
  • like
I'm not sure why, but it's not working as a timed trigger. Actually, the only way to get it working is to add a row manually in the sheet itself through insert>row. When a line is added by a user in the app, the script will not work.
  • reply
  • like
It's not working as a timed trigger because of your if statement. Rewrite the function with out that but obviously you will not be "on" that row so you will need to zip through all rows for that column and check if it is blank. The fastest way would be to define a range for the entire column put the values into an array with the range.getValues() scan the array replacing blanks with 'defaultvalue', then range.setValues(array).
  • reply
  • like
I'm a complete beginner with scripts so this might be a stupid question, but won't I have a lot of blank rows with just the default values in that case, creating a lot of false blank items in my app?
  • reply
  • like
[Edit] I finally got around to testing this code and after a bit of tweaking it works. But be aware that if you are using arrayformulas() to populate columns the getDataRange() will not work as expected. Because if you have a formula pulled down through an entire column it will throw a false positive to the .getLastRow() function and always return the last row in a Sheet.
function setDefaults() { var sh = SpreadsheetApp.getActive().getSheetByName("Sheet1"); var lastRow = sh.getDataRange().getLastRow(); var range = sh.getRange(2, 14, lastRow, 1); var data = range.getValues();
for (var i = 0; i < data.length; i++) { if (data[i][0] == "") { data[i][0] = 'defaultvalue'; } }
range.setValues(data); }
Edited
  • reply
  • like
Thanks! Should this be applied with an onChange or a timed triggger?
  • reply
  • like
Timed trigger.
  • reply
  • like
I'm a complete beginner with scripts so this might be a stupid question, but won't I have a lot of blank rows with just the default values in that case, creating a lot of false blank items in my app?
The getDataRange() function will return a range that only includes data. By using that the data array will only be as long as how many rows actually have data. Test it out on a test sheet and see what you get. ALWAYS test on temp sample sheets.
  • reply
  • like