Tuesday, June 12, 2012

Google Apps Script: How to retrieve more than 200 items from a List Page


We're constantly building customer solutions for Google Apps, Solve360, Freshbooks, and Xero with Google Apps Script and Google App Engine.   Below is a handy tip for Google Apps Script developers.

Google Apps Script
Posted on behalf of Cameron Roberts, Senior Developer, Interlockit.com

When interacting with a Google Sites list page via the Google Apps Script SitesApp service, the documented getListItems() function returns only 200 items, while the maximum length of a List Page in sites is 500 Items. This function takes advantage of undocumented parameter values that can be passed to getListItems() to retrieve all items in batches of 200.  

/*
   Function: getAllListPageItems(page)
  Parameter: page - An instance of the Google Apps Script Services "ListPage" class.
Description: A robust function which uses undocumented Google Services functonality to retrieve ALL items from a list page. At time of writing (June 2012), the Google Services getListItems() method returns only the first 200 items of a ListPage. This function will catch failed API calls and re-try until all list items have been retrieved. This function will also throw an informative exception if the passed parameter is not a valid ListPage object.

Usage Example:
 
   //retrieve items from list page and write to Log.

   var page = SitesApp.getPageByUrl('http://sites.google.com/a/yourdomain.xyz/yoursite/yourListPage');
   var items = getAllListItems(page);

   for(var i in items){
       var item = items[i];
       Logger.log(item);
       //..Perform your processing on list items here.
   }


Written by Cameron Roberts, Interlockit.com

*/

function getAllListPageItems(page){
  if(page && page.getPageType && page.getPageType() == 'ListPage'){
    var items = [];
    var start = 0;
    var size = 200;
    var failed = false;
    do{
      try{
        var item_batch = page.getListItems({start:start,max:size});
        failed = false;
        items = items.concat(item_batch);
        start += size;
      }catch(e){
        failed = true;
      }
    }while(item_batch.length == size || failed);
    return items;
  }else{
    throw 'Parameter passed to getAllListItems was not a List Page: '+page;
  }
}



The relevant post from Google, which exposes the undocumented functionality, is http://code.google.com/p/google-apps-script-issues/issues/detail?id=666

2 comments:

  1. Thanks alot! very helpful function. runs ok

    ReplyDelete
  2. Seems to work great ... thank you very much!!!

    ReplyDelete

Note: Only a member of this blog may post a comment.