Knowledge Sharing
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. Modification 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 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 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;
"1.0" encoding="UTF-8"?>
"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)"
"xsp-success" viewId="/xpbycategory.xsp" />
"xsp-failure" viewId="/xpnotauthorized.xsp" />
"tabbedPanel1"
"Insert Record" id="tabPanel1"
style="width:682.0px;text-align:center;font-size:18pt;height:349.0px"
"text-align:center;font-size:19pt"
"text-align:center;font-size:20pt"
"text-align:center;font-size:21pt"
"font-size:22pt"
"text-align:left;font-size:23pt"
"text-align:center;font-size:24pt"
"left"
style="background-color:rgb(208,242,225);width:100.0%;font-size:9pt"
"font-size:10pt"
"font-size:11pt"
"font-weight:bold;font-size:12pt"
"font-size:13pt"
"Subject FullName:" id="subject_Label1"
for="subject1"
style="text-align:left;font-weight:bold;width:172.0px;font-size:12pt" />
"margin-left:-24px;font-size:11pt"
"font-size:12pt"
"font-size:13pt"
"#{SendGoogleRecordJavaBean.subjectFirst}"
id="inputText3" style="font-size:9pt" />
 
"#{SendGoogleRecordJavaBean.subjectLast}"
id="inputText4" style="font-size:9pt" />
"#{SendGoogleRecordJavaBean.userID}"
id="inputText5" style="font-size:9pt;width:131.0px"
rendered="true" defaultValue="#{SendGoogleRecordJavaBean.userNameValue}"
disabled="true" />
"font-size:10pt"
"font-size:11pt"
"font-weight:bold;font-size:12pt"
"font-size:13pt"
"Categories:" id="categories_Label1"
for="categories1" style="font-weight:bold;font-size:12pt" />
"margin-left:-24px;font-size:11pt"
"font-size:12pt"
"font-size:13pt"
"comboBox1"
value="#{SendBulletinBean.categories}" style="font-size:9pt"
"Select Option"
itemValue="" />
"Client Dashboards"
itemValue="Client Dashboards" />
"Misc Dashboards"
itemValue="Misc Dashboards" />
"Resources Dashboards"
itemValue="Resources Dashboards" />
"Samples Dashboards"
itemValue="Samples Dashboards" />
"Web Dashboards"
itemValue="Web Dashboards" />
"font-size:10pt"
"font-size:11pt"
"font-weight:bold;font-size:12pt"
"font-size:13pt"
"Body:" id="body_Label1" for="body1"
style="font-weight:bold;font-size:12pt" />
"font-size:12pt" />
"margin-left:-24px;font-size:11pt"
"font-size:12pt"
"font-size:13pt"
"#{SendBulletinBean.body}"
id="body1" style="height:128.0px;width:519.0px;font-size:9pt" />
"font-size:10pt"
"font-size:11pt"
"font-weight:bold;font-size:12pt"
"font-size:13pt"
"Age:" id="label3" for="subject1"
style="text-align:left;font-weight:bold;width:172.0px;font-size:12pt"
rendered="false" />
"margin-left:-24px;font-size:11pt"
"font-size:12pt"
"font-size:13pt"
"#{SendGoogleRecordJavaBean.subjectAge}"
id="inputText1" style="font-size:9pt" />
 
"font-size:10pt"
"font-size:11pt"
"font-weight:bold;font-size:12pt"
"font-size:13pt"
"Height:" id="label4" for="categories1"
style="font-weight:bold;font-size:12pt" rendered="false" />
"margin-left:-24px;font-size:11pt"
"font-size:12pt"
"font-size:13pt"
"#{SendGoogleRecordJavaBean.subjectHeight}"
id="inputText2" style="font-size:9pt" />
 
"font-size:10pt"
"font-size:11pt" />
"margin-left:-24px;font-size:11pt"
"font-size:12pt"
"font-size:13pt"
"table"
style="color:red;font-size:14pt" id="messages1" />
"font-size:10pt"
"font-size:11pt" />
"margin-left:-24px;font-size:11pt"
"font-size:12pt"
"font-size:13pt"
"Submit" id="button1" style="font-size:9pt"
"onclick" submit="true"
refreshMode="complete" immediate="false" save="true"
id="eventHandler1"
SendGoogleRecordJavaBean.submitEntry()}]]>
 
"font-size:20pt;font-weight:bold" />
"font-size:20pt" />
"text-align:center;font-style:italic;font-size:24pt"
"font-weight:bold;font-style:italic;font-size:17pt" />
"text-align:center" />
"font-size:20pt;font-weight:bold"
"font-weight:bold;font-style:italic;font-size:17pt" />
"font-size:20pt" />
"text-align:center;font-style:italic;font-size:16pt"
"content" style="text-align:center;font-size:17pt" />
"footer" style="font-size:17pt" />
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.
Related Info:
http://www.dokollsolutionsinc.com/CutAndPasteGoogleSheetXpagesApp.html
Download:
http://www.openntf.org/main.nsf/project.xsp?r=project/D%C3%B6cu%20Content%20V3.0/releases/4C89F490BF16455586257D2A000B2EB5
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