0

I have a Google Sheet with a specific number of rows, and I want to use a script/macro to move a certain row to the very bottom.

screenshot1

const DESTINATION_INDEX = 4;
function myFunction() {
  const ss = SpreadsheetApp.getActive();
  const foo = ss.getRangeByName('foo');
  foo.getSheet().moveRows(foo, DESTINATION_INDEX);
}

That causes it to end up at row 3 (as the moveRows() documentation and example suggest): screenshot2

But when DESTINATION_INDEX = 5, it throws Exception: Those rows are out of bounds.

I could work around this by moving foo to the penultimate row, then moving the last row up by one. Or I could do a little insertRowAfter/deleteRows dance.
But is there any way to move a row directly to the bottom? This seems like an oversight in the API.

3
  • I think this is really impossible and I have opened an issue for it: issuetracker.google.com/issues/230005401 Please star the issue if you can replicate.
    – Jacktose
    Apr 21, 2022 at 19:39
  • 1
    Hi, interesting problem. My reaction was the opposite of yours. Considering that Row#5 doesn't exist, I'm not surprised by an error message for an INDEX of 5. OTOH, when the INDEX is 4, I'm not impressed that the data may end up at a different index than originally specified. What is the point of that? You nominate row#4, row#4 exists, yet it doesn't move the row to row #4, it moves it to Row#3 instead! My preference would be that they delete this function and start over.
    – Tedinoz
    Apr 23, 2022 at 0:44
  • @Tedinoz yeah it's a tough UI question, because the move changes the row numbers, so you need to specify the target either in terms of “old row index” or “new row index.” Unless you made it a relative number of rows?
    – Jacktose
    Apr 24, 2022 at 2:00

1 Answer 1

1

"moveRows/insertRowAfter/deleteRows" Dance

My first reaction was that Jacktose was wrong and I wrote this to prove it. But as Jacktose comments - this is merely the "dance". I was inclined to delete it, but I'll leave it here (not as an answer to Jacktose) but in case someone wants to use moveRows and they need to know the rest of the steps in the dance.

function wa_164152() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet()

  // get the last row and the number of rows
  var lastRow = sheet.getLastRow() // last row with data
  var maxRows = sheet.getMaxRows() // the last row
  //Logger.log("DEBUG: the last row = "+lastRow+" and the max rows = "+maxRows)

  // variable if rrequired to insert new row
  var newrow = 0
  
  // test if the last row = the number of rows
  // if yes, then insert a new row
  if (lastRow === maxRows){
    // set trigger for new row
    var newrow = 1
    // insert a new row
    sheet.insertRowAfter(maxRows)
  }

  
  // define row to move
  const foo = ss.getRangeByName('foo');
  //Logger.log("DEBUG: foo = "+foo.getA1Notation()+" row = "+foo.getRow())

  // create variable for the destination row 
  var targetrow = lastRow+1
  
  // move the row
  sheet.moveRows(foo, targetrow);

  // check whether a new row was inserted
  if (newrow === 1)
  {
    // delete the last row
    sheet.deleteRow(lastRow+1)
  }

}

BEFORE//AFTER

 beforeafter

2
  • That's the “insertRowAfter/deleteRows dance” I mentioned as a workaround. And yes, my real script determines the rows programmatically; this is a minimum reproducible example.
    – Jacktose
    Apr 22, 2022 at 16:41
  • This is roughly the solution I ended up going with, so I think it's good to have it documented. At least until/if Google updates the API.
    – Jacktose
    Apr 24, 2022 at 1:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.