Split a Google Sheet into multiple tabs based on column values

Question

I used a previous answer (thanks kessy!) to split 7000 or so rows into 40 or so different tabs based upon values in a column. I ran the same script on another nearly identical file and I get the error "TypeError: Cannot read property 'getRange' of null (line 5, file "Code")". I tried with a greatly simplified file and get the same error. Any help getting this to work is very much appreciated.

        function myFunction() {
  var sheet = SpreadsheetApp.getActiveSheet();

  // This var will contain all the values from column C -> Room
  var columnRoom = sheet.getRange("C:C").getValues();

  // This var will contain all the rows
  var rows = SpreadsheetApp.getActiveSheet().getDataRange().getValues();

  //Set the first row as the header
  var header = rows[0];

  //Store the rooms already created
  var completedRooms = []

  //The last created room
  var last = columnRoom[1][0]


  for (var i = 1; i < columnRoom.length; i++) {    

    //Check if the room is already done, if not go in and create the sheet
    if(!completedRooms.includes(columnRoom[i][0])) {

      //Set the Sheet name = room (except if there is no name, then = No Room)
      if (columnRoom[i][0] === "") {
        var currentSheet = SpreadsheetApp.getActiveSpreadsheet().insertSheet("No Room");
      } else {
        var currentSheet = SpreadsheetApp.getActiveSpreadsheet().insertSheet(columnRoom[i][0]);
      }


      //append the header
      currentSheet.appendRow(header);
      currentSheet.appendRow(rows[i]);
      completedRooms.push(columnRoom[i][0])
      last = columnRoom[i][0]
    } else if (last == columnRoom[i][0]) {

    // If the room's sheet is created append the row to the sheet


      var currentSheet = SpreadsheetApp.getActiveSpreadsheet()
      currentSheet.appendRow(rows[i]);
    }

  }

}

Answer 1

You can also run a loop within the loop and keep things server side for a faster result (at least it worked for me, I was having trouble with long spreadsheets timing out).

You have to know how many columns you want to pass over, maybe there is a better way to push the values than I have done (I only dabble in script).

function splitSheets() {
var theWorkbook = SpreadsheetApp.getActiveSpreadsheet();
var theSheet = theWorkbook.getSheetByName("Master");

//Let's delete any sheets that were previously split, so we can rerun the script again and again
    var sheets = theWorkbook.getSheets();

  for (i = 0; i < sheets.length; i++) {
     switch(sheets[i].getSheetName()) {
     case "Master":
          break;
     default:
        theWorkbook.deleteSheet(sheets[i]);
     }
  } 

  // This var will contain all the values from column C -> Your splitting Key
  var key = theSheet.getRange("C:C").getValues();

    // This var will contain all the rows
  var rows = theSheet.getDataRange().getValues();

  //Set the first row as the header, get the range so we can keep the formatting
  var headerFormat = theSheet.getRange("2:2");

  //Store the rooms already created
  var completedSheets = [];

//We start at i=2 because we're on row 3, row zero for the button, row one for the header
  for (var i = 2; i < key.length; i++) {
//We don't want to run the loop if we've already created the blank page and the row key is also blank.    
    if(completedSheets.includes('Blank') && key[i][0] === ""){
    //do nothing
    }else{
    //Check if the room is already done, if not go in and create the sheet
    if(!completedSheets.includes(key[i][0]) ) {
 
      //Set the Sheet name = unique key (except if there is no name, then = Blank)
      if (key[i][0] === "") {
        var currentSheet = theWorkbook.insertSheet("Blank");
      } else {
        var currentSheet = theWorkbook.insertSheet(key[i][0]);
      }

      //To avoid pasting formulas, we have to paste contents, copying allows us to keep formatting
      headerFormat.copyTo(currentSheet.getRange(1,1),{contentsOnly:true});
      headerFormat.copyTo(currentSheet.getRange(1,1),{formatOnly:true});
      
//Now here find all the rows containing the same key address and push them, this way doing it server side
      var theNewRows =[];
      var b=0;
      for(var j = 1; j < rows.length; j++) {
        if((rows[j][2] == key[i][0]) || (rows[j][2] === '' && currentSheet.getName() == "Blank")){
          theNewRows[b]=[];//Initial new array
          theNewRows[b].push(rows[j][0],rows[j][1],rows[j][2],rows[j][3],rows[j][4],rows[j][5],rows[j][6],rows[j][7],rows[j][8]);
          b++;
        }
      }
      var outrng = currentSheet.getRange(2,1,theNewRows.length,9);//Make the output range the same size as the output array
      outrng.setValues(theNewRows);
   
//The new sheet name gets added to the completed sheets list and the value of var last is updated in prep of next step
      if(currentSheet.getSheetName() == 'Blank') {
      completedSheets.push('Blank');
      last = "Blank";
      }else{
      completedSheets.push(key[i][0])
      last = key[i][0]
      }
     }
    }
  }
//And return to the Master
  SpreadsheetApp.setActiveSheet(theWorkbook.getSheetByName('Master'));
}

Example here, just click the button on the page https://docs.google.com/spreadsheets/d/1pfeU2CFDbZbA4O0b4z80l5MyCKDNQnUdkpKlzODbAiI/edit?usp=sharing

It's not perfect, but hope it helps.

Answer 2

I am not sure what is exactly your goal, but based on the error message you are getting it seems that you are not getting the active sheet properly. Instead, I would suggest you to specify the sheet by its name. Let's assume the desired name of the sheet you want to get is Sheet1. Then, in the first line of your function you can replace this:

var sheet = SpreadsheetApp.getActiveSheet();

with this:

var sheet = SpreadsheetApp.getActive().getSheetByName('Sheet1');

I also optimized your code a little by removing all the unnecessary SpreadsheetApp.getActiveSpreadsheet() calls:

function myFunction() {
  
  var ss = SpreadsheetApp.openById("SpreadsheetId");
  var sheet = ss.getSheetByName('Sheet1');

  // This var will contain all the values from column C -> Room
  var columnRoom = sheet.getRange("C:C"+sheet.getLastRow()).getValues();

  // This var will contain all the rows
  var rows = sheet.getDataRange().getValues();

  //Set the first row as the header
  var header = rows[0];

  //Store the rooms already created
  var completedRooms = []

  //The last created room
  var last = columnRoom[1][0]

  for (var i = 1; i < columnRoom.length; i++) {    

    //Check if the room is already done, if not go in and create the sheet
    if(!completedRooms.includes(columnRoom[i][0])) {

      //Set the Sheet name = room (except if there is no name, then = No Room)
      if (columnRoom[i][0] === "") {
        var currentSheet = ss.insertSheet("No Room");
      } else {
        var currentSheet = ss.insertSheet(columnRoom[i][0]);
      }

      //append the header
      currentSheet.appendRow(header);
      currentSheet.appendRow(rows[i]);
      completedRooms.push(columnRoom[i][0])
      last = columnRoom[i][0]
    } else if (last == columnRoom[i][0]) {

    // If the room's sheet is created append the row to the sheet
      sheet.appendRow(rows[i]);
    }
  }
}