Import JSON to Google Sheet using Google Apps Script

Question

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);
  cell.setValues(jsonData);
}

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

Answer 1

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.

Answer 2

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