Google

HOME
office
Google
Google Apps ScriptでSheets APIを使ったら爆速だった【GAS】

2022年4月16日 / 最終更新日時 : 2024年4月5日 officeの杜 Google

Google Apps ScriptでSheets APIを使ったら爆速だった【GAS】

Twitterを見ていた所、こちらのサイトにて「SpreadsheetAppよりもSheets APIを使ったほうが全然早い」という耳寄りな情報が流れてきて、「そんな事ってあるの？」と思い、自分でテストしてみることにしました。もし、これが事実であれば特に大量のデータの集約などをする場合、標準のSpreadsheetAppを使うよりも、6分の制限のあるGoogle Apps Scriptに於いては速度を稼ぐ優良な手段となりえます。

ということで、実際につかって検証してみます。

1 今回使用するスプレッドシート
2 Sheets APIについて
- 2.1 概要
- 2.2 リクエスト制限
3 ソースコード
- 3.1 SpreadsheetAppの場合
- 3.2 Sheets APIの場合
  - 3.2.1 事前準備
  - 3.2.2 ソースコード
4 計測結果(秒)
- 4.1 読み込みのみのテスト
- 4.2 読み書きのテスト
5 関連リンク

今回使用するスプレッドシート

Sheets APIでアクセス - Google Spreadsheet

今回の元データは診療報酬情報提供サービスの医薬品マスタ（今年度と令和2年度）のデータを利用して、この2つのデータを結合したものを、コピペするといった事で、SpreadsheetAppとSheets APIの間で速度差を計測してみたいと思います。

医薬品マスタをtempdata1,tempdata2にそれぞれ貼り付けてください。今回はテストで5000件に押さえていますが、実データは22000件ほどあります。

ちなみに、Google Apps Scriptで速度を稼ぐ・6分の壁を突破する項目は以下のエントリーになるので合わせてご覧ください。

Google Apps Scriptを高速化するテクニックまとめ【GAS】

Google Apps Scriptで6分の壁（タイムアウト）を突破する【GAS】

Sheets APIについて

概要

本来Sheets APIはGoogle Apps Scriptから利用する為のものというものではなく、様々な外部アプリやウェブサービスからAccess Tokenを使って認証し、操作する為の汎用のREST APIです。しかし、Google Apps Scriptからでも標準でサービスから「Google Sheets API」を使う事で利用する事が可能になっています（この手法はDrive APIを追加する際によく利用しますね）

よって様々な言語からREST APIの作法に従ってHTTPリクエストをする事で、Googleスプレッドシートを操作する事が可能になっています。特徴としては

様々なプログラミング言語からGoogleスプレッドシートのデータ操作を行う事が可能になる
バッチ処理が可能で、一括処理で行わせる事が可能
Google Apps Scriptから利用する場合は、Access Token取得のための認証作業や、リクエスト時に追加する必要が無い

故に、大容量データや複数シートを沢山扱う場合には、SpreadsheetAppではなく、Sheets APIのほうが全然早く処理が可能になる驚きの結果でした。こんなことなら前から使っておけばよかった。

リクエスト制限

GoogleのAPIは当然のようにリクエスト制限が設けられています。以下の制限があるので利用する場合は考慮する必要があります。

読み出しに関して、プロジェクト1日辺り制限は無制限。1分辺りは300リクエスト、ユーザ毎1分辺り60リクエスト
書き出しに関して、プロジェクト1日辺り制限は無制限。1分辺りは300リクエスト、ユーザ毎1分辺り60リクエスト

割当を超過すると、429エラー（Too Many Request）が返ってきますが、正直かなり上記の制限は緩いので、早々超過する事はないのではないかと思います。

ソースコード

今回は集める先の「シート1」そして、2つのtempdataシート。最低でも3回はスプレッドシートにアクセスしなければなりません。このパターンに於いて処理開始時の時刻と終了時刻との差を取って、どちらが処理コストが掛かるのか？を10回程度計測してみることにします。

データは5000件*2シート分、列数は35列分（セルの数に換算して、35万セル分に該当します）。

SpreadsheetAppの場合

//SpreadsheetAppの場合
function ssappexe(){
  //現在時刻を取得する
  let starttime = new Date();

  //シートを取得
  let ss = SpreadsheetApp.getActiveSpreadsheet();
  let sheet = ss.getSheetByName("シート1");
  let temp1 = ss.getSheetByName("tempdata1");
  let temp2 = ss.getSheetByName("tempdata2");

  //tempdataのデータを取得
  let temp1data = temp1.getRange("A1:AI5000").getValues();
  let temp2data = temp2.getRange("A1:AI5000").getValues();

  //結合した配列をシート1に書き出し
  let lastColumn = temp1data[0].length;　　//カラムの数を取得する
  let lastRow = temp1data.length;  　　　　//行の数を取得する
  sheet.getRange(2,1,lastRow,lastColumn).setValues(temp1data);  

  let lastColumn2 = temp2data[0].length;　　//カラムの数を取得する
  let lastRow2 = temp2data.length;  　　　　//行の数を取得する
  sheet.getRange(5002,1,lastRow2,lastColumn2).setValues(temp2data);  

  //終了時刻との差分を算出して、メッセージを表示
  let endtime = new Date();
  let diff = (endtime.getTime()-starttime.getTime()) / 1000;

  SpreadsheetApp.getUi().alert(diff + "ミリ秒掛かった");
}

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

//SpreadsheetAppの場合

function ssappexe(){

//現在時刻を取得する

let starttime = new Date();

//シートを取得

let ss = SpreadsheetApp.getActiveSpreadsheet();

let sheet = ss.getSheetByName("シート1");

let temp1 = ss.getSheetByName("tempdata1");

let temp2 = ss.getSheetByName("tempdata2");

//tempdataのデータを取得

let temp1data = temp1.getRange("A1:AI5000").getValues();

let temp2data = temp2.getRange("A1:AI5000").getValues();

//結合した配列をシート1に書き出し

let lastColumn = temp1data[0].length;　　//カラムの数を取得する

let lastRow = temp1data.length; 　　　　//行の数を取得する

sheet.getRange(2,1,lastRow,lastColumn).setValues(temp1data);

let lastColumn2 = temp2data[0].length;　　//カラムの数を取得する

let lastRow2 = temp2data.length; 　　　　//行の数を取得する

sheet.getRange(5002,1,lastRow2,lastColumn2).setValues(temp2data);

//終了時刻との差分を算出して、メッセージを表示

let endtime = new Date();

let diff = (endtime.getTime()-starttime.getTime()) / 1000;

SpreadsheetApp.getUi().alert(diff + "ミリ秒掛かった");

}

SpreadsheetAppはシートの読み出しは1シート1リクエスト消費するので、API呼び出しコストがシートの数だけ比例してしまい、ここがボトルネックになる
また、ちょいちょい「ページが応答しません」となって止まるケースに遭遇した。
全体的にコードが見にくい上に、書き出しはRangeに4点を指定して出力が必要であるため、分かりづらい
どうも、最終行を調べるendrowを使ったり、配列を結合するArray.prototype.push.applyを使うと「ページが応答しない」状態になってしまうようだ。endrowを調べるコードは以下の通り。故に今回のコードでは最終行はわかってるので、getRangeで直書きしてる。

JavaScript

var endrow = Number(ss.getLastRow()) + 1;

1

var endrow = Number(ss.getLastRow()) + 1;

Sheets APIの場合

事前準備

Google Apps ScriptでSheets APIを利用する場合には事前準備が必要です。以下の手順で追加します。

スクリプトエディタの画面にて、左サイドのサービスの＋をクリックする
Google Sheets APIを選択して追加をクリックする

これで、SheetsにつなげてAPIリクエストを送ることが可能になります。

図：APIの追加をする必要があります。

ソースコード

//Sheets APIの場合
function ssapiexe(){
  //現在時刻を取得する
  let starttime = new Date();

  //このファイルのIDを取得
  let ss = SpreadsheetApp.getActiveSpreadsheet();
  let ssid = ss.getId();

  //tempdataのデータを取得
  var response = Sheets.Spreadsheets.Values.batchGet(
    ssid,
    {ranges: 
      [
        "tempdata1!A1:AI5000", 
        "tempdata2!A1:AI5000"
      ]
    }
  );

  //resnponseからデータを取り出し
  let temp1data = response.valueRanges[0].values;
  let temp2data = response.valueRanges[1].values;

  //シートに書き出しオプション
  let option = {
    valueInputOption: 'USER_ENTERED',
    data: [
      {
        range: 'シート1!A2:AI5001',
        values: temp1data,
      },
      {
        range: 'シート1!A5002:AI10001',
        values: temp2data,
      },
    ]
  };

  //シートに一括書き出し
  let response = Sheets.Spreadsheets.Values.batchUpdate(
    option, ssid
  );

  //終了時刻との差分を算出して、メッセージを表示
  let endtime = new Date();
  let diff = (endtime.getTime()-starttime.getTime()) / 1000;

  SpreadsheetApp.getUi().alert(diff + "ミリ秒掛かった");
}

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

//Sheets APIの場合

function ssapiexe(){

//現在時刻を取得する

let starttime = new Date();

//このファイルのIDを取得

let ss = SpreadsheetApp.getActiveSpreadsheet();

let ssid = ss.getId();

//tempdataのデータを取得

var response = Sheets.Spreadsheets.Values.batchGet(

ssid,

{ranges:

[

"tempdata1!A1:AI5000",

"tempdata2!A1:AI5000"

]

}

);

//resnponseからデータを取り出し

let temp1data = response.valueRanges[0].values;

let temp2data = response.valueRanges[1].values;

//シートに書き出しオプション

let option = {

valueInputOption: 'USER_ENTERED',

data: [

{

range: 'シート1!A2:AI5001',

values: temp1data,

},

{

range: 'シート1!A5002:AI10001',

values: temp2data,

},

]

};

//シートに一括書き出し

let response = Sheets.Spreadsheets.Values.batchUpdate(

option, ssid

);

//終了時刻との差分を算出して、メッセージを表示

let endtime = new Date();

let diff = (endtime.getTime()-starttime.getTime()) / 1000;

SpreadsheetApp.getUi().alert(diff + "ミリ秒掛かった");

}

シートの読み込みも書き出しもValues.batchGetやValues.batchUpdateにて一括で処理が行えるため、リクエスト回数が1回で済みます。
コード全体がスッキリしていて非常に読みやすいです。

計測結果(秒)

読み込みのみのテスト

回数	SpreadsheetApp	Sheets API
1	3.902	1.942
2	3.941	1.573
3	5.394	2.036
4	3.962	2.034
5	4.607	1.626
6	4.142	1.817
7	3.862	1.948
8	5.261	1.946
9	4.070	1.853
10	4.555	1.912

シート1に書き出しをせずに、変数に値を格納する所までの数値ですが、Sheets APIのほうが2.8倍ほど早いですね。ここまで差があると、SpreadsheetAppで大量データを処理するのは鬼門とも言えるかもしれません。

読み書きのテスト

回数	SpreadsheetApp	Sheets API
1	29.334	9.849
2	30.207	10.711
3	29.180	10.600
4	27.785	10.913
5	29.222	9.839
6	26.364	12.059
7	26.382	10.616
8	27.143	9.648
9	28.465	11.676
10	32.204	10.775