Google Sheets AppsScript for inserting new rows based on dropdown values

Question

I have some dropdown menus in my sheet. When a selection is made in one of them, I want a number of new rows to be inserted in the sheet. Specifically, I want to be able to add the row(s) either above or below a specific existing row that I specify by the text present in column C. I then want the inserted row to populate text of my choosing in column C. Lastly, I want any inserted row(s) to continue the formula pattern I have in columns B and D.

Example spreadsheet here: https://docs.google.com/spreadsheets/d/1tIYnuJU1t3tl0kk4nTqNB7zj4ndXM6R2PpujDao8Uwc/edit#gid=0

For Example: If the "tiger" option in Menu 1 is selected, insert three rows after the "First step" Process Step Description, and name their Process Step Descriptions "has stripes", "is orange and black". and "has sharp teeth".

I still have so much trouble writing these scripts... I know how to insert a row now and do simple things like "getActiveSpreadsheet" but I get confused by much more. I get started and end up with something convoluted that doesn't work.

Below is my best attempt. I know it doesn't make much sense as it is a mish-mash of a few different scripts I've had help completing for other tasks. I've put some placeholders in square brackets for the reference I want to make but don't know how.

function DynamicFlowchart(range, sourceRange){
var rule = SpreadsheetApp.requireValueInRange(sourceRange, true).build();
range.setValue(rule);

function onEdit (){
var aCell = SpreadsheetApp.getActiveSheet().getActiveCell();
var aColumn = aCell.getColumn();
var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var range = SpreadsheetApp.getActiveSheet().getRange(aCell.getRow(), aColumn + 1);
var sourceRange = SpreadsheetApp.getActiveSpreadsheet().getRangeByName(aCell.getValue());


 if(aCell=="tiger"){
  ss.insertRowAfter([the process step description that says "first step"]);
  ss.getRange(ss.getActiveRange().getRowIndex(), 3).setValue("has stripes");
  ss.insertRowAfter([the process step description that says "has stripes"]);
  ss.getRange(ss.getActiveRange().getRowIndex(), 3).setValue("is orange and black");
  ss.insertRowAfter([the process step description that says "is orange and black"]);
  ss.getRange(ss.getActiveRange().getRowIndex(), 3).setValue("has sharp teeth");
}
}
}

Any help is appreciated.

Thanks in advance.

If the "tiger" option in Menu 1 is selected, insert three rows after the "First step" Process Step Description, and name their Process Step Descriptions "has stripes", "is orange and black". and "has sharp teeth". Would you please explain: how does one know 1) "three" rows are to be inserted, 2) to inserted the rows after row#2 (you said add the row(s) either above **OR** below a specific existing row), 3) the descriptions are "has stripes", "is orange and black" and "has sharp teeth"? I sympathise with the learning curve for scripting but would please include your best script. — Tedinoz
– Tedinoz, Commented Jun 21, 2023 at 11:50
Your "best" script was worth posting. I hope that you can compare it to the answer and see some of the differences. — Tedinoz
– Tedinoz, Commented Jun 29, 2023 at 2:12

Tanaike · Accepted Answer · 2023-06-29 02:43:20Z

You want to respond to dropdown "(Menu1)" by:

inserting three rows below
adding certain process steps, and
removing the relevant Data Validation in the new rows (added by Tedinoz)

The following differences between the OP's script and this answer are worth noting:

onEdit(e) - the answer makes use of the onEdit Event Objects. This simplifies much of the scripting.
- I have left a number of Logger.log statements in the code that the OP can use to identify the status of variables
- Also note: Logger.log(JSON.stringify(e)) which will list ALL of the event objects for a given edit.
if(aCell=="tiger"){ Vs if (editedSheetName != "Input" || editedCol !== 1 || editedValue !== "tiger"){
- if(aCell=="tiger"){ = good
- if (editedSheetName !== "Input" || editedCol !== 1 || editedValue !== "tiger"){ = better
- as the OP's spreadsheet becomes more complex, it will become important to make sure that the edit is on a given sheet, and in a given column.
!== AND ||
- combination of !== and || will identify an "invalid" edit and enable the user to stop the script
editedCell.offset(1, 1, 3).setValues(steps)
- a single line to the project descriptions
- update of the three cells is done by using an array steps to update a range defined by using offset

function onEdit(e) {
  // Logger.log(JSON.stringify(e)) // DEBUG
  var editedCol = e.range.columnStart
  var editedRow = e.range.rowStart
  var editedValue = e.value
  var editedSheet = e.range.getSheet()
  var editedCell = editedSheet.getRange(editedRow, editedCol)
  var editedSheetName = editedSheet.getName()
  // Logger.log("DEBUG: edited value = "+editedValue+", edited row = "+editedRow+", edited column = "+editedCol)
  // Logger.log("DEBUG: edited sheet name = "+editedSheet.getName()+", the edited cell Range = "+editedCell.getA1Notation())

  // test for edit on sheet:"Input", in Column:A and value:"tiger"
  if (editedSheetName !== "Input" || editedCol !== 1 || editedValue !== "tiger") {
    // Logger.log("DEBUG: INVALID edit: sheet <> input &/or col <> 1 &/or value <>tiger")
    return
  } else {
    // Logger.log("DEBUG: VALID edit: sheet = input & col = 1 & value =tiger")
    // insert blank rows
    editedSheet.insertRowsAfter(editedRow, 3);
    // insert process description steps (2 columns to thge right)
    var steps = [["has stripes"], ["is orange and black"], ["has sharp teeth"]]
    editedCell.offset(1, 1, 3).setValues(steps)
    // delete validation for the new rows
    editedSheet.getRange(3, 1, 3).setDataValidation(null)
  }
}

AFTER

Thank you so much! And much appreciate your detailed learning notes. A couple followup questions I'm looking to solve... 1) How do I continue the numbering in columns B and D as new rows are added? 2) How can I specify that I want to insert the rows after a specific Process Step Description? I looked around and my best guess is to use something like the following: var getrowindex = getRange(1, 1, getLastRow(), 1).getValues(); for(var i = 0; i < getrowindex.length; i++){ if(getrowindex[i] == "First step"){ Logger.log(getrowindex[i]) } }
How can I specify that I want to insert the rows after a specific Process Step Description? This question dealt with a specific operation and you didn't provide any context for the spreadsheet scenario and relevance of the operation. The question is silent on details relating to "the rows after a specific Process Step Description", how the dropdowns for menu 1, 2 and 3 are inserted in specific rows, and other aspects. This is too much to be addressed here; please post a new question and provide a comprehensive explanation of your scenario and the outcome that you want.
How do I continue the numbering in columns B and D as new rows are added? The question is silent on the logic behind the numbering in Columns B and D before &/or after inserting new rows. More information is required (too much to do here) and I suggest that you post a new question and include a detailed explanation of the purpose/logic of the numbering in columns B and D.

Collectives™ on Stack Overflow

Google Sheets AppsScript for inserting new rows based on dropdown values

1 Answer 1

4 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Related