Search code examples
google-apps-scriptgoogle-sheetsgoogle-apps

How to automatically insert rows based on a cell's value?


So I'm basically trying to insert an amount of rows that is equal to a cell's number value "C12:D12" + 12 extra rows to the top and 3 extra rows to the bottom. I want the rows that are going to be added to be the same as the other ones (conditional formatting, formulas etc.), starting from E13:L13 (without the values, only the row) and going under E13:L13 (Like E14:L14 and so on). To summarize, I want the amount of cells that C12:D12 has as number value + 15.

I've tried several scripts but none of them seemed to be working, I think it is because I have a template (extra rows and stuff). I have taken a look at some of the questions, but the answer is still a no.

Edit: https://docs.google.com/spreadsheets/d/113ejYdAGcCw0eX4Liyu1nL1kkZdsmOL3dY2h3xfs5KE/edit?usp=sharing for the sheet. Apologies, couldn't think of it earlier. In the most simple way, I'm looking for a way that I can keep the amount of rows in that sheet equal to the C12:D12 (=COUNTA(E13:E86)) + 12 for the top area and 3 for the bottom. Once again, I apologize as I could not think of a better explanation.


Solution

  • Is this what you are looking for? according to your sample sheet, this function add a row to A86:R86, which maintains all formats of A86:R86.

    function insertRows() {
      const ss = SpreadsheetApp.getActiveSpreadsheet();
      const sheet = ss.getSheetByName('In Training');
      sheet.getRange(86,1,1,16).insertCells(SpreadsheetApp.Dimension.ROWS);
    }