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 :
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
}
}
I think you’ll find that the following line is failing:
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 with these types of scripts, to avoid concurrent executions.
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
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.
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
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.
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
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.
Yeah, I also used to do that, but I found that it isn’t 100% reliable, because you can still have race conditions.
It will do whatever you want. The whole idea is to reliably know if the same script is already running. Consider this example:
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.
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.
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.
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 :
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?
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 ?