google sheet add button to insert new row that has formula

user_3493113 0
Viewed 2450

This is the simplified version of my data. I want to place a button at the bottom of the grey area, so that upon clicking, a new row is inserted above the bottom. the new row must have the formula as the rows before it.

enter image description here my real formulas are very complicated so I cant use arrayFormula to fulfill the data in new rows. this a link to my spreadsheet.

google-sheets
2 Answers

You can refer to this sample code that uses a custom menu to insert new row with formula based on the previous row.

Code:

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  // Or DocumentApp or FormApp.
  ui.createMenu('Custom Menu')
      .addItem('Insert New Row', 'insertRow')
      .addToUi();
}

function insertRow() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var lastRow = sheet.getLastRow();
  var lastCol = sheet.getLastColumn();

  //Exit function if current active sheet is not Sheet1
  if(sheet.getName() != "Sheet1"){
    return;
  }

  //insert new row
  sheet.insertRowAfter(lastRow);
  sheet.getRange(lastRow,2,1,3).copyTo(sheet.getRange(lastRow+1,2));
}

What it does?

  1. Get the last row and the last column that has content in the active sheet using getLastRow() and getLastColumn()
  2. Insert a new row after the last row that has content using insertRowAfter(afterPosition)
  3. Get the range of the last row that has content using getRange(row, column, numRows, numColumns)

Where:

row = your last row that has content

column = column index where you want to start (in the sample sheet it is in column B/ index 2)

numRows = should be 1 (we only want to copy the current last row that has content)

numCols = how many columns to select. In the sample sheet there are 3 columns (column B-D)

  1. Use copyTo(destination) to copy both values and formatting of the range selected in step 3 and paste it in the newly added row. Destination range should be the top-left cell. Since we want to copy it to the new row.I used getRange(last row +1, 2)

Output:

enter image description here

Note:

  • If you want to use button placed below the grey row, just use insertRow when you assign a script in your button

  • Please make sure that the last row with content is the row that has formula.

If you want to use the function on a specified list of sheets, you can add this in the sample code. This will exit the function if the current active sheet name is not included in the valid sheets listed

  var validSheets = ["Sheet1", "Sheet2"];

  if(!validSheets.includes(sheet.getName())){
    return;
  }
user_146060460

Remove the last comment ... then click on the new menu : this script will add a new line in the active sheet

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('>> Action <<')
    .addItem('Copy last row (formulas only)', 'copyLastRow')
    .addToUi();
}
function copyLastRow() {
  var sh=SpreadsheetApp.getActiveSpreadsheet().getActiveSheet()
  var rng=sh.getRange(sh.getLastRow(),1,1,sh.getLastColumn())
  rng.copyTo(sh.getRange(sh.getLastRow()+1,1,1,sh.getLastColumn()), SpreadsheetApp.CopyPasteType.PASTE_FORMULA, false)
}
user_150193800
Related