Google-sheets – Add a new row to a spreadsheet, copying down formulas when a certain cell reaches a particular value

google sheetsgoogle-apps-script

I've got a simple Google Sheet (it's literally one line), that has in A2 =now() to calculate the current time, and then a series of formulas calculating various dates/times based on the current date in column A2 all the way to column R (column 18)

What I'd like to do is for when cell J2 reaches the value of "8" is to insert a new row and copy down the formulas from A2 to R2 into the new row, which would run every month.

How do I script this? I have no scripting experience.


I've been scouring the posts around here and have managed to cobble together some script that works:

 function copyFormatAndFormulas(rangeSource, rangeDestination) {
   rangeSource.copyTo(rangeDestination, {formatOnly: true});
   var formulas = rangeSource.getFormulasR1C1();
   for(var x in formulas) {
     for(var y in formulas[x]) {
       if(formulas[x][y] == "") continue;
       rangeDestination.getCell(parseInt(x) + 1, parseInt(y) + 1).setFormulaR1C1(formulas[x][y]);}}}

 function addRow() {  

   var ss = SpreadsheetApp.getActive(); 
   var NameofSpreadsheet = ss.getName();
  // if(NameofSpreadsheet == 'Email Triggers') { //checks that we're on the correct sheet, although can't seem to get it working
     var WorkingDaysLeft = 8;
     var sheet = ss.getSheetByName('Email Triggers'); // the current sheet 
     var range = sheet.getRange(2,10,1,1); // looking for cell J2
     var data = range.getValue(); // getting the value of cell J2
     if (data == WorkingDaysLeft) { //checking the value of cell J2 to WorkingDaysLeft, if it equals 8 then run the next functions to insert new row and copy down formulas

       var sh = ss.getActiveSheet(),//
           lRow = sh.getLastRow(); 
       var lCol = sh.getLastColumn(),
           range = sh.getRange(lRow,1,1,lCol);
       sh.insertRowsAfter(lRow,1);
       var newRange = sh.getRange(lRow+1,1,1,lCol);
       copyFormatAndFormulas(range, newRange);
     }}

I'd like to have a conditional IF statement to ensure that I am on the correct spreadsheet, as there are other sheets in the workbook. Any ideas what I'm doing wrong here?

Best Answer

I'd like to have a conditional IF statement to ensure that I am on the correct spreadsheet, as there are other sheets in the workbook.

Instead of

var NameofSpreadsheet = ss.getName();

use

var activeSheet = SpreadsheetApp.getActiveSheet();
var NameofSpreadsheet = activeSheet.getName();

The above because ss.getName() always will return the name of the first sheet.