How to add new row in Google sheet with todays date as top row, copying formulas from below row?-CodePudding

I am trying to create a new row automatically, every night in Google sheet with todays date as top row.

I have added the following script and set a daily trigger and it is working fine, but I have formulas in several columns and I wish to retain them in the newly added row. Can someone help me edit the script to do this? Thanks `

function addNewRow() {
var spreadsheet = SpreadsheetApp.openById("1xwF-kM6KvOJYAfsmcDVBgO0yv6ZcFFMFvH33U7SzGtc");
var sheet = spreadsheet.getSheetByName("Attendance");
sheet.insertRowBefore(2);
var today = new Date();
var dd = String(today.getDate()).padStart(2, '0');
var mm = String(today.getMonth()   1).padStart(2, '0'); //January is 0!
var yyyy = today.getFullYear();

today = dd   '/'   mm   '/'   yyyy;
sheet.getRange(2,3).setValue(today);

CodePudding user response：

Try put this formula in A1, deleted all other values in A:C. This will genarate a set of 'week name', 'week no.' and 'date' in descending order start from TODAY to the 1st of November.

and since the data are ganarated by formula in A1, inserting rows in row 2 won't affect the result of formula.

You can than use the simple insert row everyday function to do the row inserting thing, with no need to care about the date and week values in A:C.

=ArrayFormula({
 {"Day No.","Day","Date"};
 {SPLIT(
  LAMBDA(DATES,
   CHOOSE(WEEKDAY(DATES),"Sunday","Monday","Tuesday","Wednesday","Thursday","Friday","Saturday")&";"
   &WEEKDAY(DATES)&";"
   &TEXT(DATES,"dd/mm/yyyy")
  )(SORT(DATE(2022,11,SEQUENCE(DAY(TODAY()))),1,FALSE)),
 ";")}
})

CodePudding user response：

Thank you for this. I was trying all day yesterday and came up with this to explicitly pus the formula to each row after the new row was inserted and also fill the date:

function addNewRow() {
var spreadsheet = SpreadsheetApp.openById("1xwF-kM6KvOJYAfsmcDVBgO0yv6ZcFFMFvH33U7SzGtc");
var sheet = spreadsheet.getSheetByName("Attendance");
sheet.insertRowBefore(2);
var today = new Date();
var dd = String(today.getDate()).padStart(2, '0');
var mm = String(today.getMonth()   1).padStart(2, '0'); //January is 0!
var yyyy = today.getFullYear();

var cell = sheet.getRange("B2");
cell.setFormula("=WEEKDAY(C2,1)");
var cell2 = sheet.getRange("A2");
cell2.setFormula('=TEXT(B2,"dddd")');
var cell2 = sheet.getRange("D2");
cell2.setFormula('=WEEKNUM(C2)');

today = dd   '/'   mm   '/'   yyyy;
sheet.getRange(2,3).setValue(today);


}