Problem

Google YouTube API will return a query report by JSON, which has a columnsHeaders to describe the columns and a rows to contain the data table, looks like:

{
 "kind": "youtubeAnalytics#resultTable",
 "columnHeaders": [
  {
   "name": "day",
   "columnType": "DIMENSION",
   "dataType": "STRING"
  },
  {
   "name": "views",
   "columnType": "METRIC",
   "dataType": "INTEGER"
  }
 ],
 "rows": [
  [
   "2017-07-01",
   18552.0
  ],
  [
   "2017-07-02",
   15696.0
  ],
  [
   "2017-07-03",
   21593.0
  ],
  ...
}

Google Analytics API reports the similar way.

How can we import such JSON data into Google Sheets, where we could manipulate the data, e.g. doing calculation, making some charts.

Solution

The most simplest way should be running a customized script1 from Google Spreadsheet’s Tools.

Here’s the detail instructions2:

  1. Create a new Google Spreadsheet.
  2. Click on Tools -> Script Editor.
  3. Click Create script for Spreadsheet.
  4. Delete the placeholder content and paste the code from the customized script.
  5. Rename the script to ImportJSON.gs and click the save button.
  6. Back in the spreadsheet, in a cell, you can type 
    =ImportHeaderRowJSON("https://raw.githubusercontent.com/touren/YouTube_API/master/query_report_view_by_day.json")

    The script will load the JSON data, parse it, format the columns and create a data table for you.

  7. You can create a chart on it now.

Below is the demo gif:

Import Demo

 

  1. I added a new function ImportHeaderRowJSON, based on the awesome ImportJSON script.
  2. Referenced from How to import JSON data into Google Spreadsheets in less than 5 minutes.