How Do I Copy the header row of the source sheet to new sheets using google app script/Javascript

11 views
Skip to first unread message

Levi

unread,
Apr 17, 2020, 7:31:16 AM4/17/20
to Google Apps Script Community
I have a java script/Google Script code that creates new sheets/tabs based on the values in the first column. Say if John appears 5 times in the sheet, it creates a new sheet with the name "John" and pastes all row values that have John in the first Column. If the sheets already exist it logs "sheet exists" and exists.  I now have a challenge of ensuring that while the rows are copied to a new sheet, it also copies the title row(first row). I also wanted to add a condition that, If the sheet already exists, It overwrites the contents or the sheet. Been trying different codes for two days but and really getting headway.  

I will appreciate any help here.


Here is the main sheet.




You will notice when data is copied to a new sheet, the first row is neglected.




And this is my source code.

function newSheet() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var templateSheet = ss.getActiveSheet();
    var sheet1 = ss.getSheetByName("main")
    var getNames = [...new Set(sheet1.getRange("A2:A").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");  // I would like this to overwrite existing sheet
        } else {
            //The copyTo function will copy the entire sheet
            //templateSheet.copyTo(ss).setName(getNames[i]);



            var rowIndexes = sheet1.getRange("A:A").getValues()
                .map((value, index) => [value[0], (index + 1)])
                .filter(value => value[0] === getNames[i]);
            var namedSheet = ss.insertSheet(getNames[i]);
            rowIndexes.map(index => {
                var rowValues = sheet1.getRange(index[1], 1, 1, sheet1.getLastColumn()).getValues();
                namedSheet.appendRow(rowValues[0]);
            });


            //Logger.log(rowValues);
          
            ss.setActiveSheet(ss.getSheetByName(getNames[i]));
            ss.moveActiveSheet(ss.getNumSheets());
        }
    }
}
Details

Reply all
Reply to author
Forward
0 new messages