0

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.

3
  • Welcome. Would you please add a brief description of your search/research efforts as is suggested in How do I ask a good question?. Would you also describe your own efforts to solve your problem.
    – Tedinoz
    Dec 3, 2020 at 23:12
  • The timestamp-it is what it is, though you could format it as time only. Extra sheets: may I ask, how important is it to literally have a separate sheet for every week (which is a LOT of sheets for you to manage)? Would it be acceptable to have a single extra sheet that you could query by week/date?Creating a new response sheet for each week would involve a fair bit of work.
    – Tedinoz
    Dec 3, 2020 at 23:12
  • This topic addresses the core aspect of your question (except that it does not establish the week number): Copy submitted form data to new automatically generated Google sheet
    – Tedinoz
    Dec 3, 2020 at 23:59

1 Answer 1

0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.