I am Just wondering is it possible to create editable Html table from Google sheet data, Then Provide dropdown values to each cell but keep the initial value as the current cell value, So if someone changes value using drop down then that change also gets posted back to Google sheet.
This solution I found Here by @cooper in one of the post and it is working fine but my requirement is to add dropdown to each cell in addition to this. Hope It must not be that hard. All suggestions are welcome but please provide in a very simple language as I am a beginner in this App Script World.
function getOrders() {
var ss=SpreadsheetApp.getActive();
var osh=ss.getSheetByName("1");
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-15){
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("1");
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(e){
var userInterface=HtmlService.createHtmlOutputFromFile('orders');
//SpreadsheetApp.getUi().showModelessDialog(userInterface, "OrderCounts");
return userInterface;
}
index.html file
!DOCTYPE html\>
$(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 class="dropdown">
<button onclick="myFunction()" class="dropbtn"><div id="orders"></div></button>
<div id="myDropdown" class="dropdown-content">
</div>
</div>
</body>
</html>
Drop down Value Range Script
function dropdown()
{
var sheet = SpreadsheetApp.openById("1RwxdRdhwzSs8KbKk3_eC22J9EpuK-VgoUbLKlLttm7A").getSheetByName('1');
var lastRow = sheet.getLastRow();
var myRange = sheet.getRange("N2:N" + lastRow);
var data = myRange.getValues();
Logger.log("Data = " + data);
return data;
};
Updated Html Java Script Function
</style>
<script>
function () {
google.script.run.withSuccessHandler(
function (selectList) {
var select = document.getElementById("dropdown");
for( var i=0; i<selectList.length; i++ ) {
var option = document.createElement("option");
option.text = selectList[i][0];
select.add(option);
}
}
).dropdown();
}