Getting error in Google App Script to fetch data from JSON api Webservice and write them to google spreadsheet

Question

I am trying to get data using Google App Script from a web service via api which is in json format and write it to my google sheet spreadsheet but getting error that says "The number of rows in the range must be at least 1. (line 22)"

I got a script from google search which I wrote it in the script editor of google sheets and given all permissions. Can someone please tell me how to get past the error?

Thanks

// Taken from here --> https://gist.github.com/varun-raj/5350595a730a62ca1954

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

  var url="https://cricapi.com/api/fantasySummary?apikey=apikey"; // Paste your JSON URL here

  var response = UrlFetchApp.fetch(url); // get feed
  var dataAll = JSON.parse(response.getContentText()); //
  var dataSet = dataAll;

  var rows = [],
      data;

  for (i = 0; i < dataSet.length; i++) {
    data = dataSet[i];
    rows.push([data.data, data.team, data.players, data.name]); //your JSON entities here
  }

  dataRange = sheet.getRange(1, 1, rows.length, 4); // 4 Denotes total number of entites
  dataRange.setValues(rows);

}

Given below value of response

{"dateTimeGMT":"2019-05-14T12:00:00.000Z","data":{"fielding":[{"title":"Fielding for Pakistan Innings","scores":[{"name":"CR Woakes","runout":0,"stumped":0,"bowled":2,"lbw":0,"catch":1,"pid":"247235"},{"name":"Chris Jordan","runout":0,"stumped":0,"bowled":0,"lbw":0,"catch":1,"pid":288992},{"name":"JJ Roy","runout":0,"stumped":0,"bowled":0,"lbw":0,"catch":1,"pid":"298438"},{"name":"JE Root","runout":0,"stumped":0,"bowled":0,"lbw":0,"catch":1,"pid":"303669"},{"name":"DJ Willey","runout":0,"stumped":0,"bowled":1,"lbw":0,"catch":1,"pid":"308251"},{"name":"TK Curran","runout":1,"stumped":0,"bowled":1,"lbw":1,"catch":0,"pid":"550235"}]},{"title":"Fielding for England Innings","scores":[{"name":"Asif Ali","runout":0,"stumped":0,"bowled":0,"lbw":0,"catch":1,"pid":"494230"},{"name":"Junaid Khan","runout":0,"stumped":0,"bowled":1,"lbw":0,"catch":0,"pid":"259551"},{"name":"Babar Azam","runout":0,"stumped":0,"bowled":0,"lbw":0,"catch":1,"pid":"348144"},{"name":"Shaheen Shah Afridi","runout":1,"stumped":0,"bowled":0,"lbw":0,"catch":0,"pid":"1072470"}]}],"bowling":[{"scores":[{"6s":1,"4s":9,"0s":28,"Econ":"6.70","W":"4","R":"67","M":"0","O":"10","bowler":"CR Woakes","pid":"247235"},{"6s":3,"4s":10,"0s":26,"Econ":"8.60","W":"1","R":"86","M":"0","O":"10","bowler":"DJ Willey","pid":"308251"},{"6s":1,"4s":1,"0s":14,"Econ":"5.33","W":"0","R":"32","M":"0","O":"6","bowler":"MM Ali","pid":"8917"},{"6s":0,"4s":5,"0s":20,"Econ":"6.11","W":"1","R":"55","M":"0","O":"9","bowler":"LE Plunkett","pid":"19264"},{"6s":2,"4s":7,"0s":21,"Econ":"7.40","W":"2","R":"74","M":"0","O":"10","bowler":"TK Curran","pid":"550235"},{"6s":0,"4s":3,"0s":3,"Econ":"8.50","W":"0","R":"34","M":"0","O":"4","bowler":"BA Stokes","pid":"311158"},{"6s":0,"4s":1,"0s":1,"Econ":"9.00","W":"0","R":"9","M":"0","O":"1","bowler":"JL Denly","pid":"12454"}],"title":"Bowling To Pakistan Innings"},{"scores":[{"6s":3,"4s":5,"0s":24,"Econ":"7.12","W":"1","R":"57","M":"0","O":"8","bowler":"Junaid Khan","pid":"259551"},{"6s":3,"4s":11,"0s":25,"Econ":"8.30","W":"0","R":"83","M":"0","O":"10","bowler":"Shaheen Shah Afridi","pid":"1072470"},{"6s":0,"4s":8,"0s":20,"Econ":"6.87","W":"0","R":"55","M":"0","O":"8","bowler":"Hasan Ali","pid":"681305"},{"6s":4,"4s":2,"0s":15,"Econ":"8.28","W":"1","R":"58","M":"0","O":"7","bowler":"Imad Wasim","pid":"227758"},{"6s":4,"4s":7,"0s":28,"Econ":"8.33","W":"1","R":"75","M":"0","O":"9","bowler":"Faheem Ashraf","pid":"681117"},{"6s":2,"4s":0,"0s":3,"Econ":"9.50","W":"0","R":"19","M":"0","O":"2","bowler":"Haris Sohail","pid":"318788"},{"6s":0,"4s":1,"0s":0,"Econ":"10.80","W":"0","R":"9","M":"0","O":"0.5","bowler":"Asif Ali","pid":"494230"}],"title":"Bowling To England Innings"}],"batting":[{"scores":[{"dismissal-by":{"name":"TK Curran","pid":"550235"},"dismissal":"bowled","SR":115,"6s":1,"4s":16,"B":131,"R":151,"dismissal-info":" b Curran","batsman":"Imam-ul-Haq","pid":"568276"},{"dismissal-by":{"name":"JE Root","pid":"303669"},"dismissal":"catch","SR":50,"6s":0,"4s":0,"B":4,"R":2,"dismissal-info":"c Root b Woakes","batsman":"Fakhar Zaman","pid":"512191"},{"dismissal-by":{"name":"CR Woakes","pid":"247235"},"dismissal":"bowled","SR":136,"6s":0,"4s":3,"B":11,"R":15,"dismissal-info":" b Woakes","batsman":"Babar Azam","pid":"348144"},{"dismissal-by":[{"name":"TK Curran","pid":"550235"}],"dismissal":"runout","SR":100,"6s":0,"4s":7,"B":41,"R":41,"dismissal-info":"run out (Curran)","batsman":"Haris Sohail","pid":"318788"},{"dismissal-by":{"name":"Chris Jordan","pid":288992},"dismissal":"catch","SR":79,"6s":0,"4s":2,"B":34,"R":27,"dismissal-info":"c sub (CJ Jordan) b Plunkett","batsman":"Sarfaraz Ahmed (c)  ","pid":"227760"},{"dismissal-by":{"name":"JJ Roy","pid":"298438"},"dismissal":"catch","SR":120,"6s":3,"4s":2,"B":43,"R":52,"dismissal-info":"c Roy b Woakes","batsman":"Asif Ali","pid":"494230"},{"dismissal-by":{"name":"CR Woakes","pid":"247235"},"dismissal":"catch & bowled","SR":183,"6s":0,"4s":4,"B":12,"R":22,"dismissal-info":"c & b Woakes","batsman":"Imad Wasim","pid":"227758"},{"dismissal-by":{"name":"TK Curran","pid":"550235"},"dismissal":"lbw","SR":118,"6s":0,"4s":1,"B":11,"R":13,"dismissal-info":"lbw b Curran","batsman":"Faheem Ashraf","pid":"681117"},{"dismissal":"not out","SR":200,"6s":2,"4s":1,"B":9,"R":18,"dismissal-info":"not out","batsman":"Hasan Ali","pid":"681305"},{"dismissal-by":{"name":"DJ Willey","pid":"308251"},"dismissal":"catch & bowled","SR":175,"6s":1,"4s":0,"B":4,"R":7,"dismissal-info":"c & b Willey","batsman":"Shaheen Shah Afridi","pid":"1072470"},{"dismissal":"not out","SR":0,"6s":0,"4s":0,"B":0,"R":0,"dismissal-info":"not out","batsman":"Junaid Khan","pid":"259551"},{"SR":"","6s":"","4s":"","B":"","R":"","dismissal-info":"","detail":"10 (lb 1, w 9)","batsman":"Extras","pid":0}],"title":"Pakistan Innings"},{"scores":[{"dismissal-by":{"name":"Asif Ali","pid":"494230"},"dismissal":"catch","SR":138,"6s":4,"4s":8,"B":55,"R":76,"dismissal-info":"c Asif Ali b Faheem Ashraf","batsman":"JJ Roy","pid":"298438"},{"dismissal-by":{"name":"Junaid Khan","pid":"259551"},"dismissal":"bowled","SR":137,"6s":5,"4s":15,"B":93,"R":128,"dismissal-info":" b Junaid Khan","batsman":"JM Bairstow  ","pid":"297433"},{"dismissal-by":{"name":"Babar Azam","pid":"348144"},"dismissal":"catch","SR":119,"6s":1,"4s":4,"B":36,"R":43,"dismissal-info":"c Babar Azam b Imad Wasim","batsman":"JE Root","pid":"303669"},{"dismissal-by":[{"name":"Shaheen Shah Afridi","pid":"1072470"}],"dismissal":"runout","SR":97,"6s":2,"4s":2,"B":38,"R":37,"dismissal-info":"run out (Shaheen Shah Afridi)","batsman":"BA Stokes","pid":"311158"},{"dismissal":"not out","SR":127,"6s":3,"4s":4,"B":36,"R":46,"dismissal-info":"not out","batsman":"MM Ali","pid":"8917"},{"dismissal":"not out","SR":141,"6s":1,"4s":1,"B":12,"R":17,"dismissal-info":"not out","batsman":"EJG Morgan (c)","pid":"24598"},{"SR":"","6s":"","4s":"","B":"","R":"","dismissal-info":"","detail":"12 (lb 3, nb 1, w 8)","batsman":"Extras","pid":0}],"title":"England Innings"}],"team":[{"players":[{"name":"JJ Roy","pid":"298438"},{"name":"JM Bairstow","pid":"297433"},{"name":"JE Root","pid":"303669"},{"name":"BA Stokes","pid":"311158"},{"name":"MM Ali","pid":"8917"},{"name":"EJG Morgan","pid":"24598"},{"name":"JL Denly","pid":"12454"},{"name":"TK Curran","pid":"550235"},{"name":"CR Woakes","pid":"247235"},{"name":"DJ Willey","pid":"308251"},{"name":"LE Plunkett","pid":"19264"}],"name":"England"},{"players":[{"name":"Imam-ul-Haq","pid":"568276"},{"name":"Fakhar Zaman","pid":"512191"},{"name":"Babar Azam","pid":"348144"},{"name":"Haris Sohail","pid":"318788"},{"name":"Sarfaraz Ahmed","pid":"227760"},{"name":"Asif Ali","pid":"494230"},{"name":"Imad Wasim","pid":"227758"},{"name":"Faheem Ashraf","pid":"681117"},{"name":"Hasan Ali","pid":"681305"},{"name":"Shaheen Shah Afridi","pid":"1072470"},{"name":"Junaid Khan","pid":"259551"}],"name":"Pakistan"}],"man-of-the-match":{"name":"JM Bairstow","pid":"297433"},"toss_winner_team":"England","winner_team":"England","matchStarted":true},"type":"ODI","cache3":true,"creditsLeft":202,"v":"1","ttl":6,"provider":{"source":"Various","url":"https://cricapi.com/","pubDate":"2019-05-23T07:15:07.670Z"}}

The output what I am looking is this:

If I supply data->team->players->name, it should output this (only name)

If I supply data->team->Players, it should output this(name+pid)

Similarly if I supply any other group, it should output the respective values.

Answer 1

This error is not related to the app script. This is your google sheet issue, you need to freeze the header of the sheet. Steps: 1) Select the header row 2) Go to sheet menu View > Freeze > 1 row check the screenshot

For more details see following screenshot: