{"id":4945,"date":"2022-04-05T21:51:28","date_gmt":"2022-04-05T19:51:28","guid":{"rendered":"https:\/\/gpmfactory.com\/?p=4945"},"modified":"2022-04-05T21:51:28","modified_gmt":"2022-04-05T19:51:28","slug":"integration-oracle-apex-et-google-sheets-et-google-drive","status":"publish","type":"post","link":"https:\/\/gpmfactory.com\/index.php\/2022\/04\/05\/integration-oracle-apex-et-google-sheets-et-google-drive\/","title":{"rendered":"Int\u00e9gration Oracle APEX et Google Sheets et Google Drive"},"content":{"rendered":"\n\n\n

Objet<\/h2>\n\n\n\n

Il est possible de manipuler des feuilles de calcul Google avec des APIs REST mais cela requiert un peu de codage pour la parsing du payload json.
— Ce POST n’est pas encore finalis\u00e9 —<\/p>\n\n\n\n

Normalement il est conseill\u00e9 d’utiliser une API cliente selon le langage h\u00f4te (ph, python, nodejs…). Dans notre cas , PL\/SQL, nous allons utiliser les appels REST natifs car il n’existe pas encore de client PL\/SQL..<\/p>\n\n\n\n

Pour lire une feuille Google Sheets, consulter la documentation Basic Reading<\/em>: https:\/\/developers.google.com\/sheets\/api\/samples\/reading<\/a><\/p>\n\n\n\n

Dans Oracle APEX, on pourrait utiliser une d\u00e9finition de REST Data Source mais le probl\u00e8me est que le parser <\/em>ne reconnait pas les tableaux imbriqu\u00e9s dont voici un \u00e9chantillon:
Exemple de payload <\/em>renvoy\u00e9 par l’API <\/p>\n\n\n\n

{\n\"range\": \"'F2'!A2:D5\",\n\"majorDimension\": \"ROWS\",\n\"values\": [\n[\n\"test\",\n\"4\"\n],\n[\n\"test\",\n\"3\"\n],\n[\n\"test\",\n\"2\"\n],\n[\n\"zert\",\n\"8\"\n]\n]\n}\n\n1 error has occurred\nError during REST Data Source storage or discovery: ORA-40597: JSON path expression syntax error ('$.$.values[*]') JZN-00217: Key step contains unexpected characters at position 3\nFound no data while parsing the service response.<\/pre>\n\n\n\n
\"\"<\/figure>\n\n\n\n
Il faut donc se rabattre sur une approche manuelle. Celle qui est expliqu\u00e9e dans ce post consiste \u00e0 effectuer un appel \u00e0 la m\u00e9thode GET dans un fonction PLSQL puis \u00e0 retourner le contenu dans un pipe. Cela permet, au final, de manipuler la feuille \u00e0 partir d’une requ\u00eate SQL classique ou bien d’une vue.<\/p>\n\n\n\n
l_clob := apex_web_service.make_rest_request(\np_url => 'https:\/\/sheets.googleapis.com\/v4\/spreadsheets\/' || tsheet || '\/values\/' || trange,\np_http_method => 'GET',\np_token_url => 'https:\/\/oauth2.googleapis.com\/token',\np_credential_static_id => 'GoogleSAV<\/strong>'\n);<\/pre>\n\n\n\n
Pour le range, il faut remplacer les espaces \u00e9ventuels dans le nom de la feuille par un signe +<\/p>\n\n\n\n
ex:      trange varchar2(200) := ‘R\u00e9ponses+au+formulaire+4!A1:D5’;<\/p>\n\n\n\n
Cr\u00e9ation optionnelle d’une vue<\/p>\n\n\n\n
CREATE OR REPLACE FORCE EDITIONABLE VIEW \"GOOGLE_SHEET1_V\" (\"C1\", \"C2\", \"C3\", \"C4\", \"C5\", \"C7\", \"C8\", \"C9\", \"C10\") AS\nselect \"C1\",\"C2\",\"C3\",\"C4\",\"C5\",\"C7\",\"C8\",\"C9\",\"C10\" \nfrom google_pkg.get_rows('R\u00e9ponses+au+formulaire+4')\n<\/pre>\n\n\n\n
\"\"<\/figure>\n\n\n\n
Contr\u00f4le d’acc\u00e8s.<\/h2>\n\n\n\n
Comme pour la plupart des acc\u00e8s \u00e0 des donn\u00e9es priv\u00e9es de Google, il faut utiliser le protocole OAUTH2. <\/p>\n\n\n\n
Celui-ci est bien expliqu\u00e9 dans le document de Google<\/a>.<\/p>\n\n\n\n
M\u00eame si on ne peut pas utiliser les sourec REST, on peut n\u00e9anmoins s’appuyer sur les credentials<\/em> pour all\u00e9ger la partie authentification. Sans cela, il faut r\u00e9cup\u00e9rer un code d’acc\u00e8s et le convertir en token<\/em>.<\/p>\n\n\n\n
Pour Gogle Drive, l’int\u00e9gration est plus simple car les REST data source sont op\u00e9rationnels. 
… mais, comme il ‘agit d’une donn\u00e9e priv\u00e9e, il faut une authentification lors de la phase de design ‘redirection vers la page Google) qui n’est pas support\u00e9e par l’assistant. Donc, il faut auparavant g\u00e9n\u00e9rer manuellement un token valide qu’on fournira en param\u00e8tre de type Header Authorization Bearer <TOKEN>. Le code pour g\u00e9n\u00e9rer ce token et decrit en annexe.<\/p>\n\n\n\n
Une fois la d\u00e9couverte du data profile<\/em> r\u00e9alis\u00e9e, on retire le param\u00e8tre Authorization <\/em>qui devient superflu et on sp\u00e9cifie une autorisation bas\u00e9e sur un des credentials <\/em>enregistr\u00e9s (GoogleSAV<\/em> dans notre exemple). L’emploi du param\u00e8tre p_credential_static_id dans l’appel de apex_web_service.make_rest_request est tr\u00e8s pratique car il soulage la t\u00e2che du d\u00e9veloppeur de toute la gestion du cycle de vie du token<\/p>\n\n\n\n
ANNEXES<\/h2>\n\n\n\n
Obtention d’un token pour tester une REST data source<\/h3>\n\n\n\n
Le principe consiste \u00e0 :<\/p>\n\n\n\n
Constituer une URL contenant les parametres :<\/p>\n\n\n\n
  • response_code<\/li>
  • access_type<\/li>
  • redirect_url (https:\/\/xxxxxxxxxx.yyyyy.oraclecloudapps.com\/ords\/demo\/mygoogle\/token) . Cette url correspond \u00e0 un module ORDS custom contenant un handler GET sur le template <\/em>nomm\u00e9e token<\/em>. Pour info, si on veut fourni un id de session qui sera retourn\u00e9e par Google, on ajoute le param\u00e8tre state <\/em>qui contiendra ce qu’on veut bien y mettre.<\/li>
  • client_id<\/li>
  • Demande d’un code d’acc\u00e8s (dans une branch before Header d’une page afin de provoquer une redirection<\/li><\/ul>\n\n\n\n
    Affichage d’un page d’authentification Google<\/p>\n\n\n\n
    Affichage optionnelle d’une page de consentement (envoy\u00e9e par Google)<\/p>\n\n\n\n
    Redirection vers l’URL envoy\u00e9e en param\u00e8tre<\/p>\n\n\n\n
    https:\/\/accounts.google.com\/o\/oauth2\/auth?
    scope=https:\/\/www.googleapis.com\/auth\/drive
    &response_type=code
    &access_type=offline
    &redirect_uri=
    &P0_REDIRECT.
    &client_id=xxxxxxxxxxxxxx.apps.googleusercontent.com<\/pre>\n\n\n\n
    Comme la string ne peut pas depasser 255 caract\u00e8res, on pr\u00e9enregistre l’url de redirection dans une variable globale P0_REDIRECT que l’on mentionne avec la syntaxe &P0_REDIRECT.<\/p>\n\n\n\n
    Conversion du code d’acc\u00e8s en token <\/em>(cf code source du package GOOGLE_PKG en annexe)<\/p>\n\n\n\n
    Affichage du token pour utilisation ult\u00e9rieure. La dur\u00e9e de vie de vie est d’une heure environ. Ce token  devra \u00eatre fourni dans la variable de header nomm\u00e9e AUTHORIZATION et devra \u00eatre pr\u00e9c\u00e9d\u00e9 par le mot clef Bearer <\/em>suivi d’un espace.<\/p>\n\n\n\n
    declare\nl_token varchar2(500);\nl_code varchar2(500);\nbegin\nl_code := utl_url.unescape(GOOGLE_PKG.at_get_env_parameter('code')\n);\nl_token := GOOGLE_PKG.get_token(l_code);\nhtp.p('token=' || l_token);\nhtp.p('scope=');\nhtp.p(utl_url.unescape(GOOGLE_PKG.at_get_env_parameter('scope'))\n);\nend;\n<\/pre>\n","protected":false},"excerpt":{"rendered":"
    Objet Il est possible de manipuler des feuilles de calcul Google avec des APIs REST mais cela requiert un peu de codage pour la…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"ppma_author":[150],"class_list":["post-4945","post","type-post","status-publish","format-standard","hentry","category-non-classe"],"authors":[{"term_id":150,"user_id":1,"is_guest":0,"slug":"admin8700","display_name":"Patrick","avatar_url":"https:\/\/secure.gravatar.com\/avatar\/209d5ed69b74d288390621ab4c1d3773?s=96&d=mm&r=g","0":null,"1":"","2":"","3":"","4":"","5":"","6":"","7":"","8":""}],"_links":{"self":[{"href":"https:\/\/gpmfactory.com\/index.php\/wp-json\/wp\/v2\/posts\/4945","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/gpmfactory.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/gpmfactory.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/gpmfactory.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/gpmfactory.com\/index.php\/wp-json\/wp\/v2\/comments?post=4945"}],"version-history":[{"count":16,"href":"https:\/\/gpmfactory.com\/index.php\/wp-json\/wp\/v2\/posts\/4945\/revisions"}],"predecessor-version":[{"id":4963,"href":"https:\/\/gpmfactory.com\/index.php\/wp-json\/wp\/v2\/posts\/4945\/revisions\/4963"}],"wp:attachment":[{"href":"https:\/\/gpmfactory.com\/index.php\/wp-json\/wp\/v2\/media?parent=4945"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/gpmfactory.com\/index.php\/wp-json\/wp\/v2\/categories?post=4945"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/gpmfactory.com\/index.php\/wp-json\/wp\/v2\/tags?post=4945"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/gpmfactory.com\/index.php\/wp-json\/wp\/v2\/ppma_author?post=4945"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}