1+ //sample instance
2+ //http://scn.sap.com/docs/DOC-40986
3+
4+
5+ function onOpen ( ) {
6+ SpreadsheetApp . getActiveSpreadsheet ( )
7+ . addMenu ( 'SAP NetWeaver' , [ { name :'SAP Data Wizard' , functionName :'showUI' } ,
8+ null ,
9+ { name :'Load Business Partners' , functionName :'getBusinessPartnerData' } ,
10+ { name :'Load Sales Orders' , functionName :'getSalesOrderData' } ,
11+ null ,
12+ { name :'Load Items for Sales Order' , functionName :'loadSalesItems' } ,
13+ null ,
14+ { name : 'Post to Google+' , functionName :'googleplus' } ] ) ; //future ideas
15+ }
16+
17+ function showUI ( ) {
18+ var ui = HtmlService . createTemplateFromFile ( 'ui' ) . evaluate ( ) . setSandboxMode ( HtmlService . SandboxMode . NATIVE ) . setHeight ( 550 ) ;
19+
20+ SpreadsheetApp . getActiveSpreadsheet ( ) . show ( ui ) ;
21+ }
22+
23+ function returnDate ( unix_timestamp ) {
24+ //var unix_timestamp = '1370025951';
25+ var date = new Date ( unix_timestamp * 1000 ) ;
26+ //Logger.log(date);
27+ return date ;
28+ }
29+
30+ function returnUnixtimestamp ( dateString ) {
31+ //var dateString = '2013-04-01';
32+ var parts = dateString . match ( / ( \d + ) / g) ;
33+ var date = new Date ( parts [ 0 ] , parts [ 1 ] - 1 , parts [ 2 ] ) ; // months are 0-based
34+ var timestamp = date . getTime ( ) / 1000 + '' ;
35+ return timestamp ;
36+ }
37+
38+
39+ function createOrSetActiveSheet ( sheetName ) {
40+ var ss = SpreadsheetApp . getActiveSpreadsheet ( ) ;
41+ var sheets = ss . getSheets ( ) ;
42+ var sheet ;
43+ for ( var i = 0 ; i < sheets . length ; i ++ ) {
44+ sheet = sheets [ i ] ;
45+ var currentSheetName = sheet . getSheetName ( ) ;
46+ if ( sheetName === currentSheetName ) {
47+ ss . setActiveSheet ( sheet ) ;
48+ sheet . clear ( ) ;
49+ return sheet ;
50+ }
51+ }
52+ sheet = ss . insertSheet ( ) ;
53+ sheet . setName ( sheetName ) ;
54+ ss . setActiveSheet ( sheet ) ;
55+ return sheet ;
56+ }
57+
58+ //future ideas
59+ function sendNightlyEmail ( ) {
60+ var sheet = SpreadsheetApp . getActiveSpreadsheet ( ) . getSheetByName ( 'Published Dashboard' ) ;
61+ var chart = sheet . getCharts ( ) [ 0 ] ;
62+ MailApp . sendEmail ( Session . getActiveUser ( ) . getEmail ( ) , 'Daily petition report' , 'See attached image' , { name :'Sharing chart' , attachments :[ chart . getBlob ( ) ] } ) ;
63+ }
64+
65+
66+ function getBusinessPartnerData ( ) {
67+ var ss = SpreadsheetApp . getActiveSpreadsheet ( ) ;
68+ var sheet = ss . getActiveSheet ( ) ;
69+ sheet . clear ( ) ;
70+
71+
72+ var base_url = "https://sapes1.sapdevcenter.com/sap/opu/odata/IWBEP/GWDEMO/BusinessPartnerCollection/?" ;
73+ var additionParams = "$format=json" ;
74+ var user = 'YOUR_USERNAME' ;
75+ var password = 'YOUR_PASSWORD' ;
76+ var header = 'Basic ' + Utilities . base64Encode ( user + ':' + password ) ;
77+
78+
79+ var getDataURL = base_url + additionParams ;
80+ Logger . log ( getDataURL ) ;
81+
82+ var dataResponse = UrlFetchApp . fetch ( getDataURL , { headers : { Authorization : header } , muteHttpExceptions : true } ) . getContentText ( ) ;
83+ var dataObj = JSON . parse ( dataResponse ) ;
84+ var headers = [ 'Key' , 'Name' , 'Role' , 'Website' , 'Email' , 'Address' ] ;
85+
86+ var maxLetter = String . fromCharCode ( 64 + headers . length ) ;
87+
88+ sheet . appendRow ( headers ) ;
89+
90+ sheet . getRange ( 'A1:' + maxLetter + '1' ) . setBackground ( 'Grey' ) . setFontStyle ( 'oblique' ) ;
91+
92+ ss . setColumnWidth ( 2 , 220 ) ;
93+ ss . setColumnWidth ( 3 , 89 ) ;
94+
95+ var rows = [ ] ;
96+ for ( var i = 0 ; dataObj . d . results && i < dataObj . d . results . length ; i ++ ) {
97+ var row = dataObj . d . results [ i ] ;
98+
99+ rows . push ( [ row . BusinessPartnerKey , row . Company , row . BusinessPartnerRoleText , row . WebAddress , row . EmailAddress , 'street address' ] ) ;
100+ }
101+ var rowsCount = rows . length + 1 ;
102+ ss . getRange ( 'A2:' + maxLetter + rowsCount ) . setValues ( rows ) ;
103+ }
104+
105+
106+ function getSalesOrderData ( ) {
107+ var ss = SpreadsheetApp . getActiveSpreadsheet ( ) ;
108+ var sheet = ss . getActiveSheet ( ) ;
109+ sheet . clear ( ) ;
110+
111+
112+ var base_url = "https://sapes1.sapdevcenter.com/sap/opu/odata/IWBEP/GWDEMO/SalesOrderCollection/?" ;
113+ var additionParams = "$format=json" ;
114+ var user = 'YOUR_USERNAME' ;
115+ var password = 'YOUR_PASSWORD' ;
116+ var header = 'Basic ' + Utilities . base64Encode ( user + ':' + password ) ;
117+
118+
119+ var getDataURL = base_url + additionParams ;
120+ Logger . log ( getDataURL ) ;
121+
122+ var dataResponse = UrlFetchApp . fetch ( getDataURL , { headers : { Authorization : header } , muteHttpExceptions : true } ) . getContentText ( ) ;
123+ var dataObj = JSON . parse ( dataResponse ) ;
124+ var headers = [ 'Sales Order Key' , 'Customer Name' , 'Status' , 'Currency' , 'Total Amt' , 'Tax Amt' , 'Note' , 'Created By' , 'Changed By' ] ;
125+
126+ var maxLetter = String . fromCharCode ( 64 + headers . length ) ;
127+
128+ sheet . appendRow ( headers ) ;
129+
130+ sheet . getRange ( 'A1:' + maxLetter + '1' ) . setBackground ( 'Grey' ) . setFontStyle ( 'oblique' ) ;
131+
132+ ss . setColumnWidth ( 2 , 220 ) ;
133+ ss . setColumnWidth ( 3 , 89 ) ;
134+
135+ var rows = [ ] ;
136+ for ( var i = 0 ; dataObj . d . results && i < dataObj . d . results . length ; i ++ ) {
137+ var row = dataObj . d . results [ i ] ;
138+
139+ rows . push ( [ row . SalesOrderKey , row . CustomerName , row . StatusDescription , row . Currency , row . TotalSum , row . Tax , row . Note , row . CreatedByEmployeeLastName , row . ChangedByEmployeeLastName ] ) ;
140+ }
141+ var rowsCount = rows . length + 1 ;
142+ ss . getRange ( 'A2:' + maxLetter + rowsCount ) . setValues ( rows ) ;
143+ }
144+
145+
146+ function loadSalesItems ( ) {
147+ var salesOrderKey = SpreadsheetApp . getActiveRange ( ) . getValue ( ) ;
148+ var initialPageCount = 99 ; //because a default sheet has only 100 rows to start
149+
150+ var sheet = createOrSetActiveSheet ( salesOrderKey ) ;
151+ var base_url = "https://sapes1.sapdevcenter.com/sap/opu/odata/IWBEP/GWDEMO/SalesOrderCollection('" + salesOrderKey + "')/salesorderlineitems?" ;
152+ var getDataURL = base_url + '$format=json' ;
153+
154+ var user = 'YOUR_USERNAME' ;
155+ var password = 'YOUR_PASSWORD' ;
156+ var header = 'Basic ' + Utilities . base64Encode ( user + ':' + password ) ;
157+ Logger . log ( getDataURL ) ;
158+
159+ var dataResponse = UrlFetchApp . fetch ( getDataURL , { headers : { Authorization : header } , muteHttpExceptions : true } ) . getContentText ( ) ;
160+ Logger . log ( dataResponse ) ;
161+ var dataObj = JSON . parse ( dataResponse ) ;
162+ var headers = [ 'SalesOrderItemKey' , 'ProductName' , 'Availability' , 'Note' , 'Currency' , 'NetSum' , 'Tax' , 'TotalSum' ] ;
163+ var maxLetter = String . fromCharCode ( 64 + headers . length ) ;
164+
165+ sheet . appendRow ( headers ) ;
166+ sheet . getRange ( 'A1:' + maxLetter + '1' ) . setBackground ( 'Grey' ) . setFontStyle ( 'oblique' ) ;
167+ var rows = [ ] ;
168+ for ( var i = 0 ; dataObj . d . results && i < dataObj . d . results . length ; i ++ ) {
169+ var row = dataObj . d . results [ i ] ;
170+ rows . push ( [ row . SalesOrderItemKey , row . ProductName , row . Availability , row . Note , row . Currency , row . NetSum , row . Tax , row . TotalSum ] ) ;
171+ }
172+ var rowsCount = rows . length + 1 ;
173+ sheet . getRange ( 'A2:' + maxLetter + rowsCount ) . setValues ( rows ) ;
174+ }
0 commit comments