0

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) enter image description here

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

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

15
  • Can you provide the value of response? Commented May 23, 2019 at 0:05
  • Updated value of response in the question. Thanks for looking into it. Also updated apikey in the question. Commented May 23, 2019 at 7:17
  • Thank you for adding the information. If the added value is response.getContentText(), dataSet.length becomes undefined. By this, rows becomes []. I think that the reason of your error is this. Although I thought that I tried to modify your script, unfortunately from your question I couldn't understand about the output you want. So I posted this as a comment. I apologize for my poor English skill. Commented May 23, 2019 at 7:39
  • Thank you for the quick reply. I dont know if you work with google apps script & google sheets. I need the output in google spreadsheets in the active worksheet in tabular format where all the values goes under the respective headings. 'rows.push([data.data, data.team, data.players, data.name])' should bring a list of players one below the other along with which team they belong to. I hope I am explaining my question properly. Commented May 23, 2019 at 7:51
  • 1
    My issue is resolved. I used the code from here and it worked https://github.com/bradjasper/ImportJSON/blob/master/ImportJSON.gs Commented May 24, 2019 at 12:20

1 Answer 1

2

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:

enter image description here

Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.