Google Sheets JSON export, correctly formatting JSON for push

194 Views Asked by At

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:

Google Sheet

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.

0

There are 0 best solutions below