Search code examples
google-apps-scriptgoogle-sheetssidebar

How can I populate specific columns from sidebar?

I've created a sidebar that populates columns from column "A" to column "G" with data in my spreadsheet. But the problem is that I need specific columns to be populated. I need data to start populating column from "E" to "J" and 1 separate column "L". These columns are colored with grey color. screenshot

How do I specify those columns?

My function is:

function appendData(data){
var ws = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Аркуш32");
ws.appendRow([data.product,data.clientname,data.partnername,data.adress,data.phone,data.email,data.source]);     
     }

My HTML is:

<!DOCTYPE HTML>
<html>
<head>
  <!--Import Google Icon Font-->
  <link href="https://fonts.googleapis.com/icon?family=Material+Icons" rel="stylesheet">
  <!-- Compiled and minified CSS -->
  <link rel="stylesheet" 
href="https://cdnjs.cloudflare.com/ajax/libs/materialize/1.0.0/css/materialize.min.css">

  <!--Let browser know website is optimized for mobile-->
  <meta name="viewport" content="width=device-width, initial-scale=1.0"/>
</head>

<body>

<div class="container">
  <div class="row">
    <div class="input-field col s12">
      <i class="material-icons prefix">local_mall</i>
      <input id="product" type="text" class="validate">
      <label for="product">Товар</label>
    </div>
    <div class="input-field col s12">
      <i class="material-icons prefix">account_circle</i>
      <input id="clientname" type="text" class="validate">
      <label for="clientname">ПІП клієнта</label>
    </div>
    <div class="input-field col s12">
      <i class="material-icons prefix">assignment_ind</i>
      <input id="partnername" type="text" class="validate">
      <label for="partnername">Назва партнера</label>
    </div>
      <div class="input-field col s12">
      <i class="material-icons prefix">home</i>
      <input id="adress" type="text" class="validate">
      <label for="adress">Адреса</label>
    </div>
    <div class="input-field col s12">
      <i class="material-icons prefix">phone</i>
      <input id="phone" type="text" class="validate">
      <label for="phone">Номер телефону</label>
    </div>
    <div class="input-field col s12">
      <i class="material-icons prefix">mail</i>
      <input id="email" type="text" class="validate">
      <label for="email">Емейл</label>
    </div>
    <div class="input-field col s12">
      <i class="material-icons prefix">hearing</i>
      <input id="source" type="text" class="validate">
      <label for="source">Джерело</label>
    </div>
      <div class="input-field col s12">
       <button class="btn waves-effect waves-light" id="btn">Додати
        <i class="material-icons right">send</i>
      </button>
    </div>
  </div> <!-- end row -->
    
</div>

<!-- Compiled and minified JavaScript -->
<script 
src="https://cdnjs.cloudflare.com/ajax/libs/materialize/1.0.0/js/materialize.min.js"> 
</script>
<script>
   var productBox = document.getElementById("product");
   var clientnameBox = document.getElementById("clientname");
   var partnernameBox = document.getElementById("partnername");
   var adressBox = document.getElementById("adress");
   var phoneBox = document.getElementById("phone");
   var emailBox = document.getElementById("email");
   var sourceBox = document.getElementById("source");
   
   document.getElementById("btn").addEventListener("click",addRecord);
   function addRecord(){
   var data = {
      product: productBox.value,
      clientname: clientnameBox.value,
      partnername: partnernameBox.value,
      adress: adressBox.value,
      phone: phoneBox.value,
      email: emailBox.value,
      source: sourceBox.value
   };
   
   google.script.run.appendData(data);
      productBox.value = "";
      clientnameBox.value = "";
      partnernameBox.value = "";
      adressBox.value = "";
      phoneBox.value = "";
      emailBox.value = "";
      sourceBox.value = "";
      
   }
     
</script> 
</body>
</html>
Solution

  • The question very old, but anyway...

    If you don't mind to erase the cells A..D and K, all you need is to add several empty elements '' into one line this way:

    ws.appendRow(['','','','',data.product,data.clientname,data.partnername,data.adress,data.phone,data.email,'',data.source]);

    If you have some data in the cells A..D and K that you want to keep, it will need a little bit more complicated solution.