0

I am working on google scripts which can call REST API and get the data in google spreadsheets. But different Json objects work and which I am using now does not work...

At first it was giving me an error as The coordinates or dimensions of the range are invalid. So looked to on at "The coordinates or dimensions of the range are invalid" - Google Apps Script and JSON Data

And now the result is undefined..

Really appreciate if someone can help 

function pullJSON(k) {

     var ss = SpreadsheetApp.getActiveSpreadsheet();
     var sheets = ss.getSheets();
     var sheet = ss.getActiveSheet();

     var url = "********"; // Paste your JSON URL here
     var headers = {
         "Content-Type": "application/json",

     };

     var options = {
         "method": "GET",
         "headers": headers
     };

     var response = UrlFetchApp.fetch(url, options); // get feed
     var dataAll = JSON.parse(response.getContentText()); //
     var dataSet = dataAll;
     Logger.log(dataAll.length);
     Logger.log(k);
     Logger.log(url);

     var rows = [],
         data;
     for (var k = 0; k < Object.keys(dataSet).length; k++) {
         data = [Object.keys(dataSet)[k]];
         Logger.log(data);

         rows.push([data.total1]);} //your JSON entities here

    dataRange = sheet.getRange( 1, 1,rows.length,1); // 3 Denotes total number of entites
    dataRange.setValues(rows);
 }
Community
  • 1
  • 1
Damodar Raju
  • 13
  • 2

1 Answers1

0

What you are doing at the moment is calling Object.keys() on dataSet.
This returns an array of strings, e.g. ["status", "data"].

Then you retrieve each of these keys separately and assign them as a one element array to data, so data looks like ["status"], and ["data"].
Since ["total1]" is an array of a string it doesn't have the "total1" attribute, just an element with the value equal to the name.

To get the actual total 1 value from each object within data you can

dataSet.data.forEach(function(x) {
  rows.push([x.total1]);
});

instead of the for loop.

Robin Gertenbach
  • 10,316
  • 3
  • 25
  • 37