# Credentials
#### Google Apps
Get Google Apps credentials [here](https://accounts.google.com/SignUp) if you don't have a personal gmail account or a Google Apps domain account.
#### SAP
Get SAP ID credentials [here](https://accounts.sap.com/ui/) if you don't have them
Use these to access the SAP Community Network and register for the SAP Gateway Service Consumption System
You should receive a confirmation email which looks like this:
* User and password to start consuming RESTfull oData Services on the shared SAP ERP System.
Your user-id:
Initial password:
Log in and set your permanent password
In a production implementation you'd use [Apps Script client OAuth](https://github.com/googlesamples/apps-script-oauth2) against the [SAP Gateway OAuth server](http://wiki.scn.sap.com/wiki/display/Security/OAuth+2.0+-+Constrained+Authorization+and+Single+Sign-On+for+OData+Services); the SAP demo system uses id/password credentials for simplicity.
# Review the SAP Gateway Sample Services
The Spreadsheet Actions map to these services:
Load Business Partner
* GWDEMO / EPM -> Query - Business Partner (List)
Get Sales Order Data
* Sales Order Example -> Query - Sales Order (Header)
Load Items for Sales Order
* Sales Order Example -> Query - Sales Order (Item) for a specific sales order
Note that the services can only be viewed as XML on the SAP side
# Google Apps Prequisites
Open a Chrome Incognito browser
### Log in
Log into [Google Drive](https://drive.google.com) with your credentials
Click on "Accept" at prompt which is displayed for new credentials
## [Optional] Try out a completed version of the project
If you want to skip right to the end, you can follow the "Try out Your Code" steps at the end of this lab with [this spreadsheet](https://docs.google.com/spreadsheets/d/1EY-AgbGb3hmGVY58MeZE_bFP0iAGWAFgWkHCYvsN2QY/edit)
# Build your starter project from the template
Make your own copy of the template spreadsheet
Open the [template spreadsheet](https://docs.google.com/a/ferrisargyle.com/spreadsheets/d/1_YPw-bFeIe8Voni4GFFKMUCTX9UVAJ0ySbikCahD_d0/edit)
* Make a copy: File -> Make a copy
### Create an Apps Script Project
[Create a blank project](https://developers.google.com/apps-script/managing_projects#creatingSpreadsheet) from your new copy of the sheet and save it
### Copy the template code into your project
[Create](https://developers.google.com/apps-script/managing_projects#creatingFiles) files in your new project which correspond to those in the [template code file](https://script.google.com/a/macros/ferrisargyle.com/d/1BKDgYNhGeufgC1f_5qeJ3R5LFSSSneZZKBZeNlbZk3gDFrx4oO1Rvtjs/edit) and save them.
* Create **Script** files for the first two files
* Create the CredentialsUi as an **Html** file
* The name and case of the CredentialsUi.html file is important: the other files refer to this
Copy/paste the code from the [template code file](https://script.google.com/a/macros/ferrisargyle.com/d/1BKDgYNhGeufgC1f_5qeJ3R5LFSSSneZZKBZeNlbZk3gDFrx4oO1Rvtjs/edit) into these new files.
# Add some SAP Gateway Magic
### Set up your SAP credentials
In your Initialize.gs file, add the following code at the marked spot (search for "Lab ->"):
`// Lab -> Get SAP User Id and password here`
`var USER = PropertiesService.getUserProperties().getProperty("SAPGatewayUser");`
`var PASSWORD = PropertiesService.getUserProperties().getProperty("SAPGatewayPassword");`
You'll set these credentials through the spreadsheet UI
### Set the Business Partner collection URL and data format
In your Code.gs file, add the following code at the marked spot (search for "Lab ->"):
>`// Lab -> Set SAP business partner collection url and data format here`
>`var base_url = "https://sapes1.sapdevcenter.com/sap/opu/odata/IWBEP/GWDEMO/BusinessPartnerCollection/?";`
>`var additionParams = "$format=json";`
### Fetch business partner data from SAP
In your Code.gs file, add the following code at the marked spot (search for "Lab ->"):
>`// Lab -> Fetch business partner data from SAP`
>`var dataResponse = UrlFetchApp.fetch(getDataURL,{headers: {Authorization: HEADER}, muteHttpExceptions: true}).getContentText();`
>`var dataObj = JSON.parse(dataResponse);`
### Push the Business Partner data to the spreadsheet and save the file
In your Code.gs file, add the following code at the marked spot (search for "Lab ->"):
>`// Lab -> add business partner data to spreadsheet here`
>`var rows = [];`
>`for(var i = 0;dataObj.d.results && i!=dataObj.d.results.length;i++){`
>>` var row = dataObj.d.results[i];`
>>` rows.push([row.BusinessPartnerKey,row.Company,row.BusinessPartnerRoleText,row.WebAddress, row.EmailAddress, 'street address']);`
>`}`
>`var rowsCount = rows.length+1;`
>`sheet.getRange('A2:'+maxLetter+rowsCount).setValues(rows);`
>`sheet.setName('Business Partners');`
Save the file
# Try out your code
### Set your SAP Credentials
From your spreadsheet menus:
* Click on SAP Gateway -> Set SAP Credentials
* Accept the authorization request; this authorizes your project to access your spreadsheets and call an external service
* Input your SAP Credentials
### Load some business partners
From your spreadsheet menus:
* Click on SAP Gateway -> Clear Sheet
* Click on SAP Gateway -> Load Business Partners
### Congratulations!
You've just imported SAP Gateway Business Partner data directly into Google Spreadsheets.
You can also use this to load Sales Order data, and once the data is in Google Spreadsheets, you can combine different data sets and build pivot tables and charts.