GoogleSpreadSheetのgidが分からなすぎだった件がある程度解決できたのでまとめてみたメモ。2015年1月版。

大前提としてapiの仕様が変わる可能性が普通にあるので現時点版という認識でお願いします。

以前からNode.jsから、Googleスプレッドシートに書き込むをもとにStreamingAPIで取得したツイートをGoogleSpreadSheetにリアルタイムに書き込んでみました。などの記事を書いていたのですが、GIDの仕様がイマイチわからないという議論がありました。

スプレットシートの設定とかで私の環境だけで起きてた問題だった場合はご容赦ください。

シート1枚目はうまくいった

前に書いた記事の以下のようなオブジェクトでworksheetIdを'od6'を設定すると最初のシートに書き込みができました。(噂によるとod6が1枚目、od7が2枚目..というルールらしいけど...)

以下はzaruさんの記事のコードになります。

var Spreadsheet = require('edit-google-spreadsheet');

Spreadsheet.load({
  debug: true,
  spreadsheetId: 'スプレッドシートID',
  worksheetId: 'ワークシートID', // 1つめだったら、od6
  username: '[e-mail]',
  password: 'password'
  }, function sheetReady(err, spreadsheet) {
  spreadsheet.receive(function(err, rows, info) {
    if(err) throw err;

    // 最後の行数を取得
    var nextRow = info.nextRow;
    var output = {};
    output[nextRow] = {
      1: "hoge",
      2: "piyo",
    };
    spreadsheet.add(output);
    spreadsheet.send(function(err) {
      if(err) throw err;
      console.log("comp");
    });
  });
});

実行するとこんな感じです。

$ node gas
Logging into Google...
Logged into Google
Retrieved 6 cells and 3 rows
{ spreadsheetId: 'xxxxxxxxxxxx',
  worksheetId: 'od6',
  worksheetTitle: null,
  worksheetUpdated: Invalid Date,
  authors: 'sugawara_nobisuke',
  totalCells: 6,
  totalRows: 3,
  lastRow: 3,
  nextRow: 4 }

シート2枚目からは対応表通りに動作できなかった件

ここからが本題です。

stackoverflowのこの辺りの議論で以下のような対応表がありました。

    'od6': 0, 
    'od7': 1, 
    'od4': 2, 
    'od5': 3, 
    'oda': 4, 
    'odb': 5, 
    'od8': 6, 
    'od9': 7, 
    'ocy': 8, 
    'ocz': 9, 
    'ocw': 10, 
    'ocx': 11, 
    'od2': 12, 
    'od3': 13, 
    'od0': 14, 
    'od1': 15, 
    'ocq': 16, 
    .
    .
    .

これを見るとシート0番目(一番最初のシート)はod6になり、次のシートはod7になるということでした。

なるほど。。。

先ほどのコードをod7にして再実行してみました。

$ node gas
Logging into Google...
Logged into Google

TypeError: Cannot read property 'feed' of undefined
    at /Users/sugawara_nobisuke/n0bisuke/lig/ligco-cw/scripts/node_modules/edit-google-spreadsheet/lib/index.js:422:15
    at Request._callback (/Users/sugawara_nobisuke/n0bisuke/lig/ligco-cw/scripts/node_modules/edit-google-spreadsheet/lib/index.js:105:14)
    at Request.self.callback (/Users/sugawara_nobisuke/n0bisuke/lig/ligco-cw/scripts/node_modules/edit-google-spreadsheet/node_modules/request/index.js:148:22)
    at Request.emit (events.js:98:17)
    at Request.<anonymous> (/Users/sugawara_nobisuke/n0bisuke/lig/ligco-cw/scripts/node_modules/edit-google-spreadsheet/node_modules/request/index.js:876:14)
    at Request.emit (events.js:117:20)
    at IncomingMessage.<anonymous> (/Users/sugawara_nobisuke/n0bisuke/lig/ligco-cw/scripts/node_modules/edit-google-spreadsheet/node_modules/request/index.js:827:12)
    at IncomingMessage.emit (events.js:117:20)
    at _stream_readable.js:944:16
    at process._tickCallback (node.js:442:13)

というエラー。

3枚目、4枚目も試してみたけど全てダメでした。

そもそも対応表のルールがすでに変わっているっていうこともありえると思い調べてみました。

gasでシートの情報を探ってみる

いろいろ探したら、gasのgoogleグループでこんな議論がありました。

ふむふむ、これでシート情報が取れるみたいです。

getGID.gs

function myGetGID() {

  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  for (var i = 0 ; i < sheets.length ; i++) {
    Logger.log(sheets[i].getName() + " = " + sheets[i].getIndex() + ", " + sheets[i].getSheetId());
  }

}

ということで実行してみます。虫アイコンで実行したあとに、表示>ログで確認できます。

最初だけ数値が0になってますが、二番目以降は...

あとから気づいたんですけど、urlからもわかりますね。

https;//省略/spreadsheets/d/1fLVbK-wnX4LnoEFU6wY0z5cbvPhipIwXPOoDS0zmv3I/edit#gid=652135485

という感じで gid=652135485 と明らかにデカイ数値が...シート生成のルールがやはり変わってたんでしょうか。

変換ツールを作ってみる

先ほど挙げたstackoverflowの記事内で紹介されてましたが、node.jsで簡単にツールを作ってみました。

od.js

function gid_to_wid(gid) {
  var xorval = gid > 31578 ? 474 : 31578;
  var letter = gid > 31578 ? 'o' : '';
  return letter + parseInt((gid ^ xorval)).toString(36);
}

console.log(gid_to_wid(process.argv[2]));

今回↑のgasで調べたところ

[15-01-09 18:57:56:610 JST] シート1 = 1, 0
[15-01-09 18:57:56:612 JST] シート4 = 2, 11756792
[15-01-09 18:57:56:614 JST] シート5 = 3, 2092311794

という感じで1番目のworksheetIdは0です。

実行してみます。

$ node od 0
od6

od6になりました。1番目は成功していたので納得です。次に2番目のworksheetIdの11756792を調べてみると。

$ node od 11756792
o6zzmq

o6zzmq ...!? od7とはなんだったんでしょうか。

無事に2枚目のシートに書き込めた。

初めに1枚目に書き込んだコードのworksheetIdを今判明したo6zzmq に変更してみました。

{
  debug: true,
  spreadsheetId: 'スプレッドシートID',
  worksheetId: 'o6zzmq',
  username: '[e-mail]',
  password: 'password'
  }

実行してみます。

$ node gas

Logging into Google...
Logged into Google
Retrieved 0 cells and 0 rows
{ spreadsheetId: 'xxxxxxxxxxxxxxx',
  worksheetId: 'o6zzmq',
  worksheetTitle: null,
  worksheetUpdated: Invalid Date,
  authors: 'sugawara_nobisuke',
  totalCells: 0,
  totalRows: 0,
  lastRow: 1,
  nextRow: 1 }
Sending updates...
Successfully Updated Spreadsheet
comp

。。。書き込みに成功しまいた!

遠回りしてだいぶ疲れた感じがします。

まとめ

スプレットシートのワークシートを追加した時の数値の値が前情報の対応表とは全然違う感じになっていて戸惑いましたが、ワークシートIDからGIDへの変換アルゴリズムは変わっていないようで安心しました。

このメモが他の人の役に立てば幸いです。