CODEWITHSUNDEEP
google-sheetsgoogle-apps-scriptspreadsheetss
638 sleepers
638 sleepers

Reputation: 1

how to make a copy of a sheet each for week 3 weeks in advance

I am trying to make a weekly workbook for my team to see work coverage 3-4 weeks in advance. I would like the name of the new tab to be for the week ending on Saturday. It would copy from the tab made the week before. I plan on running this trigger on Sunday morning. I have not been able to make it work correctly. I can copy the document but not get the date or I can create a tab for every week of the year all at once... I would be very grateful for any help in this puzzle. I have worked myself into a circle.

I keep getting every week tab at once (I would like it to create one tab that has the week ending date for the (name) 4 weeks in advance). I want it to pull from the previous tab created each week.

edit- code used and error

function createWeeklySheet() {

  // Get the current date

  var today = new Date();

  

  // Get the last day of the current week

  var lastday = new Date(today.getFullYear(), today.getMonth(), today.getDate() - today.getDay());



  // Check if a sheet for this week already exists

  var sheetNames = SpreadsheetApp.getActiveSpreadsheet().getSheets().map(function(sheet) { return sheet.getName(); });

  if (sheetNames.includes(formatDateString(lastday))) {

    return;

  }



  // Duplicate the template sheet and rename it based on the current week

  var templateSheet = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0]; // Assuming the first sheet is the template

  var newSheet = templateSheet.duplicate();

  newSheet.setName(formatDateString(firstDay));

}

error- Error    
ReferenceError: formatDateString is not defined
createWeeklySheet   @ get weekly sheets.gs:19

Upvotes: 0

Views: 52

Answers (1)

doubleunary
doubleunary

Reputation: 18809

The error tells that you are calling a function that isn't present. To make it work, add something like this:

function formatDateString(date) {
  const timezone = SpreadsheetApp.getActive().getSpreadsheetTimeZone();
  return Utilities.formatDate(date, timezone, 'yyyy-MM-dd');
}

The firstDay variable is undeclared as well, and it is unclear which date it should refer to.

Upvotes: 0

Related Questions