I want to split the google sheet into different workbooks, not tabs in the same workbook based on values in column A. Although I have got a script that splits the data into different workbooks but the data range in it is not dynamic like the number of columns to be added into each workbook are fixed. I want them to be dynamic like till the last column of the data range. I have tried a lot to make it dynamic by adding loops but it shows The number of columns in the data does not match the number of columns in the range. The data has 1 but the range has 12. this error. The data in the log has almost no difference for the fixed range (which is working fine) and for the dynamic range that I have tried to it But don't know why it is showing error. Have got stuck into it. any help will be highly appreciated.

This the function that I am trying.

function splitSheets() {
  var theWorkbook = SpreadsheetApp.getActiveSpreadsheet();
  var theSheet = theWorkbook.getSheetByName("Master");
  var slc = theSheet.getDataRange().getLastColumn()
  var slcv = theSheet.getRange("B1:B"   slc).getValues()
  var sheets = theWorkbook.getSheets();
  for (i = 0; i < sheets.length; i  ) {
    switch(sheets[i].getSheetName()) {
      case "Master":
    break;
      default:
      theWorkbook.deleteSheet(sheets[i]);
    }
  } 
  var key = theSheet.getRange("A:A").getValues();
  var rows = theSheet.getDataRange().getValues();
  var headerFormat = theSheet.getRange("2:2").getValues();
  var folderId = '16XVypjB5_PWe2PaBIREpDGCNQlZuWL4k'
  var completedSheets = [];

  for (var i = 2; i < key.length; i  ) { 
    // if(completedSheets.includes('Blank') && key[i][0] === ""){
    // }else{
    if(!completedSheets.includes(key[i][0]) ) {
      if (key[i][0] === "") {
        var name = 'Blank'
        var resource = {
          title: name,
          mimeType: MimeType.GOOGLE_SHEETS,
          parents: [{ id: folderId }]
        }
        var insertedFile = Drive.Files.insert(resource)
        var csid = insertedFile.id
        var currentSheet = SpreadsheetApp.openById(csid).getSheetByName("Sheet1")

        // var currentSheet = theWorkbook.insertSheet("Blank");
      } else {
        var name = key[i][0]
        var resource = {
          title: name,
          mimeType: MimeType.GOOGLE_SHEETS,
          parents: [{ id: folderId }]
        }
        var insertedFile = Drive.Files.insert(resource)
        var csid = insertedFile.id
        var currentSheet = SpreadsheetApp.openById(csid).getSheetByName("Sheet1")

        // var currentSheet = theWorkbook.insertSheet(key[i][0]);
      }

      var theNewRows =[];
      var b=0;

      for(var j = 1; j < rows.length; j  ) {
        var rown = []
        for(var c = 0; c < slcv.length; c  ){

          // some other trials
          // if((rows[j][0] == key[i][0]) || (rows[j][0] === '' && currentSheet.getName() == "Blank")){
          //   theNewRows[b]=[];
          //   theNewRows[b].push (
          //     rows[j][c].toString()
        
              // This although adds the data and range dynamically but also shows the mentioned error.
              rown.push(rows[j][c])
            // );
          //   b  ;
          // }
        }

        if((rows[j][0] == key[i][0]) || (rows[j][0] === '' && currentSheet.getName() == "Blank")){
          theNewRows[b]=[];
          theNewRows[b].push (
            rown.toLocaleString()
          
            // These are the fixed column for data rnage
            // 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],rows[j][9],rows[j][10],rows[j][11]
            );
          b  ;
        }
        Logger.log(rown)
      }
      Logger.log(theNewRows)
      // Logger.log(theNewRows)
      currentSheet.getRange("1:1").setValues(headerFormat)
      var outrng = currentSheet.getRange(2,1,theNewRows.length, slc);//Make the output range the same size as the output array
      outrng.setValues(theNewRows);
      currentSheet.autoResizeColumns(1, slc);
      if(currentSheet.getSheetName() == 'Blank') {
      completedSheets.push('Blank');
      last = "Blank";
      }else{
      completedSheets.push(key[i][0])
      last = key[i][0]
      // }
    }
    }
  }
  SpreadsheetApp.setActiveSheet(theWorkbook.getSheetByName('Master'));
}

CodePudding user response：

I overhauled and improved your script to be more readable and use a lot less Spreadsheet calls by using array methods instead.

Script:

function splitSheets() {
  var folderId = '*** FOLDER ID ***';
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheets = spreadsheet.getSheets();
  var sheet = spreadsheet.getSheetByName('Master');
  // delete sheets that are not named 'Master'
  sheets.forEach(sheetIter => {
    if(sheetIter.getSheetName() != 'Master')
      spreadsheet.deleteSheet(sheetIter);
  });
  var data = sheet.getDataRange().getValues();
  // remove 1st row (blank row)
  data.shift();
  // remove 2nd row from data and assign as headers
  var headers = data.shift();
  // get unique list of sheet names from column A
  var sheetNames = data.map(row => row[0]).filter(onlyUnique);
  // loop those unique sheetNames
  sheetNames.map(sheetName => {
    // filter data by getting only rows with same column A and sheetName
    var outputData = data.filter(row => row[0] == sheetName);
    // add header from data filtered
    outputData.unshift(headers);

    var resource = {
      title: sheetName || 'Blank',
      mimeType: MimeType.GOOGLE_SHEETS,
      parents: [{ id: folderId }]
    }

    var file = Drive.Files.insert(resource);
    var currentSheet = SpreadsheetApp.openById(file.id).getSheetByName('Sheet1');
    // write data filtered with the header
    currentSheet.getRange(1, 1, outputData.length, outputData[0].length).setValues(outputData);
    // resize the columns
    currentSheet.autoResizeColumns(1, outputData[0].length);
  });
}

// function to get unique values from array using filter
function onlyUnique(value, index, self) {
  return self.indexOf(value) === index;
}

Sample Output: