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

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

• google-sheets
• google-apps-script
• spreadsheet
• ss

• Welcome to Stack Overflow. See How do I ask a good question? and How to debug small programs. – doubleunary Commented 21 hours ago
• I don't really understand your question. I would need an example of the output you are trying to achieve. – Cooper Commented 2 hours ago
• Please make a minimal reproducible example that can be copy/paste/run without changes to illustrate the issue. – Jats PPG Commented 2 hours ago

1 Answer 1

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.