google-apps-script - 如何从侧边栏填充特定列?
问题描述
我创建了一个侧边栏,用我的电子表格中的数据填充从“A”列到“G”列的列。但问题是我需要填充特定的列。我需要数据来开始填充从“E”到“J”的列和 1 个单独的列“L”。这些列用灰色着色。
如何指定这些列?
我的功能是:
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]);
}
我的 HTML 是:
<!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>
解决方案
这个例子有一个文本框用于保存 a1Notation 中的选择范围和一个选择按钮来选择活动范围。它有一个文本框,用于输入单词文本,单词之间有空格,用作分隔符,还有一个按钮,用于将文本的单词写入所选范围的单元格。
GS代码:
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);
}
动画:
推荐阅读
- java - 将图像转换为二维数组仅获得左上象限
- php - laravel 显示 404 错误上传到我的域
- opengl - opengl 骨骼/关节动画公式
- python - Python u-Law (MULAW) 波解压为原始波信号
- javascript - 给定一个时区字符串,我如何在 JS 中将日期对象设置为该 TZ
- javascript - 运行多核机器的 express js 应用程序中的全局对象最佳实践
- dart - 将带参数的函数传递给 VoidCallback
- eclipse - apache tomcat 9.x 不适用于 Eclipse 和 Java 10.0.1
- c# - 无法在 .net core 2.1 中读取 HttpContext 正文
- excel - 用于复制工作表的简单 VBA 代码导致“Microsoft Excel 已停止工作”