Google Sheets HTML Sidebar Select Multiple Options with Comma-separated Output-CodePudding

I have a Google Sheets job tracker with an HTML sidebar interface for adding new jobs. The sidebar has some dropdowns that pull options from a different sheet.

For the "Upcharge" dropdown, I'd like to be able to select multiple options by holding Control and then have those selections separated by commas on output.

This is my HTML file:

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
  </head>

  <body>        
 
  <b>Enter the details for your new job and click the <i><font color=#c5f27b>Save New Job</font></i> button.</b><br><br><br>

<table>
  <tbody>
  
    <tr>
      <td><b>Upcharge:</b></td>
      <td><select id="Upcharge">
    <? for (let i in upchargeDPDWN) { ?>
      <option value="<?=upchargeDPDWN[i]?>"><?=upchargeDPDWN[i]?></option>
    <? } ?>
    </select></td>
    </tr>
    
    <tr>
      <td><b>Client:</b></td>
      <td><select id="Client">
    <? for (let i in clientDPDWN) { ?>
      <option value="<?=clientDPDWN[i]?>"><?=clientDPDWN[i]?></option>
    <? } ?>
    </select></td>
    </tr>
    
    <tr>
      <td><b>Description:</b>&nbsp;&nbsp;</td>
      <td><input type="text" id="Description" size="22"><br></td>
    </tr>
  </tbody>
</table><br>
    
    <!-- The user clicks this button to add the new job. -->
    <center><input type="button" onclick='saveNew()' value="Save New Job"></center><br><br><br>
    
<script>


  function saveNew(){
   //Get the value of the input fields 
   var upcharge = document.getElementById("Upcharge").value
   var client = document.getElementById("Client").value
   var description = document.getElementById("Description").value
   
   //Log input values in the web browser console
   console.log(upcharge, client, description)

   //Send values as arugments to the server side function
   google.script.run.applyNew(upcharge, client, description)
  }
  
</script>
    
</body>
</html>

This is my code to add the new job to the first empty row of the Job Log sheet:

function applyNew(upcharge, client, description) {
  var ss = SpreadsheetApp.getActive()
  var sheetLog = ss.getSheetByName("Job Log");
  
  var sheetData = ss.getSheetByName("Data");
  var rowCountData = sheetData.getMaxRows();
  var columnToCheckData = sheetData.getRange("J:J").getValues();
  var lastRowData = getLastRowSpecial(columnToCheckData);
  if (lastRowData   1 == rowCountData) {
    sheetData.insertRowsAfter(lastRowData, 100);
   }
  
  // Get number of rows
  var rowCount = sheetLog.getMaxRows();

  //Select the column we will check for the first blank cell
  var columnToCheck = sheetLog.getRange("B:B").getValues();
  
  // Get the last row based on the data range of a single column.
  var lastRow = getLastRowSpecial(columnToCheck);
  
  // If the first empty row is the last row, insert a row before it
  // (This is to ensure the data ranges for bar charts on the Dashboard sheet remain dynamic.)
  if (lastRow   1 == rowCount) {
    sheetLog.insertRowAfter(lastRow);
   }
      
  var selectedRow = lastRow   1;

  //Apply upcharge to Column D = Column 4
  sheetLog.getRange(selectedRow, 4).setValue(upcharge);
  
  //Apply client to Column L = Column 12
  sheetLog.getRange(selectedRow, 12).setValue(client);
  
  //Apply description to Column N = Column 14
  sheetLog.getRange(selectedRow, 14).setValue(description);
  
  sheetLog.insertRowAfter(selectedRow);  
};

Can someone please help me update this to allow for multiple options to be selected? All I can find via Google is scripts for selecting multiple items in data validation. Please let me know if any additional information is needed. Thanks in advance!

CodePudding user response：

Here is a simple example of getting multiple options from a select element.

HTML_Test.html

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
  </head>
  <body>
    <select id="Upcharge" multiple size="4">
      <option value="option1">Option 1</option>
      <option value="option2">Option 2</option>
      <option value="option3">Option 3</option>
      <option value="option4">Option 4</option>
      <option value="option5">Option 5</option>
      <option value="option6">Option 6</option>
      <option value="option7">Option 7</option>
      <option value="option8">Option 8</option>
      <option value="option9">Option 9</option>
      <option value="option10">Option 10</option>
    </select>
    <br>
    <input type="button" onclick='saveNew()' value="Save New Job">
    <?!= include('JS_Test'); ?>    
  </body>
</html>

JS_Test.html

<script>
  function saveNew() {
    try {
      let select = document.getElementById("Upcharge");
      let options = select.options;
      let selected = [];
      for( let option of options ) {
        if( option.selected ) selected.push(option.value);
      }
      alert(selected.toString());
    }
    catch(err) {
      alert(err);
    }
  }
</script>

Screen shot

References