スプレッドシートのurlとnameをHTMLServiceでセレクトボックスのオプションに保存したい
で作った仕組みの機能追加です。
追加する機能
- saveしたスプレッドシートをselectのoptionから自動で選択する
- removeボタンを追加して、選択されているスプレッドシートを選択肢から削除する
- スクリプトのプロパティからも削除する
コード.gs
function doGet() {
return HtmlService.createHtmlOutputFromFile("index");
}
function saveSpreadsheetURL(new_ss_url) {
var ss = SpreadsheetApp.openByUrl(new_ss_url);
var ss_name = ss.getName();
var ss_urls = getProp("ss_urls");
if(ss_urls === null) {
var obj = {}// nullならオブジェクトを作る（初回のみ）
} else {
var obj = JSON.parse(ss_urls);
}
obj[new_ss_url] = ss_name;
var json = JSON.stringify(obj);
setProp("ss_urls", json);
setProp("current_ss_url", new_ss_url);
var keys = Object.keys(obj);
setProp("current_ss_index", keys.length-1);
return json;
}
function returnCurrentSSObj() {
var obj = {}
obj["url"] = getProp("current_ss_url");
obj["index"] = getProp("current_ss_index");
return obj;
}
function updateRemoveSSJson(json) {
var obj = JSON.parse(json);
var keys = Object.keys(obj);
Logger.log(keys)
setProp("ss_urls", json);
setProp("current_ss_url", keys[0]);
setProp("current_ss_index", 0);
return json;
}
function getProp(key) {
return PropertiesService.getScriptProperties().getProperty(key);
}
function setProp(key, value) {
PropertiesService.getScriptProperties().setProperty(key, value);
}
|
index.html
<!DOCTYPE html>
<html>
<body>
<select id="ss_select">
</select>
<input type="text" id="new_ss_url">
<button id="save">save</button>
<a id="open_sheet" target="_blank">open sheet</a>
<button id="remove_ss">remove</button>
<textarea id="ss_json_ta"></textarea>
<script>
ssUrlsLoad();
function ssUrlsLoad() {
google.script.run
.withSuccessHandler(ssUrlsLoaded)
.getProp("ss_urls");
}
elem("save").onclick = saveClicked;
elem("ss_select").onchange = selectChanged;
elem("remove_ss").onclick = removeClicked;
function elem(id) {
return document.getElementById(id);
}
function ssUrlsLoaded(ssUrlsJSON) {
if(ssUrlsJSON) {
elem("ss_json_ta").value = ssUrlsJSON;
createSSList(ssUrlsJSON);
}
}
function currentSSObjLoaded(obj) {
if(obj) {
elem("ss_select").selectedIndex = obj["index"];
elem("new_ss_url").value = obj["url"];
elem("open_sheet").href = obj["url"];
}
}
function saveClicked() {
var new_ss_url = elem("new_ss_url").value.split("#gid=")[0]// + "#gid=0";
google.script.run
.withFailureHandler(onFailure)
.withSuccessHandler(ssSaved)
.saveSpreadsheetURL(new_ss_url);
}
function ssSaved(ssListJSON) {
console.log(ssListJSON);
createSSList(ssListJSON);
}
function createSSList(ssListJSON) {
var parentId = "ss_select";
removeChilds(parentId);
var obj = JSON.parse(ssListJSON);
var keys = Object.keys(obj);
var ss_select = elem(parentId);
for(var i = 0; i < keys.length; i++) {
var option = document.createElement("option");
option.id = keys[i];
option.name = obj[keys[i]];
option.innerHTML = obj[keys[i]];
ss_select.appendChild(option);
}
google.script.run.withSuccessHandler(currentSSObjLoaded).returnCurrentSSObj();
elem("ss_json_ta").value = ssListJSON;
}
function selectChanged() {
var select = elem("ss_select");
var index = select.selectedIndex;
var ssUrl = select[index].id;
elem("new_ss_url").value = ssUrl;
elem("open_sheet").href = ssUrl;
var index = select.selectedIndex;
sendCurrentSS(index, ssUrl);
}
function sendCurrentSS(index, ssUrl) {
google.script.run.setProp("current_ss_index", index);
google.script.run.setProp("current_ss_url", ssUrl);
}
function removeClicked() {
var ss_json = elem("ss_json_ta").value;
var obj = JSON.parse(ss_json);
var current_ss_url = elem("new_ss_url").value;
delete obj[current_ss_url];
ss_json = JSON.stringify(obj)
elem("ss_json_ta").value = ss_json;
google.script.run
.withFailureHandler(onFailure)
.withSuccessHandler(ssJsonUpdated)
.updateRemoveSSJson(ss_json);
}
function ssJsonUpdated(ssListJSON) {
var current_ss_url = elem("new_ss_url").value;
createSSList(ssListJSON);
}
function removeChilds(id) {
var target = elem(id);
for (var i = target.childNodes.length - 1; i >= 0; i--) {
target.removeChild(target.childNodes[i]);
}
}
function onFailure(e) {
alert([e.message, e.stack]);
}
</script>
</body>
</html>
|
