Google Sheet API integration with ABAP (ABAP2GSheet) – Join to the GitHub Project

Most business users are more confident in analyzing business data using one or more spreadsheets, sometimes linked together.

The actual releases of SAP, both SAP S / 4 and on the most recent versions of SAP ECC, allow exporting the business data in Excel format. The standard feature, used in the system to generate excel spreadsheets, has a limited operation and consequently creates simple excel sheets. These spreadsheets sometimes are not ordered without professional formatting of the layouts. Moreover, it is not possible to customize the worksheets automatically set up from the ALV lists or other tools by adding complex objects such as filters, graphs or other functionalities

It was these limitations that, years ago, inspired our team in the creation of the abap2xlsx framework, which allows developers to generate spreadsheets evolved directly from ABAP quickly.

In the latest years, the Google Suite platform has also released some API services that allow developers to generate spreadsheets (called Google Gsheet). Based on the idea of ​​the abap2xlsx framework, we decided to create a specific component that integrates directly on GSuite called ABAP2Gsheet, an Object-Oriented Framework that allows the use of the Google API.

Please note that this is the announcement of the first version, which still has many features to explore and enhance. This post is also an opportunity to collaborate with us, providing comments and feedback to enhance the code itself and adds more functionalities.

The project is available on GitHub abap2gsheet, the first version supports the following functionalities:

• Creation of a Google Sheet document
• Creation of a new Sheet from a template
• Fill a single cell of the Sheet with text, the number passed directly from ABAP variables
• Apply formats to Text and Numbers, Set colors, filters, etc.
• Protect a range of cells
• Read a range of cells
• Multi Sheets generation
• Upload an XLS document to Google Drive
• Add a Graph to Sheet
• Synchronize GSheet with an ALV

Developers can perform these operations with few lines of code without worrying about authentication and HTTP request formatting struggles, and here follow some examples.

Some examples

The examples explained below can be found in the yabap2google_demos package available in the project’s gitHub. New features, models, and demos implemented will be collected in this section.

Hello World: Creation of a basic Google Sheet filling a cell with an ABAP string

In our first example, we want to create a simple Gsheet file into the GDrive. We want to fill in the second cell of the sheet a formatted text “Hey Google… Toc… Toc..” as shown in the following image.

With just a few lines of code, we get this spreadsheet. The source code described below is divided into two sections. The first dedicated to the connection with the G-Suite and generation of the spreadsheet

And the second where we fill the text to be inserted in the specific cell of the sheet and call the API.

Two Sheets, colors, formatting and even a graph

In our second example, we want to create a complex GSheet composed of two distinct sheets: the first is the header of the file in which to insert the information relating to the document, title, and status of the revision.

and a second sheet which displays a well-formatted supplier orders list and a header section containing a summary of the master data and a graph the quantities ordered by merchandise group

With ABAP2GSheet, it is easy to apply formatting options, styles, and much other information.

ABAP2GSheet installation guide

Pre-requirements

We are targeting to support ABAP2GSheet on SAP S/4HANA 1809 and above (obviously only on-premise).

Since we count on the SAP standard authentication method to Google API (SAP oauth2 client), we have at least the same prerequisites that are:

• SAP oauth2 client is only available for SAP NetWeaver starting from AS ABAP 7.40 SP08 (Note 2043775 must be applied).
• As a prerequisite, the system administrator needs authorizations to create OAuth 2.0 Client Configurations. Make sure that the system administrator has the authorization S_OA2C_ADM with at least the activities 01, 02, and 03 in the AS ABAP system.
• Make sure that the end-users who should be allowed to use the new OAuth 2.0 Client have the required authorizations assigned. During the execution of OAuth 2.0 flows, there is a check of the authorization object “S_OA2C_USE”. This authorization object has two fields “PROFILE” and “ACTVT”. Set the authorization field values as follows:
  • S_OA2C_USE
    • PROFILE       = Z_GOOGLE_SHEETS
    • ACTVT           = 16

GITHUB

The project is pushed on GITHUB and, as an open-source project, we are open for everyone to contribute

Importing ABAP2GSheet with ABAPGIT

Install ABAP2GSheet using abapgit: just import it via ABAPGIT, make the configuration steps, and you are ready to go! If you don’t know what is abapgit, this is a useful blog https://blogs.sap.com/2017/06/21/abapgit-so-easy/ by Graham Robinson and follow the documentation on https://docs.abapgit.org/

Authentication

SAP offers a standard oAuth2 client to overcome authentication implementation concerns and store the generated token in a safe area. We have reused this standard authentication to provide our solution. This guarantees a high level of security.

The following page  contains the steps needed to configure your SAP system to use Google API:

https://wiki.scn.sap.com/wiki/display/Security/Access+Google+APIs+using+the+OAuth+2.0+Client+API

When the system configuration is done, each user that wants to make SAP access his GSuite space for reading and writing purposes should provide grants to his Google account. Note: It can also be a technical user.

The procedure is to call OA2C_GRANT transaction in the SAP GUI and log in to their Google account once. The release token will be stored and automatically refreshed on expiration.

Under the hood

We developed ABAP2GSheet encapsulating the Google REST APIs with our Custom ABAP Classes.

The Google Sheets API is structured with 4 main endpoints with different purposes:

• spreadsheets: to apply changes to the entire document
• spreadsheets.developerMetadata: to link metadata objects to a specific row or cell. These data are not visible in the spreadsheet.
• spreadsheets.sheets: to perform operations onto sheets (i.e., clone an entire sheet)
• spreadsheets.values: to apply changes to a range of cell

Please note that in this release, Spreadsheet.developerMetadata has not been implemented; it seems not relevant to our business cases.

The framework of YABAP2GOOGLE  is composed of 5 different components that collaborate to interact with the Rest API published by the GCP. The following image describes that component.

• YA2G_Common 

This component contains the main utilities used by the other components. the utilities that pertain to this section are:

• • Array Manager – utility for saving sequences of objects via Hashed table
  • Message Protocol – a message collector that allows you to record various error messages that occur internally to the application, making them available to the calling application
  • Metadata Manager – an abstraction of metadata structures to manage data for the composition of the JSON
  • Simple Check Rule – An abstraction of the cross-checks that must be performed during the generation of the Gsheet

• YA2G_API_CORE

This component contains the classes that allow calls to GCP Rest Api. in particular, a series of classes have been created that abstract calls to the APIs so as to use the same methods both for Gsheet calls and for accessing Gdrive’s permissions

• YA2G_JSON

This component reports the classes needed for JSON generation. Each JSON node is represented by a metadata class that inherits its operations and attributes from the Manager metadata. every single JSON class is linked to the other JSON classes and takes care of instantiating the child classes when requested by the calling application

• Y2G_GDRIVE

The component has the responsibility to orchestrate the transformation of the metadata classes of the previous component into JSON and to recall the various Rest API services exposed by GCP for the DRIVE component in order to read and write files and to enable the permissions. (Google Drive API Reference  Resources File and permission)

• Y2G_GSHEET

The component has the responsibility to orchestrate the transformation of the metadata classes of the previous component into JSON and to recall the various Rest API services exposed by GCP for the GSHEET component in order to create and manipulate a SHEET file  (Google Gsheet API Reference Resources preadsheet,Spreadsheet.Sheet, Spreadsheet.developerMetadata Spreadsheet.values)

How to contribute to the ABAP2GSheet open-source project

Everything is on Github, waiting for your collaboration to improve and extend the framework with new and amazing functionalities.

In the implementation backlog, aka Roadmap, we have for sure:

•   generate Pivot Tables
•   set a Cell Validation
•   Froze rows
•   Apply formulas… and more!

Contributions

At the Techedge Group, we worked on different implementation approaches before coming to the current one. With me, Marco Nicolai, I have to mention Alessandro Iannacci, Micael Teweldemedhin, and our internal sponsor and mentor Sergio Ferrari.

External References

More details on Google sheet API on reference page: https://developers.google.com/sheets/api/reference/rest/