create automatically a new sheet whose sheet name is the same as the value in column

[nagastar]

I want to create a new sheet whose sheet name is the same as the value in column D and the data in each sheet corresponds to the row of value data in column D. column D value is about title.
for example: there is new data added and I will check column D if there is different new data if there is then a new sheet will be created automatically with the same title as the new data and the data in the sheet only has rows of data related to the new data. the data comes from the google form and from the response sheet the data is taken.

the data table looks like this. sheets response.


sheets based on value column. sample lifehack sheets

I tried to use this scripts and it works to create new sheets whose names match the values in column D but the data still cannot be filtered according to the related values only. can anyone help to make the data related to the sheet title only taken from the response sheet?

Here is the scripts code.

function onOpen() {
    var menu = [{
            name : "Add",
            functionName : "newSheet"
        }
    ];
    SpreadsheetApp.getActiveSpreadsheet().addMenu("Create New Sheet", menu);
}

function newSheet() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var templateSheet = ss.getActiveSheet();
    var sheet1 = ss.getSheetByName("Sheet1")
        var getNames = sheet1.getRange("D2:D").getValues().filter(String).toString().split(",");

    for (var i = 0; i < getNames.length; i++) {
        var copy = ss.getSheetByName(getNames[i]);
        if (copy) {
            Logger.log("Sheet already exists");
        } else {
            templateSheet.copyTo(ss).setName(getNames[i]);
            ss.setActiveSheet(ss.getSheetByName(getNames[i]));
            ss.moveActiveSheet(ss.getNumSheets());
        }
    }
}

[Laurie Nason]

Hi,

I would do a number of things to make it easier to achieve what you are looking for:

• If you purely want to display the values on the "life hack" tab that relate to the form responses - then I would create a template tab that you can then create a copy each time a new title comes in from the form. (you can hide the template tab so that it's not visible to users)
• That template will have at least one more row above the responses as you will put the title into Cell A1.
• Then for your results below - use the "QUERY" function (for a great explanation on how to use it check out Ben Collins' explanation here) and utilize the "WHERE" clause in the query to select only the values on the responses tab, where your title column is the same as Cell A1.
• Then when a new response comes in with the same title - it will automagically appear on the tab
• I would also probably not use the onOpen() function to create a menu, but instead check out the "onFormSubmit" function - which will run each time the form is submitted. You can keep the menu if you want to - it's just you'll have to run it each time a new title is submitted. whereas triggering when a new form comes in will mean you don't have to actually open the form, select the menu and run the script.

Hope this helps!

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/2424eb36-ee84-45e3-b936-e26997b7816bn%40googlegroups.com.

[nagastar]

Is the query entered in A1 in the response sheets?
there is an example query?

[Laurie Nason]

No - the query would be on your tab where you only want to pull the information for that title

here's a quick example - you can then make cell B1 which ever title you want and it will update the data.

 

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/96b27bbd-8386-444c-9643-b9038501dc75n%40googlegroups.com.

--

Laurie