【GAS】WebAPIから取得した情報をスプレッドシートへ書込み

2022-02-072024-11-20

当ページのリンクには広告が含まれています。

URLをコピーしました！

WebAPIを使用すると、簡単に様々な情報にアクセスすることができます。

一方で、その情報を共有しようとすると一手間かかることが多いですよね。

今回は、WebAPIから取得した情報をスプレッドシートへ書込みを行う一連の流れをご紹介いたします。

最終的な完成系はコチラ

この記事のおすすめの人

SaaSなどのシステムから定期的に情報をスプレッドシートへ記入して更新したい
毎回システムからCSVダウンロードしてスプレッドシートへ手動で転記している
WebAPIから取得した情報を簡単に共有したい

WebAPIから情報を取得

まずはWebAPIから情報を取得します。

【GAS】外部WebAPIから情報を取得する方法【URLFetchApp】 WebAPIを使用して、外部から情報を取得したり、データを送信したい・・・。そんな時はGASのURLFetchAppを使用すると簡単に行えます。今回はGASから外部APIへアクセス…

GASのUrlFetchApp.fetch()関数を使用してリクエストを送信します。

リクエストを送るURLはJSONを返してくれるjsonplaceholderというサイトを利用します。

https://jsonplaceholder.typicode.com/

const fetchJson  = () => {
  const url = 'https://jsonplaceholder.typicode.com/'
  let response = UrlFetchApp.fetch(`${url}posts`)
}

レスポンスは下記のような配列で返却されます。

[
  {
    "userId": 1,
    "id": 1,
    "title": "sunt aut facere repellat provident occaecati excepturi optio reprehenderit",
    "body": "quia et suscipit\nsuscipit recusandae consequuntur expedita et cum\nreprehenderit molestiae ut ut quas totam\nnostrum rerum est autem sunt rem eveniet architecto"
  },
  ...
]

各項目の

userId
id
title
body

を今回はスプレッドシートのヘッダー行として利用します。

取得した情報を任意のデータ形式へ変更する

スプレッドシートに書き込むためにデータを成形します。手順としては

レスポンスデータをオブジェクト形式に変更
データを任意の配列形式へ成形
1. シートの初期化
2. ヘッダー配列の追加
3. map関数でデータを成形

となります。

レスポンスデータをオブジェクト形式に変更 – JSON.parse(response)

取得したデータを、オブジェクトとして扱うためにparseします。

ドキュメント

JSON.parse() – JavaScript | MDN

https://developer.mozilla.org/ja/docs/Web/JavaScript/Reference/Global_Objects/JSON/parse

let data = JSON.parse(response)

これでdata[0]['user_id']やdata.user_idのような形式でアクセスできるようになりました。

データを任意の配列形式へ成形

スプレッドシートへ書き込む際には、一行ごとに配列を作成します。イメージとしては

 const addArray = [
    ['a', 'b', 'c'],
    [1, 2, 3]
  ]

のように行ごとに配列にします。

また、あらかじめ空配列を作成しておき、配列を追加できるようにします。

let add_values = []

ヘッダー配列の追加

初めにヘッダー部分用の配列を追加します。

ドキュメント

Array.prototype.push() – JavaScript | MDN

https://developer.mozilla.org/ja/docs/Web/JavaScript/Reference/Global_Objects/Array/push

userIdやidなどです。

add_values.push(['userId', 'id', 'title', 'body'])

map関数でデータを成形

ヘッダー行以降に関しては、map関数で配列を追加する繰り返し処理を行います。

ドキュメント

Array.prototype.map() – JavaScript | MDN

https://developer.mozilla.org/ja/docs/Web/JavaScript/Reference/Global_Objects/Array/map

data.map(d => {
    add_values.push([d.userId, d.id, d.title, d.body])
  })

適宜Logger.logで確認してください。下記のような形式であればOKです。

 [
   [userId, id, title, body], 
   [1.0, 1.0, sunt aut facere rep...],
   ...
 ]

スプレッドシートへ書き込み

ここまででデータの準備は完了しました！スプレッドシートへ書き込んでいきます。

手順としては

シートの指定 – SpreadsheetApp.openById().getSheetByName()
範囲の指定 – sheet.getRange()
シートの初期化
スプレッドシートへの書き込み

となります。

シートと範囲の指定

まずはシートの指定を行います。

const sheet_id = 'xxxxxxxxxxxxxxxxxxxxxxxxx'
const sheet_name = 'シート1'

const sheet = SpreadsheetApp.openById(sheet_id).getSheetByName(sheet_name);

下記記事にて詳細を説明しています。ご参照ください。

【GAS】スプレッドシートに書き込みを行う方法【一括・一行追加】 GASを使用してスプレッドシートに書き込みがしたい・・・。そんな時に便利なGASの関数をご紹介いたします。スプレッドシートのシートと範囲の指定スプレッドシートの…

続いて範囲指定を行います。

スプレッドシートのA1を始点とし、列は４列で行数は作成した配列(ヘッダー＋データ)となります。

const value_length = add_values.length
const range = sheet.getRange(1,1,value_length,4)

シートの初期化

時間やスプレッドシートを開いたタイミングで処理を行う場合には、前回の処理の結果を初期化したほうが都合がいいので初期化します。

初期化には、sheet.clearContents()を使用します。

シートへの書き込み

最後にスプレッドシートへ書き込み。range.setValuses()で引数に作成した配列をします。

range.setValues(add_values)

確認

最終的なコードを確認します。

const fetchAddSheet  = () => {

  const sheet_id = 'xxxxxxxxxxxxxxxxxxxxxx'
  const sheet_name = 'シート1'

  const sheet = SpreadsheetApp.openById(sheet_id).getSheetByName(sheet_name)

  const url = 'https://jsonplaceholder.typicode.com/'
  let response = UrlFetchApp.fetch(`${url}posts`)

  const data = JSON.parse(response)

  let add_values = []
  
  add_values.push(['userId', 'id', 'title', 'body'])

  data.map(d => {
    add_values.push([
      d.userId,
      d.id,
      d.title,
      d.body
    ])
  })
  const value_length = add_values.length
  const range = sheet.getRange(1,1,value_length,4)
  sheet.clearContents()
  range.setValues(add_values)
}

内容を確認するためにGASを実行してみましょう。

スプレッドシートを確認してみると・・・