このページの内容
GASを利用してWebを表示できることが分かったので、スプレッドシートをデータベースとして利用したアプリを作成してみるにあたり、スプレッドシートの読み込みと書き込みを行えるようにしていきたいと思います。
スプレッドシートの読み込みなどを利用するにあたり、GASのスクリプトをスプレッドシートに埋め込むことにします。
下記図にあるように、スプレッドシートの画面から[ツール] → [スクリプトエディタ]へ進むとGASのエディタが起動します。
基本的な読み込みと書き込みのコードは次のようになります。
まずは読み込みのコードを掲載します
//スプレッドシート内のデータを読み込む
function GetSpreadsheet(){
//操作するスプレッドシートIDを指定して開く
//スプレッドシートのIDはスプレッドシートを開いた時のURLの https://docs.google.com/spreadsheets/d/*****************/edit#gid=########### の**の部分
var file = SpreadsheetApp.openById('1kC**************************************eqI');
var sheet = file.getSheetByName('シート1'); //操作するシート名を指定
//全データを取得する場合、最終列と最終行を取得する
var last_col = sheet.getLastColumn(); //最終列取得
var last_row = sheet.getLastRow(); //最終行取得
//データを取得する範囲を指定する
var data_range = sheet.getRange(1, 1, last_row, last_col); //.getRange(先頭行番号, 先頭列番号, 取得する行数, 取得する列数)
//データを取得する
var data_values = data_range.getValues();
//２次元配列が返される
return data_values;
}次に書き込みのコードを掲載します
//スプレッドシートにデータを書き込む
function SetSpreadSheet(data){
//ファイルとシートの指定は読み込みと同じようにしますが、シートの指定までを1行で書くこともできます。
var sheet = SpreadsheetApp.openById('1kC**************************************eqI').getSheetByName('シート2');
//データを書き込む範囲を指定して、書き込みます
//これも1行で書くことも可能です
sheet.getRange(1, 1, data.length, data[0].length).setValues(data);
}スプレッドシートのファイルの指定についてですが、読み込みのコード中のコメントにも記載しましたが、各自確認して設定してください。
※ファイルごとに値が違うので、上に記載した図でも黒塗りにしています。
読み込ませるファイルは以下のように設定しました。
コードは以下のように記述できます。
サーバー側の処理（.gsファイル）
//読み込みでも書き込みでも利用するので、変数に入れてしまう
var spreadsheetId = '1kC**************************************eqI';
//実行時にwebサービスを実行させる
function doGet(e) {
var t = HtmlService.createTemplateFromFile("index.html");
return t.evaluate();
}
//スプレッドシートのデータを読み込む
function GetSpreadsheet(){
//操作するスプレッドシートIDとシート名を指定して開く
var sheet = SpreadsheetApp.openById(spreadsheetId).getSheetByName('シート1');
//全データを取得するので、最終列と最終行を取得する
var last_col = sheet.getLastColumn(); //最終列取得
var last_row = sheet.getLastRow(); //最終行取得
//データを取得する範囲を指定して取得し、2次元配列で返す
return sheet.getRange(1, 1, last_row, last_col).getValues();
}
//スプレッドシートにデータを書き込む
function SetSpreadSheet(data){
//ファイルをIDで指定、シートをシート名で指定
var sheet = SpreadsheetApp.openById(spreadsheetId).getSheetByName('シート3');
//データを書き込む範囲を指定して、書き込みます
sheet.getRange(1, 1, data.length, data[0].length).setValues(data);
}クライアント側の処理(.htmlファイル)
※GASの説明がメインのため、htmlの説明は省略しています。
<!DOCTYPE html>
<html>
<script>
function loadingProcess(){
//サーバーからスプレッドシートのデータを取得する
google.script.run.withSuccessHandler(dispSpreadsheet).GetSpreadsheet();
}
window.addEventListener('load', loadingProcess);
//取得したデータを表示する
function dispSpreadsheet(spreadsheet_data){
var htmlTable = '<table id="tableSSData" border=1>';
//2次元配列を取得しているので、
for(var row = 0; row < spreadsheet_data.length; row++){
htmlTable += '<tr>';
for(var col = 0; col < spreadsheet_data[row].length; col++){
//編集可能にするために、テーブルの各セルににテキストエリアを設ける(テキストエリアのIDに行と列の位置を入れておく)
htmlTable += '<td><textarea id="txt' + row + col + '">' + spreadsheet_data[row][col] + '</textarea></td>';
}
htmlTable += '</tr>';
}
htmlTable += '</table>';
document.getElementById("dispArea").innerHTML = htmlTable;
}
//書き込み処理
function writeSpreadsheet(){
var tableData = document.getElementById("tableSSData");
var write_data = new Array(); //書き込みデータ格納用配列
for(var row = 0; row < tableData.rows.length; row++){
var row_data = new Array(); //1行分のデータ格納用
for(var col = 0; col < tableData.rows[row].cells.length; col++){
row_data[col] = document.getElementById("txt" + row + col).value;
}
write_data[row] = row_data; //2次元配列にする
}
//非同期通信で書き込み
google.script.run.SetSpreadSheet(write_data);
}
</script>
<head>
<base target="_top">
</head>
<body>
<div id='dispArea'>
</div>
<input type="button" value="書き込み" onclick="writeSpreadsheet();">
</body>
</html>非同期処理で問題ない(処理結果を端末で利用しない)場合は、
google.script.run.** (に.gsファイル内で記述した関数名を入れる) と記述することが出来ますし、 同期処理が必要な場合は、 google.script.run.withSuccessHandler(#####).***
と記述して、サーバーでの処理結果を#####で記述したjavascriptの関数に引数として渡すことが出来ます。
同期処理/非同期処理で悩んだ場合は、サーバーでの処理結果を利用したいかどうかで決めたらいいってことです。
分からなければすべて同期処理で書いてもOKですね。
スプレッドシートのデータを利用したGASを作成した場合、利用開始時にアクセス権限の確認が求められますので、各図にある赤枠内を選択していってください。
アカウントの選択で複数のアカウントが表示される場合は、今回のスプレッドシートを利用しているアカウントを選択してください。
パッと見、[安全なページに戻る]しか選択できないように見えますが、[詳細]を押すと以下のような画面になりますので、“******（安全ではないページ）に移動”を選択してください。
初回の作業はこれで完了です。
そうすると結果が表示されます。
読み込みはうまくできたので、書き込みを行ってみます。
上図のように２カ所記入し、[書き込み]ボタンを押します。
すると、スプレッドシート側の指定したシート(今回は“シート2”)に書き込まれ、以下のようになります。
ちゃんと“シート2”に書き込まれていることが分かります。
1行目2列目のB1セルには、Web側で書き込んだ値が入り、
2行目1列目のA2セルには、数式として認識されA1セルと同じ値が表示されます。（上図にあるように、実際にはWebでの入力のとおり“=A1”の値となっています。）
※今回の場合あらかじめ、シート2を作成していないとエラーになりますが、エラー処理を組み込んでいない＆非同期処理で実施しているのでエラーの表示などは一切されません。
エラーの表示なども行いたい場合は、try～catchを入れ、同期処理でエラー結果を戻すか、同期処理で再度読み込みを行い、読み込み結果を比較するなどの処理が必要になります。
上記記載のコードのまま実行すれば一旦実行できますが、この先いろいろと修正したい場合もあるかもしれません。
本来のWebアプリの環境ではサーバー側の処理、クライアント側の処理双方ともに書き換えた瞬間に反映されますが、GASの場合は即時反映とはならず、更新処理をしてやる必要があります。
更新は以下の図にあるように、Project Versionの欄で“New”を選択して、[更新]をクリックします。
この処理をして、ようやくWebアプリが更新されることになります。
非常に軽微な修正だけならすぐに更新しても問題なく動作すると思いますが、本来であれば正常に動作するかどうかをデバッグをする必要があります。
その場合は、下図にあるようにDeploy as web appの欄にある、”latest code”のリンクをクリックすると、テストして結果を確認することができます。
Webアプリでスプレッドシートの値はこれで表示できるようになったはずです。
この先は、私がスプレッドシートの値を表示していくうえで、困ったことなどを記載していこうと思っています。
Follow me!
お世話になります。小生がほしがっていたものが見つかったと喜んでおりましたが、GASの実行結果が失敗でした。アドバイスを頂ければ幸いです。
spreadsheetからのデータ読込は成功です。書き込みができませんでした。
コード.gs
コード26では貴方のシート名を「シート3」から「シート2」に変更しました。
コード2 ではspreadsheetのＩＤを自分のファイルのIDに変更しております。
非同期処理では、どうすべきか良くわかりませんでしたので、貴方が記載したコード
をそのままコピ－して張り付けました。
webアプリではデータを入力後、ボタンをクリックして、spredsheetのシート2を見ても何も入力されておりません。宜しくお願いいたします。
GASのSpreadsheetの書き込み処理に関しては、setValueとsetValuesの２種類が用意されています。
setValueは”１セルのみの書き込み”。setValuesは”配列の書き込み”と処理が分かれています。
ですので、データを配列で渡さずに１つのデータを書き込もうとした場合に、setValuesを利用した際にはエラーが発生してしまいます。
また、配列サイズと書き込む先として指定しているgetRangeの範囲が異なる場合もエラーが発生します。
コメントを書き込んで下さった方がどのような実装をされておられるのか分かりませんが、もし１つのデータを書き込む目的でsetValuesを使っておられるようでしたら、
sheet.getRange(1, 1, data.length, data[0].length).setValues(data);と書かれているところを、
sheet.getRange(行番号, 列番号).setValue(data);のように書かれると良いかもしれません。
※次のように書いた場合A列の最終行の次行へ追加することになります
sheet.getRange(sheet.getLastRow() + 1, 1).setValues(data);
また、私が書いたコードは非同期処理でのコードとなっています。
これでも解決しませんでしたら、またコメントしていただけたらと思います。
hr様
ご回答いただきまして大変有難うございました。お礼申し上げます。
残念ながら小生の言いたい内容が正確にお伝え出来なかった為、ご迷惑をおかけすることとなりました。もう少し小生のやりたいことを詳細にお伝えするために添付ファイルなどを交えて説明させて頂けたらと思います。ご迷惑でなければemail で質問詳細をお送りさせて頂けたらと思いますがいかがでしょうか。
宜しくお願い致します。
宇野様
当サイト上部にも表示させていただいておりますメールアドレス、
contact@digita-l-ocal.tech
へ送信していただけますでしょうか？
なお、メールの確認が遅くなることもあることはご了承ください。
宜しくお願い致します。
hr様
お世話になります。
本日再度小生が記載したコードを見直しました。
小生の勘違いがあったことが分かりました。
コードを修正して、貴方のコードの通り書き換えて実行したところ、読み取り、書き込み共成功していることを確認しました。
書き込み処理の部分で、var tableData = document.getElementById(Ａ）
のＡの部分を、勘違いしてspreadsheet IDを記載したことがミスでした。
大変ご迷惑を御かけしました。
ほんとにありがとうございました。
追記
貴ホームページの内容に対して、質問を受け付けていただけきましたが
別の内容（webアプリの内容）に対しても受け付けていただけるのでしょうか。可能であれば、もう少し調べた後でうまく行かなければ質問させて頂ければ幸いです。
宇野様
質問していただくのは構いませんが、
①当方の知識もかなり偏っておりますので、知識不足のためお答えできない場合もあります。
②時間的な問題でお答えできない場合や、回答が非常に遅くなる場合があります。
③内容の如何によっては、当サイトでテーマとして取り上げさせていただくことがあるかもしれません。
④回答の内容により不利益を被られることがあっても、当方で対応することはできません。
以上のことをご承知の上でしたら、メールにてお寄せください。
hr様
大変お世話になります。 貴方が作成したwebアプリを実行して読込みの場合では問題はみつかりませんでした。書き込みを行うと、データ数が少ない（列13列、行10行程度）場合では、問題なく書き込みできました。
ところが列数を13列で行数を30行にふやして書き込みを行った場合、元の表示に対して書き込みは行えたのですが、元データの一部が修正されていました。（小生が修正していない部分が修正されていた）
異常な部分の内容は、2行目の年月データが12行目の一部の列に転記されていたり、22行目の一部の列が空白になっておりました。
尚テストは13列目の３セル分に対して書き込みを行ってテストしました。
解決策がもしあればご教示頂ければ大変有難く思います。
t.uno
t.uno様
※長文となりますことご承知おきください
投稿していただいた記述からどのような処理を記載(コーディングを)しておられるのか分かりかねますので確定したことは言えませんが、
このような場合、まずは原因の切り分けのためにログを残して、
ログの内容をチェックされることをおすすめ致します。
今回の場合ですと、
私の方でサンプルとして紹介させていただいたコードのうち、
サーバー側の処理(.gsファイル)
内に含まれる、
function SetSpreadSheet(data){
の直後に、
for(var i=0;i
メールにて回答させていただきました。
もし届いていませんでしたら、迷惑メールフォルダ等ご確認ください。
迷惑メールフォルダにも届いていませんでしたら、再度ご連絡いただけましたらと思います。
hr様
大変お世話になっています。早速のご回答を頂きありがとうございました。
早速頂きました改良版を試してみました。大成功でした。大感激しました。
重ねてお礼申し上げます。
t.uno
hr様
Google formsで作ったスプレッドシートが読み込めませんでした。
解決策はありますか？
tomutomu様
コメントを下さったのに、長期間チェックできておらず大変失礼致しました。
Google formsにつきましては、私もこれまで触ったことがないので、解決策をご案内することは難しいのですが、
逆説的にWebアプリの公開設定をした後でGoogle formsの設定をしてみられるのはいかがでしょうか？
見当違いでしたら申し訳ございません。
hr