Is there a way to add a new sheet tab to a Google Sheet based on the timestamp date?

Question

Students take a weekly survey on Google Forms, and it is transferred to a Google Sheet. I want the Google Sheet to automatically generate a new tab based on the date (timestamp) so that each week has its own tab. Even better if we can make the timestamp simpler; I don't need the time, just the date.

Here is the form and here is the sheet.

Answer 1

For form responses received in a spreadsheet, you want to automatically generate a new tab based on the timestamp so that each week has its own tab.

To do this, it is first necessary to identify the week number. You haven't declared a preference for this, so we'll use the ISO week date where January 4 is always in week 1. We'll combine this with the methodology described in Copy submitted form data to new automatically generated Google sheet to append a copy of each response to the relevant sheet.

---

function copyData(e) {

  var ss = SpreadsheetApp.getActiveSpreadsheet()
  var responsesheetname = "Form responses 1"
  var responses = ss.getSheetByName(responsesheetname);
  
  // get data and make it a 1D array
  var response = e.range.getValues()[0];
  
  // get the time stamp and set hours to zero
  var time = response[0];

  // set the hours to zero
  time.setHours(0, 0, 0, 0);
  
  //get the year
  var thistimestampyear = time.getFullYear();
  
  // get the ISO week
  // https://weeknumber.net/how-to/javascript#:~:text=To%20get%20the%20ISO%20week,getWeekYear()%20.
  // January 4 is always in week 1.
  var week1 = new Date(thistimestampyear, 0, 4);
  // Logger.log(week1); //DEBUG
  // Adjust to Thursday in week 1 and count number of weeks from date to week1.
  var isoweek =  1 + Math.round(((time.getTime() - week1.getTime()) / 86400000 - 3 + (week1.getDay() + 6) % 7) / 7);
  //Logger.log("DEBUG: The ISO week for this timestamp is = "+isoweek)
  
  
  var sheetName = isoweek.toString();
  //Logger.log("the projected sheet name is "+isoweek)
  var sheet = ss.getSheetByName(sheetName);
  
  if (!sheet) {
    sheet = ss.insertSheet(sheetName);
    var headers = responses.getRange(1, 1, 1, responses.getLastColumn());
    headers.copyTo(sheet.getRange(1, 1));
  }
  sheet.appendRow(e.values);
}

---

Create a "on Form Submit" trigger for the function. From the Scrip editor menu: "Edit" -> "current project's triggers".

• Function to run: copyData
• Select event source: From spreadsheet
• Select event type: On form submit

---

Credit

How to get the week number from a date