I am attempting to export data in my Google Sheets to a BI dashboard (Geckoboards). For the particular widget I'm using, the JSON is required in the format like so:
{
"points": {
"point": [
{
"city": {
"city_name": "London",
"country_code": "GB"
},
"size": 10
},
{
"city": {
"city_name": "San Francisco",
"country_code": "US",
"region_code": "CA"
I don't need to worry about the size property right now, however. Currently I've modified a Google Script from an example for this purpose, but it is not working and I don't think I have the loop correctly set up to produce JSON in the proper format:
var apiKey = "#########################omitted";
function geckoDocsJSONmap()
{
var jsonSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("JSON Demo Data");
var widgetKey;
widgetKey = ##########omitted";
var aCity = jsonSheet.getRange("A2:A5").getValues();
var aCountry = jsonSheet.getRange("B2:B5").getValues();
var aRegion = jsonSheet.getRange("C2:C5").getValues();
gdMap_(widgetKey, aCity, aCountry, aRegion);
function pushWidgetUpdate_(widgetKey, payload, options)
{
var payload = JSON.stringify(payload);
var options =
{
"method" : "post",
"payload" : payload
};
var jsonResponse = UrlFetchApp.fetch("https://push.geckoboard.com/v1/send/" + widgetKey, options);
Logger.log(JSON.parse(jsonResponse.getContentText()));
return(true);
}
function gdMap_(widgetKey, aCity, aCountry, aRegion)
{
for (var i = 0; i < aCity[0].length; i++)
{
var payload =
{
api_key:apiKey,
data:
{
points:
{
point : [
{
city: {
city_name: aCity,
country_code: aCountry,
region_code: aRegion }
}
]
}
}
}
}
return(pushWidgetUpdate_(widgetKey, payload));
}
}
I can't seem to provide a picture of the Google Sheet I am using because I don't have enough reputation, but I've published it here:
Any help with this is much appreciated. I'm very much a novice still in my JavaScript ability, and even more so with Google Scripts.