google-sheetsgoogle-apps-scriptgoogle-forms

Create a new sheet tab for Google Form responses every day

I have a forum that is collecting responses and I'd like to automatically have the spreadsheet create a new sheet tab each day, and then populate that sheet tab with any Google Form responses for that date. For example, if I had 2 respones today, 11/08/2024, I'd like to have a sheet tab named 11-08 created in the morning, and those two responses automatically copied over from the Form Respones sheet to the 11-08 sheet tab. I tried with the code in the screenshot, but the query function I've created won't work with setformula in Apps Script. Is their any easier way to go about what I'm trying to do, or a way to set that query function to a cell automatically? Thanks in advance!

This is the code I used in Apps Script. I can get it to run and create a new sheet tab daily, and work with a simple formula, but not with the query formula I'm using to try and grab any results with todays date. I included a second screen shot of the intended result (I manually put in the query formula in cell A1).enter image description here

function createNewDailyTab() {

  var sheet = SpreadsheetApp.getActiveSpreadsheet();

  var today = new Date().toLocaleDateString();

  sheet.insertSheet(today); 
  sheet = SpreadsheetApp.getActive().getSheetByName(today);
  sheet.activate();

  SpreadsheetApp.getActiveSpreadsheet().moveActiveSheet(0);
  
 var cell = sheet.getRange("BA");
 cell.setFormula("=query('2024-11'!A2:H,"Select * Where A= date '"&TEXT(TODAY(),"yyyy-mm-dd")&"'",0)");

}
Solution

  • Use onFormSubmit(e) to Create a New Sheet Tab Every Form Submit

    First of all, I used onFormSubmit so you don't have to add the Query formula every single time it creates a Sheet Tab.

    I modified your code where it automatically creates a sheet tab based on the date when you submit the form. It also copies the data that you have input in the form.

    First, you will need to change your Function name to Installable trigger called onFormSubmit so that the code will trigger every form submit.

    Here is the full code:

    function onFormSubmit(e) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var data = e.values;  
  var date = Utilities.formatDate(new Date(data[0]), "GMT+8", "MM/dd/yyyy");
  var sheetnames = ss.getSheets().map(x=>x.getSheetName());

  if (!sheetnames.includes(date)) {
    ss.insertSheet(date);
  }
  ss.getSheetByName(date).appendRow(data);
}

    Sample Output:

    Output

    References: