Pagination In Apps Script

Here is a simple pagination example.
In this example, Script takes the data from Spreadsheet and presents it in a table but in a paginated form.

References

Example Demo:
Click the page numbers to try the Demo


Spreadsheet Preview which is used to populate above paginated table

Pagination Demo Doc


 
 

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

 
Comments