Pagination In Apps Script

Example Code:

In this code, you may change the global variables as per yours.

//Global variables, Change it as per yours.

spreadsheetKey = 'YOUR SS Key';//Change it to your spreadsheet key

numItemOnaPage = 7;//Number of pages which you want to be displayed on each page

function doGet() {

  var app = UiApp.createApplication().setTitle('Here is the title bar'); 

  var panel = app.createVerticalPanel().setId('myPanel');

  var ss = SpreadsheetApp.openById(spreadsheetKey);

  var sheet = ss.getSheets()[0];

  var lastRow = sheet.getLastRow();

  var lastColumn = sheet.getLastColumn();

  var numPages = ((lastRow-1)%numItemOnaPage == 0)?(lastRow-1)/numItemOnaPage:

(lastRow-1)/numItemOnaPage+1;

  var mainGrid = app.createGrid(2, 1).setId('mainGrid');

  var pageNumeGrid = app.createGrid(1, numPages+1);

  pageNumeGrid.setWidget(0, 0, app.createLabel('Page:'));

  var hoverHandler = app.createServerMouseHandler('uderLineIt');

  var outHandler = app.createServerMouseHandler('makeNormal');

  var showPageHandler = app.createServerClickHandler('showPage');

  var dataPage1 = sheet.getRange(2,1,numItemOnaPage,lastColumn).getValues();

  var header = sheet.getRange(1,1,1,lastColumn).getValues();

  var dataTable = app.createFlexTable().setBorderWidth(1).setId('table')

      .setStyleAttribute('borderCollapse','collapse'); 

  for(var k=0; k<header[0].length;k++ )

  {dataTable.setText(0, k, header[0][k].toString());}

  for(var i=0; i<dataPage1.length; i++){

    for(var j=0; j<dataPage1[0].length; j++){

      dataTable.setText(i+1, j, dataPage1[i][j].toString());

    }

  }

  for(var i=1; i<=numPages; i++){

    pageNumeGrid.setWidget(0, i,app.createLabel(i.toString())

                           .setId('page'+i.toString()).setWidth('20')

                          .addClickHandler(showPageHandler));

  }

app.getElementById('page1').setStyleAttribute('color','green')

    .setStyleAttribute('fontSize','26px');

  mainGrid.setWidget(0,0,dataTable);

  mainGrid.setWidget(1, 0, pageNumeGrid);

  panel.add(mainGrid);

  app.add(panel);

  return app;

}

function showPage(e){

  var app = UiApp.getActiveApplication();

  var pageNum = parseInt(e.parameter.source.substring(4),10);

  var ss = SpreadsheetApp.openById(spreadsheetKey);

  var sheet = ss.getSheets()[0];

  var lastRow = sheet.getLastRow();

  var numPages = ((lastRow-1)%numItemOnaPage == 0)?(lastRow-1)/numItemOnaPage:

(lastRow-1)/numItemOnaPage+1;

  for(var m=1; m<=numPages; m++){

    app.getElementById('page'+m.toString()).setStyleAttribute('color','black')

      .setStyleAttribute('fontSize','1em');

  }

  app.getElementById(e.parameter.source).setStyleAttribute('color','green')

    .setStyleAttribute('fontSize','26px');

  var datathisPage = sheet.getRange(

(pageNum-1)*numItemOnaPage+2,1,numItemOnaPage,sheet.getLastColumn())

.getValues();

  var table = app.getElementById('table');

  for(var i=0; i<datathisPage.length; i++){

    for(var j=0; j<datathisPage[0].length; j++){

        table.setText(i+1, j, datathisPage[i][j].toString());

    }

  }

  return app;

}

Modifications Possible:

This code can be modified to show Previous/Next Page options, Also, if the number of pages are very large, then only a block few page numbers can be displayed, and there will be an option to go to the next block of page numbers. Also, we can put better styling using SetStyleAttribute('arg','arg') Method.

If you have any query/suggession, you may contact me using this link.

Search KeyWords:

Apps Script Pagination, GAS Pagination, Google Apps Script Pagination Example, GAS Pagination Demo