CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
Just
Just

Reputation: 437

How to append first row data while creating new sheets dynamically in Google Apps Script

I have this code that creates new sheets dynamically but leaves out the first row because its structured such that it creates new sheets based on values in the first column A:A. How do I structure it to capture the first (title row) before appending the rest of data.

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(","))];
var title = sheet1.getRange("A1:Z1").getValues().filter(String).toString().split(",");// Not sure


    for (var i = 0; i < getNames.length; i++) {
        var copy = ss.getSheetByName(getNames[i]);
             if (copy) {

        ss.deleteSheet(copy)


                 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();
                sheet1.getLastColumn()).getValues();;

                // Append title row first???

                namedSheet.appendRow(rowValues[0]);

            ss.setActiveSheet(ss.getSheetByName(getNames[i]));
            ss.moveActiveSheet(ss.getNumSheets());

            });

Upvotes: 0

Views: 1031

Answers (1)

Iamblichus
Iamblichus

Reputation: 19339

Situation:

You want to do the following:

  • Create a new sheet for each different value in column A (if it was previously created, delete it).
  • The first row in each created sheet should be like the first row in the source sheet (main).
  • The rest of the sheet should contain only the rows in which column A value matches the sheet name.

Code sample (check inline comments):

function newSheet() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var templateSheet = ss.getActiveSheet();
  var sheet1 = ss.getSheetByName("main");
  var getFirstColumn = sheet1.getRange(2, 1, sheet1.getLastRow() - 1).getValues().map(row => row[0]); // Get column A contents (excluding row 1)
  var getNames = [...new Set(getFirstColumn)]; // Get unique values from column A (excluding row 1)
  var title = sheet1.getRange(1, 1, 1, sheet1.getLastColumn()).getValues()[0]; // Get row 1 values
  var data = sheet1.getRange(2, 1, sheet1.getLastRow() - 1, sheet1.getLastColumn()).getValues(); // Get all data in "main" sheet (excluding row 1)
  for (var i = 0; i < getNames.length; i++) { // Iterate through each unique value in column A
    var copy = ss.getSheetByName(getNames[i]);
    if (copy) ss.deleteSheet(copy); // If there is a sheet with this name, delete it
    var filteredData = data.filter(row => row[0] === getNames[i]); // Get rows from "main" sheet with current unique column A value
    var namedSheet = ss.insertSheet(getNames[i]); // Create sheet for current unique column A value
    namedSheet.appendRow(title); // Append row 1 values to new sheet
    namedSheet.getRange(2, 1, filteredData.length, filteredData[0].length).setValues(filteredData); // Copy filtered data to new sheet
  }
}

Notes:

  • There is no need to get the indexes of the rows to copy, you can just filter them accordingly without keeping the indexes. This simplifies your code significantly.
  • You can just append the first row before appending the rest of data to the sheet. You could also add the title array to the beginning of filteredData array and write all data at the same time, including first row.

Upvotes: 1

Related Questions