I am trying to change the value of a specific cell in Google Sheets based on a users selection from a dropdown menu. Also, might be worth mentioning that this dropdown menu is inside an HTML table generated from Sheets data. Everything works except for this one little thing. Help would be greatly appreciated!
HTML Snippet:
<select id="status" name="status">
<option value="current" id="current"><?= tableData[i][j] ?></option>
<option value="submitted" id="submitted">Submitted</option>
</select>
<script>
let select = document.querySelector('#status');
select.addEventListener('change', update(i, j));
</script>
Code.gs function:
var rfi= SpreadsheetApp.openById("REDACTED");
var sheet = rfi.getSheetByName('Sheet1');
function update(i, j) {
sheet.getRange(i, j).setValue("In Review");
}
If you want to pass coordinates, try
in html
<option value="<?=(i+1)+'|'+(j+1)?>" id="current"><?= tableData[i][j] ?></option>
in gs
function update(coord) {
sheet.getRange(coord.split('|')[0], coord.split('|')[1]).setValue("In Review");
}
will be finalized after your spreadsheet is published
For instance, this works and shows how to transfer coordinates
html
<!DOCTYPE html>
<html>
<head>
<script src="//ajax.googleapis.com/ajax/libs/jquery/2.1.4/jquery.min.js"></script>
</head>
<body>
<script>
<? var data = listOfItems(); ?>
</script>
<select id="choice" name="choice">
<option value="" disabled selected >Choose ...</option>
<? for (var i = 0; i < data.length; i++) { ?>
<? for (var j = 0; j < data[0].length; j++) { ?>
<option value="<?=(i+1)+'|'+(j+1)?>" ><?=data[i][j]?></option>
<? } ?>
<? } ?>
</select>
<script>
$('#choice').change(function(){
google.script.run.update ($(this).val());
});
</script>
</body>
gs
function load() {
var ui = HtmlService.createTemplateFromFile('load')
.evaluate()
.setSandboxMode(HtmlService.SandboxMode.IFRAME)
.setTitle("test ...");
SpreadsheetApp.getUi().showSidebar(ui);
}
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('mySheet')
function listOfItems() {
return sheet.getDataRange().getValues()
};
function update(coord){
sheet.getRange(coord.split('|')[0], coord.split('|')[1]).setValue("In Review");
}