I am working on a script that I can't get quite right. I need to get a script that will allow me to insert an empty row above the previous top row.

So, row 2 is always the top, but when row 2 is edited, I want it to automatically create another row above it, taking its place as row 2. I was sort of able to achieve this by creating a column I've called "Key" and when I add the text "AUTOADD" to that column it will autoadd a row above. However, I would ideally like to not have to add that text to get it to work.

Here is what I have:

function onEdit(e) {
  // get sheet
  var sheet = SpreadsheetApp.getActive().getSheetByName("ChatAgenda");

  // Get the edited range
  var editedRange = e.range;

  // check if cell A2 was edited
  if (editedRange.getA1Notation() === "A2") {
    // check if value is a desired value
    if (editedRange.getValue() === "AUTOADD") {
      // if yes to both, insert new row
      sheet.insertRowBefore(2);
    }
  }
} 

CodePudding user response：

How about inserting a checkbox in A1 of ChatAgenda

Then use this code:

function onEdit(e) {
  const sh = e.range.getSheet();
  if (e.range.columnStart == 1 && e.range.rowStart == 1 && e.value == "TRUE") {
    e.range.setValue("FALSE");//Reset the checkbox
    sh.insertRowBefore(2);
  }
} 

When you check the checkbox it adds and row and resets the checkbox