Web app - Google sheets - html table with input field

Question

We have a requirement to track and audit production, essentially we have lots of orders of which we seem to be loosing some of our products on the way (spoils etc).

To stop this we have now placed orders on to google sheets and listed a quantity of how many there should be, an employee then would write in how many was recieved.

On the web app I want to show the table and have a field where the employee can enter the count,

So if I had a google sheet I might have in column A [Product Name] column b [Start Quanity] and in column C [Counted Quantity]

On the web app it will show all 3 columns but allow the user to enter the count into the [Counted Quantity] column.

Is this possible? (Assuming I either need a input field within the html table or an editable table?

Thanks

This is possible but you'll need a reasonably strong understanding of HTML and Javascript in order to build it, essentially you need to build the form side functionality from scratch, publish it from an Apps Script, and use Javascript to move data back into the sheet. — Cameron Roberts
– Cameron Roberts, Commented Oct 30, 2019 at 17:57

Cooper · Accepted Answer · 2019-10-30 20:47:19Z

This will get all of the data in your spread but you can only edit the last column. It's built as a dialog but one could easily change that to a webapp.

I had some problems with scopes so I thought you might need them. If you don't know what do to with these then skip them and come back if you have a problem.

"oauthScopes": ["https://www.googleapis.com/auth/drive", "https://www.googleapis.com/auth/script.external_request", "https://www.googleapis.com/auth/spreadsheets","https://www.googleapis.com/auth/script.container.ui"]

ordercounts.gs:

function getOrders() {
  var ss=SpreadsheetApp.getActive();
  var osh=ss.getSheetByName("Orders");
  var org=osh.getDataRange();
  var vA=org.getValues();
  var rObj={};
  var html='<style>th,td{border: 1px solid black}</style><table>';
  vA.forEach(function(r,i){
    html+='<tr>';
    r.forEach(function(c,j){
      if(i==0){
        html+=Utilities.formatString('<th>%s</th>',c);
      }else if(j<r.length-1){
        html+=Utilities.formatString('<td>%s</td>', c);
      }else{
        html+='<td id="cell' + i + j + '">' + '<input id="txt' + i + j + '" type="text" value="' + c + '" size="20" onChange="updateSS(' + i + ',' + j + ');" />' + '</th>';
      }
    });
    html+='</tr>';
  });
  html+='</table>';
  return html;
}

function updateSpreadsheet(updObj) {
  var i=updObj.rowIndex;
  var j=updObj.colIndex;
  var value=updObj.value;
  var ss=SpreadsheetApp.getActive();
  var sht=ss.getSheetByName("Orders");
  var rng=sht.getDataRange();
  var rngA=rng.getValues();
  rngA[i][j]=value;
  rng.setValues(rngA);
  var data = {'message':'Cell[' + Number(i + 1) + '][' + Number(j + 1) + '] Has been updated', 'ridx': i, 'cidx': j};
  return data;
}

function launchOrdersDialog(){
 var userInterface=HtmlService.createHtmlOutputFromFile('orders');
 SpreadsheetApp.getUi().showModelessDialog(userInterface, "OrderCounts");
}

orders.html:

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
    <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>
  <script>
    $(function(){
      google.script.run
      .withSuccessHandler(function(hl){
        console.log('Getting a Return');
        $('#orders').html(hl);
      })
      .getOrders();

    });
    function updateSS(i,j) {
      var str='#txt' + String(i) + String(j);
      var value=$(str).val();
      var updObj={rowIndex:i,colIndex:j,value:value};
      $(str).css('background-color','#ffff00');
      google.script.run
      .withSuccessHandler(updateSheet)
      .updateSpreadsheet(updObj);
    }

    function updateSheet(data) {
      //$('#success').text(data.message);
      $('#txt' + data.ridx + data.cidx).css('background-color','#ffffff');
    }
    console.log('My Code');
  </script>
  </head>
  <body>
    <div id="orders"></div>
  </body>
</html>

Here's what the tool looks like:

All you have to do is enter the updated count and hit enter and the spreadsheet changes.

Collectives™ on Stack Overflow

Web app - Google sheets - html table with input field

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related