# Unable to trigger an appscript onEdit after glide form submission

**URL:** <https://community.glideapps.com/t/unable-to-trigger-an-appscript-onedit-after-glide-form-submission/60837>\
**Category:** Ask for Help\
**Created:** [April 22, 2023, 11:31am UTC](https://community.glideapps.com/t/unable-to-trigger-an-appscript-onedit-after-glide-form-submission/60837 "2023-04-22T11:31:58Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![Clement\_Jabot](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/clement_jabot/32/57959_2.png) [@Clement\_Jabot](https://community.glideapps.com/u/Clement_Jabot)\
**Post date:** [April 22, 2023, 11:31am UTC](https://community.glideapps.com/t/unable-to-trigger-an-appscript-onedit-after-glide-form-submission/60837/1 "2023-04-22T11:31:59Z")

</div>

Hello all,

I’m have written an appscript to automatically create rows in sheet “Bouteilles” every time a form is submitted in my glide app (=every time a new row is created in sheet “Vins”).  
My old script base on “onEdit” was working perfectly if the new row in sheet “Vins” was created manually in google sheet but not working when a glide form was submitted. I have red in different topics that onEdit is not working with Glide integration, so i have tried to replace onEdit by onChange.  
Yet it’s not working anymore. Did I miss something?  
if someone is able to help me that’d be great !

Here is my script :

```auto
function onChange(e) {
  var vinssheet = e.source.getSheetByName("Vins"); // Récupérer la feuille "Vins"
  var bouteillesSheet = e.source.getSheetByName("Bouteilles"); // Récupérer la feuille "Bouteilles"
  var row = e.range.getRow(); // Récupérer le numéro de ligne de l'édition

  var quantiteBouteilles = e.range.getCell(1, 9); // Récupérer la valeur de la cellule éditée (quantité de bouteilles)
  var bouteillesRow = bouteillesSheet.getLastRow() + 1; // Dernière ligne dans la feuille "Bouteilles" +1 (pour insérer après la dernière ligne)
  console.log('bouteillesRow : ' + bouteillesRow);

  var sourceRange = vinssheet.getRange(row, 1); // Récupérer la plage de cellules de la colonne A à G de "Vins"
  var sourceIdVin = sourceRange.getCell(1, 1); // Récupérer la valeur de la cellule de la colonne A dans "Vins" (IdVin)
  console.log('idVin : ' + sourceIdVin.getValue());

  console.log('quantité de bouteilles à insérer : ' + quantiteBouteilles.getValue());

  // Créer x lignes dans la feuille "Bouteilles"
  for (var i = 0; i < quantiteBouteilles.getValue(); i++) {
    bouteillesSheet.insertRowAfter(bouteillesRow + i); // Insérer une nouvelle ligne après la dernière ligne dans "Bouteilles"
    console.log('ligne inséré à la position : ' + bouteillesRow + i);

    // Générer un identifiant unique basé sur le numéro de ligne
    var idBouteilles = "B" + ("0000" + (bouteillesRow + i-1)).slice(-5); // Ex: B0001, B0002, etc.

    // Mettre l'identifiant unique dans la colonne A de les nouvelles lignes de "Bouteilles"
    var targetRange = bouteillesSheet.getRange(bouteillesRow + i, 1, 1, 1); // Récupérer la plage de cellules de la colonne A dans "Bouteilles" pour la nouvelle ligne
    targetRange.setValue(idBouteilles); // Mettre l'identifiant unique dans la colonne A de "Bouteilles"

    // Copier la valeur de "IdVin" de "Vins" dans la colonne B de "Bouteilles" des nouvelles lignes
    var targetRange = bouteillesSheet.getRange(bouteillesRow + i, 2, 1, 1); // Récupérer la plage de cellules de la colonne B dans "Bouteilles" pour la nouvelle ligne
    console.log('targetRange : ' + targetRange.getValue());

    var targetIdVin = targetRange.getCell(1, 1); // Récupérer la cellule de la colonne C dans "Bouteilles"
    console.log('targetIdVin : ' + targetIdVin.getValue());

    targetIdVin.setValue(sourceIdVin.getValue()); // Copier la valeur de "IdVin" de "Vins" dans la colonne B de "Bouteilles

  }
}

```

---

<div class="post-metadata">

**Author:** ![Darren\_Murphy](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/darren_murphy/32/47326_2.png) [@Darren\_Murphy](https://community.glideapps.com/u/Darren_Murphy)\
**Post date:** [April 22, 2023, 11:49am UTC](https://community.glideapps.com/t/unable-to-trigger-an-appscript-onedit-after-glide-form-submission/60837/2 "2023-04-22T11:49:43Z")

</div>

I think you’ll find that the following line is failing:

```auto
var row = e.range.getRow();

```

This is because the onChange() trigger does not support the `range` method.  
You could confirm this by adding `console.log(row);` after that line.

When working with Apps Script and Glide, you generally need to change a known value and have your script check that to determine what course of action to take.

For example, for your case you could add a “ProcessedAt” column to your “Vins” sheet, and leave that empty when the Glide form is submitted. Then your script could check each line in the “Vins” sheet and only process those rows where that column is empty. Then as it processes each row, it could add a current timestamp to that column to avoid it being processed a second time.

It’s also a good practice to use a [Lock](https://developers.google.com/apps-script/reference/lock/lock-service?hl=en#getScriptLock()) with these types of scripts, to avoid concurrent executions.

---

<div class="post-metadata">

**Author:** ![Clement\_Jabot](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/clement_jabot/32/57959_2.png) [@Clement\_Jabot](https://community.glideapps.com/u/Clement_Jabot)\
**Post date:** [April 22, 2023, 2:58pm UTC](https://community.glideapps.com/t/unable-to-trigger-an-appscript-onedit-after-glide-form-submission/60837/3 "2023-04-22T14:58:25Z")

</div>

Thanks Darren,  
I have created a new column “ProcessedAt” in my sheet “Vins” in column O. I have been unable to update my script. Would you be able to help me on that two?  
And should I set in place a trigger ?  
Thanks for your help

---

<div class="post-metadata">

**Author:** ![Uzo](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/uzo/32/22639_2.png) [@Uzo](https://community.glideapps.com/u/Uzo)\
**Post date:** [April 22, 2023, 3:05pm UTC](https://community.glideapps.com/t/unable-to-trigger-an-appscript-onedit-after-glide-form-submission/60837/4 "2023-04-22T15:05:56Z")

</div>

Are you running it without a trigger? onChange is just a name of the function

---

<div class="post-metadata">

**Author:** ![Darren\_Murphy](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/darren_murphy/32/47326_2.png) [@Darren\_Murphy](https://community.glideapps.com/u/Darren_Murphy)\
**Post date:** [April 22, 2023, 3:13pm UTC](https://community.glideapps.com/t/unable-to-trigger-an-appscript-onedit-after-glide-form-submission/60837/5 "2023-04-22T15:13:52Z")

</div>

I’d do something like this:

```auto
function check_vins() {
  var lock = LockService.getScriptLock();
  var success = lock.tryLock(1000);
  if (!success) {
    console.warn("Could not obtain exclusive lock, aborting");
    return;
  }
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Vins');
  var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues().shift();
  var processed_at_index = headers.indexOf('ProcessedAt');
  var data = sheet.getRange(2, 1, sheet.getLastRow() - 1, sheet.getLastColumn()).getValues();
  var current_row = sheet.getLastRow();
  while (data.length > 0) {
    var row = data.pop();
    var processed_at = row[processed_at_index];
    if (processed_at === '') {
       // call your script to add rows to your other table here
      var now = new Date();
      sheet.getRange(current_row, processed_at_index + 1).setValue(now);
    }
    current_row --;
  }
  lock.releaseLock();
}

```

Obviously I haven’t tested that, because I don’t have a copy of your sheet. But it should be pretty close to what you need.

You could call the above either using an onChange trigger, or as a timed trigger. It depends how often your sheet is updated which would be better.

---

<div class="post-metadata">

**Author:** ![Uzo](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/uzo/32/22639_2.png) [@Uzo](https://community.glideapps.com/u/Uzo)\
**Post date:** [April 22, 2023, 3:17pm UTC](https://community.glideapps.com/t/unable-to-trigger-an-appscript-onedit-after-glide-form-submission/60837/6 "2023-04-22T15:17:26Z")

</div>

I would get an active row… not the last one… and check e for edit, so it won’t fire when deleting a row… I have never used LockService… what is it do? @Darren_Murphy

---

<div class="post-metadata">

**Author:** ![Darren\_Murphy](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/darren_murphy/32/47326_2.png) [@Darren\_Murphy](https://community.glideapps.com/u/Darren_Murphy)\
**Post date:** [April 23, 2023, 5:10am UTC](https://community.glideapps.com/t/unable-to-trigger-an-appscript-onedit-after-glide-form-submission/60837/7 "2023-04-23T05:10:22Z")

</div>

> [@Uzo](#):
>
> I have never used LockService… what is it do? @Darren_Murphy

It prevents concurrent executions of the same script. Imagine you have a timed trigger that runs every minute. If (for whatever reason) the script takes longer than one minute to execute, then you could have a situation where there are two instances of the script running at the same time, both processing the same data - which can lead to problems. The same can happen with onChange triggers. Using a Lock avoids this problem.

---

<div class="post-metadata">

**Author:** ![Uzo](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/uzo/32/22639_2.png) [@Uzo](https://community.glideapps.com/u/Uzo)\
**Post date:** [April 23, 2023, 5:11am UTC](https://community.glideapps.com/t/unable-to-trigger-an-appscript-onedit-after-glide-form-submission/60837/8 "2023-04-23T05:11:51Z")

</div>

tnx… i use app flush for that… it waits until other scripts finish

---

<div class="post-metadata">

**Author:** ![Darren\_Murphy](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/darren_murphy/32/47326_2.png) [@Darren\_Murphy](https://community.glideapps.com/u/Darren_Murphy)\
**Post date:** [April 23, 2023, 5:22am UTC](https://community.glideapps.com/t/unable-to-trigger-an-appscript-onedit-after-glide-form-submission/60837/9 "2023-04-23T05:22:28Z")

</div>

Do you mean [this](https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet-app?hl=en#flush)?

Can you give me an example of how you’d use that to avoid concurrent executions of the same script?

---

<div class="post-metadata">

**Author:** ![Uzo](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/uzo/32/22639_2.png) [@Uzo](https://community.glideapps.com/u/Uzo)\
**Post date:** [April 23, 2023, 5:23am UTC](https://community.glideapps.com/t/unable-to-trigger-an-appscript-onedit-after-glide-form-submission/60837/10 "2023-04-23T05:23:48Z")

</div>

yes, this one… same way as on the sample… i hope that it makes wait until the other script is finished, or I’m wrong? and I’m putting SpreadsheetApp.flush(); in the begging of the script

---

<div class="post-metadata">

**Author:** ![Darren\_Murphy](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/darren_murphy/32/47326_2.png) [@Darren\_Murphy](https://community.glideapps.com/u/Darren_Murphy)\
**Post date:** [April 23, 2023, 5:30am UTC](https://community.glideapps.com/t/unable-to-trigger-an-appscript-onedit-after-glide-form-submission/60837/11 "2023-04-23T05:30:28Z")

</div>

That’s not the way I read it. As far as I can see, all it does it apply all pending changes to a sheet immediately, rather than batching them.

Putting at the beginning of a script will apply all pending changes, but if there is another instance of the same script still running in the background, it could make further changes before it finishes that cause problems.

I think using a Lock would be a much safer approach.

---

<div class="post-metadata">

**Author:** ![Uzo](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/uzo/32/22639_2.png) [@Uzo](https://community.glideapps.com/u/Uzo)\
**Post date:** [April 23, 2023, 5:32am UTC](https://community.glideapps.com/t/unable-to-trigger-an-appscript-onedit-after-glide-form-submission/60837/12 "2023-04-23T05:32:25Z")

</div>

I also use script variables… to mark that script is running… then clear it… and other script waits until that variable is cleared.

but your function will stop running the script, right? it will not finish?

---

<div class="post-metadata">

**Author:** ![Darren\_Murphy](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/darren_murphy/32/47326_2.png) [@Darren\_Murphy](https://community.glideapps.com/u/Darren_Murphy)\
**Post date:** [April 23, 2023, 5:37am UTC](https://community.glideapps.com/t/unable-to-trigger-an-appscript-onedit-after-glide-form-submission/60837/13 "2023-04-23T05:37:32Z")

</div>

> [@Uzo](#):
>
> I also use script variables… to mark that script is running… then clear it… and other script waits until that variable is cleared.

Yeah, I also used to do that, but I found that it isn’t 100% reliable, because you can still have race conditions.

> [@Uzo](#):
>
> but your function will stop running the script, right? it will not finish?

It will do whatever you want. The whole idea is to reliably know if the same script is already running. Consider this example:

```auto
var lock = LockService.getScriptLock();
  var success = lock.tryLock(1000);
  if (!success) {
    console.warn("Could not obtain exclusive lock, aborting");
    return;
  }

```

In that example, it wasn’t able to get an exclusive lock, so it aborted. But that’s only because I added a return statement. That could have been anything - maybe call another function, whatever.  
It all depends on the use case. I think the main point is that using LockService is the most reliable way to detect concurrent executions of the same script.

---

<div class="post-metadata">

**Author:** ![Uzo](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/uzo/32/22639_2.png) [@Uzo](https://community.glideapps.com/u/Uzo)\
**Post date:** [April 23, 2023, 5:40am UTC](https://community.glideapps.com/t/unable-to-trigger-an-appscript-onedit-after-glide-form-submission/60837/14 "2023-04-23T05:40:27Z")

</div>

hmm… so it cant lock when the other script is running for 1 sec… then aborts… but is this for any script… or the same? i mean function

---

<div class="post-metadata">

**Author:** ![Darren\_Murphy](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/darren_murphy/32/47326_2.png) [@Darren\_Murphy](https://community.glideapps.com/u/Darren_Murphy)\
**Post date:** [April 23, 2023, 5:49am UTC](https://community.glideapps.com/t/unable-to-trigger-an-appscript-onedit-after-glide-form-submission/60837/15 "2023-04-23T05:49:32Z")

</div>

It prevents the same section of code running simultaneously. So you could have 1000 scripts running at the same time, each one with its own lock, and it would have no impact at all. It just stops having two instances of the same code block/function running at the same time.

---

<div class="post-metadata">

**Author:** ![Uzo](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/uzo/32/22639_2.png) [@Uzo](https://community.glideapps.com/u/Uzo)\
**Post date:** [April 23, 2023, 5:50am UTC](https://community.glideapps.com/t/unable-to-trigger-an-appscript-onedit-after-glide-form-submission/60837/16 "2023-04-23T05:50:43Z")

</div>

Thank you so much!

---

<div class="post-metadata">

**Author:** ![Clement\_Jabot](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/clement_jabot/32/57959_2.png) [@Clement\_Jabot](https://community.glideapps.com/u/Clement_Jabot)\
**Post date:** [April 23, 2023, 10:41am UTC](https://community.glideapps.com/t/unable-to-trigger-an-appscript-onedit-after-glide-form-submission/60837/17 "2023-04-23T10:41:37Z")

</div>

First when my script was working for manual inputs, I was using onEdit which was triggered automatically. With the script I copied, I set in place a trigger on change but it wasn’t working anymore.

---

<div class="post-metadata">

**Author:** ![Clement\_Jabot](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/clement_jabot/32/57959_2.png) [@Clement\_Jabot](https://community.glideapps.com/u/Clement_Jabot)\
**Post date:** [April 23, 2023, 10:52am UTC](https://community.glideapps.com/t/unable-to-trigger-an-appscript-onedit-after-glide-form-submission/60837/18 "2023-04-23T10:52:32Z")

</div>

Hi Darren,  
Thank you for taking the time !  
I must precise that I was able to write the first script with the help of chat gpt. I’m struggling with the solution I understood that I need to replace “//call your script…” with my function but I’m struggling. If it’s not too much to ask and you can take a look … here is a copy of my sheet and script :

> **[CaveIn Glide](https://docs.google.com/spreadsheets/d/1eSD50AOdYzU8YdH5koyHjuzWsRnZeIYsD3nbozEBeBM/edit?usp=sharing)**
>
> Vins
> 
> idVin,etiquette,region,sousRegion,appellation,cru,millesime,couleur,quantiteAchat,prixUnitaire,referenceAchat,debut,fini,dateCreation,ProcessedAt,note,commentaire
> V0001,Sud-Ouest,Cahors,Cahors,Château Tourmentine,2006,Rouge,5,8,5,Leclerc...

---

<div class="post-metadata">

**Author:** ![Darren\_Murphy](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/darren_murphy/32/47326_2.png) [@Darren\_Murphy](https://community.glideapps.com/u/Darren_Murphy)\
**Post date:** [April 23, 2023, 12:30pm UTC](https://community.glideapps.com/t/unable-to-trigger-an-appscript-onedit-after-glide-form-submission/60837/19 "2023-04-23T12:30:26Z")

</div>

I can see your sheet, but I can’t see your script as I would need edit access for that.  
However, having now seen your sheet (especially the Bouteilles sheet), it seems quite apparent to me that you are trying to use Apps Script to do something that most likely would be trivial to do with a handful of Glide computed columns.

If I can show you how to do what you need without using Apps Script, are you open to that?

If yes, can you please explain the purpose of the Bouteilles sheet?

---

<div class="post-metadata">

**Author:** ![Clement\_Jabot](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/clement_jabot/32/57959_2.png) [@Clement\_Jabot](https://community.glideapps.com/u/Clement_Jabot)\
**Post date:** [April 23, 2023, 12:47pm UTC](https://community.glideapps.com/t/unable-to-trigger-an-appscript-onedit-after-glide-form-submission/60837/20 "2023-04-23T12:47:56Z")

</div>

Oh that would be great ! I’m trying to build an app to manage personal wine cellars. The user would add a wine (“vin” in french) in Glide through a form. For each wine he can have x bottles (“bouteilles”. in french) of that wine. And then he will place the bottles in specific spot in his wine cellar (“cave” in french).  
That’s why after the user has created a new wine, I have created a script to create x bottles for that wine. And finally the user is able to place the bottles in empty spots of wine cellar.  
Is it clear or do you want me to further explain the context ?

[Next page](https://community.glideapps.com/t/unable-to-trigger-an-appscript-onedit-after-glide-form-submission/60837.md?page=2)