HTML Spreadsheet

This script allows you to edit all cells in a spreadsheets data range from an HTML dialog.

function onOpen()//You can use this to create a menu in your spreadsheet to run the dialog.

{

SpreadsheetApp.getUi().createMenu('HTML Spreadsheet')

.addItem('Run Spreadsheet in Dialog', 'htmlSpreadsheet')

.addToUi();

}

This is a simple example of a web app that puts an editable spreadsheet on an HTML Page. Publish as a webapp.

Code.gs:

var SSID='SpreadsheetID';

var sheetName='Sheet Name';

function htmlSpreadsheet(mode)

{

var mode=(typeof(mode)!='undefined')?mode:'dialog';

var br='<br />';

var s='';

var hdrRows=1;

var ss=SpreadsheetApp.openById(SSID);

var sht=ss.getSheetByName(sheetName);

var rng=sht.getDataRange();

var rngA=rng.getValues();

s+='<table>';

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="10" onChange="updateSS(' + i + ',' + j + ');" />' + '</th>';

}

else

{

s+='<td id="cell' + i + j + '">' + '<input id="txt' + i + j + '" type="text" value="' + rngA[i][j] + '" size="10" onChange="updateSS(' + i + ',' + j + ');" />' + '</th>';

}

s+='</tr>';

}

s+='</table>';

//s+='<div id="success"></div>';

s+='</body></html>';

switch (mode)

{

case 'dialog':

var userInterface=HtmlService.createHtmlOutputFromFile('htmlss').setWidth(1000).setHeight(450);

userInterface.append(s);

SpreadsheetApp.getUi().showModelessDialog(userInterface, 'Spreadsheet Data for ' + ss.getName() + ' Sheet: ' + sht.getName());

break;

case 'web':

var userInterface=HtmlService.createHtmlOutputFromFile('htmlss').setWidth(1000).setHeight(450);

return userInterface.append(s).setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL);

}

function updateSpreadsheet(i,j,value)

{

var ss=SpreadsheetApp.openById(SSID);

var sht=ss.getSheetByName(sheetName);

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 doGet()

{

var output=htmlSpreadsheet('web');//You can also deploy it as a webapp.

return output;

}

htmlss.html:

<!DOCTYPE html>

<html>

<head>

$(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>