Contents

Abstract

This document shows how to build a simple Schema Extractor using the Force.com Toolkit for Google Data APIs. The Toolkit is open source and hosted in a subversion repository on Google Code.

This sample uses the toolkit and a simple Visualforce Component, a Visualforce wizard page and a few Apex Classes to extract force.com object meta-data and save it in a Google Spreadsheet. The code is intended to show a few methods and does not attempt to be a complete application.

Visualforce page

This page is called SchemaExtract, it is the entry point for this application.

 <apex:page controller="SchemaExtractController">
	<apex:form >

		<!--  this component gathers the session information required by the page -->
		<c:getAuthSubToken scope="http://spreadsheets.google.com/feeds/" />
		<!--  <h2>session token is {!$CurrentPage.parameters.sesstoken}</h2>  -->

		<apex:pageBlock title="Step 1:Select Google Spreadsheet" mode="edit"
			rendered="{!selectedSheet==null}">
			<apex:selectList value="{!selectedSheet}" multiselect="false"
				size="6">
				<apex:selectOptions value="{!spreadSheets}" />
			</apex:selectList>
			<apex:commandButton value="Continue" action="{!continuePage}" />
		</apex:pageBlock>

		<apex:pageBlock title="Step 2:Select Force.com Object" mode="edit"
			rendered="{!AND(selectedSheet!=null,selectedObject==null)}">
			<apex:selectList value="{!selectedObject}" multiselect="false"
				size="6">
				<apex:selectOptions value="{!objectList}" />
			</apex:selectList>
			<apex:commandButton value="Continue" action="{!extractMore}" />
		</apex:pageBlock>

		<apex:pageBlock title="Extract Log" rendered="{!logLen>0}">
			<a href="{!sheetUrl}" >Visit Spreadsheet</a>
			<apex:dataTable value="{!log}" var="msg" id="theTable"
				rowClasses="odd,even" styleClass="tableClass">
				<apex:column >
					<apex:facet name="header">Message</apex:facet>
					<apex:outputText value="{!msg}" />
				</apex:column>
			</apex:dataTable>
		</apex:pageBlock>

	</apex:form>
</apex:page>

Controller

This controller will require several more test methods before it can be considered more than strictly educational.

public class SchemaExtractController {
	/* simple logging */
	List<String> logMessages = new List<String>(); 
	public string[] log { get { return logMessages; } } 
	public integer logLen { get { return logMessages.size(); }}

	// manage the session token from a querystring param
	public string authToken { public get { 
		return ApexPages.currentPage().getParameters().get('sesstoken');
	} private set; }

	// manage user selections for sheet name and object name
	public string selectedSheet {get; set;}
	public string sheetUrl { get {return 'http://spreadsheets.google.com/ccc?key='+selectedSheet; }}
	
	public List<SelectOption> getSpreadSheets() {
 		
 	 	List<SelectOption> options = new List<SelectOption>();
		if ( authToken == null ) return options;
		
		SpreadsheetService service = new SpreadsheetService();  
		service.setAuthSubToken( this.authToken );  
		GoogleData sheets = service.getSpreadsheets(); 
		
		String idVal;
		String[] tokens;
 		if(sheets != null){
	 	 	for(xmldom.element entry: sheets.entries){
	 	 		idVal = entry.getElementByTagName('id').nodeValue;
	 	 		tokens = idVal.split('/');
	 	 		idVal = tokens[tokens.size()-1];
	 	 		options.add(new SelectOption(idVal,
	 	 						entry.getElementByTagName('title').nodeValue)
	 	 					);
	 	 	}
 		}
 	 	return options;
  	}

	
	public string selectedObject { get; set; }	
 	public List<SelectOption> ObjectList { get {  	 	
 	 	List<SelectOption> options = new List<SelectOption>();
 	 	Map <String, Schema.SObjectType> schemaMap = Schema.getGlobalDescribe();
		for(String name:schemaMap.keySet()){
			options.add(new SelectOption(name,name));// availObjList.add(name);	
		} 
 	 	return options;
  	}}

	// wizard navigation steps
	public PageReference continuePage() {return null;	}
	public PageReference extractMore(){
		extract();
		return null;
	}
	
	// main routines to query meta data and generate google sheet
	public void extract(){
		
		try{
			logMessages.clear(); 
	        Map <String, Schema.SObjectType> schemaMap = Schema.getGlobalDescribe();
			Schema.DescribeSObjectResult descRes;
			Map<String, Schema.SObjectField> fieldMap ;		
		
			SpreadsheetService service = new SpreadsheetService();  
			service.setAuthSubToken(this.authToken);  
			logMessages.add('Extract to Selected Sheet Id= '+ this.SelectedSheet);
		
			xmldom.element oneSpreadSheet = service.getSpreadsheetById( this.SelectedSheet ); 
			
			String name = selectedObject; 
			descRes = schemaMap.get(name).getDescribe();
			fieldMap = descRes.fields.getMap();
			xmldom.element wrkSheet = new xmldom.element('entry');
			xmldom.element title = new xmldom.element('title') ;		
			title.nodeValue = name;
			wrkSheet.appendChild( title );
		
			xmldom.element rc = new xmldom.element('gs:rowCount');
			rc.nodeValue = String.valueOf(fieldMap.values().size() + 5);
			wrkSheet.appendChild( rc); 	
			xmldom.element cc = new xmldom.element('gs:colCount');
			cc.nodeValue = '15';
			wrkSheet.appendChild( cc); 	
			
			GoogleData.Worksheet addedWorkSheet = new GoogleData.Worksheet( wrkSheet);
			addedWorkSheet = service.insertWorksheet( oneSpreadSheet , addedWorkSheet );
			logMessages.add(name+':worksheet created'); 
		
			if ( extractObjectMetaData(name,service,addedWorksheet,fieldMap)){
				logMessages.add('[SUCCESS] Object '+name+': Extract Successful');	
			} else {
				logMessages.add('[FAILED] Object '+name+': Extract Failed. !!!');	
			}
			
		} catch( Exception ex ){
			logMessages.add(ex.getMessage());
		}
	}	
	
	private boolean extractObjectMetaData(	String name,
										SpreadSheetService service,
										GoogleData.Worksheet wrkSheet,
										Map<String, Schema.SObjectField> fieldMap)
	{
		
		
		string cellFeedUrl = wrkSheet.getCellFeedUrl();
		// specify range to avoid data overflow
		Integer totFields = fieldMap.values().size(); 
		Integer fldsPerBatch = 20;
		GoogleData feed;
		GoogleData.Cell cell;
		Integer startIndex=1,endIndex=1;
		if(fieldMap.keySet().size()> fldsPerBatch){
			startIndex=1;
			endIndex=fldsPerBatch+1;
		}else{
			startIndex=1;
			endIndex=totFields+1;
		}
		logMessages.add('['+name+']: Fetching Cell Range: start='+startIndex+',end='+endIndex);
		try{
			feed = service.getFeedRange( cellFeedUrl ,'R'+startIndex+'C1:R'+endIndex+'C5');
		}catch(Exception ex){
			system.debug('['+name+']:ERROR:'+ex.getMessage());
			return false;	
		}	
		logMessages.add('['+name+']: feed is null '+(feed == null));
		Map<String,GoogleData.Cell> cellMap = wrkSheet.cellFactoryAsMap( feed.entries );  
		if(startIndex == 1){
			cell = cellMap.get('R1C1');
			if(cell != null)
				cell.content = 'Field Label';
			
			cell = cellMap.get('R1C2');
			if(cell != null)
				cell.content = 'Datatype';
	
			cell = cellMap.get('R1C3');
			if(cell != null)
				cell.content = 'Picklist Values';
	
			cell = cellMap.get('R1C4');
			if(cell != null)
				cell.content = 'Field Properties';
			
			cell = cellMap.get('R1C5');
			if(cell != null)
				cell.content = 'API Field Name';
		}		
		Integer currRow=1,curNbrFlds=1;
		Schema.DescribeFieldResult fldDescRes;
		List <Schema.PicklistEntry> pickValList;
		String tempStr,currFldName;

		try{
			for(String fldName: fieldMap.keySet()){
				currFldName = fldName;
				currRow++;
				curNbrFlds++;
				fldDescRes = fieldMap.get(fldName).getDescribe();
				cell = cellMap.get('R'+currRow+'C1');
				//logMessages.add('['+name+'] Got cell for row#'+currRow+', isNull='+(cell ==null)); 
				cell.content = fldDescRes.getLabel();
		
				cell = cellMap.get('R'+currRow+'C2');
				tempStr = fldDescRes.getType()+'';
				tempStr = tempStr.replace('.',';');
				List<String> tokens = tempStr.split(';');
				if(tokens.size() == 3){
					tempStr = tokens[2];	
				}
				if(fldDescRes.getType() == Schema.DisplayType.String ){
					cell.content = tempStr+'('+fldDescRes.getLength()+')';
				}else{
					cell.content = tempStr;
				} 
	
				cell = cellMap.get('R'+currRow+'C3');
				if(fldDescRes.getType() == Schema.DisplayType.Picklist){
					pickValList = fldDescRes.getPicklistValues();
					if(pickValList!= null && pickValList.size() >0){
						tempStr='';
						for(Schema.PicklistEntry entry:pickValList){
							if(entry.isActive()){
								tempStr+=entry.getLabel()+',';	
							}
						}
						if(cell != null){
							tempStr = tempStr.replace('&',''');
							cell.content = tempStr;
						}
					}
				}
	
				cell = cellMap.get('R'+currRow+'C4');
				tempStr ='';
				if(fldDescRes.isNillable()){
					tempStr +='Required';
				}
				if(cell != null)
					cell.content = tempStr;
	
				cell = cellMap.get('R'+currRow+'C5');
				if(cell != null)
					cell.content = fldDescRes.getName();
				
				if(curNbrFlds == fldsPerBatch){
					if(totFields < currRow){
						break;
					}
					service.updateCells(wrkSheet,cellMap.values());
					curNbrFlds=0;
					/*
					logMessages.add('['+name+']: currRow='+currRow);
					logMessages.add('['+name+']: (currRow+fldsPerBatch)='+(currRow+fldsPerBatch));
					logMessages.add('['+name+']: totFields='+totFields);
					*/
					if(totFields > (currRow+fldsPerBatch)){
						startIndex = currRow+1;
						endIndex = currRow+1+fldsPerBatch;
					}else{
						startIndex = currRow+1;
						endIndex = totFields+1;
					}
					feed = service.getFeedRange( cellFeedUrl ,'R'+startIndex+'C1:R'+endIndex+'C5');
					//logMessages.add('['+name+']: Got new range of cells.....');
					cellMap = wrkSheet.cellFactoryAsMap( feed.entries );  
						
				}
				logMessages.add('['+name+']: Completed Processing Field:'+fldName);
			}
			logMessages.add('['+name+']: Calling for remaining fields...');
			logMessages.add('['+name+']: cellMap is null:'+(cellMap == null));
			service.updateCells(wrkSheet,cellMap.values());
			logMessages.add('['+name+']: Status Code:'+service.response.getStatusCode());
			
		}catch(Exception ex){
			logMessages.add('['+name+']:ERROR: currFldName='+currFldName+',currRow='+currRow);
			system.debug('['+name+']:ERROR:'+ex.getMessage());
			//throw ex;
			return false;	
		}
		logMessages.add('['+name+']: Completed All Fields');
		return true;
	}
	
	
	public static testmethod void testSchemaExtract(){
		SchemaExtractController controller = new SchemaExtractController();
		List<SelectOption> tt = controller.ObjectList;
		string tmp = controller.AuthToken;
		controller.AuthToken = 'bad'; 
		controller.getSpreadSheets(); 
		controller.extractMore();
		controller.continuePage();
	
	}
	
}

Component

This component is called getAuthSubToken, it is used in the main page to grab a session token with the user's permission. Careful readers will note that this component may redirect your browser twice, once to get a token, and once again to exchange that token for a session token used by the API. There may be a better way of doing this, but this appears to work.

The final result is the sesstoken query string is present on the URL for the Visualforce page to use. Many thanks to Anand Narasimhan for the ideas used here.

If you already have a session id, you can pass this on the query string like so to your page: &sesstoken=validsession, and this component will quietly do nothing.

<apex:component controller="AuthSubTokenCompContrller">
	<!--  controller provides the method for getconvertTokenToSession 
	This converts a token into a sesstoken and stores it in the current page
	removing the one-time use token from the querystring in the process
	-->

	<apex:attribute name="scope"
		description="The scope url sent to Google Data API" type="String"
		required="true" />

	<apex:outputPanel rendered="{!AND($CurrentPage.parameters.token == null,  $CurrentPage.parameters.sesstoken == null)}">
		<script> 
	 
	var forceReDirect = '/_ui/core/google/GoogleAuthSubCallback?url=' ;
    var googleRedir = 'https://www.google.com/accounts/AuthSubRequest?next=';
 	var next = window.location.pathname;
 					
	// destination (relateive path) that will capture the token and store it for your app	
	// you can pass your own query string parms
	var myApp = encodeURIComponent( next ); 
    var nextUrl = window.location.protocol + '//' + window.location.host + 
    			forceReDirect + myApp ;  
    var tokenRequestUrl  = googleRedir + encodeURIComponent( nextUrl ) + 
		        "&session=1&secure=0&scope={!scope}";
    window.location.href = tokenRequestUrl; // go there now
</script>
	</apex:outputPanel>

	<apex:outputPanel rendered="{!AND( $CurrentPage.parameters.token != null , $CurrentPage.parameters.sesstoken == null)}">
		<script>
		 window.location.href = "{!convertTokenToSession}"; 
		</script>
	</apex:outputPanel>

	<apex:outputPanel rendered="{!$CurrentPage.parameters.sesstoken != null}" id="session">
		<!--  this panel was used for debugging  -->
	</apex:outputPanel>

</apex:component>

Component controller

public class AuthSubTokenCompContrller {
  // should only be used after we have a token
    public string convertTokenToSession { get { 
    	string tt = ApexPages.currentPage().getParameters().get('token');
    	system.assert(tt!=null,' missing token paramater');
    	ApexPages.currentPage().getParameters().remove('token');
    	
    	string st = AuthSubUtil.exchangeForSessionToken( tt ) ;
    	ApexPages.currentPage().getParameters().put('sesstoken',st);
    	system.debug( ApexPages.currentPage().getUrl()); 
    	return ApexPages.currentPage().getUrl(); 
    } }
}

Instructions

Install the Toolkit classes, then install these sources into your developer org. Create a Tab that will launch the SchemaExtract.page with no parameters.

Run this page and allow the access to your google account, select a spreadsheet, then select an object and finally the export is performed. You can now view the sheet in Google Docs, the app adds a new workbook for each object you select.

You must install in this order for best results:

Paste into your developer org

  • the Apex Classes for component controller (AuthSubTokenCompContrller) and page controller ( SchemaExtractController)
  • create the visualforce component getAuthSubToken
  • finally create the Visualforce page SchemaExtract

Create a tab that will launch SchemaExtract

Step 1.

Wiki.png


Step 2. ExtractStep2.png


Step 3.

ExtractStep3.png


Step 4. ExtractStep4.png

Credits

Many thanks to Anand Narasimhan for the idea and a working set of code to start with, I proceeded to cut that down to the simplest demo possible and added the visualforce component that does the AuthSub process to abstract that a bit. Enjoy! Ron Hess