CODEWITHSUNDEEP
javascripthtmlgoogle-apps-scriptgoogle-sheets
Timogavk
Timogavk

Reputation: 869

How to set cell value from Google sheet to radio button in HTML form and update the cell after form submission?

I have a Google sheet with custom HTML form.

<html>
  <head>
    <base target="_top">
    <link rel="stylesheet" href="https://ssl.gstatic.com/docs/script/css/add-ons1.css">
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js"></script>    
    
  </head>
  <body>
    <form id="myform">
      <div class="form-group" >
        <label for="target_row">Row</label>
        <input type="text" class="form-control" name="target_row" id="target_row" value="<?= target_row?>">        
      </div>      
      <div class="form-group" style="display: flex; margin: 10px;">
        <div>
            <label for="amount_input">Сумма (руб.)</label>
            <input type="number" class="form-control" name="amount_input" id="amount_input" placeholder="00000" value="<?= amount_input?>" required>        
        </div>
        <div id="operation_name" style="margin: 10px;">
            <input type="radio" id="revenue" name="revenue" value="Доход">
            <label for="revenue">Доход</label><br>
            <input type="radio" id="cost" name="cost" value="Расход">
            <label for="cost">Расход</label><br>
        </div>
        </div>     
        <div class="block">
            <button type="submit" class="action">Подтвердить</button>
        </div>
        </form>
        <script>     
        document.querySelector("#myform").addEventListener("submit", 
        function(e)
        {
        e.preventDefault();    
        console.log(this)
        google.script.run.withSuccessHandler(()=> google.script.host.close()).editItem(this);
        }
        );
    </script>
  </body>
</html>

I update template with values from the sheet and run form with .showModalDialog

  function editCell(){
    let ss = SpreadsheetApp.getActiveSpreadsheet();
    let sh = ss.getSheetByName('SHEET_NAME')    
    let ui = SpreadsheetApp.getUi();     
    let editRange = sh.getActiveRange()
    let row = editRange.getRow(); // ---> target_row            
    let amount = sh.getRange(row, 5).getValue(); //---> amount_input
    let operation = sh.getRange(row, 6).getValue() //---> operation_name
    let html = HtmlService.createTemplateFromFile('inputform');            
    html.target_row = row;    
    html.amount_input = amount;
    html.operation_name = operation;
    let output = HtmlService.createHtmlOutput(html.evaluate()).setHeight(400).setWidth(300);
    ui.showModalDialog(output, 'NEW') 
  }

Then submit form with new values and trying to it set new values into the sheet

function editItem(form_data){
    const ss = SpreadsheetApp.getActiveSpreadsheet();
    const sheet = ss.getSheetByName('SHEET_NAME');
    let targetRow = form_data.target_row;      
    let amount = form_data.amount_input;  
    let operation = form_data.operation_name;        
    let targetRange = sheet.getRange(targetRow, 2, 1, 2);      
    targetRange.setValues([[ amount, operation]]);   
  }

I'm getting correct value in amount and nothing in operation. Where I'm wrong and how to fix it?

Upvotes: 0

Views: 101

Answers (1)

Jescanellas
Jescanellas

Reputation: 2608

In the HTML file, operation_name and amount_input are in different Classes. The easiest solution is to set the id operation_name as a Class too:

<div class="operation_name" style="margin: 10px;">
            <input type="radio" id="revenue" name="revenue" value="Доход">
            <label for="revenue">Доход</label><br>
            <input type="radio" id="cost" name="cost" value="Расход">
            <label for="cost">Расход</label><br>
        </div>

Then form_data returns an object:

{ amount_input: '3',
  cost: 'Расход',
  target_row: '1',
  revenue: 'Доход' }

So the code at code.gs needs to be:

let amount = form_data.amount_input;  
let cost = form_data.cost;
let revenue = form_data.revenue

Hope this helps

Upvotes: 1

Related Questions