CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Goldorak
Goldorak

Reputation: 13

Have only the rows containing the value in Column B copied into the new sheet

I am trying to accomplish 2 things with Google Sheets. I have a Main sheet with data and I would like to:

  1. Create new sheet with the name based on a cell value in Column B (accomplished)
  2. Copy the rows containing those values in B in that new sheet

Part 1 works fine with this script:

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

function newSheet() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var templateSheet = ss.getActiveSheet();
    var sheet1 = ss.getSheetByName("Sheet1")
        var getNames = sheet1.getRange("B2:B").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());
        }
    }
}

The problem is when new sheets are created it copies the content of the main sheet. I would like to have only the rows containing the value in Column B copied into the new sheet.

Upvotes: 1

Views: 176

Answers (2)

Wicket
Wicket

Reputation: 38296

Instead of using copyTo you might use one of the insertSheet methods of Class Spreadsheet, then copy rows having the required value in column B into the new sheet.

The specific code to copy the rows depends on what really need to copy (values, displayed values, formulas, cells formatting, rich text cell content formatting, notes, data validation, conditional formatting)

Let say that you are only interested in passing the values, you could use something like the following in the getNames for loop:

var data = sheet1.getDataRange().getValues().filter(row => row[1] === getNames[i])
var newSheet = ss.insertSheet(getNames[i]);
if( data.length > 0 ) newSheet.getRange(1,1,data.length,data[0].length).setValues(data);

Related

Upvotes: 2

Yuri Khristich
Yuri Khristich

Reputation: 14537

You can just add one more function to remove redundant rows from any sheet. Something like this:

function main() {
  var sheet = SpreadsheetApp.getActiveSheet()
  remove_rows("aaa", sheet); // remove all rows, that contain 'aaa' in first column
}

function remove_rows(value, sheet) {
  var data = sheet.getDataRange().getValues();
  var new_data = data.filter(x => x[0].indexOf(value)>=0)
  sheet.getDataRange().clearContent();
  sheet.getRange(1,1,new_data.length,new_data[0].length).setValues(new_data);
}

Upvotes: 1

Related Questions