CODEWITHSUNDEEP
javascripthtmlgoogle-apps-scriptgoogle-sheets
Timogavk
Timogavk

Reputation: 889

How to update form from elements from Google Sheet?

I have a custom HTML form in my Google sheet.

<!DOCTYPE html>
<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">        
      </div>
      <div class="form-group">
        <label for="category_name">Статья</label>
        <input type="text" class="form-control" name="category_name" id="category_name" placeholder="Статья">        
      </div>
      <div class="form-group">
        <label for="trans_date">Дата транзакции</label>
        <input type="date" class="form-control" name="trans_date" id="trans_date" placeholder="Дата транзакции">
      </div>
      <div class="form-group">
        <label for="expl_name">Описание</label>
        <input type="text" class="form-control" name="expl_name" id="expl_name" placeholder="Описание">
      </div>
      <div class="form-group">
        <label for="amount_input">Сумма (руб.)</label>
        <input type="number" class="form-control" name="amount_input" id="amount_input" placeholder="00000">
      </div>
      <div class="form-check">
       <input type="checkbox" class="form-check-input" id="new_row">
       <label class="form-check-label" name="new_row" for="new_row">Добавить, как новую запись</label>
     </div>
     <button type="submit" class="btn btn-primary">Подтвердить</button>
     </form>
     <script>     
      document.querySelector("#myform").addEventListener("submit", 
      function(e)
      {
      e.preventDefault();    //stop form from submitting
      console.log(this)
      google.script.run.withSuccessHandler(()=> google.script.host.close()).addNewItem(this);
      }
      );
    </script>
  </body>
</html>

I call this form from function with installed onEdit trigger. I want to fill form fields with expected values from editCell(e) 

function editCell(e){
  let sh = e.range.getSheet();
  if(sh.getName() !== "Лист1")return;
  let row = e.range.getRow(); // ---> target_row
  let category = sh.getRange(row, 2).getValue() //---> category_name
  let transDate = sh.getRange(row, 3).getValue() //---> trans_date
  let expl = sh.getRange(row, 4).getValue() //---> expl_name
  let amount = sh.getRange(row, 5).getValue() //---> amount_input
  openCustomForm();
}

function openCustomForm(){
  let html = HtmlService.createTemplateFromFile('form').evaluate();
  SpreadsheetApp.getUi().showModalDialog(html, 'Новое значение')
}

Then update form manually and set new values to the same cells in the sheet or add new row with new values

function addNewItem(form_data){
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName('Лист1');
  let targetRow = form_data.target_row;  
  let category = form_data.category_name;
  let transDate = form_data.trans_date;
  let expl = form_data.expl_name;
  let amount = form_data.amount_input;
  let newRow = form_data.new_row;
  if (newRow === "true"){
    ss.insertRowBefore(5);
    targetRow = 5;    
    }
  let targetRange = sheet.getRange(targetRow, 2, 1, 4);  
  targetRange.setValue(category, transDate, expl, amount);
}

How can I update the form with new values and get correct result?

Upvotes: 0

Views: 83

Answers (1)

Neven Subotic
Neven Subotic

Reputation: 1429

As far as I understand the question, you want to input the values from the Spreadsheet into the Form.

In that case you can pass values from the back-end to the front-end by adding the key to the template object. See the example below:

// A template which evaluates to whatever is bound to 'foo'.
var template = HtmlService.createTemplate('<?= foo ?>');
template.foo = 'Hello World!';
Logger.log(template.evaluate().getContent());  // will log 'Hello World!'

So on your front-end, you just add the values to the input like so:

<input type="text" value="<?= foo ?>">

Upvotes: 1

Related Questions