Triggering onEdit() function in .gs when updating sheet through the app

Hi everyone,

I’m a newb programmer and I hacked through some google script tutorials to get a spread sheet that auto-updates certain cells when they are edited.

For example, I have a field for the street address, the city, and the state, and zip… and then a field for the complete shipping address which is a concatenation of those 4 fields. Also, the sheet autogenerates an order number every time any field is edited. I’m happy to show the code if anyone is interested. It’s not anything elegant or novel…

Anyway, the question I have is, will the google script run when the sheet is updated through the app? It seemed like it does not run. So for example if I enter something on a blank line on google sheets the order number is auto generated, but when I enter a new order in the app it doesn’t seem to run any of the google script code I wrote into the addEdit() function.

Thanks and having a blast making apps.

Peter

Hi…I wouo like to know how the order number is been generated
…the script

can you please post the script you wrote and which cells is the app writing to

this is the google script. the app is writing to a few fields, one of them is pie count and one of them is address. The app writes to the correct fields but the script doesn’t seem to run.

function onEdit(x) {
addOrder(x)
}

function addOrder(x){

var row = x.range.getRow();
var col = x.range.getColumn();

var price1 = 8.99
var price2 = 10.99

var editDate = new Date();

var orderNumber = row - 1;

var streetAddress = x.source.getSheetByName(“Orders”).getRange(row, 15).getValue();
var city = x.source.getSheetByName(“Orders”).getRange(row, 16).getValue();
var state = x.source.getSheetByName(“Orders”).getRange(row, 17).getValue();
var zip = x.source.getSheetByName(“Orders”).getRange(row, 18).getValue();
var commaSpace = ", ";
var deliveryAddress = streetAddress + commaSpace + city + commaSpace + state + commaSpace + zip

var pie1Count = x.source.getActiveSheet().getRange(row, 7).getValue();
var pie2Count = x.source.getActiveSheet().getRange(row, 8).getValue();
var pie3Count = x.source.getActiveSheet().getRange(row, 9).getValue();
var pie4Count = x.source.getActiveSheet().getRange(row, 10).getValue();

var customerName = x.source.getActiveSheet().getRange(row, 12).getValue();
var customerPhone = x.source.getActiveSheet().getRange(row, 13).getValue();

var totalPieCount = pie1Count + pie2Count + pie3Count + pie4Count;

var subtotal = pie1Count * price1 + pie2Count * price1 + pie3Count * price1 + pie4Count * price2;

if(row > 1 && x.source.getActiveSheet().getName() == “Orders”) {
if(x.source.getActiveSheet().getRange(row, 1).getValue() == “”) {
x.source.getActiveSheet().getRange(row, 1).setValue(orderNumber);
x.source.getActiveSheet().getRange(row, 2).setValue(editDate);
x.source.getActiveSheet().getRange(row, 4).setValue(editDate);
x.source.getSheetByName(“Pie Makers”).getRange(row, 1).setValue(orderNumber);
}
x.source.getSheetByName(“Orders”).getRange(row, 4).setValue(editDate);
x.source.getSheetByName(“Orders”).getRange(row, 5).setValue(totalPieCount);
x.source.getSheetByName(“Orders”).getRange(row, 6).setValue(subtotal);
x.source.getSheetByName(“Orders”).getRange(row, 14).setValue(deliveryAddress);
x.source.getSheetByName(“Pie Makers”).getRange(row, 2).setValue(editDate);
x.source.getSheetByName(“Pie Makers”).getRange(row, 3).setValue(totalPieCount);
x.source.getSheetByName(“Pie Makers”).getRange(row, 5).setValue(customerName);
x.source.getSheetByName(“Pie Makers”).getRange(row, 6).setValue(customerPhone);
x.source.getSheetByName(“Pie Makers”).getRange(row, 7).setValue(pie1Count);
x.source.getSheetByName(“Pie Makers”).getRange(row, 8).setValue(pie2Count);
x.source.getSheetByName(“Pie Makers”).getRange(row, 9).setValue(pie3Count);
x.source.getSheetByName(“Pie Makers”).getRange(row, 10).setValue(pie4Count);
}
if(row > 1 && x.source.getSheetByName(“Orders”).getRange(row, 3) == “TRUE”){
x.source.getSheetByName(“Pie Makers”).getRange(row, 13).setValue(“TRUE”);
}
}

Why are you not using glide to add up your totals, create the concatenation, and create the order number?
Everything that your asking for glide can do for you without needing a script. can you send me a link to your app so that I may look at it?

I am still learning?

also, I want to be able to edit the spreadsheet directly and have things like the date edited update…

Ok, in glide have you watched any of the tutorials on relations? If you have not I recommend taking a look at them. You can setup relationships that allow you to lookup items and combine data from different areas. Also if you are adding the customer information directly to your sheets and your sheets only you can setup glide to edit the information and it would make it much simpler.

If you dont mind in detail explain the layout or send me a link and I can set this up the way you are describing.

I looked at the relations tutorial. I will have another look.

I sent you a message with the link to the app.

Right now my friend is running her pie business off a google sheet and the drivers she is working with have to do everything through text and input addresses by hand etc. I am just trying to find a way to automate some of that but they need the functionality of the sheet more than the app right now. Do you know if there is just some way to get the .gs file to run when the app accesses the sheet?

thanks for your help btw.

p

There is a way but looking at it I fear it would break again. If you dont mind. Send me the google sheet link. I can rebuild this for you in like an hour. I dont need names and addresses or anything just a template that shows which columns they are in.

I don’t mind sending you the google sheet but I am still without a lot of details from the friend I am working this out for. so it might be a bit frustrating. I think it’s better if I can learn from you, thanks.

but again, there is no way to use the .gs script outside of editing the sheet?

No problem. The issue using the script is that your making it do all the work for you. It creates a delay in the app. If the app is not a paid for (pro) app then you wont see the updates immediately. Not to mention that google sheets only updates onedit as well. So best case scenario you have a 2 to 5 minute update on your hands. Have you worked with google sheets or excel in the past? If so that is great, I would start with the formulas that the script is running and converting them to formulas in the cells.

If they are formulas in the cells the user will break it.

2 to 5 min update isn’t a killer but I am not ready to go pro…

there is a onchange command
but the problem with google sheets is that they update when necessary
go into file>spreadsheet settings>calculation and then set it to update evern minute and on change.

you can lock those cells

ok, so instead of onEdit use onChange? I will look up that function.

but then they also have to know to copy them and the date/time stamp is important too which could easily be forged if not scripted.

There is a way to prevent all of that. The timestamp can be done through glide as well. I do not charge to make the app. If you have a template or can get one I can make the app using it, send it to you and you can see what I have done. I think it would be a better approach than the scripting. Scripting is mostly for automation which in this case you really don’t need.