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
  • I am getting an error as --Cannot convert Array to Object[][] ,After using this code 'rows.push(dataSet[[data.total1]])' This is my Json Dataset. '{ "status": "success", "data": [ { "*****@gmail.com": {}, "email": "*****@gmail.com", "total1": *****, "date1": "*****" }, { "*****2@gmail.com": {}, "email": "*****2@gmail.com", "total1": *****, "date1": "*****", "total2": *****, "date2": "*****" },' – Damodar Raju Oct 10 '16 at 17:51
  • Please check my updated answer, this should work for the structure you described. – Robin Gertenbach Oct 10 '16 at 19:26
  • Still the same error Cannot convert Array to Object[][]. (line 40, file...). – Damodar Raju Oct 11 '16 at 16:48
  • Sorry, of course you still need to push an array, I updated the answer – Robin Gertenbach Oct 11 '16 at 17:49
  • Great! Please consider accepting the answer if it helped you. Thanks :) – Robin Gertenbach Oct 12 '16 at 06:00