CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetstriggersgoogle-forms
user1284567
user1284567

Reputation: 141

onEdit() function does not triggered when change was made by automatic script

my onEdit() function calling to other function when there is a change in the sheet.

if the user makes the change all works fine. but if the change was made by google-form (the form fill some cells with the answers it gets) onEdit() does not trigger.

do I miss something?

Upvotes: 0

Views: 6315

Answers (4)

Pawan Choudhary
Pawan Choudhary

Reputation: 1

If I make changes manually, DATE function is triggered and it works fine. But if the changes is not made by me and it is made automatically based on some condition, onEdit() is not triggered.

Can anyone help how can I achieve my requirement? I need the date/time to capture automatically whenever there is any change in a cell of google sheet. This is image of my Google sheet. enter image description here

Find below my function.

----------------------------------------------------------------------------
function onEdit(e){

  if( e.range.getSheet().getName() === "TEST" ) { 
    if(e.range.columnStart === 3 && e.range.rowStart >= 2){
       if(e.value === "SORT" || e.value ==="lONG"){
           e.range.offset(0,1).setValue(new Date());       
        }
      }
    }
}

Upvotes: 0

Filip Blomqvist
Filip Blomqvist

Reputation: 45

What i have done is that i created an extra sheet with a cell that is the same where the primarecell is(the cell that changes). Leave it for now. I open the scriptapp and write down the code(the code that should happen if the cell changes). Then i wrte down "if" and take the two value i both cells and make an trigger that goes on every minute. With that said, if The cells are the same the "if" is looping on "true". But if something change on the primary cell, it will be "false" and you use "else". Under"else" you should have what ever function you want when soemthing change in that cell. + You must have a funtion where you insert the new value form the primarycell to the "check cell" if u want this too function as loop. Otherwise the "if" code will loop "false" every minute becuse the both cells are not the same.

My english and explaining is not that good:(

Upvotes: 1

tehhowch
tehhowch

Reputation: 9872

Yes, you forgot to read the documentation for the simple triggers:

onOpen(e) runs when a user opens a spreadsheet, document, or form that he or she has permission to edit.
onEdit(e) runs when a user changes a value in a spreadsheet.
onInstall(e) runs when a user installs an add-on.
doGet(e) runs when a user visits a web app or a program sends an HTTP GET request to a web app.
doPost(e) runs when a program sends an HTTP POST request to a web app.

and installed triggers:

Even though installable triggers offer more flexibility than simple triggers, they are still subject to several restrictions:

  • They do not run if a file is opened in read-only (view or comment) mode.

  • Script executions and API requests do not cause triggers to run. For example, calling FormResponse.submit() to submit a new form response does not cause the form's submit trigger to run.

  • Installable triggers always run under the account of the person who created them. For example, if you create an installable open trigger, it will run when your colleague opens the document (if your colleague has edit access), but it will run as your account. This means that if you create a trigger to send an email when a document is opened, the email will always be sent from your account, not necessarily the account that opened the document. However, you could create an installable trigger for each account, which would result in one email sent from each account.

  • A given account cannot see triggers installed from a second account, even though the first account can still activate those triggers.

Consider what would happen if your onEdit(e) was activated by programmatic changes, such as if your onEdit function alters the spreadsheet values...

In your situation, where you want form submission to activate your on edit function, You will need to install a form submission trigger (there is no simple trigger for form submissions).

An example function to receive your form submission trigger:

function giveMeAnInstalledFormSubmitTrigger(formSubmitEventObject) {
  if(!formSubmitEventObject) throw new Error("You called this from the Script Editor");
  var newEventObject = /* do something with the formSubmitEventObject */;
  // Call the on edit function explicitly
  onEdit(newEventObject);
}

You can read more about the event objects that triggered functions receive in the Apps Script documentation: https://developers.google.com/apps-script/guides/triggers/events

Upvotes: 3

James D
James D

Reputation: 3152

onEdit() will not be triggered by another script editing a sheet or by a form submission.

You can use onFormSubmit(e) instead depending on what your function does it would be a good idea to use the e parameter of the trigger.

https://developers.google.com/apps-script/guides/triggers/events#form-submit

Upvotes: 3

Related Questions