CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Chris Grundy
Chris Grundy

Reputation: 95

How to easily split large Google sheet into separate sheets of 200 rows?

I frequently deal with large spreadsheets and am looking for a way to easily split these into rows of 200.

To explain more clearly: I have a spreadsheet containing one sheet (or tab?) with 2000 rows.

Currently, I would open a new sheet (in the same work book), mark the first 200 rows and copy and paste them into a new sheet. Then I mark the next 200 rows and copy and paste them into a new sheet etc.

Is there a way of automating this process or speeding it up with a function?

Thanks for your time and apologies for my poor explanation.

Upvotes: 5

Views: 9710

Answers (3)

Oranges13
Oranges13

Reputation: 1084

Create a new sheet.

If your sheet has headers, click on cell A1 of your new sheet and enter =ARRAYFORMULA( -- click on your original sheet and click on the header row. The formula should complete to =ARRAYFORMULA(NameOfSheet!1:1)

In Cell A2 of your new sheet, enter =ARRAYFORMULA( -- click on your original sheet and select row 2. The formula should auto complete to =ARRAYFORMULA(NameOfSheet!2:2). You will want to modify the second number in the function to encompass as many rows as you wish to copy to the new sheet. For example, to copy 200 rows, you would use the formula =ARRAYFORMULA(NameOfSheet!2:202)

You can continue to create additional sheets -- simply duplicate the second sheet and augment the first and second numbers to copy additional sections of your sheet, for example: =ARRAYFORMULA(NameOfSheet!203:402) =ARRAYFORMULA(NameOfSheet!403:602), etc.

Upvotes: 1

Yochai Lehman
Yochai Lehman

Reputation: 372

this script should do the trick, just replace the numberOfLines 

function myFunction() {
   var numberOfLines = 10;
  var sheet = SpreadsheetApp.getActiveSheet();
  var data = sheet.getDataRange().getValues();
  var yourNewSheet = {};
   for (var i = 0; i < data.length; i++) {
     var tabName = i%numberOfLines;
     if(tabName == 0){
         yourNewSheet = sheet.getParent().insertSheet("tab #" + i);
     }
    yourNewSheet.appendRow(data[i]);
  }
}

Upvotes: 5

Max Makhrov
Max Makhrov

Reputation: 18717

You could use an ImportRange formula, like Chris Hick suggested here.

Or you may try using scripts. See more info here:

https://developers.google.com/apps-script/reference/spreadsheet/range

If you have no luck with this, please tell what you've tried so far so we could suggest any improvements.

Upvotes: 1

Related Questions