In this tutorial, we will leverage the information from Google Sheets and display it in SAP Analytics Cloud using custom widget. In this case, we will show the stock price information from Google Sheets GOOGLEFINANCE formula in SAP Analytics Cloud custom widget.
All functions in this widget is using the built-in GOOGLEFINANCE formula from Google Sheets to get the stock price information with this syntax:
Sample of usage:
You can read more details here.
We will be using libraries from Google Sheets and AmCharts to display the chart. I downloaded the Google Sheets and AmCharts libraries to localhost, the reason is to load the files faster in SAC.
Let's download the following libraries:
Create the following folder structure in your localhost:
Copy the downloaded libraries to box.
Now we need to create box.json and box.js that I will go through each of them in the below section.
box.json
Over here, we defined the web components (i.e, main), properties (i.e, value, formula, width and height) and methods (i.e, setValue and getValue) for our custom widget app. You need to adjust the web server URL and path accordingly. For my case is http://localhost/SAC/sacgooglesheetstock.
box.js
The web component "main" is the entry point of our app.
We need to run a web server to run the custom widget app. I have done it using a simple Apache web server.
Firstly, we need to upload the custom widget with the following steps:
Once we have uploaded the widget, now we need to create an Analytics Application in SAC.
That's all the information and steps we need. If you'd like to use other chart other than AmCharts, you can do so also, just call the respective JavaScript libraries for that chart. Do let me know if you have questions. Thanks.
Create Google Sheets
- Create a spreadsheet called sacsheets. You can name it other name you like.
- Add a sheet called Data. If you want to change it, you need to update in custom widget.
- Get the spreadsheet ID as we will use it in custom widget.
All functions in this widget is using the built-in GOOGLEFINANCE formula from Google Sheets to get the stock price information with this syntax:
GOOGLEFINANCE(ticker, [attribute], [start_date], [end_date], [interval])
- ticker
It is an abbreviation used to uniquely identify publicly traded securities of a particular stock. For e.g. AAPL and MSFT represent the tickers for Apple Inc. and Microsoft Corporation respectively.
- attribute
This is an optional parameter that specifies the type of information GOOGLEFINANCE formula should fetch. - start_date
It indicates the date from which the historical data needs fetching. This is an optional parameter.
- end_date
This parameter, along with the start_date, will indicate the time frame between which Google Sheets should fetch the stock information.
- interval
It’s an optional parameter that indicates the frequency. The two possible inputs are “WEEKLY” and “DAILY”.
Sample of usage:
GOOGLEFINANCE("NASDAQ:GOOG", "price", DATE(2014,1,1), DATE(2014,12,31), "DAILY")
GOOGLEFINANCE("NASDAQ:GOOG","price",TODAY()-30,TODAY())You can read more details here.
Create SAC Custom Widget
We will be using libraries from Google Sheets and AmCharts to display the chart. I downloaded the Google Sheets and AmCharts libraries to localhost, the reason is to load the files faster in SAC.
Let's download the following libraries:
- api.js: https://apis.google.com/js/api.js
- core.js: https://www.amcharts.com/lib/4/core.js
- charts.js: https://www.amcharts.com/lib/4/charts.js
- animated.js: https://www.amcharts.com/lib/4/themes/animated.js
Create the following folder structure in your localhost:
Copy the downloaded libraries to box.
Now we need to create box.json and box.js that I will go through each of them in the below section.
box.json
Over here, we defined the web components (i.e, main), properties (i.e, value, formula, width and height) and methods (i.e, setValue and getValue) for our custom widget app. You need to adjust the web server URL and path accordingly. For my case is http://localhost/SAC/sacgooglesheetstock.
{
"id": "com.fd.googlesheetsstock",
"version": "1.0.0",
"name": "Google Sheets Stock",
"description": "A Google Sheets Stock demo",
"newInstancePrefix": "Googlesheetsstock",
"icon": "http://localhost/SAC/sacgooglesheetstock/box/icon.png",
"vendor": "FD",
"eula": "EULA",
"license": "2.0",
"webcomponents": [
{
"kind": "main",
"tag": "com-fd-googlesheetsstock",
"url": "http://localhost/SAC/sacgooglesheetstock/box/box.js",
"integrity": "",
"ignoreIntegrity": true
}
],
"properties": {
"value": {
"type": "string",
"description": "value",
"default": ""
},
"formula": {
"type": "string",
"description": "formula",
"default": ""
},
"width": {
"type": "integer",
"default": 50
},
"height": {
"type": "integer",
"default": 50
}
},
"methods": {
"setValue": {
"description": "Sets the Google Sheets Stock value.",
"parameters": [
{
"name": "newValue",
"type": "string",
"description": "Google Sheets Value"
},
{
"name": "newFormula",
"type": "string",
"description": "Google Sheets Formula"
}
],
"body": "this.value = newValue; this.formula = newFormula;"
},
"getValue": {
"returnType": "string",
"description": "Returns the Google Sheets Stock value.",
"body": "return this.value;"
}
},
"events": {
"onClick": {
"description": "Called when the user clicks the Box."
}
}
}box.js
The web component "main" is the entry point of our app.
- onCustomWidgetAfterUpdate()
The main logic of the app is in this method. We'll get the value for value and formula from the changedProperties.
The value is the widget's name which user has defined in app designer scripting and the formula is the Google Sheets GOOGLEFINANCE formula to get the stock price information.
If the app is first time loaded, it will create the div container for the chart and save the variable div id, value and formula in an array. And finally, it loads the external Google Sheets and AmCharts libraries and call the Draw function to render the chart.
If the app has already been loaded, it will get the value and formula and call the GoogleSheets and AmChart custom function to render the chart.
- GoogleSheets() function.
The entry point is initClient() where we initialize the API client library and sets up sign-in state listeners.
You need to enable the Google Sheets API and create API key to get the API_KEY and CLIENT_ID. See this for more details. Once the signed in status changes, it will call the updateSignInStatus(). - updateSignInStatus()
In this function, it will call makeApiCall_BatchUpdate().
- makeApiCall_BatchUpdate()
This function will update the row Data!A1 with value, for example, NYSE:PFE and Data!B1 with formula, for example, =GOOGLEFINANCE("NYSE:PFE","price",TODAY()-30,TODAY()).
You need to update the spreadsheetId with sheet ID. Finally, the makeApiCall_Get() function is called.
- makeApiCall_Get()
This function is to get the values from range Data!B:C and pass the data to Amchart() function to draw the chart.
- Amchart()
If the app is first time loaded, it will initialize and create the chart parameters, renders and saves the variable in an array for future use. Otherwise, it updates the chart.
Using the Widget
We need to run a web server to run the custom widget app. I have done it using a simple Apache web server.
Firstly, we need to upload the custom widget with the following steps:
- Click Browse and select Custom Widgets.
- Click plus button to add the custom widget.
- Click Select File to upload box.json file.
- Upload box.json file.
- The widget is successfully uploaded.
Once we have uploaded the widget, now we need to create an Analytics Application in SAC.
- Click Create and select Analytic Application.
- Click plus button to add the custom widget.
- Add the following elements as shown in the below picture onto canvas.
- Insert the following script in Googlesheetstock and InputField. We use the setValue() method to set the value and formula.
var value = InputField_1.getValue();
var radiobutton_val = RadioButtonGroup_1.getSelectedKey();
Googlesheetsstock_1.setValue("Googlesheetsstock_1|" + value, "=GOOGLEFINANCE(\"" + value + "\",\"price\",TODAY()-" + radiobutton_val+ ",TODAY())");
- Click Run Analytics Application to run the app.
That's all the information and steps we need. If you'd like to use other chart other than AmCharts, you can do so also, just call the respective JavaScript libraries for that chart. Do let me know if you have questions. Thanks.
References:
- SAP Managed Tags:
3 Comments
