CPI Open Connectors Adapter: Google Sheets use case

[former_member609476]

Introduction

Recently SAP introduced the new Open Connectors adapter for SAP CPI. In the following blog post I will use the new adapter to connect with the Google Sheets API. The idea is to showcase how the adapter works and how easy it is to integrate with a Google Cloud application.

In this example we will receive an XML file with sales revenue information from some external system. The file will be processed by the CPI and posted via Open Connectors adapter to Google Sheets. The result will be a fully formatted revenue report sheet (including conditional formatting rules). For every received xml file, a new worksheet (or new page) will be generated in our main sheet.

 

Step 0: Where do we stand now? What do we expect as the result?

We have a revenue report as an XML:

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

<RevenueReport>

    <Period>201901</Period>

    <RevenueRecord>

        <Country>Germany</Country>

        <ActualRevenue>1300000</ActualRevenue>

        <TargetRevenue>1400000</TargetRevenue>

        <Currency>EUR</Currency>

    </RevenueRecord>

    <RevenueRecord>

        <Country>France</Country>

        <ActualRevenue>800000</ActualRevenue>

        <TargetRevenue>700000</TargetRevenue>

        <Currency>EUR</Currency>

    </RevenueRecord>

[...]

</RevenueReport>

The content of the report should be parsed and formatted into this empty sheet as a new worksheet:

Step 1: Create a new Instance of Google Sheets Connector in Open Connectors

That is quite easy and described in other blog posts such as this one from Arpita Jain. Make a new instance and check for the right calls in API Docs:



Safe the Open connector user credentials in CPI key store as described in Arpitas blog post while you are at it.

 

Step 2: Configure Integration Flow on CPI

Create a new iFlow. Mine looks like this:



We receive the payload through a HTTP endpoint. The external system can simply use the http post method to send the XML payload in the request body.

The XML is processed by this Groovy script to construct a JSON Array in the way the sheets API expects it:

import com.sap.gateway.ip.core.customdev.util.Message;

import java.util.HashMap;

def Message processData(Message message) {

    def body = message.getBody(java.lang.String) as String

	def dataGrid2 = [];

	def xml = new XmlSlurper().parseText(body);

	def dataGrid =

			[

				["Revenue Report"],

				[],

				["Period: ", xml.Period.toString()],

				[],

				["Country", "Actual", "Target", "Difference", "Currency"]

			]

	xml.RevenueRecord.each {  RevenueRecord ->

		dataGrid.add(

				[

					RevenueRecord.Country.toString(),

					RevenueRecord.ActualRevenue,

					RevenueRecord.TargetRevenue,

					(RevenueRecord.ActualRevenue.toInteger()-RevenueRecord.TargetRevenue.toInteger()),

					RevenueRecord.Currency.toString()

				]

				)

	}

	def dataGridString = dataGrid.inspect()

	dataGridString = dataGridString.replaceAll("\'", "\"")

	message.setProperty("data", dataGridString);

	return message;

}

Note: Calculation of the field “difference” is done in the script in line:

(RevenueRecord.ActualRevenue.toInteger()-RevenueRecord.TargetRevenue.toInteger()),

Obviously, it’s also possible to do this calculation in Google Sheets, if you pass the correct references to the cells of “Target” and “Actual” values.

Both approaches have advantages and disadvantages. Just to name two: If you calculate in CPI you can use the results for other logic at the same time. If the formula is in Google Sheets the end user can easily comprehend how the value is calculated.

The array is saved as the exchange property data.

We have processed the incoming message payload. Now we can overwrite the payload to generate a new worksheet:



This payload is then transmitted through the new Open Connectors adapter to Google Sheets:



The WorksheetID which is returned by the request will be saved in exchange properties for later use.

 

In the next step the data from the first step is inserted into a JSON structure as Google Sheets expects it. With that payload the sheet can be filled with data:



Next the payload is sent to Google Sheets via the Open Connectors adapter:



Now the worksheet is filled with data, but contains no formatting at all:



In the next step the payload is set to the following JSON to achieve the desired formatting in Google Sheets:

{

    "requests": [

        {

            "repeatCell": {

                "range": {

                    "sheetId": ${property.worksheetID},

                    "endRowIndex": 1,

                    "endColumnIndex": 1

                },

                "cell": {

                    "userEnteredFormat": {

                        "textFormat": {

                            "fontSize": 14,

                            "bold": true

                        }

                    }

                },

                "fields": "userEnteredFormat.textFormat.bold"

            }

        },

        {

            "repeatCell": {

                "range": {

                    "sheetId": ${property.worksheetID},

                    "startRowIndex": 2,

                    "endRowIndex": 3,

                    "startColumnIndex": 1,

                    "endColumnIndex": 2

                },

                "cell": {

                    "userEnteredFormat": {

                        "textFormat": {

                            "bold": true

                        }

                    }

                },

                "fields": "userEnteredFormat.textFormat.bold"

            }

        },

        {

            "repeatCell": {

                "range": {

                    "sheetId": ${property.worksheetID},

                    "startRowIndex": 4,

                    "endRowIndex": 5,

                    "startColumnIndex": 0,

                    "endColumnIndex": 1

                },

                "cell": {

                    "userEnteredFormat": {

                        "textFormat": {

                            "bold": true

                        },

                        "backgroundColor": {

                            "red": 0,

                            "green": 0.7,

                            "blue": 0

                        }

                    }

                },

                "fields": "userEnteredFormat(backgroundColor,textFormat)"

            }

        },

        {

            "repeatCell": {

                "range": {

                    "sheetId": ${property.worksheetID},

                    "startRowIndex": 4,

                    "endRowIndex": 5,

                    "startColumnIndex": 1,

                    "endColumnIndex": 3

                },

                "cell": {

                    "userEnteredFormat": {

                        "textFormat": {

                            "bold": true

                        },

                        "backgroundColor": {

                            "red": 0,

                            "green": 0.5,

                            "blue": 0.9

                        }

                    }

                },

                "fields": "userEnteredFormat(backgroundColor,textFormat)"

            }

        },

        {

            "repeatCell": {

                "range": {

                    "sheetId": ${property.worksheetID},

                    "startRowIndex": 4,

                    "endRowIndex": 5,

                    "startColumnIndex": 3,

                    "endColumnIndex": 4

                },

                "cell": {

                    "userEnteredFormat": {

                        "textFormat": {

                            "bold": true

                        },

                        "backgroundColor": {

                            "red": 0.9,

                            "green": 0.5,

                            "blue": 0

                        }

                    }

                },

                "fields": "userEnteredFormat(backgroundColor,textFormat)"

            }

        },

        {

            "repeatCell": {

                "range": {

                    "sheetId": ${property.worksheetID},

                    "startRowIndex": 4,

                    "endRowIndex": 5,

                    "startColumnIndex": 4,

                    "endColumnIndex": 5

                },

                "cell": {

                    "userEnteredFormat": {

                        "textFormat": {

                            "bold": true

                        },

                        "backgroundColor": {

                            "red": 0.5,

                            "green": 0.5,

                            "blue": 0.5

                        }

                    }

                },

                "fields": "userEnteredFormat(backgroundColor,textFormat)"

            }

        },

        {

            "repeatCell": {

                "range": {

                    "sheetId": ${property.worksheetID},

                    "startRowIndex": 5,

                    "startColumnIndex": 1,

                    "endColumnIndex": 4

                },

                "cell": {

                    "userEnteredFormat": {

                        "numberFormat": {

                            "type": "NUMBER",

                            "pattern": "#,##0"

                          }

                    }

                },

                "fields": "userEnteredFormat(numberFormat)"

            }

        },

        {

            "addConditionalFormatRule": {

                "rule": {

                    "ranges": [

                        {

                            "sheetId": ${property.worksheetID},

                            "startColumnIndex": 3,

                            "endColumnIndex": 4,

                            "startRowIndex": 5

                        }

                    ],

                    "booleanRule": {

                        "condition": {

                            "type": "NUMBER_LESS",

                            "values": [

                                {

                                    "userEnteredValue": "0"

                                }

                            ]

                        },

                        "format": {

                            "backgroundColor": {

                                "green": 0.2,

                                "red": 0.8

                            }

                        }

                    }

                },

                "index": 0

            }

        },

        {

            "addConditionalFormatRule": {

                "rule": {

                    "ranges": [

                        {

                            "sheetId": ${property.worksheetID},

                            "startColumnIndex": 3,

                            "endColumnIndex": 4,

                            "startRowIndex": 5

                        }

                    ],

                    "booleanRule": {

                        "condition": {

                            "type": "NUMBER_GREATER",

                            "values": [

                                {

                                    "userEnteredValue": "0"

                                }

                            ]

                        },

                        "format": {

                            "backgroundColor": {

                                "green": 0.8,

                                "red": 0.2

                            }

                        }

                    }

                },

                "index": 1

            }

        }

    ]

}

This request contains all the formatting definitions, including the one for conditional formatting incase the revenue difference between target and actual is either positive or negative.

In the last step the payload request is made through the Open Connectors adapter again.

The result of the Integration Flow should look like the following:

Summary

With a reasonable amount of work use cases like this report can be implemented with the help of Open Connectors. The advantage here is, business user can proceed working in a way which is familiar to them. An export as excel is possible with a few clicks. All the sharing and collaborating features of sheets can be leveraged as well.

The scenario can be extended in several ways. One example is to send email alerts with the sheet URL to certain users if the revenue difference exceeds a certain amount. In fact, only a few additional steps are necessary for that:

1. Set additional flag in Groovy script when calculating the difference.


2. Before “End” set a router, which checks the flag and either goes to end or a sub Integration Flow


3. Send email in sub Integration Flow with the URL constructed from the sheet ID.

As you can see the addition of Open Connectors opens a lot of options while reducing the complexity of the implementation compared to other approaches. In case of questions to this scenarios or other potential use cases for SAP Open Connectors, feedback is always welcome

 

PS: Here you can find a collection of Divya’s blog posts for further reading material about Open Connectors.

[r_herrmann]

Hi Saraj,

thanks for the blog. I saw that you calculated the difference in Groovy and wonder if it would be possible to pass a formula to column D instead of a pre-calculated value. (I think passing precalculated values contradict the idea of spreadsheets.) Do you know if it's possible to pass formulas?

Regards,
Raffael

[former_member609476]

Hi Raffael,

thanks for your question.
Indeed, that’s also possible. For that you need to use the query

?valueInputOption=USER_ENTERED

as far as i know. The formula is than passed as String i.e. “=c6-B6”. Before putting the values in cell, the Google Sheets API will parse the value to type formula. Here you can find more details.

With this Connector it did not work right away, I believe the query was suppressed or passed in a slightly different manner from Open Connectors to Google Sheets API. To Fix that you could modify the Connector. I choose not to do this, since the Post is already quite long for a quick demo :).

Regards

Saraj

[r_herrmann]

Hi Saraj,

thanks for the feedback. I'll try out your suggestions.

Regards,
Raffael