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
Instead of
use
The above because
ss.getName()
always will return the name of the first sheet.