ImportJSON関数とは
そんなものはない
GoogleスプレッドシートにはIMPORT関連の関数が5つありますが、JSONを読み込むための関数は用意されていません。
| 関数 | 説明 |
|---|---|
| IMPORTDATA | 指定したURLのデータを、.csv（カンマ区切り値）形式または.tsv（タブ区切り値）形式でインポートします。 =IMPORTDATA("https://example.com/foo.csv") |
| IMPORTFEED | RSSフィードやAtomフィードをインポートします。 =IMPORTFEED("http://news.google.com/?output=atom") |
| IMPORTHTML | HTMLページ内の表やリストからデータをインポートします。 =IMPORTHTML("http://en.wikipedia.org/wiki/Demographics_of_India","table",) |
| IMPORTRANGE | 指定したスプレッドシートからセルの範囲を読み込みます。 =IMPORTRANGE("https://docs.google.com/spreadsheets/d/foobar", "シート1!A1:C10") |
| IMPORTXML | XML、HTML、CSV、TSV、RSS フィード、Atom XML フィードなど、さまざまな種類の構造化データからデータをインポートします。 =IMPORTXML("https://en.wikipedia.org/wiki/Moon_landing", "//a/@href") |
そのため、JSONを読み込むための関数は自作する必要があります。
ImportJSON関数の作成方法
1. メニューの「拡張機能 => Apps Script」を選択してスクリプトエディタを表示します。
2. ファイルの追加(＋)でImportJSON.gsのコードを追加します。
https://github.com/bradjasper/ImportJSON/blob/master/ImportJSON.gs
以上の手順だけでImportJSONというJSONをURLから読み込んで表示させる関数が使えるようになります。
例
=ImportJSON("https://dummyjson.com/products/")
2025年7月19日追記: ImportJSON関数で読み込めなくなった
記事のリンク先のスプレッドシートを見直したら、dummyjsonの仕様が変わっていて、取得できなくなっていました。
dummyjsonのデータの配列内の子に配列オブジェクトが追加されるようになっていたのですが、前述のImportJSON関数ではこのようなデータを正しく読み込めません。
自作importJSON関数のコードと使い方
ということでiwb.jp版のimportJSON関数を自作しました。
importJSON関数を使用するコードは以下の通りです。
こちらのコードをメニューの「拡張機能 => Apps Script」から追加すれば使用可能です。
JavaScript
function importJSON(url, isSingle = false) {
try {
const response = UrlFetchApp.fetch(url)
const json = JSON.parse(response.getContentText())
const isArrayJson = Array.isArray(json)
if (!isSingle) {
isSingle = /\/\d+$/.test(url);
}
if (isSingle) {
const rows = flattenWithArrays(json)
return rows
} else {
const maxArrayKey = Object.entries(json)
.filter(([_, value]) => Array.isArray(value))
.reduce((max, [key, value]) => {
return value.length > (json[max]?.length ?? 0) ? key : max
}, '')
const arrayData = isArrayJson ? json : json[maxArrayKey]
if (!isSingle && !Array.isArray(arrayData)) {
return [['エラー'], ['配列のキーが見つかりません']]
}
const rows = []
let headerAdded = false
arrayData.forEach(item => {
const result = flattenWithArrays(item)
if (!headerAdded) {
rows.push(...result)
headerAdded = true
} else {
rows.push(...result.slice(1))
}
})
return rows
}
} catch (e) {
return [['エラー'], [e.toString()]]
}
}
function flattenJSON(obj, prefix = '', result = {}) {
for (const key in obj) {
const value = obj[key]
const path = prefix ? `${prefix} ${key}` : key
if (typeof value === 'object' && value !== null && !Array.isArray(value)) {
flattenJSON(value, path, result)
} else if (Array.isArray(value)) {
result[path] = value.join(', ')
} else {
result[path] = value
}
}
return result
}
function flattenWithArrays(obj) {
const base = {}
const arrays = {}
for (const key in obj) {
const value = obj[key]
if (Array.isArray(value) && value.every(v => typeof v === 'object')) {
arrays[key] = value
} else {
base[key] = value
}
}
const baseFlat = flattenJSON(base)
if (Object.keys(arrays).length === 0) {
const keys = Object.keys(baseFlat)
const values = keys.map(k => baseFlat[k])
return [keys, values]
}
const result = []
const arrayKey = Object.keys(arrays)[0]
const items = arrays[arrayKey]
items.forEach((item) => {
const row = {}
const flatItem = flattenJSON(item, arrayKey)
Object.assign(row, baseFlat, flatItem)
result.push(row)
})
const allKeys = new Set()
result.forEach(r => {
Object.keys(r).forEach(k => allKeys.add(k))
})
const headers = Array.from(allKeys)
const rows = [headers]
result.forEach((r) => {
const row = headers.map(h => r[h] ?? '')
rows.push(row)
})
return rows
}シンプルに使えるようにしたかったので、JSONのオブジェクト内の配列のlengthが一番大きいものをデータとして読み込みます。(配列の場合はそのまま読み込む)
ただし、URLの最後が /数字 になっている場合は複数ではなく単数のデータとみなして読み込むようにしています。
第2引数にtrueを指定することで、URLの最後が /数字 でなくても、単数のデータとみなして読み込みます。(下記のサンプル参照)
実際にこちらのコードでサンプルのシートを確認すると、正しく取得されていることが確認できます。
=importJSON("https://dummyjson.com/products?limit=3")
=importJSON("https://dummyjson.com/products/1")
=importJSON("https://dummyjson.com/test", true)
=importJSON("https://dummyjson.com/products/categories")
=importJSON("https://dummyjson.com/recipes?limit=3")
この結果を見て、なぜ「id: 1」などがそれぞれ3行ずつあるのかと思う方がいるかもしれませんが、これは子に配列オブジェクトを含む場合はHTMLのtableで表にする際にtd(セル)の結合をして以下のように表示するためです。
| id | title | reviews rating | desc |
|---|---|---|---|
| 1 | foo | 3 | concat! |
| 4 | |||
| 5 | |||
| 2 | bar | 7 | concat! |
| 8 | |||
| 9 |
td(セル)が結合されたtableタグをJavaScriptで書き出すコードは以下の通りです。
JavaScript
function generateRowspanTable(data, groupKey) {
if (data.length === 0) return ''
const keys = Object.keys(data[0])
const rowspanMap = {}
keys.forEach(key => {
rowspanMap[key] = []
let prevGroup = null
let prevValue = null
let count = 0
for (let i = 0; i <= data.length; i++) {
const cur = data[i] || {}
const curGroup = cur[groupKey]
const curValue = cur[key]
const isSameGroup = curGroup === prevGroup
const isSameValue = curValue === prevValue
if (i < data.length && isSameGroup && isSameValue) {
count++
} else {
if (count > 0) {
rowspanMap[key].push({ index: i - count, span: count })
}
count = 1
prevValue = curValue
prevGroup = curGroup
}
}
})
let html = '<table border="1">\n <thead>\n <tr>'
keys.forEach(key => {
html += `<th>${key}</th>`
})
html += '</tr>\n </thead>\n <tbody>\n'
for (let i = 0; i < data.length; i++) {
html += ' <tr>'
keys.forEach(key => {
const cellInfo = rowspanMap[key].find(info => info.index === i)
const skip = rowspanMap[key].some(info => i > info.index && i < info.index + info.span)
if (skip) {
return
} else if (cellInfo) {
html += `<td rowspan="${cellInfo.span}">${data[i][key]}</td>`
} else {
html += `<td>${data[i][key]}</td>`
}
})
html += '</tr>\n'
}
html += ' </tbody>\n</table>'
return html
}
// example
const jsonData = [
{ id: 1, title: 'foo', 'reviews rating': 3, desc: 'concat!' },
{ id: 1, title: 'foo', 'reviews rating': 4, desc: 'concat!' },
{ id: 1, title: 'foo', 'reviews rating': 5, desc: 'concat!' },
{ id: 2, title: 'bar', 'reviews rating': 7, desc: 'concat!' },
{ id: 2, title: 'bar', 'reviews rating': 8, desc: 'concat!' },
{ id: 2, title: 'bar', 'reviews rating': 9, desc: 'concat!' }
]
document.body.innerHTML = generateRowspanTable(jsonData, 'id')データの配列の中に配列オブジェクトが存在するケースもたまにあるので、これらに対応したコードであればデータを取得後に汎用的に利用することができます。
