Googleスプレッドシートをjsから読み書きしてみた

Googleスプレッドシート奮闘記

ゴール：Googleスプレッドシートを簡易で読み書きできるアプリ作成

実装方針：

1. Googleスプレッドシートの値をJSONで取得
2. GASでGoogleスプレッドシートにPOSTで書き込み

大きく分けるとこんな感じ。

1. Googleスプレッドシートの値をJSONで取得

まずは取得したいスプレッドシートをweb公開しておく。（ファイル→WEBに公開...）

スプレッドシートの値をJSONデータで取得するには、以下を叩けばいいみたい。

https://spreadsheets.google.com/feeds/cells/[key]/[worksheetId]/public/values?alt=json

ここで、

• key
• worksheetId

を調べる。

◆keyの取得

keyはスプレッドシートを開いた時のURLを参照すればすぐに分かる。

https://docs.google.com/spreadsheets/d/[key]/edit#gid=xxx

◆worksheetIdの取得

問題なのはworksheetIdのほう。

文献読んでもようわからん（笑）

いろいろ調べた結果、とりあえず下記のスクリプトをGAS側に仕込んで実行後、ログ確認でシートごとのworksheetIdの取得に成功！

worksheetIdを調べたいスプレッドシートから、
ツール→スクリプトエディタ→下記コードを張り付けして実行→ログ表示

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

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

◆JSONデータ取得

上記方法で調べ終わったら、あとはjs側から下記みたいに叩いて処理してやればOK。

entryとか、JSON覗くとデータまとまって受け取れてることが確認できる。

$.getJSON("https://spreadsheets.google.com/feeds/cells/[key]/[workshwwtId]/public/values?alt=json", function(d){
 　　　　//処理を実行
     var arr=[];
     for(var i = 0; i < d.feed.entry.length; i++) {
         var dd = d.feed.entry[i];
         ・・・(中略)・・・
         arr[dd.gs$cell.row-1][dd.gs$cell.col-1] = dd.gs$cell.$t;

     } 
 }

参考：

GoogleスプレッドシートのデータをJSONで取得してみた | アライドアーキテクツのクリエイターブログ

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

2. GASでGoogleスプレッドシートにPOSTで書き込み

まずはGoogleスプレッドシート側でPOSTを受け取って、シートに書き込みスクリプトを用意しておく。

function doPost(e) { 
 var ss = SpreadsheetApp.openById("xxxxxx");
 var sheet = ss.getSheetByName("xxxx");
 sheet.getRange(1,1).setValue(e.parameter.testdata);
}

上記を対象のスプレッドシートのGAS側に記述→ウェブアプリケーションとして公開

公開後、最新のコード確認というリンクが表示されるので、押下すると対象のページに飛ぶのでそこのURLをコピっとく。

こんな感じのやつ

https://script.google.com/macros/s/hogehoge/exec

あとはjs側で、上記のURLにPOSTするフォームを作成する

<form action="https://script.google.com/macros/s/hogehoge/dev" method="post">
・・・(中略)・・・
</form>

今回だと外部においたjsからのアクセスを想定しての実装だったのでいろいろゴニョゴニョしたけど、

実際GAS側にhtmlファイル作れたりもするのでそっちのほうが楽にできるかも。

そのへんに関しては下記ページなど、調べればでてきますんで、また次回（あれば）

http://webos-goodies.jp/archives/build_a_webapp_with_google_apps_script.html