Google Sheet: Shift Data Down One row or Add One Row Above Data When Cell B2 Is Not Empty

Question

In Google Sheets I am looking to automatically shift all rows down 1 row after inserting data into B2.

I'm looking to maintain a clear Row 2, no matter how much data is inserted, such as the example provided below:

Row 1   Data1   Data2   Data3   Data4   Data5   Data6
Row 2                       
Row 3   asdasd  zxczxc  qweqwe  asdasd  zxczxc  qweqwe
Row 4   asdasd  zxczxc  qweqwe  asdasd  zxczxc  qweqwe
Row 5   asdasd  zxczxc  qweqwe  asdasd  zxczxc  qweqwe
Row 6   asdasd  zxczxc  qweqwe  asdasd  zxczxc  qweqwe
Row 7   asdasd  zxczxc  qweqwe  asdasd  zxczxc  qweqwe
Row 8   asdasd  zxczxc  qweqwe  asdasd  zxczxc  qweqwe
Row 9   asdasd  zxczxc  qweqwe  asdasd  zxczxc  qweqwe

I understand I can manually insert rows but I'm looking for a method to speed up data entry at work. Any input is appreciated. I wouldn't mind if I could bundle a function into a button at the top of the sheet if that's possible.

Answer 1

The best and most effective solution is to create a macro to insert a new row above Row2.

To create the macro:
1 - From the main menu, choose "Tools", "Macros", "Record macro". The macro status shows at the bottom of the screen; you can ignore it for the time being.
2 - Highlight Row 2 in the "row column" at the far left of the screen; right-click on Row2, then choose "Insert 1 above". 3 - Now go to macro status at the bottom of the screen and click "Save" and give your macro a sensible name, and a keyboard shortcut.
4 - Now run your macro using the keyboard shortcut, or go back to the main menu, "Tools", "Macros". Your macro will appear at the bottom of the panel; click the macro name.
5 - The system will advise Authorisation is required. Click Continue and follow the prompts to give authorisation.

That's it.

You can create a button to link to the macro. I'll leave that to you to research. One tip: put the button in/on/above/ Row 1, otherwise as you insert rows, your button will move progressively down the screen.

FWIW, Infoinspired have a good tutorial on How to Record and Run Macros in Google Sheets.

Answer 2

I wanted to do something similar: Keep Frozen Row 1 with Headers Add row 2 when I type in cell A2 so that I can add data to top of sheet instead of scrolling to the bottom.

Thanks to a colleague, Domanik, who worked this through with me and other helpful folks on stackoverflow, we created this script:

function onEdit(e) {
  var myRange = SpreadsheetApp.getActiveSheet().getRange('A2:A2000'); //<<< Change Your Named Ranged Name Here

  //Let's get the row & column indexes of the active cell
  var row = e.range.getRow();
  var col = e.range.getColumn();

  //Check that your active cell is within your named range
  if (col >= myRange.getColumn() && col <= myRange.getLastColumn() && row >= 
    myRange.getRow() && row <= myRange.getLastRow()) { //As defined by your Named Range
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getSheets()[0];
    sheet.insertRowBefore(2);//Replace Your Custom Code Here
  }
}