title: split the sheet into different workbooks by column values with dynamic range in apps script
body: <p>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 <strong>last column</strong> of the <strong>data range</strong>. I have tried a lot to make it dynamic by adding loops but it shows <code>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.</code> 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.</p>
  <p>This the function that I am trying.</p>
  <pre><code>function splitSheets() {
    var theWorkbook = SpreadsheetApp.getActiveSpreadsheet();
    var theSheet = theWorkbook.getSheetByName(&quot;Master&quot;);
    var slc = theSheet.getDataRange().getLastColumn()
    var slcv = theSheet.getRange(&quot;B1:B&quot; + slc).getValues()
    var sheets = theWorkbook.getSheets();
    for (i = 0; i &lt; sheets.length; i++) {
      switch(sheets[i].getSheetName()) {
        case &quot;Master&quot;:
      break;
        default:
        theWorkbook.deleteSheet(sheets[i]);
      }
    } 
    var key = theSheet.getRange(&quot;A:A&quot;).getValues();
    var rows = theSheet.getDataRange().getValues();
    var headerFormat = theSheet.getRange(&quot;2:2&quot;).getValues();
    var folderId = '16XVypjB5_PWe2PaBIREpDGCNQlZuWL4k'
    var completedSheets = [];
  
    for (var i = 2; i &lt; key.length; i++) { 
      // if(completedSheets.includes('Blank') &amp;&amp; key[i][0] === &quot;&quot;){
      // }else{
      if(!completedSheets.includes(key[i][0]) ) {
        if (key[i][0] === &quot;&quot;) {
          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(&quot;Sheet1&quot;)
  
          // var currentSheet = theWorkbook.insertSheet(&quot;Blank&quot;);
        } 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(&quot;Sheet1&quot;)
  
          // var currentSheet = theWorkbook.insertSheet(key[i][0]);
        }
  
        var theNewRows =[];
        var b=0;
  
        for(var j = 1; j &lt; rows.length; j++) {
          var rown = []
          for(var c = 0; c &lt; slcv.length; c++){
  
            // some other trials
            // if((rows[j][0] == key[i][0]) || (rows[j][0] === '' &amp;&amp; currentSheet.getName() == &quot;Blank&quot;)){
            //   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] === '' &amp;&amp; currentSheet.getName() == &quot;Blank&quot;)){
            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(&quot;1:1&quot;).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 = &quot;Blank&quot;;
        }else{
        completedSheets.push(key[i][0])
        last = key[i][0]
        // }
      }
      }
    }
    SpreadsheetApp.setActiveSheet(theWorkbook.getSheetByName('Master'));
  }
  </code></pre>