Insert and Copy a Row (Including Formulas) With Google Apps Script

Sometimes we use Google Sheets like simple, flat, databases. Pump a bunch of data into a row and there is a whole host of things you can do. Utilizing the live document features and real-time sharing of Sheets and things can get pretty powerful.

One of the problems encountered is users inserting or adding new rows to the bottom of a sheet. Many times doing this can result in cells being left out of formulas in other sheets. A nice feature for the users is to add a menu item for automatically inserting a new row into the sheet and copying the formatting and data validation while at the same time preserving the formulas in other sheets.

A simple Google Apps Script can handle the task. Follow along the code below:

// global 
var ss = SpreadsheetApp.getActive();

// set a menu for users to interact with
function onOpen() {
  var menu = [{name:"Insert New Record", functionName:"insertRecord"}];
  ss.addMenu("Options", menu);
}

// main function for inserting the new row
function insertRecord() {
  
  var sh = ss.getActiveSheet();                  // define the active sheet
  var lRow = sh.getLastRow();                    // store the last row
  var lCol = sh.getLastColumn();                 // store the last column
  
  var range = sh.getRange(lRow,1,1,lCol);        // store the range to copy from
  var formulas = range.getFormulas();            // store the formulas to copy from
  
  sh.insertRowsBefore(lRow, 1);                  // insert a new row above the last
  
  var newRange = sh.getRange(lRow, 1, 1, lCol);  // store the location of the new row
  range.copyTo(newRange);                        // copy saved row data to the new row
  
  newRange.setFormulas(formulas);                // set the formulas on the new row
}

, ,

Comments are closed.