Google Sheets – How to Extend Formulas and Validation to Newly Added Rows

google sheetsgoogle-apps-script

I have formulas and validation in columns D, E, L, M and N. I want these formulas and validation to be automatic whenever I add rows using the option "Add more rows at the bottom", not using insert or copy & paste.

Sheets example

Is it possible to do it and if so can you please create a code for me?

Best Answer

Edit: fixed version of this: How to automatically insert a new row and retain functions/formulas from last row?

Also copies data validation rules.

Code

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

function onOpen() {
  var menu = [{name:"Add New Last Row", functionName:"addRow"}];
  ss.addMenu("Extra", menu);
}

function addRow() {
  var sh = ss.getActiveSheet(), lRow = sh.getLastRow(); 
  var lCol = sh.getLastColumn(), range = sh.getRange(lRow,1,1,lCol);
  sh.insertRowsAfter(lRow, 1);
  range.copyTo(sh.getRange(lRow+1, 1, 1, lCol), {contentsOnly:false});
}

Related Solutions

Google Sheets – How to Reuse Google Apps Scripts in a New Spreadsheet

You can add your script as a library in the new spreadsheet. Lets start from the beginning. This sample code will add a string to the active cell:

function libTest(text) {
  var sh = SpreadsheetApp.getActiveSpreadsheet()
    .getActiveSheet().getActiveCell();
  sh.setValue(text);
}

Save the script, by creating a version:
enter image description here

Next, copy the Project Key from the script editor:
enter image description here

Publish the script as a web app (execute only you):
enter image description here

In the new spreadsheet, open the script editor. Chose Manage Libraries from the resources option and enter and select the key/library:
enter image description here

In the script editor you can now add the following code:

function test(){
  libTest.libTest("jacobjan");
}

Google-sheets – How to set Custom data validation in Google Sheets (A really simple step-by-step guide needed)

Overview

From Extend Google Docs, Sheets, and Forms with Apps Script - Docs editors Help

If you’d like to learn Apps Script but you’re new to programming, Codecademy offers a great online JavaScript course for beginners. (Note that this course was not developed by and is not associated with Google.) Once you feel like you have a handle on JavaScript, try either of these Apps Script quickstart tutorials: Macros, menus, and custom functions or Managing form responses.

Broad steps

Design the automated procedure (similar to a macro) aka script.
Create a Google Apps Script project. A bound script is the easiest way for custom validations for Google spreadsheets.
Create the main script.
Set the trigger method (optional):
Execute the script. Repeat as it be necessary.

Note: The script could be called from the Script editor.

References

Docs editor Help
Google Apps Script - Google Developers
Data validation - Extending Google Sheets - Google Developers