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

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

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

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

最終的な完成系はコチラ

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

WebAPIから情報を取得

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

GASUrlFetchApp.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"
  },
  ...
]

各項目の

  1. userId
  2. id
  3. title
  4. body

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

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

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

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

となります。

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

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

let data = JSON.parse(response)

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

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

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

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

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

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

let add_values = []

ヘッダー配列の追加

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

ドキュメント

userIdidなどです。

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

map関数でデータを成形

ヘッダー行以降に関しては、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...],
   ...
 ]

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

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

手順としては

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

となります。

シートと範囲の指定

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

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

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

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

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

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

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を実行してみましょう。

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

無事書き込みができていますね!

最終的な完成系はコチラ

トリガーの設定

最後にGASの起動タイミングを設定します。

今回はスプレッドシートを開くタイミングで実行されたいので

  • イベントのソースを選択 – スプレッドシートから
  • イベントの種類を選択 – 起動時

に設定して保存でOKです!

これは色んなことに応用できそうですね!ぜひ試してみてください!

トリガーをスプレッドシート起動時に設定した場合、他者が開いても実行されないので注意してください!複数人で共有する場合には時間主導型で定期実行をお勧めします。

おすすめのUdemy学習動画

ビジネスパーソンに贈る業務効率化大全 〜Google Apps Scriptによる業務の自動化〜

おすすめ学習書籍

¥2,860 (2022/02/12 18:36時点 | Amazon調べ)
\楽天ポイント5倍セール!/
楽天市場
\楽天ポイント5倍セール!/
楽天市場
\楽天ポイント5倍セール!/
楽天市場

この記事が気に入ったら
フォローしてね!

よかったらシェアしてね!

コメント

コメントする

目次
閉じる