Döcu Content

Google Worksheets inputText

JavaAgent/JavaBean

Xpages Application


System Requirements:

Download Domino Designer 8.5.3 Environment (DDE)

http://www.ibm.com/developerworks/downloads/ls/dominodesigner/


Download /Make available Google Spreadsheets API

https://developers.google.com/google-apps/spreadsheets/

https://developers.google.com/google-apps/documents-list/



Introduction:

In this tutorial, we are attempting to use inputText controls to Add data to Google Drive Worksheet. Modifcation again is made to existing code to convert to JavaAgent, and bring view data submitted via JavaBean into program to access Google Spreadsheet. See below for code samples to run JavaAgent program, using Xpages form controls...


Disclaimer:

Information contained in the following is presented as is. This tutorial assumes you have basic Lotus Notes Configuration, Programming knowledge, and are familiar with Google APIs.


Döcu Content JavaAgent/JavaBean/Xpages

At this point, we assume you have created skeleton code for Xpages Form, JavaAgent, and JavaBean... Copy and paste code into DDE, over generated code, run your App to submit new Worksheet data to back-end, perform handshake with Google, areas of interests are highlighted for your convenience.


Related Info:

https://www.youtube.com/watch?v=hemoGCIygZA&list=UUSImDTpK0oe7QrPsYOE4nww



Copy and Paste Page Design code


WorkSheetsAddJavaAgent.java;


/**

* Created from copy: 2014.05.17.2.34.AM

* GoogleAddRowForXpagesJavaAgent | WorksheetsAddJavaAgent.java

* Inser Row(s) to Worksheets in specific Spreadsheet, housed on Google Drive

*/

//...

//Google imports

import com.google.gdata.client.spreadsheet.*;

import com.google.gdata.data.spreadsheet.*;

import com.google.gdata.util.*; //...

//Java imports

import java.io.IOException;

import java.net.*;

import java.util.*;


//...

//Lotus Domino imports

import lotus.domino.AgentBase;

import lotus.domino.AgentContext;

import lotus.domino.Document;

import lotus.domino.Session;

import lotus.domino.View;

import lotus.domino.local.Database;


/**

* @author Dököll Solutions, Inc.

* @version 2014.05.17.2.34.AM

*

*/

public class WorksheetsAddJavaAgent extends AgentBase {


//declare and initialise variables for Worksheets data

static String GOOGLE_SHEET_VIEW = "GoogleWorksheetsDataView";

private String FirstName = "firstname";

private String LastName = "lastname";

private String Age = "age";

private String Height = "height";



public void NotesMain() {


try {


Session session = getSession();

// load info to console for debugging purposes


// load agentContext

AgentContext agentContext = session.getAgentContext();

// ...

// find database based on session found

Database database = (Database) agentContext.getCurrentDatabase();

// Find view in question according to current database

View view = database.getView(GOOGLE_SHEET_VIEW);

// declare document variables

Document currDoc;

Document tempDoc;

// grab first doc

currDoc = view.getFirstDocument();


while (currDoc != null) {

// prepare values to plug into Google Spredsheet/Worksheet(s)

String SubjectFirstName = currDoc

.getItemValueString("subjectFirst");

String SubjectLastName = currDoc

.getItemValueString("subjectLast");

String SubjectAge = currDoc.getItemValueString("subjectAge");

String SubjectHeight = currDoc

.getItemValueString("subjectHeight");


// grab Google Credds

String USERNAME = "yourgoogleaccount@gmail.com";

String PASSWORD = "youraccountpassword";


// reference Service type

SpreadsheetService service = new SpreadsheetService(

"CompanyWorksheetsAdd");

try {

service.setUserCredentials(USERNAME, PASSWORD);

} catch (AuthenticationException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}


// TODO: Authorize the service object for a specific user (see

// other sections)


// Define the URL to request. This should never change.

URL SPREADSHEET_FEED_URL = null;

try {

SPREADSHEET_FEED_URL = new URL(

"https://spreadsheets.google.com/feeds/spreadsheets/private/full");

} catch (MalformedURLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}


try {

// Make a request to the API and get all spreadsheets.

SpreadsheetFeed feed = (SpreadsheetFeed) service.getFeed(

SPREADSHEET_FEED_URL, SpreadsheetFeed.class);

List<SpreadsheetEntry> spreadsheets = feed.getEntries();


if (spreadsheets.size() == 0) {

// TODO: There were no spreadsheets, act accordingly.

}


// TODO: Choose a spreadsheet more intelligently based on

// your

// app's needs.

SpreadsheetEntry spreadsheet = spreadsheets.get(0);

System.out.println(spreadsheet.getTitle().getPlainText());


// Get the first worksheet of the first spreadsheet.

// TODO: Choose a worksheet more intelligently based on your

// app's needs.

WorksheetFeed worksheetFeed = service.getFeed(spreadsheet

.getWorksheetFeedUrl(), WorksheetFeed.class);

List<WorksheetEntry> worksheets = worksheetFeed

.getEntries();

WorksheetEntry worksheet = worksheets.get(1);


// Fetch the list feed of the worksheet.

URL listFeedUrl = worksheet.getListFeedUrl();

ListFeed listFeed = (ListFeed) service.getFeed(listFeedUrl,

ListFeed.class);


// Create a local representation of the new row.

ListEntry row = new ListEntry();

row.getCustomElements().setValueLocal(FirstName,

SubjectFirstName);

row.getCustomElements().setValueLocal(LastName,

SubjectLastName);

row.getCustomElements().setValueLocal(Age, SubjectAge);

row.getCustomElements()

.setValueLocal(Height, SubjectHeight);


// Send the new row to the API for insertion.

row = (ListEntry) service.insert(listFeedUrl, row);

} catch (MalformedURLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

} catch (IOException e) {

// TODO Auto-generated catch block

e.printStackTrace();

} catch (ServiceException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}


// Get next document

tempDoc = view.getNextDocument(currDoc);


// recycle currDoc

currDoc.recycle();

// set currDoc to tempDoc

currDoc = tempDoc;


session.recycle();


}


} catch (Exception e) {

e.printStackTrace();

}


}


}



Döcu Content JavaBean/Xpages Button

We are using a copy of prior code to run Xpages and insert into Worksheets on Google Drive using inputText controls and a button. Copy and paste code below into DDE and run this boy...


SendGoogleRecordJavaBean.java;

/**

* Created from Original: 2014.05.19.5.25.AM

* New Google Spreadsheet data Posting via Xpages form and JavaBean

*/

package com.dokoll.solutions.inc.google.dev;


//...

//Java imports...

import java.io.Serializable;

import java.text.SimpleDateFormat;

import java.util.Calendar;

import java.util.Random;


//...

//faces imports...

import javax.faces.context.FacesContext;

import javax.servlet.http.Cookie;

import javax.servlet.http.HttpServletRequest;


//domino imports...

import lotus.domino.NotesException;

import lotus.domino.local.Database;

import lotus.domino.local.Document;


/**

* @author Dököll Solutions, Inc.

* @version 2014.05.19.5.25.AM

*/


public class SendGoogleRecordJavaBean implements Serializable {


private static final long serialVersionUID = 1L;

private String Date_Format = "MM/dd/yy";

// declare variables, and add static values as test

SimpleDateFormat simpledateformat = new SimpleDateFormat(Date_Format);

Calendar calendar = Calendar.getInstance();

// declare variables

private String UserNameValue;

private String RoleNameValue;

private String UserID;

private String RoleID;

// @version 2011.08.16.3.14.PM

private String ItemDate = simpledateformat.format(calendar.getTime());

// Reformat the date to our needs, we needs this format

// (2010.12.30.10.13.AM)

// @version: 2013.11.03.10.14.PM

private String Doc_Date_Format = "yyyy.MM.dd.HH.mm a";

SimpleDateFormat docdateformat = new SimpleDateFormat(Doc_Date_Format);

Calendar docalendar = Calendar.getInstance();

private String ItemNumber = docdateformat.format(docalendar.getTime());

// 2011.09.20.11.20.AM

// Randomizing to get a ticket number

// TO DO: add the date or the user's ID to the random number.

// Alternatively, once can reformat the a date variable as such

// 2012.11.10.8.20 + session ID, or random Number

// arrive at a fancy ticket number, for this exercise the random will do

Random Rand = new Random();

// private String ItemNumber;

private String SubjectFirst;

private String SubjectLast;

private String SubjectAge;

private String SubjectHeight;

private String Categories;


/**

* @return the categories

*/

public String getCategories() {

return Categories;

}


/**

* @param categories

* the categories to set

*/

public void setCategories(String categories) {

Categories = categories;

}


/**

* @return the userID

*/

public String getUserID() {


return UserID;

}


/**

* @param userID

* the userID to set

*/

public void setUserID(String userID) {

UserID = userID;

}


/**

* @return the roleID

*/

public String getRoleID() {

return RoleID;

}


/**

* @param roleID

* the roleID to set

*/

public void setRoleID(String roleID) {

RoleID = roleID;

}


/**

* @return the userNameValue

*/

public String getUserNameValue() {

return UserNameValue;

}


/**

* @param userNameValue

* the userNameValue to set

*/

public void setUserNameValue(String userNameValue) {

UserNameValue = userNameValue;

}


/**

* @return the roleNameValue

*/

public String getRoleNameValue() {

return RoleNameValue;

}


/**

* @param roleNameValue

* the roleNameValue to set

*/

public void setRoleNameValue(String roleNameValue) {

RoleNameValue = roleNameValue;

}


/**

* @return the subjectFirst

*/

public String getSubjectFirst() {

return SubjectFirst;

}


/**

* @param subjectFirst

* the subjectFirst to set

*/

public void setSubjectFirst(String subjectFirst) {

SubjectFirst = subjectFirst;

}


/**

* @return the subjectLast

*/

public String getSubjectLast() {

return SubjectLast;

}


/**

* @param subjectLast

* the subjectLast to set

*/

public void setSubjectLast(String subjectLast) {

SubjectLast = subjectLast;

}


/**

* @return the subjectAge

*/

public String getSubjectAge() {

return SubjectAge;

}


/**

* @param subjectAge

* the subjectAge to set

*/

public void setSubjectAge(String subjectAge) {

SubjectAge = subjectAge;

}


/**

* @return the subjectHeight

*/

public String getSubjectHeight() {

return SubjectHeight;

}


/**

* @param subjectHeight

* the subjectHeight to set

*/

public void setSubjectHeight(String subjectHeight) {

SubjectHeight = subjectHeight;

}


/**

* @return the itemNumber

*/

public String getItemNumber() {

return ItemNumber + "-" + Rand.nextInt();

}


/**

* @param itemNumber

* the itemNumber to set

*/

public void setItemNumber(String itemNumber) {

ItemNumber = itemNumber;

}


/**

* @return the itemDate

*/

public String getItemDate() {

return ItemDate;

}


/**

* @return the pageID

*/

public String getPageID() {

return PageID;

}


/**

* @param pageID

* the pageID to set

*/

public void setPageID(String pageID) {

PageID = pageID;

}


/**

* @param itemDate

* the itemDate to set

*/

public void setItemDate(String itemDate) {

ItemDate = itemDate;

}


// 2011.09.20.12.23.PM

// Grabbing User IPs

// TO DO: Get User's real IP, this item gets the external IP

HttpServletRequest httpServletRequest = (HttpServletRequest) FacesContext

.getCurrentInstance().getExternalContext().getRequest();

String UserIP = httpServletRequest.getRemoteAddr();


HttpServletRequest reqURL = (HttpServletRequest) FacesContext

.getCurrentInstance().getExternalContext().getRequest();

String PageID = reqURL.getRequestURL().toString();


/**

* @return the userIP

*/

public String getUserIP() {

return UserIP;

}


/**

* @param userIP

* the userIP to set

*/

public void setUserIP(String userIP) {

UserIP = userIP;

}


public SendGoogleRecordJavaBean() {


// get userCookies

FacesContext facesContext = FacesContext.getCurrentInstance();

String cookieName = null;

Cookie cookie[] = ((HttpServletRequest) facesContext

.getExternalContext().getRequest()).getCookies();

if (cookie != null && cookie.length > 0) {

for (int i = 0; i < cookie.length; i++) {

cookieName = cookie[i].getName();

if (cookieName.equals("cookieKeyUser")) {

UserNameValue = cookie[i].getValue();

System.out

.println("SendGoogleRecordJavaBean! Found this UserNameValue Cookie..."

+ UserNameValue);

}

if (cookieName.equals("cookieKeyRole")) {

RoleNameValue = cookie[i].getValue();

System.out

.println("SendGoogleRecordJavaBean! Found this RoleNameValue Cookie..."

+ RoleNameValue);

} else

System.out.println("Cookies not found...");


// TODO: Add this method to JSFUtil class,

// also delete the cookies when logging out

}


}

}


// button code

public void submitEntry() {


try {


// get the current database being used

Database database = (Database) FacesContext.getCurrentInstance()

.getApplication().getVariableResolver().resolveVariable(

FacesContext.getCurrentInstance(), "database");


// instantiate the document create call

Document submitDocument = (Document) database.createDocument();

// submit using GoogleSpreadsData form (Alternatively, one can add

// the Alias of that form)

submitDocument.appendItemValue("form", "GoogleWorksheetsData");

// using appendItemValue to insert in the fields needed

// notice we are referencing the field on the Xpage, and the

// JavaBean variables

submitDocument.appendItemValue("userID", UserID);

submitDocument.appendItemValue("roleID", RoleID);

submitDocument.appendItemValue("itemNumber", ItemNumber);

submitDocument.appendItemValue("subjectFirst", SubjectFirst);

submitDocument.appendItemValue("subjectLast", SubjectLast);

submitDocument.appendItemValue("subjectAge", SubjectAge);

submitDocument.appendItemValue("subjectHeight", SubjectHeight);

submitDocument.appendItemValue("categories", Categories);


submitDocument.appendItemValue("userIP", UserIP);

// no need to show this on the Xpage

submitDocument.appendItemValue("itemDate", ItemDate);

submitDocument.appendItemValue("pageID", PageID);


// saves the data, based on above fields to GoogleSpreadsData form

submitDocument.save();


System.out

.println("Connecting to Notes Agent, inserting rows to Worksheets...");

// ...

// Run the agent...

database.getAgent("GoogleAddRowForXpagesJavaAgentAlias")

.runOnServer();


// cleans up the system

submitDocument.recycle();


// throwable initialized if there is an error, either in the field

// or the query

} catch (NotesException e) {

// print this error to the server

e.printStackTrace();

}


}


}






xpgoogleworksheetsinsert.xsp;



<?xml version="1.0" encoding="UTF-8"?>

<xp:view xmlns:xp="http://www.ibm.com/xsp/core" pageTitle="Bulletin Posting Page"

xmlns:xc="http://www.ibm.com/xsp/custom" style="background-color:rgb(234,253,239)">



<xp:this.resources>

<xp:script src="/docucontentGACodeTest.js" clientSide="true" />

</xp:this.resources>



<xp:this.navigationRules>

<xp:navigationRule outcome="xsp-success" viewId="/xpbycategory.xsp" />


<xp:navigationRule outcome="xsp-failure" viewId="/xpnotauthorized.xsp" />


</xp:this.navigationRules>


<xp:tabbedPanel id="tabbedPanel1">

<xp:tabPanel label="Insert Record" id="tabPanel1">

<xp:panel

style="width:682.0px;text-align:center;font-size:18pt;height:349.0px">

<p style="text-align:center;font-size:19pt">

<xp:div style="text-align:center;font-size:20pt">

<xp:div style="text-align:center;font-size:21pt">

<b style="font-size:22pt">

<xp:div style="text-align:left;font-size:23pt">

<xp:div style="text-align:center;font-size:24pt">

<xp:table align="left"

style="background-color:rgb(208,242,225);width:100.0%;font-size:9pt">

<xp:tr style="font-size:10pt">

<xp:td style="font-size:11pt">


<xp:div style="font-weight:bold;font-size:12pt">

<xp:div style="font-size:13pt">

<xp:label value="Subject FullName:" id="subject_Label1"

for="subject1"

style="text-align:left;font-weight:bold;width:172.0px;font-size:12pt" />


</xp:div>

</xp:div>

</xp:td>

<xp:td style="margin-left:-24px;font-size:11pt">

<xp:div style="font-size:12pt">

<xp:div style="font-size:13pt">



<xp:inputText value="#{SendGoogleRecordJavaBean.subjectFirst}"

id="inputText3" style="font-size:9pt" />

&#160;

<xp:inputText value="#{SendGoogleRecordJavaBean.subjectLast}"

id="inputText4" style="font-size:9pt" />


<xp:inputText value="#{SendGoogleRecordJavaBean.userID}"

id="inputText5" style="font-size:9pt;width:131.0px"

rendered="true" defaultValue="#{SendGoogleRecordJavaBean.userNameValue}"

disabled="true" />

</xp:div>

</xp:div>


</xp:td>

</xp:tr>

<xp:tr style="font-size:10pt">

<xp:td style="font-size:11pt">

<xp:div style="font-weight:bold;font-size:12pt">

<xp:div style="font-size:13pt">

<xp:label value="Categories:" id="categories_Label1"

for="categories1" style="font-weight:bold;font-size:12pt" />


</xp:div>

</xp:div>


</xp:td>

<xp:td style="margin-left:-24px;font-size:11pt">

<xp:div style="font-size:12pt">

<xp:div style="font-size:13pt">


<xp:comboBox id="comboBox1"

value="#{SendBulletinBean.categories}" style="font-size:9pt">

<xp:selectItem itemLabel="Select Option"

itemValue="" />


<xp:selectItem itemLabel="Client Dashboards"

itemValue="Client Dashboards" />

<xp:selectItem itemLabel="Misc Dashboards"

itemValue="Misc Dashboards" />


<xp:selectItem itemLabel="Resources Dashboards"

itemValue="Resources Dashboards" />


<xp:selectItem itemLabel="Samples Dashboards"

itemValue="Samples Dashboards" />


<xp:selectItem itemLabel="Web Dashboards"

itemValue="Web Dashboards" />


</xp:comboBox>

</xp:div>

</xp:div>


</xp:td>

</xp:tr>

<xp:tr style="font-size:10pt">

<xp:td style="font-size:11pt">

<xp:div style="font-weight:bold;font-size:12pt">

<xp:div style="font-size:13pt">

<xp:label value="Body:" id="body_Label1" for="body1"

style="font-weight:bold;font-size:12pt" />


</xp:div>

</xp:div>

<xp:div style="font-size:12pt" />


</xp:td>

<xp:td style="margin-left:-24px;font-size:11pt">

<xp:div style="font-size:12pt">

<xp:div style="font-size:13pt">

<xp:inputTextarea value="#{SendBulletinBean.body}"

id="body1" style="height:128.0px;width:519.0px;font-size:9pt" />



</xp:div>

</xp:div>




</xp:td>



</xp:tr>


<xp:tr style="font-size:10pt">

<xp:td style="font-size:11pt">


<xp:div style="font-weight:bold;font-size:12pt">

<xp:div style="font-size:13pt">

<xp:label value="Age:" id="label3" for="subject1"

style="text-align:left;font-weight:bold;width:172.0px;font-size:12pt"

rendered="false" />


</xp:div>

</xp:div>

</xp:td>

<xp:td style="margin-left:-24px;font-size:11pt">

<xp:div style="font-size:12pt">

<xp:div style="font-size:13pt">




<xp:inputText value="#{SendGoogleRecordJavaBean.subjectAge}"

id="inputText1" style="font-size:9pt" />

&#160;


</xp:div>

</xp:div>


</xp:td>

</xp:tr>

<xp:tr style="font-size:10pt">

<xp:td style="font-size:11pt">

<xp:div style="font-weight:bold;font-size:12pt">

<xp:div style="font-size:13pt">

<xp:label value="Height:" id="label4" for="categories1"

style="font-weight:bold;font-size:12pt" rendered="false" />


</xp:div>

</xp:div>


</xp:td>

<xp:td style="margin-left:-24px;font-size:11pt">

<xp:div style="font-size:12pt">

<xp:div style="font-size:13pt">



<xp:inputText value="#{SendGoogleRecordJavaBean.subjectHeight}"

id="inputText2" style="font-size:9pt" />

&#160;


</xp:div>

</xp:div>


</xp:td>

</xp:tr>





<xp:tr style="font-size:10pt">

<xp:td style="font-size:11pt" />


<xp:td style="margin-left:-24px;font-size:11pt">



<xp:div style="font-size:12pt">

<xp:div style="font-size:13pt">

<xp:messages layout="table"

style="color:red;font-size:14pt" id="messages1" />


</xp:div>

</xp:div>

</xp:td>

</xp:tr>

<xp:tr style="font-size:10pt">

<xp:td style="font-size:11pt" />


<xp:td style="margin-left:-24px;font-size:11pt">

<xp:div style="font-size:12pt">

<xp:div style="font-size:13pt">

<xp:button value="Submit" id="button1" style="font-size:9pt">

<xp:eventHandler event="onclick" submit="true"

refreshMode="complete" immediate="false" save="true"

id="eventHandler1">


<xp:this.action><![CDATA[#{javascript:SendGoogleRecordJavaBean.submitEntry()}]]></xp:this.action>

</xp:eventHandler>

</xp:button>

&#160;


</xp:div>

</xp:div>

</xp:td>

</xp:tr>

</xp:table>

<xp:span style="font-size:20pt;font-weight:bold" />


<xp:span style="font-size:20pt" />


</xp:div>

<xp:div style="text-align:center;font-style:italic;font-size:24pt">


<xp:span style="font-weight:bold;font-style:italic;font-size:17pt" />


</xp:div>

</xp:div>

</b>

</xp:div>

</xp:div>


</p>

</xp:panel>

</xp:tabPanel>

</xp:tabbedPanel>

<xp:div style="text-align:center" />

<xp:span style="font-size:20pt;font-weight:bold">

<xp:span style="font-weight:bold;font-style:italic;font-size:17pt" />

<xp:span style="font-size:20pt" />

</xp:span>

<xp:div style="text-align:center;font-style:italic;font-size:16pt">

<div id="content" style="text-align:center;font-size:17pt" />



<div id="footer" style="font-size:17pt" />






</xp:div>

</xp:view>




Döcu Content Worksheets View (Xpages/Google Drive)


Full page design: Here is the button in question...





current cell records







TIP: Be sure to login to Google Drive account prior to launching code...


Conclusion:

You can now run code from Xpages form, add text to inputText controls, click button to insert row(s) to Worksheets, publically available on Google Drive.


Questions, comments, please post a brief message on our Contact form on the main site.


Thank you for coming...


Version:2014.05.19.9.29.AM