GAS PropertiesService to Save and Return Sort Orde

2019-08-20 17:47发布

站内问答 / 前沿技术
970 2 6

QUESTION

How can I use PropertiesService to store an array from index.html, send the array to code.gs, and return the array in index.html?

SPECIFIC CASE

In a Google Web App, I have a group of sortable lists (made using JQuery UI Sortable). I want to save the most recent order/position of each li. I'm attempting to have that order/position "persist" when the page is refreshed or closed.

EXAMPLE

If you see the default Sortable, you could change the order of the items. If you refreshed the page, or closed it and return, the items would be in their original order.

WHERE I'M HAVING TROUBLE

I am able to get the array to show up in the console, but I don't know how to get it back to code.gs. I think I am now, but I'm not sure. Beyond that, I don't know how to "read" that PropertiesService so that the array is returned to index.html. I'm not really sure what I'm doing so if someone could slow walk me it would be appreciated!

ALTERNATIVES

I also looked into writing directly to the spreadsheet where the values originate. I'm not really sure how to do that either. I made some attempts, and was able to get "undefined" as a value in a spreadsheet cell.

FULL CODE (note: the list items are formed using an array, so they will not show up here): https://jsfiddle.net/nateomardavis/Lmcjzho2/1/

PARTIAL CODE

code.gs

function doGet() {
  return HtmlService.createHtmlOutputFromFile('index');
}

function webAppTest() {
  getTeamArray();
}

function getTeamArray() {

  var ss = SpreadsheetApp.getActive();
  var sheet = ss.getSheetByName('TEST');
  var range = sheet.getRange(2, 1, 1000, 1);
  var values = range.getValues();

  var teamsArray = [];

  for (var i = 0; i < values.length; ++i) {
    teamsArray.push(values[i][0]);
  }

  var uniqueArray = [];

  uniqueArray.push(teamsArray[0]);

  for (var i in teamsArray) {
    if ((uniqueArray[uniqueArray.length - 1] != teamsArray[i]) && (teamsArray[i] !== "")) {
      uniqueArray.push(teamsArray[i]);
    }
  }
  return uniqueArray;
}

function savePositions(myProperty, positions) {
  PropertiesService.getScriptProperties().setProperty("myProperty", JSON.stringify(positions));
};

function getPositions() {
  var returnedObj = PropertiesService.getScriptProperties()
};

index.html

  <body>



    <div id="myList" class="connectedSortable">MY LIST</div>

    <table id=table1>
      <div id="team1">
        <p>TEAM 1</p>
        <br>
        <div id="group" v>SELECTED</div>
        <ul id="team1s" name='team1s' class="connectedSortable"></ul>
        <div id="group">ALTERNATE</div>
        <ul id="team1a" name='team1a' class="connectedSortable"></ul>
      </div>
    </table>

    <table id=table2>
      <div id="team2">
        <p>TEAM 2</p>
        <br>
        <div id="group" v>SELECTED</div>
        <ul id="team2s" name='team2s' class="connectedSortable"></ul>
        <div id="group">ALTERNATE</div>
        <ul id="team2a" name='team2a' class="connectedSortable"></ul>
      </div>
    </table>

    <table id=table3>
      <div id="team3">
        <p>TEAM 3</p>
        <br>
        <div id="group" v>SELECTED</div>
        <ul id="team3s" name='team3s' class="connectedSortable"></ul>
        <div id="group">ALTERNATE</div>
        <ul id="team3a" name='team3a' class="connectedSortable"></ul>
      </div>
    </table>

    <table id=table4>
      <div id="team4">
        <p>TEAM 4</p>
        <br>
        <div id="group" v>SELECTED</div>
        <ul id="team4s" name='team4s' class="connectedSortable"></ul>
        <div id="group">ALTERNATE</div>
        <ul id="team4a" name='team4a' class="connectedSortable"></ul>
      </div>
    </table>

    <script>
      $(function() {
        google.script.run.withSuccessHandler(buildOptionsList)
          .getTeamArray();
      });


      function buildOptionsList(uniqueArray) {
        var div = document.getElementById('myList');
        for (var i = 0; i < uniqueArray.length; i++) {
          var ul = document.createElement('ul');
          var li = document.createElement('li');
          var cLass = li.setAttribute('class', 'ui-state-default');
          var iD = li.setAttribute('id', uniqueArray[i]);


          li.appendChild(document.createTextNode(uniqueArray[i]));
          div.appendChild(ul);
          div.appendChild(li);
        }
      }

      $(function() {
        $("#myList, #team1s, #team1a, #team2s, #team2a, #team2s, #team3s, #team3a, #team4s, #team4a").sortable({
          connectWith: ".connectedSortable",
          update: function(event, ui) {
            var changedList = this.id;
            var order = $(this).sortable('toArray');
            var positions = order.join(';');

            console.log({
              id: changedList,
              positions: positions

            });

            //Instead of using JSON to save, can I use the spreadsheet itself to save the positions and then pull it from there as I did with "buildOptionsList" above?

            function saveList() {
              google.script.run.savePositions("myProperty", JSON.stringify(positions));

              JSON.parse("myProperty");
            }
          }
        })
      });


      $(function getPositions(event, ui) {
        var changedList = this.id;
        var order = $(this).sortable('toArray');
        var positions = order.join(';');

        console.log({
          id: changedList,
          positions: positions
        });

      });

    </script>
  </body>

</html>

2条回答
迷人小祖宗
2楼-- · 2019-08-20 18:10

It's also possible to just use the browser's localStorage client side.

localStorage.setItem('id', positions); //Store positions in users browser
localStorage.getItem('id'); //Retrieve the stored positions later

Notes:

  • For this to work, the url(document.domain of the iframe="*.googleusercontent.com") from which your script is deployed must remain constant. During my brief testing, it was constant even when changing from /dev to /exec of the parent(script.google.com) and even during version update. But there's no official reference.

  • This solution is better than properties service, if you have multiple users, as each one will have their own data stored in their own browsers and there are no server calls during each change.

查看更多
0人赞 添加讨论(0) 举报
Melony?
3楼-- · 2019-08-20 18:16

Using google.script.run simple example:

<script>
function sendStringToServer() {
  var string=$('#text1').val();
  google.script.run
  .withSuccessHandler(function(s){
    alert(s);
  })
  .saveString(string);
}
</script>

Google Script:

function myFunction() {
  PropertiesService.getScriptProperties().setProperty('MyString', string);
  return "String was saved in Service";
}
查看更多
0人赞 添加讨论(0) 举报
登录 后发表回答
相关问题
查看全部
相关文章
查看全部
收藏的人(6)