Google Sheets, Insert Partial Row

Question

I have a script that I use to insert 'transactions' into a sheet. The actual transaction record is only about 5 columns wide. It was OK to insert a new row every time because no other columns were in use. But now I want to create a sub list of transactions to the right for a quick break down of the most common Type. I don't want to use a column filter because I share the document in a read only mode and for obvious reasons, some persons are not allowed to modify the document but still need to see the sub list. When the script inserts the new row, then the references from the rest of the sheet are altered too, even with with the $ prefix for the row portion of the cell reference.

function InsertNewTransactionOnDemandAccount( date, value, title, fontWeight, fontType, type, account )
{
  if( isNaN(value) ) return;
  var activeSpreadsheetApp = SpreadsheetApp.getActive();
  var activeSpreadsheet = activeSpreadsheetApp.getSheetByName( 'On Demand Account' );
  var activeCell = activeSpreadsheet.getRange( 6, 1 );
  var activeRow = activeCell.getRow();
  var activeColumn = activeCell.getColumn();

  activeSpreadsheet.insertRowBefore( activeRow );

I have scoured the various forums that I can find on the subject but I can't find a solution. The line at insertRowBefore is where I would like to just insert a five column wide row.

Below is a redacted screen shot. Yes, it is an allowance and spending tracker for my kids. Yes, I needed to with hold the amounts for their privacy and mine too.

Iamblichus Iamblichus · Accepted Answer · 2020-05-27T08:49:07

You can use moveTo(target) to shift the first 5 columns one row down, while ignoring the rest of columns.

Just replace this line:

activeSpreadsheet.insertRowBefore(activeRow);

With this:

var rangeToMove = activeSpreadsheet.getRange(activeRow, 1, activeSpreadsheet.getLastRow() - activeRow + 1, 5);
rangeToMove.moveTo(rangeToMove.offset(1, 0));

Notes:

I assumed that the range to shift down corresponds to the first 5 columns (A to E). Please edit the second and/or fourth parameter of getRange if that's not the case.

Google Sheets, Insert Partial Row

1 Answers

Notes:

Reference: