embedding interactive google spreadsheets api

Question

Using google spreadsheets api, is it possible to embed spreadsheet onto website that is editable without saving it on user's google drive?

For each user that logs in to our webservice, we want to retrieve the user's data from our database and show it to the user on spreadsheet. The user can edit, and upon confirming it, data on spreadsheet will be uploaded onto our database. During the process, we do not want the data to be saved on user's google drive, and the user should not be required to authorize access to their google drive. Is this possible with Google spreadsheet api?

Also, I am looking for some good examples that works like this but could not find any. Any suggestions? Thank you very much.

Cooper Cooper · Accepted Answer · 2017-07-21T04:57:00

Here's a simple example of creating and editable html version of your spreadsheet. You could deploy this as a web app if you wish. I developed it as a modeless dialog. To edit any cell just make the change and hit enter. The back ground with turn yellow and after the spreadsheet is updated the background color returns to white. There are no other controls.

I imagine that you could add many additional enhancements but this covers some of the basics including client to server and server back to client communications.

The google script code:

function htmlSpreadsheet()
{
  var br='<br />';
  var s='';
  var hdrRows=1;
  var ss=SpreadsheetApp.getActiveSpreadsheet();
  var sht=ss.getActiveSheet();
  var rng=sht.getDataRange();
  var rngA=rng.getValues();
  s+='<table width="100%">';
  for(var i=0;i<rngA.length;i++)
  {
    s+='<tr>';
    for(var j=0;j<rngA[i].length;j++)
    {
      if(i<hdrRows)
      {
        s+='<th id="cell' + i + j + '">' + '<input id="txt' + i + j + '" type="text" value="' + rngA[i][j] + '" size="8" onChange="updateSS(' + i + ',' + j + ');" />' + '</th>';
      } 
      else
      {
        s+='<td id="cell' + i + j + '">' + '<input id="txt' + i + j + '" type="text" value="' + rngA[i][j] + '" size="8" onChange="updateSS(' + i + ',' + j + ');" />' + '</th>';
      }
    }
    s+='</tr>';
  }
  s+='</table>';
  //s+='<div id="success"></div>';
  s+='</body></html>';

  var userInterface=HtmlService.createHtmlOutputFromFile('htmlss').setWidth(1000).setHeight(450);
  userInterface.append(s);
  SpreadsheetApp.getUi().showModelessDialog(userInterface, 'Spreadsheet Data for ' + ss.getName() + ' Sheet: ' + sht.getName());
}

function updateSpreadsheet(i,j,value)
{
  var ss=SpreadsheetApp.getActiveSpreadsheet();
  var sht=ss.getActiveSheet();
  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;
}

The htmlss.html file:

<!DOCTYPE html>
<html>
  <head>
    <script src="//ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js"></script>
    <script>
    $(function() {

    });
    function updateSS(i,j)
    {
      var str='#txt' + String(i) + String(j);
      var value=$(str).val();
      $(str).css('background-color','#ffff00');
      google.script.run
         .withSuccessHandler(successHandler)
         .updateSpreadsheet(i,j,value)
    }
    function successHandler(data)
    {
      //$('#success').text(data.message);
      $('#txt' + data.ridx + data.cidx).css('background-color','#ffffff');
    }
    console.log('My Code');
    </script>
    <style>
      th{text-align:left}
    </style>
  </head>
  <body>
  <div id="success"></div>

embedding interactive google spreadsheets api

1 Answers