I have a sheet here. Our 2-week pay periods run from Sunday to Saturday. I want each employee to have his or her own time sheet like the one above which shows the current 2-week pay period always at the top with previous pay periods beneath.

I want F15 to always reflect the total of the current pay period so that I can share that cell's total on a master sheet for each employee as a single line item. I need a script to automatically add and populate the appropriate dates and weekdays for the next pay period as soon as the previous period ends.

For example, on the sheet above, when 5/13/2017 rolls over to 5/14/2017, 14 new rows will be added at the top of the page populated with the dates for the new 2-week period. That would mean that F15 would now be totaling the hours of the next two weeks as they are filled in each day by the employee.


Ok, I have this now, but it doesn't autofill the new dates and I think it might be moving the current 2 weeks to the bottom of the whole sheet instead of just moving them down. Any thoughts would greatly be appreciated!

function addNewTimePeriod() {
  var addressTime,currentData,lastRow,

  addressTime = "A2:D29";
  timeSheetName = "hrs";


  ss = SpreadsheetApp.getActiveSpreadsheet();
  sh = ss.getSheetByName(timeSheetName);
  lastRow = sh.getLastRow();

  range = sh.getRange(addressTime);
  currentData = range.getValues();

  sh.getRange(lastRow+1, 1, currentData.length, currentData[0].length).setValues(currentData);

  range.clearContent();//clear the values in the current pay period

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.