Fill data in a GoogleDocs SpreadSheet from IBMi via Rest WebService

4
486

Now, with 7.1 and 7.2 with some PTF prerequisite (Building a REST service with integrated web services server for IBM i: Part 1), we can expose our RPG programs and service programs as Rest Web Service ….and this open a world of possibilities.

In this post I try to fill a Google Docs Spreadsheet with a RPG Service Program via a Rest Web Service.

Can you imagine ? Write your Budget or an Order or a Price List or something else in a GDocs Spreadsheet and upload data directly to your tables/files on IBMi … or fill your Spreadsheet with an RPG program.

Read more to see step by step process..

Step 1: Create our module and Service Program

Here’s source of a small service programm with a function “TS_ITEMGET” with 2 input parms (“From” and “To”) and some outuput parameters (Nr of elements, an Array of DS to return a data grid, some HTTP Status output parameters).

In this example I use a QTEMP temporary table ..  but you can use your data

ctl-opt nomain pgminfo(*pcml:*module);
ctl-opt bnddir('XSERVRMS') ;
//-----------------------------------------------------------------------*
// TSGETITEM Test Webservice: Get Item from IBMi to GoogleDocs
// Spreadsheets
//-----------------------------------------------------------------------*
dcl-c Http_Ok const(200);
dcl-c Http_NotFound const(404);
dcl-ds brarti extname('BRARTI0F') end-ds;
dcl-ds Itemsds qualified template;
Id char(15);
Desc char(10);
Price packed(21:4);
end-ds;
dcl-ds items likeds(Itemsds) dim(1000);
dcl-s i int(5);
dcl-s Id char(15);
dcl-s Desc char(50);
dcl-s Price packed(15:4);
//-----------------------------------------------------------------------*
//Procedure ts_itemsget
//-----------------------------------------------------------------------*
DCL-PROC ts_itemsget export;
dcl-pi ts_itemsget;
i_from char(100) ;
i_to char(100) ;
o_nrofelements int(5);
o_ds likeds(Itemsds) dim(1000) options(*varsize);
o_error char(100);
httpStatus int(10);
httpHeaders char(100) dim(10);
end-pi;
// Gestione dei servizi del WS
clear o_error;
clear o_ds;
o_nrofelements=1;
clear items;
clear i;
// Create an example for All-About-i.info
// I create a temporary table only for example
// you can read your file/table or everything else
// as a normal RPG programm
Create_example();
exec sql
declare c1 cursor for
select *
from QTEMP/ITEMS
where id between :i_from and :i_to
order by id;
exec sql open c1;
dow 1=1;
exec sql fetch c1 into :Id, :Desc, :Price;
if sqlcod<>0;
exec sql close c1;
leave;
ENDIF;
i+=1;
items(i).id=Id;
items(i).desc=Desc;
items(i).price=Price;
ENDDO;
select;
when i=0;
o_error='ERROR - No Items found';
httpStatus=Http_NotFound;
other;
httpStatus=Http_Ok;
O_NrOfElements=i;
o_ds=items;
ENDSL;
return;
END-PROC;
DCL-PROC Create_Example;
exec sql
CREATE or REPLACE TABLE QTEMP/ITEMS
(ID char(10), desc char(100), price dec(15 , 4));
exec sql
delete from QTEMP/ITEMS
where 1=1;
exec sql
INSERT INTO QTEMP/ITEMS
(ID, desc, price)
VALUES('A001', 'Apple Iphone 6', 700);
exec sql
INSERT INTO QTEMP/ITEMS
(ID, desc, price)
VALUES('A002', 'Apple Iphone 6S', 800);
exec sql
INSERT INTO QTEMP/ITEMS
(ID, desc, price)
VALUES('S001', 'Samsung S6 32GB', 639);
exec sql
INSERT INTO QTEMP/ITEMS
(ID, desc, price)
VALUES('S002', 'Samsung S6 EDGE', 739);
END-PROC;

 

Step 2: Bind source to export SRVPGM functions

STRPGMEXP SIGNATURE('TS_ITEMS L.1')
export symbol(ts_itemsget)
ENDPGMEXP

 

Step 3: Build Module and Service Program

CRTSQLRPGI OBJ(mylib/TS_ITEMS) SRCFILE(mylib/mysrcpf) SRCMBR(TS_ITEMS) OBJTYPE(*MODULE) OPTION(*EVENTF) REPLACE(*YES) DBGVIEW(*SOURCE)
CRTSRVPGM SRVPGM(mylib/TS_ITEMS) MODULE(TS_ITEMS) EXPORT(*SRCFILE) SRCFILE(mylib/mysrcpf) SRCMBR(TS_ITEMSBN) ACTGRP(*CALLER)
 

 

Step 4: Expose Rest Web Service with “IBM Web Administration for i”

Log in Web Administration for i

http://myibmi:2001/HTTPAdmin

and follow steps to publish a rest service …

 

IBM WebAd4i 01IBM WebAd4i 02

IBM WebAd4i 03

IBM WebAd4i 04

IBM WebAd4i 06

IBM WebAd4i 07

IBM WebAd4i 08

IBM WebAd4i 09

IBM WebAd4i 10

To return only valid rows of a RPG array with a fixed dimension (array parameters cannot be varying !) … you have to return a numeric parm with rigth number of elements of your output and when you depkoy your webservice play with flag “Detects length fields” … see next image …
restservice step 4x

 

Step 5: Test our Rest Web Service

I normaly use Advance Rest Client Chrome Estension … but you can use SoapUi or similar solutions. Invoke your rest function passing parameters as path URL and look at JSON output.

Test Rest WS 01

Test Rest WS 02

 

Step 6: Open Google Docs in your browser, ad a spreadsheet doc and write down this Google Script code

Pay attention to some rows:

Your WebService URL … must be an external IP, reachable from Google servers … you have to open this port on firewall

var url = ‘http://myesternalIP:10010/web/services/TS_ITEMS’
+ ‘/’+From
+ ‘/’+To;

And here is the “real call” at your webservice, get Json result and parse it …

var response = UrlFetchApp.fetch(url);
var json = response.getContentText();
var data = JSON.parse(json);

 

 

Gdocs 01

Gdocs 02

This script, on open document add a new menu with our new function.

Our function “Get Data From IBMi” call a webservice passing “from” e “to” parameters … and get back a data grid with id, description and price for our items.

 

// This script works with the Brackets Test spreadsheet to create a tournament bracket
// given a list of players or teams.
var RANGE_START = 'FirstCell';
var RANGE_FROM = 'From';
var RANGE_TO = 'To';
var SHEET_ITEMS = 'Items';
var CONNECTOR_WIDTH = 15;
// This method adds a custom menu item to run the script
function onOpen() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
ss.addMenu("Get Data From IBMi",
[{ name: "Get Items", functionName: "getItems" }]);
}
// Get a list of items from IBMi
function getItems() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheetItems = ss.getSheetByName(SHEET_ITEMS);
var rangeStart = ss.getRangeByName(RANGE_START);
var rangeFrom = ss.getRangeByName(RANGE_FROM);
var rangeTo = ss.getRangeByName(RANGE_TO);
var sheetItems = ss.getSheetByName(SHEET_ITEMS);
// Get the range
var From = rangeFrom.getValue();
var To = rangeTo.getValue();
var Maxelements=100;
var OffsetRow=rangeStart.getRowIndex();
//for (var i = 0; i < Maxelements; i++) {
// var rng=sheetItems.getRange(i+OffsetRow, 1);
// rng.setValue(From);
// var rng=sheetItems.getRange(i+OffsetRow, 2);
// rng.setValue(To);
// }
var url = 'http://myesternalIP:10010/web/services/TS_ITEMS'
+ '/'+From
+ '/'+To;
var response = UrlFetchApp.fetch(url);
//Logger.log(response);
var json = response.getContentText();
//Logger.log(json);
var data = JSON.parse(json);
Logger.log(data.o_DS[0].ID);
//Browser.msgBox(data.O_DS.Items(1));
i=0;
var o_NROFELEMENTS=data.o_NROFELEMENTS;
Logger.log(o_NROFELEMENTS);
for (var i = 0; i < o_NROFELEMENTS; i++) {
var ItemID=data.o_DS[i].ID.toString().replace(' ', '');
var ItemDesc=data.o_DS[i].DESC;
var ItemPrice=data.o_DS[i].PRICE;
var r=i+OffsetRow;
var c=1;
var rng=sheetItems.getRange(r, c);
rng.setValue(''' + ItemID);
c=c+1;
var rng=sheetItems.getRange(r, c);
rng.setValue(ItemDesc);
c=c+1;
var rng=sheetItems.getRange(r, c);
rng.setValue(ItemPrice);
}
}

 

Step 7: It’s time to test our code … yes ! here our data on our sheet !

Close and reopen new doc and execute our function from new menu !

Awsome !

 

 

Gdocs 03

Gdocs 04

 

 

4 COMMENTS

  1. Hi, good article! I tried this and it Works.
    I have one question about the JSON response. I get many empty JSON records {“ID”:”A002″,”DESC”:”Apple Ipho”,”PRICE”:800.0000},{“ID”:””,”DESC”:””,”PRICE”:0.0000},{“ID”:””,”DESC”:””,”PRICE”:0.0000}, as many as defined in the data structure dcl-ds items likeds(Itemsds) dim(1000);

    How can i get rid off these empty JSON records?

    Best regards,
    Magne

    • Hi Magne, to return only some elements of an array when you deploy your rest webservice you have to unflag “Detect length fields” and choose a specific numeric output parm with number of elements valid in your array …
      Look at the end of step 4 …

    • Sorry Glenn … BRARTI0F is one on my ERP Table … only for example I read this table and return some of my references … you can use your tables/files or what you neeed for your app. Try with your items detail table … and return your real items to Gdoc