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
- S_OA2C_USE
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.ferrari2.
External References
More details on Google sheet API on reference page: https://developers.google.com/sheets/api/reference/rest/
- SAP Managed Tags:
10 Comments
