Home > Mobile >  Changing Google Sheets Cell Value from JavaScript Dropdown Menu
Changing Google Sheets Cell Value from JavaScript Dropdown Menu

Time:02-25

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");
}

CodePudding user response:

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

sample

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");
}
  • Related