1

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?

0

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.

  • Thanks Ruben, that seems to have worked! I still need to get the triggers right, as i want it to run automatically when I don't have the spreadsheet open. Is that best using OnChange/onEdit/Timebased? – HR DataReporting Dec 4 '18 at 14:40
  • @HRDataReporting If you want a trigger that run without having to open the spreadsheet, you should use a time-driven trigger. – Rubén Dec 4 '18 at 16:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.