bookmark_borderGoogle Sheets API – batchUpdate

batchUpdate() works on values if you use sheet.values().batchUpdate() and works on sheet properties if you use sheet..batchUpdate()

So I wanted to be notified for my tradingBot (made in Python), if a certain KPI was met. On my Mac, no problem. I use Notify.

from notifypy import Notify

But I wanted to receive a notification even when I am not sitting at my computer. I figured with the help of Google I could achieve this. So I write to a Google Sheet some values and there I trigger an AppScript which sends me an email if KPI is met.

First attempt:

    values = [[datetime, last, change, gain, loss, rsi]]
    part1 = {
        'values': values

    result = sheet.values().update(
        spreadsheetId=SAMPLE_SPREADSHEET_ID, range="Sheet1!A2:F2",
        valueInputOption="RAW", body=part1).execute()

    values = [["insert into ticker (datetime, last, change,gain,loss) values ( '{}',{:f} ,{:f} ,{:f}, {:f} )".format(
        datetime, last, change, gain, loss, rsi)]]
    part2 = {
        "values": values
    result = sheet.values().update(
        spreadsheetId=SAMPLE_SPREADSHEET_ID, range="Sheet1!A5",
        valueInputOption="RAW", body=part2).execute()

execute() was called twice, for each row I update. Hence triggering the AppScript (which sends an email) twice. I always wondered why I receive duplicates. Couldn’t figure it out.

Then I received a warning from Google: too many emails sent, with some sort of Log

Google AppScript Log

So I saw: aha, it actually calls the “myFunction” twice. I immediately knew it had to do with how I update the sheet.

I quickly figured there’s a “batchUpdate” function. But documentation was awfully bad. Sheets does know 2 different kind of requests. One kind updates the sheet’s properties (colours/rows, design stuff). The other updates it’s values. BUT they don’t tell that very explicitly. By luck I figured it works like this:

    values = [datetime, last, change, gain, loss, rsi]
    part1 = {
        "range": "Sheet1!A2:F2",

        "values": [

    values = [["insert into ticker (datetime, last, change,gain,loss) values ( '{}',{:f} ,{:f} ,{:f}, {:f} )".format(
        datetime, last, change, gain, loss, rsi)]]
    part2 = {
        "range": "Sheet1!A5",
        "values": values

    requests = {
        "valueInputOption": "RAW",
        "data": [

            part1, part2



and the important part is the “.values()” part in the last line. I first tried without it and Google told me: don’t know range, don’t know data, don’t find fields and other stuff.

As soon as I added the .values() it worked seamless

Looking forward to your comments

bookmark_borderINDIP: add visual indicator to type ahead

that’s how it looks:

field with type ahead waiting for input:
field with type ahead thinking
field with type ahead failure

this is the XML code of the field for the events:
<xp:eventHandler event=”onkeypress” submit=”false” id=”eventHandler2″>
<xp:this.script><![CDATA[if (event.keyCode==9 || event.keyCode==13){

<xp:eventHandler event=”onblur” submit=”false” id=”eventHandler3″>

and this is the style assigned to the field:
<![CDATA[#{javascript:if( currentDocument.isEditable()){ return ‘useTypeAhead’; }}]]>

this is needed in the CSS:
background: #fff url(“TypeAhead.gif”) no-repeat right;
width: 20px;
.dijitValidationContainer .dijitValidationIcon{
width: 20px;

this is needed in a CSJS library (cudos to Mark Roden and Sven Hasselbach)
function x$(idTag, param, jd){ //Updated 28 Feb 2012
// hardcoded
idTag=idTag.replace(/:/gi, “\:”)+(param ? param : “”);
return( jd==”d” ? “#”+idTag : $(“#”+idTag));

var inputField;

//addVisual function used to add a loading image to the screen and make it visible
//expected input string similar to view1_:id1_:viewPanel1
function addVisual(idTag){
var newImage=”url(‘loading.gif’)” //Change me for your server
inputField=dojo.query(x$(“widget_”+idTag, ” .dijitValidationIcon”, “d”))“backgroundImage”, newImage);“visibility”, “visible”);“backgroundRepeat”, “no-repeat”);“backgroundPosition”, “right”);
dojo.query(x$(“widget_”+idTag, ” .dijitValidationContainer”, “d”)).style(“display”, “block”)


//— hijack dojo XHR calls
//Thanks to Sven Hasselbach for this ajax intercept code
var typeAheadLoad;

dojo.addOnLoad( function(){

 /*** hijacking xhr request ***/
 if( !dojo._xhr )
    dojo._xhr = dojo.xhr;

 dojo.xhr = function(){
       var args = arguments[1];
       if( args[‘content’] ){
          var content = args[‘content’];
             if( content[‘$$ajaxmode’] ){
                if( content[‘$$ajaxmode’] == “typeahead” ){
                   /*** hook in load function ***/
                   typeAheadLoad = args[“load”];

                   /*** overwrite error function ***/
                //   args[“error”] = function(){
                 //     alert(‘Error raised!’)
                //   };
                   /*** hijack load function ***/
                   args[“load”] = function(arguments){
                      /*** On Start ***/
                     // alert(“On Start!”);
                      /*** call original function ***/
                      /*** On Complete ***/
                  if (arguments.toLowerCase()==”<ul></ul>”){
                  var newImage=”url(‘typeAheadFailure.gif’)” //Change me for your server   
        “backgroundImage”, newImage)   
        “background”, “white”)
    dojo._xhr( arguments[0], arguments[1], arguments[2] );

//removeVisual function used to remove the loading image from the screen and make it hidden
//expected input string similar to view1_:id1_:viewPanel1
function removeVisual(idTag){
var inputField=dojo.query(x$(“widget_”+idTag, ” .dijitValidationIcon”, “d”))“backgroundImage”, “”)“visibility”, “hidden”)
dojo.query(x$(“widget_”+idTag, ” .dijitValidationContainer”, “d”)).style(“display”, “none”)

//Checks to see if the visual icon is still running – if so then assume fail and kill it
function checkTimer(idTag){

dojo.query(x$(“widget_”+idTag, ” .dijitValidationContainer”, “d”)).style(“display”, “none”)


bookmark_borderINDIP: how to figure out the id of an element which triggered an event!

here’s the scenario:

We have what we call dynamic tables. It lets the user add as many rows as he requires. Really neat.
In some of the fields we use type ahead.

Recently I figured: it would be nice to see an indicator if the server is “thinking”… something like
I could do it easily via one specific field from which I knew it’s id …

But in those dynamic tables I have no clue on which field the event for type ahead was triggered….

until now:
Cudos to Paul Calhoun with his incredibly useful tips about CSJS (something I usually avoid as much as I can)

There I found the solution! You can call SERVER side JS from CLIENT side JS… isn’t that fantastic?

here’s how:
call the SSJS like this:
“#{javascript:<ServerSide JavaScript>}”

example to alert the id of an element via it’s event, in this case onFocus, which was triggered:
alert( “#{javascript:BackingBean.getElementId(this)}” )

For those ones interested what the getElementId does in my Java Bean:
UIInput input = (UIInput) handler.getParent();
return input.getClientId(FacesContext.getCurrentInstance());

Of course the UIInput could be changed to something more generic like UIComponent

stay tuned for my next blog which tells how I achieved proper visual indication

bookmark_borderINDIP: beware of the document.copytoDatabase method!

We had to merge two applications, so I created a background agent which copied all required documents to the “new” database. The customer tested and we set a due date for the real migration.
The night before actual migration I run an agent which deleted all “test” migration documents before the real agent to copy the documents run again!

Next morning I came to the office and thought: oh my dear. something went wrong! Only a few dozen documents had been copied, I thought!

So I rerun the agent again. First all seemed to be fine. Then a few hours later documents started disappearing. With no obvious reasons.

It took me about 3 hours to figure out that the method document.copyToDatabase would create the same UniversalId again!

So after having deleted the first round of “test” documents and recopied again at due date, the universalids of the documents had been in the deletion list of the LN db already. That’s why they started disappearing again after a while!

So I had to delete everything again to make sure, rerun the copy agent again but this time change the universalid.

Took me roughly a day to fix everything, something which normally would have taken about one hour.

bookmark_borderINDIP: xPages isEditable() won’t work on panels with READ ACL! see my workaround

If you have workflow applications then it’s most likely that you wanna make certain parts of your xPage READonly for certain states (eg. such that an approver cannot change the details of the request he has to approve)

In old days we used access controlled sections. xPages gives us the possibility to add ACLs to panels, that servers (almost) the same purpose.

Why only almost? Cause all elements you put there to set data (buttons, links etc.) won’t be hidden even if you use currentDocument.isEditable().

Here’s my workaround:

on the Visible property of such a button etc.
instead of just:

currentDocument.isEditable() && !YourClass.hasParentReadACL(getClientId(this.getId()))

now create a YourClass (we use our StandardUtil as it contains “standard” methods) and add this method:

public static boolean hasParentReadACL(String id) {
String[] components = id.split(“:”);
UIComponent parent = null;
String parentId = null;
int i = 2;
// the || is important as repeat controls within custom controls use the same id, so we have to
// get their parent as unique parent
while (null == parent || parent instanceof XspDataIterator) {
parentId = components[components.length – i++];
parent = JSFConnector.findComponent(parentId);

UIViewRoot root = FacesContext.getCurrentInstance().getViewRoot();
boolean hasACL = false;

while (root != parent) {
if (parent instanceof UIPanelEx) {
if (null != ((UIPanelEx) parent).getAcl()) {
hasACL = true;
parent = parent.getParent();

return hasACL;

and voila all your additional buttons etc. are hidden if one of the parent panels has an ACL!
Of course you might change the code to really check for a READ ACL. We only use ACLs for READ so this check is at the moment superfluous.

ah.. a not so minor thing: we define the StandardUtil in a SSJS library to easy reference StandardUtil, in your case you may wanna use:
&& com.yourcompany.YourClass.hasParentReadACL()

bookmark_borderINDIP: sort a document collection in Java (Java heroes may skip this)

Update: the result of the blog below landed here. Thanks to Nathan Freeman, hihi, kudos to me???

In Lotus Script it was not a simple task to sort a document collection. In Java it sort of is.

Idea: use a comparator class, supply with an array of fields upon which the “sorting” should happen and let the Collections.sort do it’s magic:

Comparator class:

public class CollectionComparator implements Comparator<Document> {

String[] sortFields = null;

public CollectionComparator(String[] sortFields) {
this.sortFields = sortFields;

public int compare(Document doc1, Document doc2) {

try {
int compared = 0;
// loop all sortFields
for (String field : sortFields) {
Item item1 = doc1.getFirstItem(field);
Item item2 = doc2.getFirstItem(field);
switch (item1.getType()) {
case Item.TEXT:
case Item.AUTHORS:
case Item.NAMES:
case Item.READERS:
String val1 = doc1.getItemValueString(field);
String val2 = doc2.getItemValueString(field);
compared = val1.compareTo(val2);
if (0 != compared) {
return compared;
case Item.NUMBERS:
Double d1 = doc1.getItemValueDouble(field);
Double d2 = doc2.getItemValueDouble(field);
compared = d1.compareTo(d2);
if (0 != compared) {
return compared;

case Item.DATETIMES:

DateTime dt1 = item1.getDateTimeValue();
DateTime dt2 = item2.getDateTimeValue();
compared = dt2.timeDifference(dt1);
if (0 != compared) {
return compared;

return 0;
} catch (NotesException e) {

return 0;


Calling the sort:
// fill a list with the documents
List<Document> docs = new ArrayList<Document>();
doc = dc.getFirstDocument();
while (null != doc) {
doc = dc.getNextDocument(doc);

// sort the list
String[] sortFields = new String[] { “fieldName” }; // you can also supply multiple fields “field1”, “field2”
Collections.sort(docs, new CollectionComparator(sortFields));

// retrieve values
for (Document docSorted : docs) {
All code samples and downloads are copyright Michael Zischeck and licensed under Apache License 2.0 

bookmark_borderINDIP/CRYFH: having legacy Lotus Script code do your work!

In our company we do have a wonderful, flexible, proven, stable own-built workflow engine, but it is written in Lotus Script code. baah..

I didn’t want to rewrite the whole thing in Java…

But you may now think: hey why does he not run an agent?

I tried, and I failed, how else!!! Always the way I understand things, they will never really work!

Luckily I then I found Kathy Browns article how to do it and it works!!!


  • put some value into the NotesDocument to “control” your logic in the LS agent
  • run the agent with the document as parameter
  • write some field in the agent into your document: NO NEED TO SAVE THE DOCUMENT!!!
  • read the result from you NotesDocument in your xPage code and process it
My mistake was: I tried to update a field which was visible on the xPage, and here my approach failed. 
Or could I manually “refresh” the xPage after the agent has run? Seems to be maybe a “timing” issue, cause the value is there, it’s only just not displayed in the field on the xPage. Anyone?

How we use this technique with our workflow engine:
The engine can require a comment to be given for certain target state. But the target state I don’t know yet in the xPage, cause this logic is handled in LS. But with the technique above I can create an agent provide the target action (reject/approve or something else), calculate the target state and find out if for that state a comment is required. If so I can then call a dialog in the xPage where the user can enter his comment. In the agent which runs then, to process the workflow, I then simply read this comment field and process it. In our case it’s written to a workflow log and also included in the notification to the approver of the next state
Nathan: did you read my comment to your comment? I bow to you..

bookmark_borderunQUINDIPping QUINDIP and other accronyms

according to Tim Tripcony (read the comment on this blog) quindip means confusion, I am now trying to unConfuse you about the confusion I created with my accronyms I use 
  • INDIP: INDispensabletIP (those ones which I deem very important in any xPages developers knowledge bag)
  • QUINDIP: QUickINDIP (those ones I deem quickly explained, 2min to read,  understand and implement)
  • FRULE: FRUstratedLearningExpierence (things I learned the hard way (shall I repeat how hard?), ie. it took me hours/days to get the hang of it and mostly in the end it was so obvious only I lacked some very basic knowledge in the first place)
  • CRYFH: CRYForHelp (when I have no clue anymore how to proceed and I need your input)
  • YOUFEB: YOUrFEedBack required (please gimme comments, ask questions what you want me to blog about etc)
  • DYHOP: DoYouHaveOtherProposals for accronyms? 😉
ups.. something very important I almost forgot…

let the game begin (provide your ideas in the comments): any idea what the anagram of in-mood is?

well.. a hint: in-mood is the anagram of the actual word. When I had my own company few years ago, I was so in (good) mood about stuff I was doing that time.. that I figured: great this will be my company name!

Unfortunately I cannot offer anything as a price to the winner besides maybe some special mentioning in a blog entry or my personal effort trying to help to solve an interesting xPage problem!