Read data from a gSheet into a SAPUI5/Fiori app, using the identity of the user who interacts with the application.
Use the Google GSI library to obtain the Google API access token.
Proxy the Google library via the app router (xs-app.json) in order to load it with the UI5 loader, in spite of the absence of a ‘.js’ extension.

Goal

Google Sheets – gSheets – are a nice, user-friendly way to capture and work with structured data.

The goal of this blog post is to give an example of how data stored in gSheets can be made available to a SAPUI5/Fiori app at a low cost and with little effort.

A notable feature of the example is that the gSheet is accessed with the identity of the user who interacts with the SAPUI5/Fiori application. There is no need for any kind of service, technical or generic user.

Step-by-Step Instructions

Google:
1. Create a new gSheet:
  1. Title: ‘gSheet in Fiori’
  2. Content of sheet ‘Sheet1’:
    
    Greeting Addressee
    
    Hello world
2. Note the ID – the spreadsheetId – of the gSheet, e.g. ‘1f4Z…H9EE’.
3. Test API access to the gSheet:
  1. Open the API method spreadsheets.values.get.
  2. Observe the HTTP request pattern: ‘GET https://sheets.googleapis.com/v4/spreadsheets/{spreadsheetId}/values/{range}’
  3. Fill in the ‘Try this method’ form:
    1. Use the spreadsheetId for above.
    2. Range: ‘Sheet1!A1:Z1000’.
    3. In the ‘Credentials’ section:
      1. Leave only the ‘https://www.googleapis.com/auth/spreadsheets.readonly’ Google OAuth 2.0 scope.
      2. Untick ‘API key’.
    4. Click ‘Execute’ and follow the appearing dialogues.
    5. Observe the response:
```
{
  "range": "Sheet1!A1:Z1000",
  "majorDimension": "ROWS",
  "values": [
    [
      "Greeting",
      "Addressee"
    ],
    [
      "Hello",
      "world"
    ]
  ]
}
```
SAP cloud platform ‘BTP’:
1. Open your SAP BTP trial account (or equivalent).
2. Open your Business Application Studio ‘Dev Space’.
3. On the ‘Welcome’ page, click ‘Start from template’:
  1. ‘SAP Fiori application’
  2. ‘SAPUI5 freestyle’ / ‘SAPUI5 Application’
  3. Data source: None
  4. View name: View1
  5. Application title: ‘gSheet in 7 Minutes’
  6. Application namespace: ‘com.sap.blogs’
  7. Description: ‘How to read a gSheet into your Fiori app in 7 minutes.’
  8. Add deployment configuration: Yes
  9. Please choose the target: Cloud Foundry
  10. Destination name: None
  11. Add application to managed application router?: Yes
4. Preview the application with ‘start-noflp’.
5. Right click on ‘mta.yaml’ / ‘Build MTA Project’
6. F1 / ‘Deploy MTA Archive’
  1. Follow the instructions (mind the API endpoint).
7. Go to cloud cockpit, ‘HTML5 Applications’ tab, and follow the link to subscribe to the ‘Launchpad’ service.
8. Find ‘comsapblogsproject1’ on the ‘HTML5 Applications’ tab, and launch the application.
  1. Note the URL, and the domain of the application, e.g. ‘ondemand.com’.
Google:
1. Go to the Google Cloud cockpit, and create a new project:
  1. Project name: ‘gSheet in Fiori’
2. Go to ‘APIs & Services’ / ‘Libraries’ and enable the ‘Google Sheets API’ API.
3. Go to ‘APIs & Services’ / ‘OAuth consent screen’, configure a consent screen:
  1. User Type: External, ‘Create’
  2. App name: ‘gSheet in Fiori’
  3. App domain / ‘Application home page’: URL of the application from above.
  4. Authorized domains:
    - ondemand.com
  5. Save and continue.
  6. Scopes: click ‘Add or Remove Scopes’:
    1. Search for, and add scope ‘https://www.googleapis.com/auth/spreadsheets.readonly’.
  7. Save and continue.
  8. Add test users. Save and continue.
4. Go to ‘APIs & Services’ / ‘Credentials’.
  1. Click ‘+ Create Credentials’ / ‘OAuth client ID’.
  2. Application type: web application.
  3. Name: ‘gSheet in 7 Minutes’
  4. ‘Authorized JavaScript origins’:
    1. URI of the app from above, e.g. ‘https://b4…trial.launchpad.cfapps.us10.hana.ondemand.com’.
    2. URI of the App Studio dev space, e.g. ‘https://b4…trial.us10cf.trial.applicationstudio.cloud.sap’.
    3. URI of the preview application in the App Studio, e.g. ‘https://port8080-workspaces-ws-b…j.us10.trial.applicationstudio.cloud.sap’.
  5. Click ‘Create’.
  6. Note down the ‘Client ID’ and ‘Client Secret’, e.g. ’10…31-gf…ka.apps.googleusercontent.com’ and ‘GO…go’.

SAP Business Application Studio (BAS):

Configure the app loader to find Google’s GSI client library.
Unfortunately the GSI library doesn’t have a ‘.js’ extension, causing the loader to miss it. The workaround presented here is to proxy the library via the “^/com-google-accounts-gsi-client[.]js$” route of the managed app router:

In ‘webapp/Component.js’:

sap.ui.loader.config({
    paths: {
        "com/google/accounts/gsi/client": "com-google-accounts-gsi-client"  // = https://accounts.google.com/gsi/client (note the absence of '.js'!)
    },
    shim: {
        'com/google/accounts/gsi/client': {
            amd: true,
            deps: [],
            exports: 'google' // google.accounts.oauth2
        }
    },
    async: true
});

Define the route in ‘xs-app.json’:

    {
        "authenticationType": "xsuaa",
        "csrfProtection": false,
        "source": "^/com-google-accounts-gsi-client[.]js$",
        "target": "/gsi/client",
        "destination": "google-accounts"
    },

Define destination ‘google-accounts’ in ‘mta.yaml’:

# Section resources[name = com-sap-blogs-project1-destination-service].parameters.config.init_data.instance.destinations:
          - Name: google-accounts
            Authentication: NoAuthentication
            ProxyType: Internet
            Type: HTTP
            URL: https://accounts.google.com

For routing to work in the BAS dev space, configure the following in ‘ui5.yaml’:

# Section server.customMiddleware[name = fiori-tools-proxy].configuration.backend:
          - path: /com-google-accounts-gsi-client.js
            pathPrefix: "/gsi/client"
            url: https://accounts.google.com

Configure the app in ‘webapp/manifest.json’, using the URL of the Google ‘doc’ gSheet UI, the URL of the spreadsheets.values.get API HTTP request, and the OAuth client ID from above:

// Add "app" model to "sap.ui5"."models":
            "app": {
                "type": "sap.ui.model.json.JSONModel",
                "settings": {
                    "sGoogleapisSheetsSpreadsheetURL": "https://sheets.googleapis.com/v4/spreadsheets/1f4Z...H9EE/values/Sheet1!A1%3AZ1000",
                    "sGoogleDocsSpreadsheetURL": "https://docs.google.com/spreadsheets/d/1f4Z...H9EE/edit#gid=0",
                    "sGoogleOAuth2ClientID": "10...ka.apps.googleusercontent.com",
                    "sAddressee": "-",
                    "sGreeting": "-"
                }
            }

Make sure ‘webapp/model/models.js’ loads the GSI library:

sap.ui.define([
    ...
    "com/google/accounts/gsi/client"
],
    /**
     * provide app-view type models (as in the first "V" in MVVC)
     * ...
     * @param {any} __google
     */
    function (..., __google) {

Load ‘sap/m/MessageToast’ and ‘sap/ui/core/Popup’ as well in ‘webapp/model/models.js’:

sap.ui.define([
    ...
    "sap/m/MessageToast",
    "sap/ui/core/Popup",
    ...
],
    /**
     * provide app-view type models (as in the first "V" in MVVC)
     * ...
     * @param {import('sap/m/MessageToast').default}                    MessageToast
     * @param {import('sap/ui/core/Popup')}                             Popup
     * ...
     */
    function (..., MessageToast, Popup, ...) {

Create function ‘getGoogleOauthAccessToken’ which obtains a Google access token in ‘webapp/model/models.js’:

            /**
             * @typedef {{
             *  access_token: string,   // The access token of a successful token response.
             *  expires_in: number,     // The lifetime in seconds of the access token.
             *  hd: string,             // The hosted domain the signed-in user belongs to.
             *  prompt: string,         // The prompt value that was used from the possible list of values specified by TokenClientConfig or OverridableTokenClientConfig.
             *  token_type: string,     // The type of the token issued.
             *  scope: string,          // A space-delimited list of scopes that are approved by the user.
             *  state: string,          // The string value that your application uses to maintain state between your authorization request and the response.
             *  error: string,          // A single ASCII error code.
             *  error_description: string,  // Human-readable ASCII text providing additional information, used to assist the client developer in understanding the error that occurred.
             *  error_uri: string       // A URI identifying a human-readable web page with information about the error, used to provide the client developer with additional information about the error.
             * }} GoogleOauth2ClientTokenResponse
             */
            /**
             * Get Google access token, see https://developers.google.com/identity/oauth2/web/reference/js-reference .
             * @returns {Promise<GoogleOauth2ClientTokenResponse>}
             */
            async getGoogleOauthAccessToken(sGoogleOauth2ClientId, sCurrentUserEmail) {
                const tokenPromise = new Promise((resolve, reject) => {
                    // https://developers.google.com/identity/oauth2/web/reference/js-reference
                    const googleOauth2Client = __google.accounts.oauth2.initTokenClient({
                        /**
                         * @param {GoogleOauth2ClientTokenResponse} tokenResponse
                         */
                        callback: (tokenResponse) => {
                            // "Users may close the account chooser or sign-in windows,
                            //  in which case your callback function will not be invoked." (!?)
                            if (tokenResponse && tokenResponse.access_token) {
                                resolve(tokenResponse);
                            } else {
                                reject(tokenResponse);
                            }
                        },
                        client_id: sGoogleOauth2ClientId,
                        /**
                         * @param {Error} oError
                         */
                        error_callback: (oError) => {       // property 'i' of googleOauth2Client
                            reject(oError);
                        },
                        hint: sCurrentUserEmail,            // The email address for the target user.
                        prompt: "",
                        scope: 'https://www.googleapis.com/auth/spreadsheets.readonly'
                    });
                    googleOauth2Client.requestAccessToken();
                });
                return tokenPromise;
            },

Create function ‘getCurrentUserEmail‘ which obtains the current user’s email address:

            /**
             * @returns {Promise<string>}
             */
            async getCurrentUserEmail() {
                /**
                 * User API Service | https://help.sap.com/docs/BTP/65de2977205c403bbc107264b8eccf4b/b80abb01ef084bc098636348b1d618af.html
                 * @typedef {{
                 *  "firstname": string,
                 *  "lastname": string,
                 *  "email": string,
                 *  "name": string,             // john.doe@sap.com"
                 *  "scopes"?: Array<string>,   // ["openid"]
                 *  "displayName": string       // "John Doe (john.doe@sap.com)"
                 * }} CurrentUser
                 */
                /** @type{CurrentUser} */
                const oCurrentUser = await $.ajax({ url: 'user-api/currentUser', dataType: "json" });
                return oCurrentUser.email;
            },

For this to work, the ‘User API Service‘ of the managed app router must be exposed. Add this route in ‘xs-app.json’:

    {
        "source": "^/user-api(.*)$",
        "target": "$1",
        "service": "sap-approuter-userapi",
        "authenticationType": "xsuaa"
    },

In the BAS, use the following to mock this service:

Create a static route in ui5.yaml:

# Section server.customMiddleware:
    # https://www.npmjs.com/package/@sap/ux-ui5-tooling?activeTab=readme#3-serve-static
    - name: fiori-tools-servestatic
      afterMiddleware: compression
      configuration:
        paths:
          - path: /user-api/currentUser
            src: "localService/currentUser.json"

Create ‘localService/currentUser.json’ with your SAP identity, assuming you used your Google email address for it:

{
    "firstname": "Your",
    "lastname": "User",
    "email": "your.user@gmail.com",
    "name": "your.user@gmail.com",
    "scopes": [
        "openid"
    ],
    "displayName": "Your User (your.user@gmail.com)"
}

Create function ‘loadGsheetDataToAppModel’ which loads the gSheet to the ‘app’ model in ‘webapp/model/models.js’:

            /**
             * @param {'sap/ui/core/UIComponent'} oComponent 
             * @returns {Promise<void>}
             */
            async loadGsheetDataToAppModel(oComponent) {
                try {
                    const oAppModel = oComponent.getModel("app");
                    const sGoogleOauth2ClientId = oAppModel.getProperty("/sGoogleOAuth2ClientID");

                    // 0 Get current user's email address
                    const sCurrentUserEmail = await this.getCurrentUserEmail();

                    // 1 Authenticate with Google
                    /** @type {GoogleOauth2ClientTokenResponse} */
                    const oAccessToken = await this.getGoogleOauthAccessToken(sGoogleOauth2ClientId, sCurrentUserEmail);

                    // 2 Load gSheet
                    const sGoogleapisSheetsSpreadsheetURL = oAppModel.getProperty("/sGoogleapisSheetsSpreadsheetURL");
                    /**
                     * @typedef {{
                     *  range: string,                  // "Sheet1!A1:Z1000"
                     *  majorDimension: string,         // "ROWS"
                     *  values: Array<Array<string>>    // [["Greeting", "Addressee"], ["Hello", "world"]]
                     * }} GSheetValues
                     */
                    /** @type GSheetValues */
                    const oGSheetResponseData = await $.ajax({
                        url: sGoogleapisSheetsSpreadsheetURL,
                        headers: {
                            Authorization: `Bearer ${oAccessToken.access_token}`
                        }
                    });

                    // 3 Set app model properties
                    const sGreeting = oGSheetResponseData.values[1][0];
                    const sAddressee = oGSheetResponseData.values[1][1];
                    oAppModel.setProperty('/sGreeting', sGreeting, null, true);
                    oAppModel.setProperty('/sAddressee', sAddressee, null, true);

                } catch (err) {
                    let message = "";
                    if (err) {
                        // Could be for ex: oError: with '{"message":"Failed to open popup window","stack":"Error: Failed to ope...els.js:355:61)","type":"popup_failed_to_open"}'
                        if (err instanceof Error) { message = err.message; }
                        // '{"error":"access_denied"}'
                        else if (err.error && typeof err.error === 'string') { message = err.error; }
                    }
                    if (message) {
                        // Consider a 'sap/m/Dialog' instead.
                        MessageToast.show(message, {
                            "at": Popup.Dock.CenterCenter,
                            "duration": 7000,
                            "my": Popup.Dock.CenterCenter,
                            // autoClose: false
                        });
                    } else {
                        throw err;
                    }
                }
            }

Load (asynchronously) the gSheet data into the app model in ‘webapp/Component.js’:

                // load gSheet data into app model (async)
                models.loadGsheetDataToAppModel(this);

Set the greeting into a ‘Text’ element on ‘webapp/view/View1.view.xml’. For convenience, also include a link to the gSheet:

        <content>
            <Text id="_IDGenText1" text="{app>/sGreeting} {app>/sAddressee}!"/>
            <!-- Make sure you include xmlns:html="http://www.w3.org/1999/xhtml" in the <mvc:View> tag above. -->
            <html:hr/>
            <Link id="_IDGenLink1" text="{i18n>link1Text}" target="_blank" href="{app>/sGoogleDocsSpreadsheetURL}"/>
        </content>

Add ‘link1Text’ to ‘webapp/i18n/i18n.properties’:
```
link1Text=Open gSheet
```

Start the app in the BAS, for example with ‘npm run start-noflp’:
1. ‘Failed to open popup window’ is shown. Allow popups. Reload the app.
2. The ‘Sign in with Google’ popup is shown. Make sure you see your own email address: configure it in ‘localService/currentUser.json’. If you change this file, restart the app with ‘npm run start-noflp’.
3. Examine the consent dialog, and choose ‘Continue’.
  1. You can revoke consent by visiting Google’s ‘Apps with access to your account‘.
4. The app displays the greeting from the gSheet, e.g.:
5. If you restart the app, no consent is needed. After a brief message that accompanies the access token request – ‘One moment please…’ – the app starts.
Increase ‘applicationVersion’ in ‘webapp/manifest.json’, and deploy the app to your trial account:
1. Build the MTA project and deploy.
2. Start the deployed application. When asked, allow popups. Consent is not needed again, as long as your SAP cloud email is the same as the one configured in ‘localService/currentUser.json’.

Recommendations

App Configuration

Certain elements of app configuration – such as the Google OAuth client ID and the gSheet – should not be stored in ‘webapp/manifest.json’. The author recommends loading this configuration from the ‘Business Rules Capability‘ of the ‘Workflow Management‘ service. See ‘How to use the business rules service to configure an HTML5 application‘.

Token Caching

You can cache the access token, even across reloads, using ‘sap/ui/util/Storage‘.

SAPUI5 Types

SAPUI5/Fiori development can be greatly accelerated by the help of typing. Check out this link to get started: ‘@sapui5/ts-types-esm‘. These steps may help:

Run:

npm install --save-dev @sapui5/ts-types-esm @types/jquery @types/node @tsconfig/node14

Have a ‘webapp/tsconfig.json’:

// https://www.typescriptlang.org/docs/handbook/declaration-files/dts-from-js.html
// ui5-typescript-helloworld/tsconfig.json | https://github.com/SAP-samples/ui5-typescript-helloworld/blob/main/tsconfig.json
{
  "compilerOptions": {
    "noEmit": true,
    "checkJs": true,
    "allowJs": true,
    // "noImplicitAny": true,
    "preserveConstEnums": true,
    "strict": false,
    "lib": ["DOM"],
    "types": [
      "@sapui5/ts-types-esm",
      "jquery",
      "node"
    ]
  }
  , "extends": "@tsconfig/node14/tsconfig.json"
}

In order to take types from your own source code into account, restructure your module definitions like this:

sap.ui.define([
    "sap/m/MessageToast",
    "sap/ui/core/Popup",
    ...
],
    /**
     * @param {import('sap/m/MessageToast').default}                    MessageToast
     * @param {import('sap/ui/core/Popup')}                             Popup
     * ...
     */
    function (MessageToast, Popup, ...) {
        "use strict";

        const models = {
            ...
        };
        try { module.exports = models; } catch (err) { }
        return models;
    });

Acknowledgements

I thank Bartlomiej Wala for proofreading and testing this blog post.

Author and Motivation

Laszlo Kajan is a SAP cloud – aka. BTP – developer, present on the SAPUI5 field since 2015.

The motivation behind this blog post is to provide a reusable example that enabled and accelerates the use of Google Workspace resources such as Google Sheets in SAPUI5/Fiori applications.

References

Tags: SAP BTP, Cloud Foundry environment SAP Business Application Studio SAP Fiori

Sara Sampaio

Author Since: March 10, 2022

0 0 votes

Article Rating

0 Comments

Inline Feedbacks

View all comments