Getting Credentials for Microsoft Excel Online

To get credentials for Microsoft Excel Online, follow these steps:

1. Get an authorization code using this link in your browser (remember to insert your Client ID):

CODE

https://login.microsoftonline.com/common/oauth2/v2.0/authorize?client_id=<CLIENT_ID>&response_type=code&redirect_uri=http%3A%2F%2Flocalhost%2F&response_mode=query&scope=offline_access+Files.Read+Files.Read.All+Files.Read.Selected+Files.ReadWrite+Files.ReadWrite.All+Files.ReadWrite.AppFolder+Files.ReadWrite.Selected+User.Read

2. After authentication, you will be redirected to an URL like the following:

CODE

http://localhost/?code=Mf6a38952-f544-ff69-4ad5-fcbac7001984

3. Copy the code at the end of the URL string. Then create a dummy connection/data source that will be used for getting the refresh token:

SQL

CALL SYSADMIN.createConnection (
    name => 'www', 
    jbossCliTemplateName => 'ws'
);;

CALL SYSADMIN.createDatasource (
    name => 'www', 
    translator => 'ws'
);;

4. Edit the first 3 declared variables with your actual <CLIENT_ID>, <CLIENT_SECRET>, <AUTH_CODE>, and run the following code:

CODE

BEGIN
    DECLARE string client_id = '<CLIENT_ID>';
    DECLARE string client_secret = '<CLIENT_SECRET>';
    DECLARE string code = '<AUTH_CODE>';
    
    DECLARE string grand_type ='authorization_code';
    DECLARE string scope = 'offline_access Files.Read Files.Read.All Files.Read.Selected Files.ReadWrite Files.ReadWrite.All Files.ReadWrite.AppFolder Files.ReadWrite.Selected User.Read'; 
    DECLARE string request = 'client_id=' || client_id || '&redirect_uri=http%3A%2F%2Flocalhost%2F&grant_type=' || grand_type 
        || '&scope=' || URLENCODE (scope, 'UTF-8')
        || '&code=' || code 
        || '&client_secret=' || URLENCODE (client_secret, 'UTF-8');

    SELECT 
        j.expires_in,
        j.ext_expires_in,
        j.access_token,
        j.refresh_token,
        j.token_type,
        j.scope,
        TO_CHARS (a.result, 'UTF-8')
    FROM www.invokeHttp (
        action => 'POST',
        endpoint => 'https://login.microsoftonline.com/common/oauth2/v2.0/token',
        requestContentType => 'application/x-www-form-urlencoded',
        request => request
    ) a,
    OBJECTTABLE (LANGUAGE 'javascript' 'JSON.parse (tokens);'
        PASSING 
            CAST (TO_CHARS (a.result, 'UTF-8') AS string) AS tokens 
        COLUMNS 
            expires_in integer 'dv_row.expires_in',
            ext_expires_in integer 'dv_row.ext_expires_in',
            access_token string 'dv_row.access_token',
            refresh_token string 'dv_row.refresh_token',
            token_type string 'dv_row.token_type',
            scope string 'dv_row.scope'
    ) AS j;
END;;

The returned column will contain the refresh token.