Trigger Help Needed - I think

[Luis Gomez]

Hello!

I am having issues with a trigger not running when a cell is filled in with data from another sheet. 

On the sheet that I am trying to run the trigger I have the following function bringing unique information in:

=(UNIQUE('Responses'!D1:D))

This is working great. When a unique entry is entered the data shows up.

Then I have this script that I want to trigger when the above function gets a new entry.

function onEdit(e)
{
var sheet = e.source.getActiveSheet();
if (sheet.getName() == "Calculations") //"order data" is the name of the sheet where you want to run this script.
{
var actRng = sheet.getActiveRange();
var editColumn = actRng.getColumn();
var rowIndex = actRng.getRowIndex();

var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues();
var dateCol = headers[0].indexOf("Start Date") + 1;
var orderCol = headers[0].indexOf("Name") + 1;
if (dateCol > 0 && rowIndex > 1 && editColumn == orderCol)
{
sheet.getRange(rowIndex, dateCol).setValue(Utilities.formatDate(new Date(), "UTC+8", "MM-dd-yyyy"));
}
}
}

The script runs but only when I delete the data and it gets regenerated by the Unique function. 

Any help will be appreciated.

Thank you!

Luis

[Clark Lind]

I think because the 'unique()' sheet function is really just a display filter, it has no idea what the actual content is, the data length, etc. Does the data ever change, or do rows ever get removed? 

[Luis Gomez]

Nothing changes until I delete the unique item. After deletion the cell gets repopulated with the unique value that I just deleted and that triggers the script. I think I see what the problem is thanks to your comment. The unique value is just a display so that doesn't make the trigger go, when I delete the item that is what is making it run. I need to come up with another automated way to make the onEdit trigger go.

[Jonathan Butler]

The onEdit trigger only runs when a human interacts with the Google sheet. So it won't work when you have a cell automatically filled with data from another sheet by script. Alternatively, you can use a custom function to trigger a web app call to achieve the same result. You need to call the web app because a custom function lacks the permissions to be able to edit the sheet in any way. 

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/84a99ea0-bb54-4026-ac52-ac8e04a71760n%40googlegroups.com.

[Jonathan Butler]

Honestly, after looking at your code. You should be able to do this with only the custom function. Just pass the date and order columns to the function and return the result you want.