How to save content edited on an html page to a Google sheet-CodePudding

I currently have code that pulls all information from a google sheet and displays it as an editable html in a modal dialogue box.

However, I am trying to find a way so that when I make the edits in one or multiple of the cells in the box, that it would write to the google sheet. However, I am lost on how I would begin to save the edited content and then overwrite the google sheet content with the new information. Any advice on the direction I should look would be greatly appreciated.

code.GS

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Jigs & Tools')
      .addItem('First item', 'displayHtmlViewer')
      .addToUi();
}

function include(filename) {
 return HtmlService.createHtmlOutputFromFile(filename)
        .getContent();
}

function displayHtmlViewer(file = "dashboard"){
  var html = HtmlService.createTemplateFromFile(file)
            .evaluate()
            .setHeight(2000)
            .setWidth(2000);
 SpreadsheetApp.getUi().showModalDialog(html, 'J&T Dashboard');
}

function getData1(){
  var ss=SpreadsheetApp.getActive();
  var rg=ss.getDataRange();
  var vA=rg.getValues();
  var html='<style>td,th{border:1px solid #111;}</style><table>';
  if(vA.length>0){
    for(var i=0;i<vA.length;i  ){
      html ='<tr>';
      for(var j=0;j<vA[i].length;j  ){
        if(i==0){
          html =Utilities.formatString('<th>%s</th>', vA[i][j]);
        }else{
          html =Utilities.formatString('<td>%s</td>', vA[i][j]);
        }
      }
    }
    html ='<table>';
  }
  Logger.log(html)
  return html;
}

dashboard.html

<html>
  <head>
    <base target="_top">
    <?!=include('CSS');?>
    <?!= include('res1') ?>

  </head>
  <script>
    function getData() {
      var rows = google.script.run.useDataRange();
      alert(rows);
    }
  </script>
   <body style="background-color:powderblue;">
      <div class="btnGroup1" style="width:100%">
        <button style="width:33%" button onclick="google.script.run.getLengthSS()">Filter Views</button>
        <button style="width:33%">Edit Filter Views</button>
        <button style="width:33%">Options</button>
      </div>
<div class="divider"/> </div>
      <div contenteditable><div id="table"></div></div>
    <?!= include('script1') ?>

      
  </body>
</html>

script1

<script>
  $(function(){
    google.script.run
    .withSuccessHandler(function(hl){
      document.getElementById('table').innerHTML=hl;
    })
    .getData1();
  });
  console.log('My Code');
</script>

res1

<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
<link rel="stylesheet" href="//code.jquery.com/ui/1.12.1/themes/base/jquery-ui.css">
<script src="https://code.jquery.com/ui/1.12.1/jquery-ui.js"></script>

CodePudding user response：

I believe your goal as follows.

You want to update the Spreadsheet when the HTML table is updated.

In this case, how about the following modification?

Modified script:

HTML and Javascript side:

And, please modify dashboard.html as follows. id is added like <div contenteditable id="sample">.

<html>
  <head>
    <base target="_top">
    <?!=include('CSS');?>
    <?!= include('res1') ?>

  </head>
  <script>
    function getData() {
      google.script.run.withSuccessHandler(rows => alert(rows)).useDataRange();
    }

  </script>
   <body style="background-color:powderblue;">
      <div class="btnGroup1" style="width:100%">
        <button style="width:33%" button onclick="google.script.run.getLengthSS()">Filter Views</button>
        <button style="width:33%">Edit Filter Views</button>
        <button style="width:33%">Options</button>
      </div>
<div class="divider"/> </div>
      <div contenteditable id="sample"><div id="table"></div></div>
    <?!= include('script1') ?>

      
  </body>
</html>

Please modify script1 as follows. Added eventListener. By this, when the HTML table is edited and the focus is out, the script for updating Spreadsheet is run.

<script>
  document.getElementById("sample").addEventListener("blur",function(){
    const values = [...document.getElementById("sampletable").rows].map(r => [...r.cells].map(c => c.innerText));
    google.script.run.setValues(values);
  });

  $(function(){
    google.script.run
    .withSuccessHandler(function(hl){
      document.getElementById('table').innerHTML=hl;

    })
    .getData1();
  });
  console.log('My Code');
</script>

Google Apps Script side:

In this case, please modify the function of getData1 as follows. And, please add new function setValues for updating the Spreadsheet as follows. I added new function for updating Spreadsheet. And I added id to the table. And I thought that html ='<table>'; might be html ='</table>';.

function setValues(values) {
  var ss = SpreadsheetApp.getActive();
  ss.getDataRange().setValues(values);
}

function getData1() {
  var ss = SpreadsheetApp.getActive();
  var rg = ss.getDataRange();
  var vA = rg.getValues();
  var html = '<style>td,th{border:1px solid #111;}</style><table id="sampletable">';
  if (vA.length > 0) {
    for (var i = 0; i < vA.length; i  ) {
      html  = '<tr>';
      for (var j = 0; j < vA[i].length; j  ) {
        if (i == 0) {
          html  = Utilities.formatString('<th>%s</th>', vA[i][j]);
        } else {
          html  = Utilities.formatString('<td>%s</td>', vA[i][j]);
        }
      }
    }
    html  = '</table>';
  }
  Logger.log(html)
  return html;
}

Note:

In order to run the script for updating Spreadsheet, input event can be also used instead of blur. But, when input is used, even when only one character is edited in the cell, the script is run. So I used blur. In this case, when the cells are edited and the focus is out from the table, the script for updating Spreadsheet is run.
By the way, in your dashboard.html, although getData() is not used, google.script.run doesn't return directly the value. Please be careful this. If you want to retrieve the values from Google Apps Script side, please use withSuccessHandler like your script script1.