2019年11月17日日曜日

スプレッドシートのurlとnameをHTMLServiceでセレクトボックスのオプションに保存したい（機能追加）


スプレッドシートの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>