Reputation: 11

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>

Upvotes: 1

Answers (2)

Yuri Khristich

Reputation: 14537

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.

Upvotes: 0

Cooper

Reputation: 64100

This example has a text box for holding the select range in a1Notation and a select button to select the active range. It has a text box for entering word text with spaces between the words which are used as delimiters and a button for writing the words of the text into the cells of the selected range.

GS Code:

function showSideBarDialog() {
  var html='<html><head>';;
  html+='<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script><link rel="stylesheet" href="//code.jquery.com/ui/1.12.1/themes/base/jquery-ui.css"><script src="https://code.jquery.com/ui/1.12.1/jquery-ui.js"></script>';
  html+='</head><body>';
  html+='<input type="text" id="select" placeholder="Select Range and Press Select" size="40" /><br /><input type="button" value="Select"onClick="selectRange();" />';
  html+='<textarea rows="4" cols="30" id="txt1" placeholder="Enter some word text (spaces required)"></textarea><br /><input type="button" value="Write" onClick="postText();" />';
  html+='<script>function selectRange(){google.script.run.withSuccessHandler(function(r){$("#select").val(r);}).selectRange();}';
  html+='function postText(){google.script.run.postText({text:$("#txt1").val(),range:$("#select").val()});}';
  html+='</script>';
  html+='</body></html>';
  SpreadsheetApp.getUi().showSidebar(HtmlService.createHtmlOutput(html).setTitle('Write to Range'));
}

function selectRange() {
  const ss=SpreadsheetApp.getActive();
  const sh=ss.getActiveSheet();
  const rg=sh.getActiveRange();
  return rg.getA1Notation();
}

function postText(obj) {
  var tA=obj.text.split(' ');
  console.log(obj.text);
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getActiveSheet();
  var rg=sh.getRange(obj.range);
  var vs=rg.getValues();
  var n=0;
  for(var i=0;i<vs.length;i++) {
    for(var j=0;j<vs[i].length;j++) {
      if(n<tA.length) {
        vs[i][j]=tA[n++];
      }else{
        rg.setValues(vs);
        return;
      }
    }
  }
  rg.setValues(vs);
}

Animation:

Upvotes: 1

How can I populate specific columns from sidebar?

Answers (2)

Related Questions