シート切り替えとデータ形式（配列orオブジェクト）の選択に対応
以前、こんな感じでブラウザでGoogleスプレッドシート上のデータを読み書きするプログラムを書きました。
-
-
GoogleスプレッドシートをWebアプリのデータベース代わりに使う
GoogleスプレッドシートをローカルWebアプリのデータ保存先として使えるようにしました
続きを見る
この時は、スプレッドシートの1行目のデータ（項目名）をキーとしたオブジェクトの配列をブラウザから取得したり、逆に書き込んだりできるようにしました。
その後、この仕組みを使ったアプリケーションを作っているときに「場合によっては2次元配列の方が便利な時がある」と気づきました。
例えば、スプレッドシートの項目名には日本語を使いたいときが多いですが、Javascriptのオブジェクトのキーが日本語というのはちょっと危ない気がします。
そこでプログラムを少し変更して、ブラウザに転送するデータを配列にするか、オブジェクトにするかを選択できるようにしました。
ブラウザ側で配列とオブジェクトを変換してもよいのですが、今回はサーバー側で行うようにしました。
また、Googleスプレッドシートは複数のシート（タブ）を持つことができます。
今回のアプリケーションではシートのリストも取得してプルダウンメニューに格納するようにしました。
メニューから任意のシート名を選択すると、そのシートのデータが表示されるようになっています。
ついでにドキュメント名も取得して表示しました。
前回の開発でブラウザとサーバーのやりとりに関する基本構造はできていたのでそれほど苦労せずに機能を拡張できました。
GETリクエストのパラメーターで取り出す情報を変える
やっていることは前回と同じなのですが、おさらいしてみます。
サーバー側に欲しい情報の種類を伝えるには、GETリクエストのパラメーターを使っています。
GETリクエストはjQueryの$.ajaxを使っています。
//ブラウザ側
$.ajax({
type: "GET",
url: END_POINT, //GAS WebアプリのURL
data: { sheetNo: sheet_Number, dataFormat: data_Format }
}).done((result) => { // 成功した時の処理
}).fail((error) => { // 失敗した時の処理
}).always((data) => {// 常にやる処理
});
//GoogleスプレッドシートのGAS
function doGet(e) {
const reqParam = e.parameter;
const dataFormat = reqParam.dataFormat || "arr";
const sheetNo = parseInt(reqParam.sheetNo) || 0;
if (sheetNo == 0) {
....以下つづく
sheetNoパラメーターを省略するか0にすると、サーバー（GAS）はドキュメント名とシート名のリスト（配列）を返してきます。
GASでドキュメント名とシート名を取得するには以下のようなメソッドを使います。
var ss = SpreadsheetApp.getActiveSpreadsheet(); var docName = ss.getName(); var sheetNames = ss.getSheets().map(sheet => sheet.getName()); //シート名が入った配列
sheetNoが1以上の時は、その数字に合わせたシートのデータを2次元配列かオブジェクトで返します。
ラジオボタンはformにしておくと値の取得が楽だった
データの形式はdataFormatパラメーターに"arr"と書いてあれば２次元配列で帰ってきます。
それ以外だとオブジェクトで帰ってきます。一応、"obj"という文字列を渡しています。
パラメーターで使う値はその都度HTMLのラジオボタン要素のvalueから取得しています。
グループ化されたラジオボタンの中で、どれが選ばれているかを取得するには、ループでひとつずつラジオボタンの状態をチェックするのかと思いました。
しかし、formタグで囲っておくと簡単に参照できるんですね。
{form要素}.{ラジオボタンのname属性}.value
//HTML
<form id="data_format_selector">
<input type="radio" name="data_format" value="arr" checked="checked" >配列
<input type="radio" name="data_format" value="obj" >オブジェクト
</form>
//Javascript
dataFormatSelector = document.getElementById("data_format_selector");
data_Format = dataFormatSelector.data_format.value; // name属性のvalueで選択されているボタンの値を取り出せる
さ
サーバーから2次元配列を返すのは簡単です。
sheet.getDataRange().getValues();を使うとスプレッドシートのデータを2次元配列として取り出せるので、それをそのままJSON.stringifyして戻すだけです。
var ss = SpreadsheetApp.getActiveSpreadsheet(); sheet = ss.getSheets()[sheetNo - 1]; const dataArray = sheet.getDataRange().getValues(); return ContentService.createTextOutput(JSON.stringify(dataArray));
シート名のプルダウンメニューはselect要素にoption要素をappendChildで追加する
プルダウンメニューにシート名とvalue値を設定するのは、サーバーから取得したシート名の配列をforEachで回してappendChildしました。
//html
<select id="selectSheet" class="controlElement"></select>
//javascript
const sheetSelector = document.getElementById("selectSheet");
$.ajax({
type: "GET",
url: END_POINT,
}).done((result) => {
setSheetNames(JSON.parse(result).sheetNames);
..........以下略
function setSheetNames(sheetNamesArray) {
sheetSelector.innerHTML = "";
document.createElement('option')
sheetNamesArray.forEach((sheetName, index) => {
let option = document.createElement('option');
option.setAttribute('value', index + 1);
option.innerHTML = sheetName;
sheetSelector.appendChild(option);
});
}
メニューやボタンにはchangeイベントに読み込み処理をセットして、選択と同時に情報を更新するようにしています。
これは使い勝手を良くするためというよりも、POST機能を使うときにボタンなどの設定とテキストエリア内の情報に食い違いがでないようにするためです。
サーバーへアクセスしている最中はコントロールを触れないように不活性化しています。
全ソースを載せておきます。お使いになるときは、ブラウザ側のスクリプトでEND_POINTという定数にGAS WebアプリのURLをセットしてください。
ブラウザ側のJavascript（get_pot_gas.js）とHTML（index.html）
document.addEventListener('DOMContentLoaded', function () {
const END_POINT = "https://script.google.com/macros/s/*************************************/exec";
//スプレッドシート（タブ）の番号
const SHEET_NO = 1;
document.getElementById("getSheetNames").addEventListener('click', getSheetNames, false);
document.getElementById("postButton").addEventListener('click', postToGAS, false);
document.getElementById("getButton").addEventListener('click', getFromGAS, false);
let dataObj = {};
const sheetSelector = document.getElementById("selectSheet");
const msgBox = document.getElementById("textArea");
const wrapper = document.getElementById("wrapper");
//データ形式選択ラジオボタンの設定
dataFormatSelector = document.getElementById("data_format_selector");
document.getElementsByName("data_format").forEach((radioBtn) => {
radioBtn.addEventListener("change", (e) => {
getFromGAS();
});
});
//シート選択プルダウンメニューの設定
sheetSelector.addEventListener('change', function () {
getFromGAS(this.value);
});
getSheetNames();//起動時のデータ取得
//----------- 以下、関数定義--------------------------
function getSheetNames() {
waitForResponce("Loading Sheet List...")
$.ajax({
type: "GET",
url: END_POINT,
// data: { sheetNo: SHEET_NO }
}).done((result) => { // 成功した時の処理
console.log("get done:" + result);
setSheetNames(JSON.parse(result).sheetNames);
document.getElementById("docName").textContent = "ドキュメント：" + JSON.parse(result).docName;
getFromGAS(1);
}).fail((error) => { // 失敗した時の処理
alert('Error:' + JSON.stringify(error));
}).always((data) => {// 常にやる処理
// do something
enableControlElements();
});
}
function setSheetNames(sheetNamesArray) {
sheetSelector.innerHTML = "";
document.createElement('option')
sheetNamesArray.forEach((sheetName, index) => {
let option = document.createElement('option');
option.setAttribute('value', index + 1);
option.innerHTML = sheetName;
sheetSelector.appendChild(option);
});
}
function postToGAS(sheet_Number, data_Format, content) {
//引数を使う時はここをコメントアウト
sheet_Number = sheetSelector.value;
data_Format = dataFormatSelector.data_format.value;//ラジオボタンの状態を取得
content = msgBox.value.trim();
//-----ここまで
//データがJSONかどうかのチェック、うまく機能していないので無くてもいいかも
try {
const checkJSON = JSON.parse(content);
if (checkJSON.length > 0 && Object.keys(checkJSON).length > 0) {
console.log("data is OK");
} else {
throw "data is not array of object";
}
}
catch (e) {
alert("error:" + e);
return;
}
//POST送信
$.ajax({
type: "POST",
url: END_POINT,
dataType: "json",
data: { sheetNo: sheet_Number, dataFormat: data_Format, data: content }
})
.then(
(result) => { // 成功した時の処理
console.log(JSON.stringify(result));
},
(error) => { // 失敗した時の処理
alert('Error:' + JSON.stringify(error));
}
);
}
function getFromGAS(sheet_Number = 1, data_Format = "obj", msg = "Loading Data from Google Spread Sheet, Please wait...") {
waitForResponce(msg);
//引数を使う時はここをコメントアウト
sheet_Number = sheetSelector.value;
data_Format = dataFormatSelector.data_format.value;//ラジオボタンの状態を取得
//-----
$.ajax({
type: "GET",
url: END_POINT,
data: { sheetNo: sheet_Number, dataFormat: data_Format }
}).done((result) => { // 成功した時の処理
dataObj = JSON.parse(result);
console.log(dataObj);
if (!dataObj) throw "no correct data from google spread sheet";
msgBox.value = JSON.stringify(dataObj);
if (data_Format === "arr") {
console.log("dataObj[0]:" + dataObj[0]);
} else {
const keys = Object.keys(dataObj[0]);
console.log("Keys:" + keys);
}
}).fail((error) => { // 失敗した時の処理
alert('Error:' + JSON.stringify(error));
}).always((data) => {// 常にやる処理
// do something
enableControlElements();
});
}
//データ読み込み中にメッセージを表示してコントロールを操作不可にする
function waitForResponce(msg) {
msgBox.value = msg;
Array.from(document.getElementsByClassName("controlElement")).forEach((element) => {
element.disabled = true;
});
}
//コントロールの不活性を解除
function enableControlElements() {
Array.from(document.getElementsByClassName("controlElement")).forEach((element) => {
element.disabled = false;
});
}
document.getElementById("testBtn").addEventListener("click", () => {
//チェック・デバッグ用のコードをここに書く
});
});
<!DOCTYPE html>
<html lang="ja">
<head>
<meta charset="UTF-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Get/Post with Google SpreadSheet</title>
</head>
<body>
<p>GET and POST <br>
to Google SpreadSheet</p>
<div id="wrapper">
<p id="docName"></p>
<select id="selectSheet" class="controlElement"></select>
<button id="getSheetNames" class="controlElement">シート名を取得</button>
<p>
<form id="data_format_selector" class="controlElement">
取得形式：<input type="radio" name="data_format" value="arr" checked="checked" class="controlElement">配列
<input type="radio" name="data_format" value="obj" class="controlElement">オブジェクト
</form>
</p>
<textarea id="textArea" rows="5" cols="40" class="controlElement"></textarea>
<br>
<button id="postButton" class="controlElement">POST</button>
<button id="getButton" class="controlElement">GET</button>
<button id="testBtn" class="controlElement">TEST</button>
</div>
<script src="https://code.jquery.com/jquery-3.5.1.min.js"
integrity="sha256-9/aliU8dGd2tb6OSsuzixeV4y/faTqgFtohetphbbj0=" crossorigin="anonymous"></script>
<script src="./get_post_gss.js"></script>
</body>
</html>
var ss = SpreadsheetApp.getActiveSpreadsheet();
function doGet(e) {
const reqParam = e.parameter;
const dataFormat = reqParam.dataFormat || "arr";
const sheetNo = parseInt(reqParam.sheetNo) || 0;
if (sheetNo == 0) {
const objToSend = {};
objToSend.docName = ss.getName();
objToSend.sheetNames = ss.getSheets().map(sheet => sheet.getName());
return ContentService.createTextOutput(JSON.stringify(objToSend));//ドキュメント名とシート名のリストを配列で返す
}
sheet = ss.getSheets()[sheetNo - 1];
const dataArray = sheet.getDataRange().getValues();
//配列を返すとき
if (dataFormat === "arr") return ContentService.createTextOutput(JSON.stringify(dataArray));
//オブジェクトを返すとき
const keys = dataArray[0];//配列の先頭をオブジェクトのキーとして取り出す
dataArray.shift();
const dataObj = dataArray.map((array) => {
const temp = {};
keys.map((key, index) => {
temp[key] = array[index];
});
return temp;
});
const dataObj2 = {};
keys.forEach((key, index) => {
dataObj[key] = dataArray.map((arr) => {
return arr[index];
});
});
return ContentService.createTextOutput(JSON.stringify(dataObj));
}
function doPost(e) {
const reqParam = e.parameter;
const sheetNo = parseInt(reqParam.sheetNo) || 1;
sheet = ss.getSheets()[sheetNo - 1];
var data = JSON.parse(reqParam.data);
const dataFormat = reqParam.dataFormat || "obj";
postJsonToSpreadSheet(data, dataFormat);
return ContentService.createTextOutput(JSON.stringify({ result: "post done" })); //JSONを返すとエラーにならない？
}
function postJsonToSpreadSheet(arrObj, dataFormat) {
//受け付けるJSONは、[{key1:data1, key2:data2,....},...]、オブジェクトが配列になっている形式
//オブジェクトのキーがスプレッドシートの項目名として1行目に入力される
sheet.clear();
//渡されたデータが2次元配列なら単純に書き込み
if (dataFormat === "arr") {
const lastColumn = arrObj[0].length; //1個め配列の長さ＝カラムの数を取得する
const lastRow = arrObj.length; //行の数を取得する
sheet.getRange(1, 1, lastRow, lastColumn).setValues(arrObj);
return; Î
};
//渡されたデータがオブジェクトの場合、まずタイトル行を書き込み
const keys = [Object.keys(arrObj[0])];//setValuesには必ず2次元配列を渡すのでこうなっている
sheet.getRange(1, 1, 1, keys[0].length).setValues(keys);//項目名を書き込み
//オブジェクトからデータ書き込み用の2次元配列を作成
const arrToWrite = arrObj.map((obj) => {
const arr = [];
for (const key of keys[0]) { arr.push(obj[key]); }
return arr;
});
const lastColumn = arrToWrite[0].length; //1個め配列の長さ＝カラムの数を取得する
const lastRow = arrToWrite.length; //行の数を取得する
sheet.getRange(2, 1, lastRow, lastColumn).setValues(arrToWrite);
}
セルのデータだけでなく、ドキュメント名やシート名も取得できるようになったので、かなり汎用性が高くなりました。
safariだと動かない！
私はデフォルトブラウザをChromeにしています。
で、試しにちょっとsafariで動かしてみたら見事にエラーがでて動きませんでした。
コンソールを見ると最初にシートのリストを取得するところですでにエラーになってしまいます。
firefoxでは動きました。
アマチュアの強みで私はChromeで動けばそれでいいのですが、なんでかなー