How to make Spreadsheet automatically add new rows?

user_15912282 0
Viewed 885
  • My spreadsheet just importrange('SheetName';'A:A') from another spreadsheet, the number of rows will be more and more and sometimes it will get full causing #Ref error, I have to manually go in and add new rows. Is there a way to have the spreadsheet auto add rows on its own every time Importrange() needs to add rows? (Because this is an intermediate spreadsheet and the results will go to another spreadsheet). Thanks!
google-apps-script google-sheets
1 Answers

You can try using this function

function insertRow() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange('A1').activate();
  spreadsheet.getCurrentCell().getNextDataCell(SpreadsheetApp.Direction.DOWN).activate();
  spreadsheet.getCurrentCell().getNextDataCell(SpreadsheetApp.Direction.DOWN).activate();
  spreadsheet.getActiveSheet().insertRowsAfter(spreadsheet.getActiveRange().getLastRow(), 1);
  spreadsheet.getActiveRange().offset(spreadsheet.getActiveRange().getNumRows(), 0, 1, spreadsheet.getActiveRange().getNumColumns()).activate();
};

It takes you to the last cell on column A (no matter if it has any data or not, as long as it doesn't have any gaps/empty rows on the way down. If it does, just add extra copies of the getNextDataCell(SpreadsheetApp.Direction.DOWN) code line until it gets you there), and then adds a new row after, so you can call it before running your Importrange().

Also, if you want to add more than one row at a time, just change the last argument in

spreadsheet.getActiveSheet().insertRowsAfter(spreadsheet.getActiveRange().getLastRow(), 1);
user_157780230
Related