Introduction
The purpose of this blog is to show you how to create/modify/delete google sheets via background programs from SAP.
Since these are background jobs run by the SAP system, it is not up to a user to authenticate to google since there is no human action. So we cannot choose the methods where the user authenticates directly to perform the extraction to google like this for example:
To work with this method, you have to look at the documentation here:
https://www.sap.com/documents/2018/07/56e0dd6d-0f7d-0010-87a3-c30de2ffd8ff.html
In the case of server to server connection, we will work in this way:
Development
- Create a Google Cloud Platform project:
- Within the project, create a service account
Navigate to IAM&Admin -> Service accounts and create a new service account. Type in a name and description and press create. On the next screen you have to select a role.
Type in IAP in the search and select the role IAP-secured Web App User.
- AND GET THE KEY IN P12 FORMAT
After your service account created, you can create your key in P12 format
The P12 file download should start to your local machine. The service account allows access to the cloud resources, so store it securely. Note the shown secret for later use.
- Import service account certificate to STRUST
Before we can write ABAP code and consume Google API, we have to make sure two things: First, we need to import the P12 file into the AS ABAP system, second we have to make sure, google is a trusted source for communication.
- Create new SSF Application
We have to create a new entry in table SSFAPPLIC. Go to transaction SE16 and open the table. Create a new entry.
Use JWR_SI for APPLIC and select everything except B_INCCERTS, B_DETACHED, B_ASKPWD. As Description we set JWT Signature. This entry will be later a new node in transaction STRUST where we can import certificates. Save the new entry.
Next open transaction SSFA. Press “New Entries”. Create a new entry as shown below
We now have a new node in transaction STRUST.
- Import Certificates into STRUST
Open transaction STRUST and a new node should be available with name “SSF JWT Signature”.
Go into “Edit” mode, right click the new node and select “Create”.
In the next window put Algorithm to “RSA” Key Strength to “2048” and Signature Algorithm to “SHA256”.
Confirm the selection and the new node will now be available for imports. From the top menu select “PSE->Import” and select the service account P12 file you have downloaded. You might need to enter the secret that was shown when downloading the P12 file from GCP.
Now the P12 file is loaded into the “File” node in STRUST. Next, we need to move it from “File” node to the right SSF Application. On the top menu select “PSE->Save as”. In the next window select “SSF Application” and select the SSF application we have created in the previous steps (JWR_SI).
Confirm the selection and press save. With this, we have now imported the Service Account P12 file into the ABAP AS and can use it to sign our JWT for requests to GCP. With STRUST we have a secure place to store the service account private key and certificate information.
We have now the GCP service account private key and certificate imported into the system.
- Create the connection in SAP
Next, we need to make Google to be a trusted source for communication. This can be achieved by importing the Google Root CA into STRUST.
Download the Google certificate needed from the browser (see with your SAP BASIS team). In transaction STRUST on SSL client SSL Client (Anonymous).
Google is now a trusted source for communication and we have created the HTTP connections that will be used for communication in our ABAP code later.
Note : You can test your connection by clicking on “Connection test” :
You should have a 404 error (it’s normal)
If you have an SSL error message, then you have a issue with your Google certificates.
Try to see with your SAP BASIS Team the errors in ICM logs (TCODE SMICM)
- Create the ABAP methods that will :
– Create and sign a JWT token in SAP
– Send it to Google for exchange against an authentication token to be placed in the header of each API call to google resources (drive, sheets, docs, etc…)
- Create class ZCL_GCP_API_HANDLER
CLASS zcl_gcp_api_handler DEFINITION
PUBLIC
FINAL
CREATE PUBLIC .
PUBLIC SECTION.
TYPES:
BEGIN OF zgcp_response,
content TYPE string,
cookies TYPE tihttpcki,
code type i,
reason type string,
END OF zgcp_response.
CLASS-METHODS create_rs256_signed_jwt
IMPORTING
iv_jwt_header TYPE zgcp_jwt_header
iv_jwt_payload TYPE zgcp_jwt_payload
iv_ssf_profilename TYPE string
iv_ssf_id TYPE string
iv_ssf_result TYPE i
RETURNING VALUE(rv_signed_jwt_base64) TYPE string.
* RAISING zcx_gcp_api_handler.
CLASS-METHODS exchange_jwt_with_oidc_token
IMPORTING
iv_exchange_destination TYPE c
iv_jwt_token TYPE string
RETURNING VALUE(rv_oidc_base64) TYPE string.
* RAISING zcx_gcp_api_handler.
CLASS-METHODS do_api_request
IMPORTING
iv_destination TYPE c
iv_oidc_token TYPE string
iv_method TYPE string
iv_xcontent TYPE xstring OPTIONAL
iv_content TYPE string OPTIONAL
iv_sub_uri TYPE string OPTIONAL
it_header_fields TYPE tihttpnvp OPTIONAL
it_cookies TYPE tihttpcki OPTIONAL
iv_content_type TYPE string DEFAULT 'application/json'
RETURNING VALUE(rs_response) TYPE zgcp_response.
* RAISING zcx_gcp_api_handler.
CLASS-METHODS get_iat_unixtime RETURNING VALUE(rv_iat) TYPE int4.
PROTECTED SECTION.
PRIVATE SECTION.
TYPES:
ltty_tssfbin TYPE STANDARD TABLE OF ssfbin WITH KEY table_line WITHOUT FURTHER SECONDARY KEYS,
BEGIN OF oidc_token_json,
access_token TYPE string,
END OF oidc_token_json.
CLASS-METHODS string_to_binary_tab
IMPORTING
iv_string TYPE string
RETURNING VALUE(rt_bin_tab) TYPE ltty_tssfbin.
* RAISING zcx_gcp_api_handler.
CLASS-METHODS binary_tab_to_string
IMPORTING
it_bin_tab TYPE ltty_tssfbin
iv_length TYPE ssflen
RETURNING VALUE(rv_string) TYPE string.
* RAISING zcx_gcp_api_handler.
CLASS-METHODS base64_url_encode
CHANGING
iv_base64 TYPE string.
ENDCLASS.
CLASS ZCL_GCP_API_HANDLER IMPLEMENTATION.
* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Static Private Method ZCL_GCP_API_HANDLER=>BASE64_URL_ENCODE
* +-------------------------------------------------------------------------------------------------+
* | [<-->] IV_BASE64 TYPE STRING
* +--------------------------------------------------------------------------------------</SIGNATURE>
METHOD base64_url_encode.
REPLACE ALL OCCURRENCES OF '=' IN iv_base64 WITH ''.
REPLACE ALL OCCURRENCES OF '+' IN iv_base64 WITH '-'.
REPLACE ALL OCCURRENCES OF '/' IN iv_base64 WITH '_'.
ENDMETHOD.
* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Static Private Method ZCL_GCP_API_HANDLER=>BINARY_TAB_TO_STRING
* +-------------------------------------------------------------------------------------------------+
* | [--->] IT_BIN_TAB TYPE LTTY_TSSFBIN
* | [--->] IV_LENGTH TYPE SSFLEN
* | [<-()] RV_STRING TYPE STRING
* +--------------------------------------------------------------------------------------</SIGNATURE>
METHOD binary_tab_to_string.
CALL FUNCTION 'SCMS_BINARY_TO_STRING'
EXPORTING
input_length = iv_length
encoding = '4110'
IMPORTING
text_buffer = rv_string
TABLES
binary_tab = it_bin_tab
EXCEPTIONS
failed = 1
OTHERS = 2.
* IF sy-subrc <> 0.
* RAISE EXCEPTION TYPE zcx_gcp_api_handler
* EXPORTING
* textid = zcx_gcp_api_handler=>zcx_bintostr_conversion_failed.
* ENDIF.
ENDMETHOD.
* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Static Public Method ZCL_GCP_API_HANDLER=>CREATE_RS256_SIGNED_JWT
* +-------------------------------------------------------------------------------------------------+
* | [--->] IV_JWT_HEADER TYPE ZGCP_JWT_HEADER
* | [--->] IV_JWT_PAYLOAD TYPE ZGCP_JWT_PAYLOAD
* | [--->] IV_SSF_PROFILENAME TYPE STRING
* | [--->] IV_SSF_ID TYPE STRING
* | [--->] IV_SSF_RESULT TYPE I
* | [<-()] RV_SIGNED_JWT_BASE64 TYPE STRING
* +--------------------------------------------------------------------------------------</SIGNATURE>
METHOD create_rs256_signed_jwt.
DATA lt_input_bin TYPE STANDARD TABLE OF ssfbin.
DATA lt_output_bin TYPE STANDARD TABLE OF ssfbin.
DATA lv_input_length TYPE ssflen.
DATA lv_output_length TYPE ssflen.
DATA lv_output_crc TYPE ssfreturn.
DATA lt_signer TYPE STANDARD TABLE OF ssfinfo.
DATA lv_unix_iat TYPE string.
DATA(lv_jwt_payload) = /ui2/cl_json=>serialize(
data = iv_jwt_payload
pretty_name = /ui2/cl_json=>pretty_mode-low_case
).
DATA(lv_jwt_header) = /ui2/cl_json=>serialize(
data = iv_jwt_header
pretty_name = /ui2/cl_json=>pretty_mode-low_case
).
DATA(lv_jwt_header_base64) = cl_http_utility=>encode_base64( unencoded = lv_jwt_header ).
DATA(lv_jwt_payload_base64) = cl_http_utility=>encode_base64( unencoded = lv_jwt_payload ).
DATA(lv_data_base64) = |{ lv_jwt_header_base64 }.{ lv_jwt_payload_base64 }|.
base64_url_encode(
CHANGING
iv_base64 = lv_data_base64
).
TRY.
lt_input_bin = string_to_binary_tab( iv_string = lv_data_base64 ).
* CATCH zcx_gcp_api_handler INTO DATA(lo_cx).
* RAISE EXCEPTION TYPE zcx_gcp_api_handler
* EXPORTING
* textid = lo_cx->textid.
ENDTRY.
lt_signer = VALUE #( ( id = iv_ssf_id profile = iv_ssf_profilename result = iv_ssf_result ) ).
lv_input_length = strlen( lv_data_base64 ).
CALL FUNCTION 'SSF_KRN_SIGN'
EXPORTING
str_format = 'PKCS1-V1.5'
b_inc_certs = abap_false
b_detached = abap_false
b_inenc = abap_false
ostr_input_data_l = lv_input_length
str_hashalg = 'SHA256'
IMPORTING
ostr_signed_data_l = lv_output_length
crc = lv_output_crc " SSF Return code
TABLES
ostr_input_data = lt_input_bin
signer = lt_signer
ostr_signed_data = lt_output_bin
EXCEPTIONS
ssf_krn_error = 1
ssf_krn_noop = 2
ssf_krn_nomemory = 3
ssf_krn_opinv = 4
ssf_krn_nossflib = 5
ssf_krn_signer_list_error = 6
ssf_krn_input_data_error = 7
ssf_krn_invalid_par = 8
ssf_krn_invalid_parlen = 9
ssf_fb_input_parameter_error = 10.
* IF sy-subrc <> 0.
* RAISE EXCEPTION TYPE zcx_gcp_api_handler
* EXPORTING
* textid = zcx_gcp_api_handler=>zcx_signature_failed.
* ENDIF.
TRY.
DATA(lv_signature) = binary_tab_to_string(
it_bin_tab = lt_output_bin
iv_length = lv_output_length
).
* CATCH zcx_gcp_api_handler INTO DATA(lo_zcx).
* RAISE EXCEPTION TYPE zcx_gcp_api_handler
* EXPORTING
* textid = lo_zcx->textid.
ENDTRY.
DATA(lv_jwt) = |{ lv_data_base64 }.{ cl_http_utility=>encode_base64( unencoded = lv_signature ) }|.
base64_url_encode(
CHANGING
iv_base64 = lv_jwt
).
rv_signed_jwt_base64 = lv_jwt.
ENDMETHOD.
* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Static Public Method ZCL_GCP_API_HANDLER=>DO_API_REQUEST
* +-------------------------------------------------------------------------------------------------+
* | [--->] IV_DESTINATION TYPE C
* | [--->] IV_OIDC_TOKEN TYPE STRING
* | [--->] IV_METHOD TYPE STRING
* | [--->] IV_XCONTENT TYPE XSTRING(optional)
* | [--->] IV_CONTENT TYPE STRING(optional)
* | [--->] IV_SUB_URI TYPE STRING(optional)
* | [--->] IT_HEADER_FIELDS TYPE TIHTTPNVP(optional)
* | [--->] IT_COOKIES TYPE TIHTTPCKI(optional)
* | [--->] IV_CONTENT_TYPE TYPE STRING (default ='application/json')
* | [<-()] RS_RESPONSE TYPE ZGCP_RESPONSE
* +--------------------------------------------------------------------------------------</SIGNATURE>
METHOD do_api_request.
DATA lo_client_api TYPE REF TO if_http_client.
DATA lv_response TYPE string.
DATA lv_oidc TYPE string.
CALL METHOD cl_http_client=>create_by_destination
EXPORTING
destination = iv_destination
IMPORTING
client = lo_client_api
EXCEPTIONS
argument_not_found = 1
destination_not_found = 2
destination_no_authority = 3
plugin_not_active = 4
internal_error = 5
OTHERS = 6.
* IF sy-subrc <> 0.
* RAISE EXCEPTION TYPE zcx_gcp_api_handler
* EXPORTING
* textid = zcx_gcp_api_handler=>zcx_api_dest_not_found.
* ENDIF.
IF lo_client_api IS BOUND.
lv_oidc = |Bearer { iv_oidc_token }|.
lo_client_api->request->set_header_fields( fields = it_header_fields ).
lo_client_api->request->set_content_type( content_type = iv_content_type ).
lo_client_api->request->set_method( method = iv_method ).
* set jwt token auth
lo_client_api->request->set_header_field(
name = 'Authorization' ##NO_TEXT
value = lv_oidc
).
lo_client_api->request->set_header_field(
name = 'content-type'
value = iv_content_type
).
IF iv_sub_uri IS NOT INITIAL.
cl_http_utility=>set_request_uri(
request = lo_client_api->request
uri = iv_sub_uri
).
ENDIF.
IF iv_xcontent IS NOT INITIAL.
lo_client_api->request->set_data( data = iv_xcontent ).
ENDIF.
IF iv_content IS NOT INITIAL.
lo_client_api->request->set_cdata( data = iv_content ).
ENDIF.
LOOP AT it_cookies ASSIGNING FIELD-SYMBOL(<cookie>).
lo_client_api->request->set_cookie(
EXPORTING
name = <cookie>-name " Name of cookie
path = <cookie>-path " Path of Cookie
value = <cookie>-value " Cookie value
domain = <cookie>-xdomain " Domain Name of Cookie
expires = <cookie>-expires " Cookie expiry date
secure = <cookie>-secure " 0: unsaved; 1:saved
).
ENDLOOP.
lo_client_api->send( ).
lo_client_api->receive(
EXCEPTIONS
http_communication_failure = 1
http_invalid_state = 2
http_processing_failed = 3
).
* IF sy-subrc <> 0.
* RAISE EXCEPTION TYPE zcx_gcp_api_handler
* EXPORTING
* textid = zcx_gcp_api_handler=>zcx_api_receive_failed.
* ENDIF.
rs_response-content = lo_client_api->response->get_data( ).
lo_client_api->response->get_status( IMPORTING code = rs_response-code
reason = rs_response-reason ).
lo_client_api->response->get_cookies( CHANGING cookies = rs_response-cookies ).
ENDIF.
ENDMETHOD.
* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Static Public Method ZCL_GCP_API_HANDLER=>EXCHANGE_JWT_WITH_OIDC_TOKEN
* +-------------------------------------------------------------------------------------------------+
* | [--->] IV_EXCHANGE_DESTINATION TYPE C
* | [--->] IV_JWT_TOKEN TYPE STRING
* | [<-()] RV_OIDC_BASE64 TYPE STRING
* +--------------------------------------------------------------------------------------</SIGNATURE>
METHOD exchange_jwt_with_oidc_token.
DATA lo_client TYPE REF TO if_http_client.
DATA ls_response TYPE oidc_token_json.
CALL METHOD cl_http_client=>create_by_destination
EXPORTING
destination = iv_exchange_destination
IMPORTING
client = lo_client
EXCEPTIONS
argument_not_found = 1
destination_not_found = 2
destination_no_authority = 3
plugin_not_active = 4
internal_error = 5
OTHERS = 6.
* IF sy-subrc <> 0.
* RAISE EXCEPTION TYPE zcx_gcp_api_handler
* EXPORTING
* textid = zcx_gcp_api_handler=>zcx_oauth_dest_not_found.
* ENDIF.
IF lo_client IS BOUND.
lo_client->request->set_method( if_http_request=>co_request_method_post ).
lo_client->request->set_formfield_encoding( formfield_encoding = if_http_entity=>co_formfield_encoding_encoded ).
lo_client->request->set_form_field(
EXPORTING
name = 'grant_type'
value = 'urn:ietf:params:oauth:grant-type:jwt-bearer'
).
lo_client->request->set_form_field(
EXPORTING
name = 'assertion'
value = iv_jwt_token
).
lo_client->send( ).
lo_client->receive(
EXCEPTIONS
http_communication_failure = 1
http_invalid_state = 2
http_processing_failed = 3
).
* IF sy-subrc <> 0.
* RAISE EXCEPTION TYPE zcx_gcp_api_handler
* EXPORTING
* textid = zcx_gcp_api_handler=>zcx_oauth_token_receive_fail.
* ENDIF.
DATA(lv_response_json) = lo_client->response->get_cdata( ).
/ui2/cl_json=>deserialize(
EXPORTING
json = lv_response_json
pretty_name = /ui2/cl_json=>pretty_mode-camel_case
CHANGING data = ls_response ).
* IF ls_response-id_token IS INITIAL.
* RAISE EXCEPTION TYPE zcx_gcp_api_handler
* EXPORTING
* textid = zcx_gcp_api_handler=>zcx_oauth_token_receive_fail.
* ENDIF.
rv_oidc_base64 = ls_response-access_token.
ENDIF.
ENDMETHOD.
* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Static Public Method ZCL_GCP_API_HANDLER=>GET_IAT_UNIXTIME
* +-------------------------------------------------------------------------------------------------+
* | [<-()] RV_IAT TYPE INT4
* +--------------------------------------------------------------------------------------</SIGNATURE>
METHOD get_iat_unixtime.
DATA lv_unix_iat TYPE string.
GET TIME STAMP FIELD DATA(lv_timestamp).
CONVERT TIME STAMP lv_timestamp TIME ZONE 'UTC' INTO DATE DATA(lv_date) TIME DATA(lv_time).
cl_pco_utility=>convert_abap_timestamp_to_java(
EXPORTING
iv_date = lv_date
iv_time = lv_time
iv_msec = 0
IMPORTING
ev_timestamp = lv_unix_iat
).
rv_iat = substring( val = lv_unix_iat off = 0 len = strlen( lv_unix_iat ) - 3 ).
ENDMETHOD.
* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Static Private Method ZCL_GCP_API_HANDLER=>STRING_TO_BINARY_TAB
* +-------------------------------------------------------------------------------------------------+
* | [--->] IV_STRING TYPE STRING
* | [<-()] RT_BIN_TAB TYPE LTTY_TSSFBIN
* +--------------------------------------------------------------------------------------</SIGNATURE>
METHOD string_to_binary_tab.
DATA lv_xstring TYPE xstring.
CALL FUNCTION 'SCMS_STRING_TO_XSTRING'
EXPORTING
text = iv_string
encoding = '4110'
IMPORTING
buffer = lv_xstring
EXCEPTIONS
failed = 1
OTHERS = 2.
* IF sy-subrc <> 0.
* RAISE EXCEPTION TYPE zcx_gcp_api_handler
* EXPORTING
* textid = zcx_gcp_api_handler=>zcx_strtobin_conversion_failed.
* ENDIF.
CALL FUNCTION 'SCMS_XSTRING_TO_BINARY'
EXPORTING
buffer = lv_xstring
TABLES
binary_tab = rt_bin_tab.
ENDMETHOD.
ENDCLASS.- Create 2 structures zgcp_jwt_header and zgcp_jwt_payload.
– ALG stands for Algorithm and will include the algorithm that is used for encryption which is RS256
– TYP stands for token type and will be JWT
– ISS stands for issuer and will be the name of our Google Service Account
– AUD stands for audience, basically the consumer of the token
– TARGET_AUDIENCE is the id of out OAUTH client at Google
– IAT stands for issued at time and is a timestamp in UNIX time, when we created the token
– EXP stands for expires and is a timestamp in UNIX time when the token will expire.
- Create a program that will call the above methods to identify itself.
*&---------------------------------------------------------------------*
*& Report ZCL_GCP_JWT_AUTH_TEST
*&---------------------------------------------------------------------*
*&
*&---------------------------------------------------------------------*
REPORT ZCL_GCP_JWT_AUTH_TEST.
*1
DATA(lv_iat) = zcl_gcp_api_handler=>get_iat_unixtime( ).
DATA(ls_jwt_payload) = VALUE zgcp_jwt_payload( iss = 'your email google service account'
* aud = 'https://www.googleapis.com/oauth2/v4/token'
scope = 'https://www.googleapis.com/auth/drive'
aud = 'https://oauth2.googleapis.com/token'
* target_audience = '110934787806204234349'
iat = lv_iat
exp = lv_iat + 30 ).
DATA(ls_jwt_header) = VALUE zgcp_jwt_header( typ = 'JWT'
alg = 'RS256' ).
*2
TRY.
DATA(lv_signed_jwt) = zcl_gcp_api_handler=>create_rs256_signed_jwt(
EXPORTING
iv_jwt_header = ls_jwt_header
iv_jwt_payload = ls_jwt_payload
iv_ssf_profilename = 'SAPJWR_SI400.pse'
iv_ssf_id = '<implicit>'
iv_ssf_result = 28
).
* CATCH zcx_gcp_api_handler.
ENDTRY.
*3
TRY.
DATA(lv_oidc) = zcl_gcp_api_handler=>exchange_jwt_with_oidc_token(
iv_exchange_destination = 'GCP_OAUTH2_TOKEN'
iv_jwt_token = lv_signed_jwt
).
* CATCH zcx_gcp_api_handler.
ENDTRY.- We specify all the variables necessary for the creation of the JWT and its signature
- Launching the program to generate and sign the JWT.
- Launch the class to generate the authentication token by sending the signed JWT to Google which returns a token. The google token is in lv_oidc variable
With this token, it is now possible to call google APIs by specifying the token in the call header.
Now we can create a test program calling the google sheets APIs with our authentication token
You can test your calls here :
https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets.values/update
By inspecting, it is possible to find the url to specify in the call to have this operation:
To see example calls in SAP, I created a test program :
Example for a clear sheet:
Example to add values in the spreadsheet:
