I am using Apps Script in Google Sheets to import JSON from Google Analytics. I have previously used the ImportJSON function to easily get the JSON from an open API just using =ImportJSON(url) within a cell. Because I am using Apps Script to authorise the Analytics API stuff (this all works fine), I am also using it to insert the JSON data into my sheet. ImportJSON returns a two-dimensional array containing the data, with the first row containing headers. However, I am having difficulty getting the range of the data so that I can add the values to the sheet.

This is what I have:

function makeRequest() {
  var analyticsService = getAnalyticsService();
  var apiUrl = 'https://www.googleapis.com/analytics/v3/data/ga?ids=removed&start-date=30daysAgo&end-date=yesterday&metrics=ga%3AuniquePageviews&dimensions=ga%3ApagePath&sort=-ga%3AuniquePageviews&filters=ga%3ApagePath%3D%40%2FKnowledgeBank%2FFactsheetForFarmers.aspx&max-results=10&access_token=' + analyticsService.getAccessToken()
  var sheet = SpreadsheetApp.getActiveSheet();
  var jsonData = ImportJSON(apiUrl);
  var cell = sheet.getRange(jsonData.length,jsonData[0].length);

The error I get is Incorrect range height, was 3 but should be 1

  • try var cell = sheet.getRange(1, 1, jsonData.length,jsonData[0].length);
    – JPV
    Commented Jan 6, 2016 at 11:19
  • With that, the error I get is Incorrect range width, was 25 but should be 27
    – Abbie
    Commented Jan 6, 2016 at 11:20
  • 1
    I don't do much JSON conversion, but I thought ImportJSON is a spreadsheet function (formula). To Parse JSON in javascript (GAS) you need JSON.parse I think. See also: medium.com/@paulgambill/… and here:stackoverflow.com/questions/2658980/…
    – JPV
    Commented Jan 6, 2016 at 12:20
  • Also check out: gist.github.com/mhawksey/1442370
    – JPV
    Commented Jan 6, 2016 at 12:23
  • 1
    Check: ImportJSON at GitHub for the ImportJSON() function.
    – kenorb
    Commented Jul 30, 2018 at 20:05

2 Answers 2


As JPV pointed out, the line

var jsonData = ImportJSON(apiUrl);

is not valid in Apps Script (unless you have defined ImportJSON function somewhere). To fetch something from an external URL in Apps Script, one uses UrlFetchApp. So the line could be

var jsonData = JSON.parse(UrlFetchApp.fetch(apiUrl).getContentText());

After this, jsonData is a JavaScript object parsed from the JSON string returned by the server. To import it in a spreadsheet, one has to create a double array suitable for passing to setValues. How to do this depends on the structure of the object.

The pullJSON gist that you referenced gives a simple model of this process, which works when jsonData is already an array. But different APIs work differently: for example, Stack Exchange API returns an object with several "wrapper" properties, one of which is items that holds an array of objects of interest. So in this case one would loop over the elements of jsonData.items, extracting the properties of each object in the array and pushing them into a double array.


It is also possible to pull data from a JSON file to Google Sheets without using Google Apps Script. What you need to do, is to install add-on, then fill out the fields, set up an automatic data schedule and run it for the first time (to make sure it fetches all data correctly). I attach screenshot of the form itself screenshot of the required parameters

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.