CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
maxloo
maxloo

Reputation: 491

GAS/Google Sheets is wrong in the application of insertRows, insertRowAfter, insertRowsAfter

I've tried a few ways to code for insertion of rows, but they give the wrong outputs repeatedly.

In the spreadsheet, when I type IF in cell C2, a blank row should be inserted AFTER the C2 IF. This happens and is correct. But when I subsequently type IF in cell C3, the GAS interpreter inserted a blank line at the C3 row and pushed the C3 IF to C4. This should not happen. It should insert a blank row AFTER the row where the C3 IF was at, but it did not. The same thing happens when I use AND, WHEN and OR. Each of these have different insertion codes. There should not be any blank rows between the rows of IF, AND, WHEN and OR. Is there a solution for this?

I've shared my simplified Google Sheet and the reduced code (a minimal reproducible example) at:

https://docs.google.com/spreadsheets/d/11skN3VLxSqQOqT7NX6tXTUY6kFYivbr9w-Yn2pI4z1s/edit?usp=sharing

And here's the reduced code for ease of analysis:

function onEdit(e) {
  let c = e.range
  let cvalue = e.value;

  if (cvalue == "IF") {
    c.getSheet().insertRowAfter(c.rowStart);
  }
  if (cvalue == "AND") {
    c.getSheet().insertRows(c.rowStart + 1,1);
  }
  if (cvalue == "WHEN") {
    c.getSheet().insertRowsAfter(c.rowStart, 1);
  }
  if (cvalue == "OR") {
    c.getSheet().insertRowAfter(c.getRowIndex());
  }
}

I've also attached 2 pictures: the first of which is what's supposed to happen, and the other is the wrong output because the GAS interpreter malfunctioned:

correct output wrong output

Upvotes: 4

Views: 336

Answers (1)

TheMaster
TheMaster

Reputation: 50799

  • This is a web user interface bug and should be reported to Google sheets team through Menu "Help"> "Help Sheets improve". You may add a link to this answer.

Sequence of events:

  • Selection at C2
  • User inputs IF in C2 and presses enter
  • Selection at C3 after user presses enter button
  • Script activates and inserts a new row after C2
  • After new row insertion, Old Row 3 is now Row 4
  • Selection should be at C4(and it is at C4), but visibly, on the web user interface, the selected border(blue border signifying selection) is at the new Row 3 instead of the old Row 3, which is now Row 4.
  • User not knowing that the selection is at C4( because the UI lied that it is at new C3) types something in, but now, UI recognizes it's lie, repents and shows the selection at C4.

Workarounds:

  • Use tab instead of enter
  • Move selection up and down after new row creation
  • Automatically select a previous row and a new row using script(.activateAsCurrentCell()) to make UI repent soon and seamlessly.

Workaround script:

function onEdit(e) {
  const c = e.range
  const cvalue = e.value;
  console.log({rg:c.getA1Notation(),e})

  if (cvalue == "IF") {
    c.getSheet().insertRowAfter(c.rowStart);
  }
  if (cvalue == "AND") {
    c.getSheet().insertRows(c.rowStart + 1,1);
  }
  if (cvalue == "WHEN") {
    c.getSheet().insertRowsAfter(c.rowStart, 1);
  }
  if (cvalue == "OR") {
    c.getSheet().insertRowAfter(c.getRowIndex());
  }
  if(/OR|WHEN|AND|IF/g.test(cvalue)){
   c.activateAsCurrentCell();
   SpreadsheetApp.flush();
   c.offset(1,0).activateAsCurrentCell()
  };
}

Upvotes: 2

Related Questions