Google-sheets – Import JSON to Google Sheet using Google Apps Script

google sheetsgoogle-apps-scriptimportjson

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

Best Answer

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.

Related Topic