How use OnEdit scripts with cells that have non-static locations (Google Sheets)?

1
votes

I am looking for help with Google Sheets on how to activate an OnEdit script for cells that do not have a static row address (but do have a defined column) and also cells that may not exist yet until created by one of these OnEdit scripts. To explain....

I am creating a google sheets workbook template to help put together project costs and compare the costs of all needed materials from competing vendors. For example, say we're building a woodshed. "Woodshed" will be project name, and each material needed to build it will be a "product" within the project (5d nails, 2x4s, etc). Then, under each product there is the option to put in multiple difference sources (home depot, Ace Hardware, etc) for the same product and their various prices.

I am attempting to set up multiple dropdown cells in Column N to function like "buttons" that will activate OnEdit scripts to insert additional, properly formatted rows for adding new material sources, or new products, or entire additional projects into the same sheet whenever someone uses a dropdown to select a specific value. E.g., open the dropdown that says "Add New Source(s) for 5d nails (select quantity)." Say you select "3" from the dropdown, it would then add 3 new, properly formatted rows under the "5d nail" product section for adding details about 3 new 5d nail vendors and their pricing.

(I am avoiding using regular clickable "buttons" because that functionality does not work on mobile versions of google sheets yet)

MY PROBLEM:

The very function of these scripts will be to add new rows, which will affect the cell address for any dropdown OnEdit cells located further down in the sheet. Likewise, each new product or project added to this workbook via these buttons will require its own, NEW "Add new..." dropdown button of its own which obviously is pretty difficult to account for in the script.

I would welcome any ideas on how to go about this, if it's even possible??

The following workbook is my work-in-progress google sheet, feel free to copy to a new workbook and play with it: https://docs.google.com/spreadsheets/d/1J3cS3FmVxycs0bbuTPyIxoKZ-C0ojSIHHc4z9DmlNqs/edit#gid=662584781

1
javascriptgoogle-apps-scriptbuttongoogle-sheetsdynamic
I don’t understand the problem onEdit triggers fire only on user edit which occur in cells that that have a row and column - Cooper
Since I don't follow links to offsite resources you will have to provide additional examples posted within your question. Currently I believe you saying that the action of an edit in a cell in a certain column initiates an action which is somehow dependent upon values that exist within the current row. Beyond that I have no additional insight in to what you are asking. - Cooper
Thanks for responding, Cooper! I tried just now to screenshot and add the image to the post, but unfortunately I don't have enough of a reputation to add images yet :( - Tim Miller
I will try to explain further....In the past I've created sheets with dropdowns cells in static locations, and set up OnEdit to activate when that cell is edited. This then allows me to perform different actions depending on which value was selected from the dropdown. In my new sheet, I would like to do the same thing, but there will be multiple dropdown "buttons" that add new rows, which will change the cell address of any buttons located below the added row, and thus will mean that the coded cell address for OnEdit will no longer be looking at the correct cell address, so it won't work. - Tim Miller
That is the first problem. The second problem is that the functions of one of the buttons I would like to have would add an entire new table to the current sheet, along with a few buttons that didn't exist beforehand. So, I also need someway of adding these new button locations to the list of cell addresses where the OnEdit script is checking for changes. Does that make sense?? - Tim Miller

1 Answers

0
votes

By studying your example I see that after choosing one option from the dropdown menu, some rows are going to get pushed in the bottom rows. I understand that you want to run an onEdit() trigger that manages those. You can do so easily, but you have to keep in mind that «onEdit(e) runs when a user changes a value in a spreadsheet». That means that the trigger will react to the user clicking in the dropdown menu, but it is blind about the rows added by the code.

So the idea here is simple: you should run the code when the trigger gets activated, not after the rows get appended. Ideally you can modify the code that adds the extra rows, so it would run an additional script with your desired goal. Please ask me for any clarifications about this approach.