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.
-
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.– TedinozDec 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.– TedinozDec 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– TedinozDec 3, 2020 at 23:59
1 Answer
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